5 ms·
> I absolutely adore Postgres's tweaks to the SQL language Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or
by twic 5y ago
> I absolutely adore Postgres's tweaks to the SQL language
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!
- eatonphil 5y agoMySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
- jacobmischka 5y agoI assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.
- felixfbecker 5y agoThere 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 agoIt 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 agoOh, 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 agoAny “platonic ideals” mention outside its natural scope deserves attention.
- dmitryminkovsky 5y agoYou don't think the backticks lend a nice old school vibe?
- gunapologist99 5y agoIf you use any of the really amazing JSON or Array stuff, you're stepping outside of the standard.. but it's worth it!
- dkersten 5y agoI absolutely love Postgres' JSON and array support. Its saved me from needing a document database in a few cases, which drastically simplified the overall deployment (one DB instead of two, or trying to represent relational stuff in a document DB). I really like JSON for stuff that is user defined, that I don't need to query into, but postgres' support means I still can should I ever need to. Which has happened once or twice for debugging.
- 1propionyl 5y ago100%. I've dealt with many small-to-medium sized projects where the usual approach would have been some form of EAV mess. 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.
- fn1 5y ago> but postgres' support means I still can should I ever need to. 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'));
- dkersten 5y agoYes, Postgres' jsonb and array support lets you not only query into these fields, but also, like you say, index into them too. Its really the best of both worlds: you get a great solid relational database, but if you need document database features, you don't really lose out on anything by using json, except that the queries are a bit more ugly. I'm super happy with postgres these days, its rock solid and full of fantastic features.
- munk-a 5y agoPersonally, back before JSON became a standard thing - I made heavy use of HSTORE to clean up some insanely cludgey string packing BS we'd been doing - leveraging recursive CTEs using the UNION definition allowed us to move a fixed tier system into a self-referential one that saved us a lot of headaches as well. 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.
- paulddraper 5y ago> as far as i know, i am sticking to standard SQL 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.
- dijit 5y agoAlso the c++ bindings are very mature. 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”
- ipaddr 5y agoWhy would alphabetically be better? Wouldn't you want first in first out?
- jolux 5y agoIt means that the order they fire is imperative and determined by how you run your migrations.
- paulddraper 5y agoThe problem is that it doesn't leave you a great way to adjust order. It's just kinda weird. (To be fair, table columns share this characteristic.)
- marcosdumay 5y ago> To be fair, table columns share this characteristic. The table columns order is meaningless, but the triggers order is very relevant.
- paulddraper 5y agoTable column order is not meaningless. ``` INSERT INTO foo SELECT * FROM bar ``` The harder problem however, is order in composite types.