I've seen similar with regards to Hippiedoc's posts. A community hospital will typically (from what I've seen) have at least a psychiatrist for consult service.
I don't know what type of number a hospital may want before they consider it's effective or not to have a psychiatrist on board.
I've never seen any program specifically needing a CL psychiatrist for that service. Having a fellowship in this field is definitely going to advance your knowledge of this area, and it is a very big area. All medical disorders can have some psychiatric impact. There can sometimes be very complex interplay between a mental and physical disorder. That said, in terms of finding a job, in general (unless you're going for academia), I don't see it being a big opportunity maker.
A possibly irrelevant personal note: From my own experience, most psychiatric consults tend to be lame ducks. The patient could be a disgruntled (but not mentally ill) patient that the nursing staff didn't want to deal with, so they call up the attending and ask for a psychiatry consult, then write "psychotic" or "depressed", when in fact there was no reason to believe the patient was either.
As a resident, this was frustrating because I had to spend at least an hour (sometimes 5) trying to figure out why the consult was called in the first place, calling up the attending ("I only ordered it because the nurse asked me.", asking the nurse on duty ("I'm not the one who ordered it, I don't know why the 3rd shift nurse ordered it. Mr. X has been fine all day), and then talking to the patient ("Doctor, I'm not depressed. The only thing that happened last night was I told the nurse I miss my cat.")
The attending in the program didn't seem to care because all he or she had to do was briefly read my note and sign it. They didn't have to shlept for 1-5 hours trying to play detective over an issue where it really was a nurse spot diagnosing depression without using any DSM critiera--because someone said they missed their cat.
At the state hospital where I work, we typically ask for consults from other psychiatrists because we have very dangerous patients. If a patient murdered 3 people, then ritualistically burned their bodies, you will likely want more than one doctor confirming the patient is safe for discharge. We also have several patients who are treatment resistant to Clozaril. When that happens we sometimes consult Henry Nasrallah, M.D. (yes, THE Henry Nasrallah) to check out the case after we've tried everything we could think of short of ECT.