I know people think Freud was an idiot. Maybe I shouldn't have been so lazy in my research.
As someone who has struggled with depression and SI since childhood - probably not unlike a lot of folks on this forum - I just remember hearing the Eros vs Thanatos theory in high school and how I related to the desire to live as well as the desire to die.
I've read quotes from public figures who were never suicidal having to deal with the sudden trauma of public humiliation making statements like, "If I had a gun I would've killed myself," only to have that suicidal urge pass.
So, Freud may not have been the best example, but I think there is something to the theory that we have a primal need to survive and a primal fear of death as well as maybe also a subconscious desire to escape life when it becomes too scary or to escape life in general or to escape the pain of clinical depression and other types of mental health problems.
I think the problem people have with Freud is that most of his theories aren't based on actual empirical evidence. This seems to be a genetic problem with psychiatry in general. Most of Freud's assertions were cooked up by his (if I remember accurately) cocaine fried mind.
The point is, like Freud, we can all invent theories to explain things. The issue is that in all other walks of life, except things like religion, we are required to present empirical evidence to back up our ideas. In psychiatry as with Freud his theories are presented as axioms. If you challenge them or find flaws, then they usually have a nice "diagnosis" to explain why you are in denial. Then you've got layers and layers of nonsense to "explain". According to Occam's razor, you don't increase explanatory power by increasing complexity.
Of course the psychiatrist will say that their trade is more of an art than a science. ("Special Pleading" if you are interested in logical fallacies!) But I doubt you would trust an artist to say, create a covid vaccine or perform open heart surgery. I simply will not offer deference or respect to someone who believes that they can avoid the burden of proof.
My theory as to why we have this cognitive dissonance when it comes to wanting to survive while wanting to die is that suicide is an intellectual resolution to suffering. I'm not making any arguments about it being right or wrong. Survival is like a perpetual loop like a piece of code that runs in our instinctive brain. And here's the bit that may take a bit of a leap, but it's based on the way we conceptualise pain.
We want to "survive" pain by making it stop. It may just be that counter intuitively we resolve this by even being prepared to die to "survive" it. We may be the only beings that understand our own mortality so we end up ironically understanding that the solution will result in death. So we end up constrained by the "survival loop".
Well at least that's my sleep deprived brains way of attempting to articulate it. Sorry if it's more jumbled than I perceive it to be!