No it is not required to do a fellowship. Most people who do this work in the real world are not fellowship trained. Many of these fellowships go unfilled and most people to who complete C-L fellowships are IMGs looking to kill a year or train at a more prestigious institution than where they did residency. Many academic centers will prefer people who are board certified in consultation-liaison psychiatry but as that pool is small it is not absolutely necessary. community hospitals are unlikely to care about whether you are certified/fellowship trained in this area. If you think you might want to be a C-L fellowship director or director of a C-L Psychiatry division at an academic medical center, it is probably a good idea to do the fellowship. If not, then it may not matter. If you do a fellowship, make sure it is somewhere good where you get broad exposure to both inpatient and outpatient consults including transplantation psychiatry, psycho-oncology, neuropsychiatry, HIV psychiatry, perinatal psychiatry, integrated care, possibly smaller fields like cardiac psychiatry etc.
positions that are 100% inpatient consult are unusual and often lower paid. Much of the work of CL services is not billable. On average about 40% of the work is compensated by insurers; the remainder is not. This can vary from institution to institution. At my institution, only about 20% of our work on C-L is reimbursed on what we bill. What this means is you should only accept these jobs on a salaried basis (or if an independent contractor, for an hourly rate or per consult seen) and never based on collections or RVUs. Even if you were paid on RVUs billed and not collected, you would still lose out, as we often spend longer seeing fewer patients, providing liaison services, educating nursing staff, discussing cases with risk management etc, so the volume is lower than in inpatient or outpatient. Most commonly, people combine C-L with outpatient psychiatry, outpatient C-L, inpatient, research, teaching etc.