What are you really going to gain by doing an addiction psychiatry fellowship? Any decent psychiatry residency training program will teach you principles of management of intoxication/withdrawal as well as contingency management for buprenorphine. You can get practice using acamprosate/naltrexone and some of the off label things during your clinic year (not that any are particularly effective or difficult to use). And I'm sure (depending on how your program allocates funding) you could use elective time to do work in a methadone clinic. Most of the psychotherapies are a standard part of psychiatry residency anyway. Plus, most addiction psych fellowships are VA based, and addiction treatment in the VA is very different than addiction treatment in private practice or in community based settings (for a lot of reasons).
Being board certified in addiction psychiatry really just means paying more money so you can say you are board certified (and get some AAAP discounts). ASAM is the more powerful organization and offers more hospital based fellowships. But again, you probably won't learn much more in an ABAM fellowship that you couldn't realistically learn in an academic setting. Want to see DTs and learn how to dose precedex/phenobarb? Do a MICU elective. Want to learn how to use the ASAM criteria? This can be learned over the weekend in an online course.
Since you don't want to do research, by doing a fellowship you are basically losing another year of attending level income that is likely not going to expand your job/income opportunities (particularly in oversaturated areas in the northeast). There are plenty of addiction psychiatry jobs that are more than happy to hire a psychiatrist (ie, without an addiction fellowship). The only worthwhile, clinical fellowships in psychiatry are child (because competency in clinical care of child psychiatric patients actually requires 2 years of additional training) and forensics (more for political reasons than anything else). If you wanted to do addiction research, it would be a better use of time to do a T32 research fellowship with a big name researcher.
All that said, people tend to flock to Yale (most established, have VA and non VA options as well as addiction medicine), MGH/McClean, NYU with Marc Galanter (also have an ABAM fellowship), and Columbia with Herb Kleber. MUSC has some superstars (Kathleen Brady, Ray Anton), and Hugh Myrick is one of their rising stars, but the fellowship is largely in bred (ie, everyone comes from MUSC)