4 ms·

There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. But it's true that PSQL sticks very closely to the standard or

There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. 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...

petergeoghegan 5y ago
It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. 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.
- munk-a 5y ago
  Oh, it's totally fair to criticize the standard. Those clowns lock their deliberation and standard definitions behind huge paywalls. 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.
- dzolob 5y ago
  Any “platonic ideals” mention outside its natural scope deserves attention.
dmitryminkovsky 5y ago
You don't think the backticks lend a nice old school vibe?

petergeoghegan 5y ago

It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. 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.

munk-a 5y ago
Oh, it's totally fair to criticize the standard. Those clowns lock their deliberation and standard definitions behind huge paywalls. 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.
dzolob 5y ago
Any “platonic ideals” mention outside its natural scope deserves attention.

dmitryminkovsky 5y ago

You don't think the backticks lend a nice old school vibe?