But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant.
Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...
You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking.
I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.
The best way to learn the SQL standard is to read the Postgres docs since they clearly call out non-compliances - there is no other easy to access and clear explanation of the SQL standard.
PostgreSQL documentation is very good about comparing each feature to the standard.
For example, the SQL standard specifies that triggers fire in the historical order they are added to the table.
PostgreSQL chose to fire them alphabetically.
Technically nonconformant, but in practical terms, far better.
This was surprising for my old tech lead who was sheepish on moving away from MySQL.
He would later refer to the MySQL bindings as “brain dead”
Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)
And then any second trigger that relies on that value
But I also think that it's unnecessarily problematic to make the order non-deterministic.
It just makes easier to understand what happens when triggers are invoked, without having to actually force specific cases by direct experimentation.
I did not even know that the standard prescribed a FIFO order, looks like a pretty bad idea to me, actually.
(To be fair, table columns share this characteristic.)
The table columns order is meaningless, but the triggers order is very relevant.
``` INSERT INTO foo SELECT * FROM bar ```
The harder problem however, is order in composite types.
Just using a BSON column for such attributes hits the Pareto 80/20 sweet spot, _and_ I can query against them much more easily than in an EAV schema (far fewer joins).
They're great for when the schema is mostly normalized, but there's just a little bit of denormalized stuff that doesn't warrant completely architecting the schema around it.
And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE:
CREATE INDEX ON publishers((info->>'name'));
It would be horribly painful to stick to _just_ SQL/JSON for manipulations, and I have absolutely zero idea what the coverage is in other platforms, but portability isn't completely off the table like it used to be
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
Other folks have mentioned :: instead of CAST AS (which is really nice when you need to write it a bunch of times) but it's mostly the diverse built-in function set and constraint support that keeps me an enthusiastic Postgres supporter.
Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else?
The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!