SQL's problems can be seen by comparing the true relational database theory with what SQL can do.
In particular, relational database theory does not require tables to have homogeneous table rows that consist entirely of scalar values. That was an efficiency optimization introduced back in the 70s, for perfectly sensible reasons at the time. But writing that into the language at such a fundamental level bends the whole language around it in a really unpleasant way compared to what it could be. For a similar example, consider the difference between a language that has no first-class function references, vs. one that has them added in. The first language may do everything you need, but you'll be bending a lot of code around that problem.
Of course, a variety of extensions have been bodged on to the sides of SQL over the years to deal with the resulting problems, and generally in a good engine like Postgres or something you can do almost anything you want. I'd also say that if you can understand what I mean when I say "Postgres is actually a much better database than understanding SQL would lead you to believe", you're on the track of what I mean.
Again, I'm not saying SQL is bad, I'm saying its success is holding back the things that could be better.
Homogenous values are not required, but having atomic/scalars as a fundamental unit of a relation is a pretty core concept in database normalization theory.
Or are you saying database normalization is a performance optimization and we should judge RDBMS' by their support for relational algebra/calculus and not normalization theory?
I agree SQL is not perfect but I don't know of many examples of alternative languages (that are not completely proprietary).
It honestly would be helpful to see some alternatives that you think are actually good (as the ones I have seen in the past are fairly awful). The homogeneous rows point is valid but I'm not entirely sure even SQL requires that (as you can have non scalar data types as columns in many databases... although I suppose that is to your point about postgres).
And yeah I mainly only use Postgres so I'm biased :)
In particular, relational database theory does not require tables to have homogeneous table rows that consist entirely of scalar values. That was an efficiency optimization introduced back in the 70s, for perfectly sensible reasons at the time. But writing that into the language at such a fundamental level bends the whole language around it in a really unpleasant way compared to what it could be. For a similar example, consider the difference between a language that has no first-class function references, vs. one that has them added in. The first language may do everything you need, but you'll be bending a lot of code around that problem.
Of course, a variety of extensions have been bodged on to the sides of SQL over the years to deal with the resulting problems, and generally in a good engine like Postgres or something you can do almost anything you want. I'd also say that if you can understand what I mean when I say "Postgres is actually a much better database than understanding SQL would lead you to believe", you're on the track of what I mean.
Again, I'm not saying SQL is bad, I'm saying its success is holding back the things that could be better.