What does Postgres do better than MySQL?

Here's a Reddit post that I'm happy with. It's about what does Postgres do that MySQL doesn't? It's not comprehensive - just some of the features and issues I've encountered that I tried to pour out into that post. I tried to give MySQL a few props for what they've done better but I had to really , and most of it is just speculation (like, I've HEARD that managing its replication is easier...)

Here it is: https://www.reddit.com/r/SQL/comments/exrc9s/postgres_vs_mysql/fgcy1vj/

I added a few more bullet points in the subsequent comment, as well: https://www.reddit.com/r/SQL/comments/exrc9s/postgres_vs_mysql/fgfslze

SQL here != SQL there.

Can we please stop thinking some SQL command you used on database server x is universal? As you may be aware, I love answering questions on reddit, r/sql, to help people out with their SQL questions. But unfortunately I'm constantly finding myself having to ask, "Which database are you using?" as well as correcting others who suggest solutions that only work on a different database than what the person having the problem is using. This is because people think SQL works the same everywhere, I guess. 

There are extensions to SQL on all the major database systems. None of them implements every feature in all of the SQL specs. Even if they did, the SQL specs have many optional features. So for one feature, it might say, "This feature works like THIS, but OPTIONALLY it works like THAT." So even if we had two SQL databases that fully implement SQL (we don't), they could vary in behavior.

This morning I was frustrated by getting downvoted when correcting a commenter who gave a MS SQL Server-specific solution to a poster who didn't even say he's on MS SQL Server! 

So I made this meme to vent my frustrations.

SQL here != SQL there.

Stackoverflow answer on what Postgres has that MySQL lacks

The user u/truilus on Reddit (r/SQL) pointed out this Stackoverflow answer. It's really good: https://stackoverflow.com/a/8182996

It would be even better if it pointed out the various other quirks and oddities that MySQL has that Postgres doesn't, like this one I talked about in my previous post here: http://mwrynn.blogspot.com/2018/09/my-coworker-recently-came-up-to-me.html

Or, about how the MySQL query planner is pretty much inferior.

Or, if it talked about WHY each of these features in this huge list are useful things. I thought I'd write here about one, single Postgres feature that it is incredibly useful:

Transactional DDL: This is a really big one for me, particularly with respect to SQL scripts and rolling out incremental schema changes. Let's say I want to apply the following changes to an existing schema, to upgrade it from schema version i to schema version i+1:

   0. begin;
   1. insert into table x;
   2. create table y;
   3. insert into y;
   4. alter table z add column abc;
   5. update z set abc = ...;
   6. commit;

In Postgres I can roll out this whole set of statements in an all or nothing manner. In MySQL (and to be fair, some other databases as well), if say statement #5 fails for ANY reason - could be a bad foreign key reference, lack of disk space, or anything else - errors can and will happen - I'm stuck with a partially applied set of changes. What if the insert (statement #1) only makes business sense in conjunction with the update in the last statement? Well too bad, because statement 3 (alter table) forced a commit. So now there's "bad data" on prod until I have a chance to manually revert everything.

In Postgres it goes like this: Uh oh, statement 5 failed? Roll it all back! We're back at schema version i, not schema version i + random mess, where random mess != 1. Ta-da!