I really enjoyed this thread and the input many of you wise folks have provided.
I'll provide a naive, bushy-tailed intern perspective that perhaps might take you back to what got you into psych or at least a narrative born from inexperience.
Thinking back on my experience as a medical student and when I first did my psychiatry rotation, a lot has changed. I think one of the coolest aspects of our job is to confront things that are very frequently stigmatized and "too uncomfortable to talk about" head-on. I remember being uncomfortable asking someone about suicide, trauma-related stuff, and psychosis. It was weird and felt intrusive. Now, it's something I do without a passing thought. We deal with issues that people are often uncomfortable talking about with others - hell, the patients themselves may not even be able to confront these things - and provide an outlet to begin processing and dealing with these issues, whether that be with psychopharm, psychotherapy, or other interventions. No, this isn't something I think about on a daily basis, but in retrospect with a sufficiently global perspective, it is kind of a cool thing. There is nothing special about psychiatrists being able to ask these questions - anyone can do it - but it forms such an integral part of our daily practice that we actually develop comfort with these issues to the point that we can talk about them tacitly and in an environment that provides a sufficient degree of comfort to the patient while perhaps providing the treatment that can enable those folks to begin dealing with those very painful things. Good luck getting a surgeon to have an in-depth discussion on suicidality, why a patient's cheating wife launched him into a florid period of substance use, or any other "cliche" story we've probably seen a hundred times. Further, good luck to the surgeon should they attempt to have a meaningful discussion on these issues to help a patient develop insight into what's going on, convince them to accept treatment, or any other numerous basic tasks that we perform.
On consults, even the frustrating ones can be interesting or helpful to the consulting service, especially, as others have said, if you're able to decode the actual issue(s) behind a useless consult question like "homeless" or "tearful." No, you may not be able to solve those problems when the patient is being discharged the next day, but in your discussion of why a patient is homeless you may happen upon some substantial issues that have never been discussed before and open the door for treatment that up to that point hasn't been opened. I like the more medically-intensive, psychopharm heavy cases too, but even in the absence of those skills I think there are still things we are able to offer other teams that we probably take for granted without a passing thought.
I tire just as much as anyone of the patient who is on his/her 20th admission for substance-induced issues, but I've found that digging deep with many of these patients can bring to the surface some interesting and even actionable nuggets, sometimes seemingly unrelated to the presenting issue at hand. I think part of the practice is getting so much exposure to people that we can somewhat intuitively detect weirdness and know when to start probing to understand things that, prima facie, may not appear to require further investigation. Just as a medical student may not pick up on small, seemingly insignificant things in the history that have substantial importance to psychiatric management, so too with our consulting services.
I'm not entirely optimistic about the field. There are certainly plenty of negative things that go on with our daily practice. There are things about it that I can't stand. Sometimes I get bored. But just as cardiologists are experts of the heart, dermatologists experts of the skin, etc. - and, by the way, they very likely get bored with pacemaker placements, stent deployments, shave biopsies, and all of the other technical procedures we've developed - we also have our field of expertise. I think we may more liable to take those skills for granted because compared to, say, the more technical solutions to other medical problems, they seem pretty basic. But I think it's important to not lose sight of the fact that there are unique skills that we provide to both our patients and services that ask for our input. Sure, a lot of that is psychopharmacology, a lot of that is related to diagnosis, but I think just as much - if not more - is simply because we're comfortable dealing with issues that may make other folks, including physicians, extremely uncomfortable. That comfort allows us to get to the point of being able to even offer psychopharmacological solutions to problems. It allows us to attempt to separate the wheat from chaff - or at least make an attempt at doing so - so that we can try and prioritize an often-times long list of concurrent issues that many of our patients come with. That experience comes from years of seeing the spectrum of human behavior, the spectrum of determinants that play a role in that behavior, and attempting to identify slight variations on themes we've seen an innumerable number of times before.
These are soft skills, sure, but that doesn't in and of itself reduce their value, utility to our practice, or importance when doing something even as "basic" as routine depression management. It also doesn't make us any less experts in our own little world. The medical treatments we offer may be fairly straightforward (at least to those that demean the field), the diagnostic process apparently subjective and uncomplicated ("hold on, let me check the DSM and make sure I have a sufficient number of boxes checked"), and the task of talking with someone "so easy a caveman can do it," and yet plenty of people are so incompetent at these things that it's laughable.
Now those things may not be suited to your interests or what kind of work you envision doing. That's a completely different question and issue. But I do think it's important to recognize that we do offer expertise, that expertise takes significant time and experience to develop, and while it may not be "respected," it isn't, ergo, useless or unimportant.