| When you dig into it, the powerful idea here is the relational algebra, and its equivalence to a first-orderish logic.
I put up with SQL so I can use relations, and I love Postgres because it has the least bad SQL (by a mile!)
But SQL is a terrible, no good, very bad language.
I don’t really understand why folks who love the relational model aren’t perpetually up in arms about SQL being their only option. Much better query languages are known and well studied.
I’d love to see Postgres offer say Datalog. But until that Halcyon day, we could at least aggressively extend SQL to make it less awful.
This is a strange post. Why is SQL bad and how do your reconcile that with managing 99%+ of all data? It's so bad that we have systems that plug into sql to query data outside of tables like Athena or Excel.
Why are you not using pgadmin4? Yes. Psql as a command line isn't great for humans. It's spectacular for computers though. So we have pgadmin4, which I would take over any other database ui.
Do you not want your views to change with underlying base tables changing? Do a fully specified select. It's better programming anyway. Do you want an api? That seems like a bad idea (i would never implement it) but you also have a postgres socket, flask, and sqlalchemy or psycopg2. It would take a few hours to write your own. Again, please don't do that. You will almost surely lose user information like who decided to delete your client data and your api would likely require user privileges to get passed by token (sso would be a nightmare to authenticate) or simply give root privileges to an api. Both are honestly really bad.
Now if postgres had the ability to do schema change tracking with rollback... now that would be a victory. But there are sort of 3rd party solutions that sort of work some of the time. It's a hard problem and automated sql generation, particularly automated schema migrations, are really hard to build in general and there are specific things that are damn hard to not break.
Thanks, Ben
On Thu, Feb 10, 2022 at 06:25:45PM +0100, Peter J. Holzer wrote:
> On 2022-02-10 18:22:29 +0100, Peter J. Holzer wrote:
> > On 2022-02-09 21:14:39 -0800, Guyren Howe wrote:
> > > • SELECT * - b.a_id from a natural join b
> > > □ let me describe a select list by removing fields from a relation. In
> > > the example, I get all fields in the join of a and b other than the
> > > shared key, which I only get once.
> >
> > Natural join already does this.
> >
> > My use case for such a feature are tables which contain one column (or a
> > small number of columns) which you usually don't want to select: A bytea
> > column or a very wide text column. In a program I don't mind (in fact I
> > prefer) listing all the columns explicitely, but exploring a database
> > interactively with psql typing lots of column names is tedious
> > (especially since autocomplete doesn't work here).
>
> Forgot to add: I think that the syntax would have to be more explicit.
> It's too easy to mix up
> SELECT * - b.a_id FROM ...
> and
> SELECT *, - b.a_id FROM ...
>
> Maybe
> SELECT * EXCEPT b.a_id FROM ...
Yes, this was proposed on hackers a few months ago and a patch was
proposed:
https://www.postgresql.org/message-id/flat/892708.1634233481%40sss.pgh.pa.us#1f17923ad50a1442867162991c54ead9
The last post was from October of 2021 so you can email the author to
ask about its status.
--
Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
![]() |
