PostgreSQL and SQL:2023 | Hacker News Reader

PostgreSQL and SQL:2023

peter.eisentraut.org

129 pointsmariuz33 comments

Related thread yesterday:

https://news.ycombinator.com/item?id=36154102

SQL:2023 has been released (iso.org) | 251 points | 118 comments

Since we're here talking about Postgres and SQL standards. Here's my wishlist:

- Support for arrays of foreign keys. This one has been worked on a couple of times but hasn't made it into a release. https://commitfest.postgresql.org/17/1252/ - I think this would be an absolutely phenomenal way of handling sorting, instead of having to use linked lists and CTEs or some type of "order/position" column

- Support for deferring NOT NULL and CHECK constraints to the end of a transaction (just ran into this problem yesterday)

Edit: typos

> Support for arrays of foreign keys.

Standards-wise, the first step would be arrays in general :)

> Support for deferring NOT NULL and CHECK constraints to the end of a transaction

Agreed 100%

Is there a database that supported arrays of foreign keys?

We use it in Palantir, that is a proprietary overlay on top of Spark. It supports this feature reasonably fine.

Of the major databases, only Oracle and PostgreSQL have array extensions and neither support FKs as array elements.

I'm curious how people think this feature should work? ON DELETE SET NULL, ON DELETE CASCADE, something else?

I would love it if the would remove the key from the array.

“Array of foreign keys” has been at the top of my list for so long. Would simplify sooooo much.

I followed the link about arrays of foreign keys but couldn't see what it was saying. Could you briefly summarise for me?

> - Support for deferring NOT NULL and CHECK constraints to the end of a transaction (just ran into this problem yesterday)

I'm curious what the use case of this is?

Deferrable constraints are usually considered for foreign keys, since there you might have to juggle updates to multiple tables and might violate the constraint in the intermediate states. But that doesn't appear to apply in that way to CHECK constraints.

2 tables that reference each other and the foreign key columns both have a NOT NULL constraint.

As a reminder; the main purpose of the SQL standard is for governments and Oracle to negotiate sales contracts. The OSS databases don't need ISO. They can just coordinate using the Internet.

But PostgreSQL is still known for being very compliant with the SQL standard right? At least that’s what I recall hearing.

I use PostgreSQL a bit and I like it. I am not generally concerned about the SQL standard though. The PostgreSQL official docs are good, and you can find a lot of PostgreSQL specific questions and answers on the internet.

But it is my impression that people were saying that it follows the standard pretty closely.

Without too much exaggeration, the real SQL standard is the intersecting behaviours of PostgreSQL and SQLite. If the ISO standard disagrees with those features then that standard is wrong and needs to be corrected. So yes, Postgres does follow the standard closely.

Can you state why this is the case? Are there products that need to use both?

I believe the assertion is that between those two youve accounted for a significant majority of the usage of SQL in the world.

Agreed. But aside from that I could easily see someone using PostgreSQL on their server, and storing data client side in SQLite

PostgreSQL's documented position indicates the standard does matter. Conformance to the standard is described as a "target" rather than PosrtgreSQL's feature set as being "defining". This stands to reason: you can't conform to a target without the target pre-existing and you'd already be conformant if you were setting the standard.

PostgreSQL's current documentation states:

"PostgreSQL development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense." -- https://www.postgresql.org/docs/15/features.html

This basic stance is documented at least as far back as PostgreSQL 7.3:

"PostgreSQL development tends to aim for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. At the time of this writing, ballotting is under way for a new revision of the standard, which, if approved, will eventually become the conformance target for future PostgreSQL development." -- https://www.postgresql.org/docs/7.3/features.html

I can only assume that the person you're responding to is advocating a personal opinion on what should count as the standard setting process for SQL (be that formal or informal). That person's opinion does not appear to be shared by the PostgreSQL team as if they believed that... they'd be conformant by definition and there would be no need to accommodate the standard. If they didn't care about the standard, presumably it wouldn't be listed as an "aim" in any capacity.

from the docs:

"Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170."

[1] https://www.postgresql.org/docs/current/features.html

A bit meta on my part, but I'd recommend referencing the version specific URLs from the PostgreSQL documentation when backing up a quoted claim. Your quote will referenceable now, but anyone seeing it later may see different number or the statement might be omitted from whatever the "current" version is in the future.

So instead of:

https://www.postgresql.org/docs/current/features.html

I'd recommend:

https://www.postgresql.org/docs/15/features.html

Yes.

Though it takes liberties whenever it feels the standard chosen something poorly.

Random examples:

* Standard says FKs are deferred by default. PostgreSQL uses immediate by default.

* Standards says triggers are executed in the order added. PostgreSQL executes them in alphabetical orders.

And even so, Postgres is closer to the spec than Oracle is. (Based on perusing https://modern-sql.com)

Ticking boxes is fun and all, but I like it because it at least gets everyone on the same page for features, functions, etc across databases.

I think I'd rather not have much of a difference between Oracle and Postgresql, if only because it would make it given "certain people" more arguments against switching from Oracle to Postgresql.

> rather not have much of a difference between Oracle and Postgresql

That will make Postgresql blatantly non-standard and you will instead get the old "it's the same thing, why bother changing" argument against switching from Oracle to Postgresql.

The price?

Considering how painful it is to even configure Oracle client software, I cannot disagree more. I'd much rather deal with PostgreSQL any given day of the week, and should you need much more than what's in the box, that's the point that the six figure consultants and DBAs earn their income. Oracle (and DB2) are just a massive mess to actually work with.

PostgreSQL is really nice in terms of dev features. MS-SQL is nice in terms of Ops features. And even Firebird is easier to use in practice than Oracle/DB2. Don't add friction to your life when you don't need to. The only place I've ever worked that chose Oracle over MS-SQL or PostgreSQL was a larger company where the Oracle DBA team was generally easier to work with.

Sure they can but do they? Where's the WHATWG of SQL?

Property Graph Queries would be cool addition to PG. Looking forward to this.

There is a PostgreSQL extension (Apache AGE) [1] that provides graph database functionality, implementing the proposed SQL:2023 specification for property graphs queries might be easier in that context?

[1] https://age.apache.org/

The bigger question is when will ChatGPT be updated to be able to write SQL 2023.

"I apologize for the confusion. It seems that the SQL:2023 query I mentioned in my previous response is not a valid query in ${your-database-here}. I apologize for the incorrect information."

The fact that this got downvoted shows disruption is in the air. Don’t shoot the messenger.

Reply on news.ycombinator.com