8 ms·
PostgreSQL and SQL:2023
- roenxi 3y agoAs 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.
- codetrotter 3y agoBut 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.
- roenxi 3y agoWithout 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.
- osigurdson 3y agoCan you state why this is the case? Are there products that need to use both?
- netcraft 3y agoI believe the assertion is that between those two youve accounted for a significant majority of the usage of SQL in the world.
- codetrotter 3y agoAgreed. But aside from that I could easily see someone using PostgreSQL on their server, and storing data client side in SQLite
- deleted 3y ago[deleted]
- sbuttgereit 3y agoPostgreSQL'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 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 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.
- ellisv 3y agofrom 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 https://www.postgresql.org/docs/current/features.html
- sbuttgereit 3y agoA 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 https://www.postgresql.org/docs/current/features.html I'd recommend: https://www.postgresql.org/docs/15/features.html https://www.postgresql.org/docs/15/features.html
- paulddraper 3y agoYes. 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.
- sam_goody 3y agoAnd even so, Postgres is closer to the spec than Oracle is. (Based on perusing https://modern-sql.com https://modern-sql.com)
- thejosh 3y agoTicking 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.
- tgv 3y agoI 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.
- ElectricalUnion 3y ago> 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.
- tgv 3y agoThe price?
- tracker1 3y agoConsidering 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.
- chx 3y agoSure they can but do they? Where's the WHATWG of SQL?
- samwillis 3y agoRelated thread yesterday: https://news.ycombinator.com/item?id=36154102 https://news.ycombinator.com/item?id=36154102 SQL:2023 has been released (iso.org) | 251 points | 118 comments
- truth_seeker 3y agoProperty Graph Queries would be cool addition to PG. Looking forward to this.
- IceHegel 3y agoThe bigger question is when will ChatGPT be updated to be able to write SQL 2023.
- ElectricalUnion 3y ago"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."
- IceHegel 3y agoThe fact that this got downvoted shows disruption is in the air. Don’t shoot the messenger.
- evangow 3y agoSince 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/ 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
- paulddraper 3y ago> 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%
- meotimdihia 3y agoIs there a database that supported arrays of foreign keys?
- lolive 3y agoWe use it in Palantir, that is a proprietary overlay on top of Spark. It supports this feature reasonably fine.
- paulddraper 3y agoOf 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?
- evangow 3y agoI would love it if the would remove the key from the array.
- zwily 3y ago
- nologic01 3y agoThere 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/ https://age.apache.org/