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.
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.
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.
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.
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.
"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."
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)
> - 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.
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?
34 comments
[ 2.7 ms ] story [ 85.4 ms ] threadI 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.
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.
"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
So instead of:
https://www.postgresql.org/docs/current/features.html
I'd recommend:
https://www.postgresql.org/docs/15/features.html
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.
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.
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.
https://news.ycombinator.com/item?id=36154102
SQL:2023 has been released (iso.org) | 251 points | 118 comments
- 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
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%
I'm curious how people think this feature should work? ON DELETE SET NULL, ON DELETE CASCADE, something else?
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.
[1] https://age.apache.org/