Please humor me if I sound a bit triggered in this response, as MySQL pre-v8.0 is like if the creators went to data analysts and asked: "what features could we remove, and what changes could we make to an SQL-standard-compliant DB (postgres), in order to slow you down as much as possible, hinder your ability to read and write queries, and generally make your life a living hell?"
I really get that MySQL is good for what it does, from an engineer's point of view. It is an absolute piss-poor excuse for a database, prior to v8.0.
So what's wrong with MySQL (again, prior to v8.0, but no one seems to use the damn current version)
-Not ANSI SQL compliant (unlike Postgres)
-No CTEs/WITH clause (?!)
-no WINDOW FUNCTIONS (?!?!?!?)
-"schemas are called databases" which makes for bizarre interpretation of `information_schema` queries, which behave the same across all other DBs except mySQL. What I mean to say is MySQL calls each schema it's own database. This results in having to connect the same DB multiple times to other programs/APIs/inputs which accept JDBC.
-Worse replication options than postgres, not default ACID compliant,
-Don't know the programming term for this... but the horrendous "select col1, col2, col3... colN, count()
from table group by 1" implicit group by. Meaning the system takes your INVALID query, and does things underneath the hood to return a result. Systems should enforce correct syntax (you must group by all non-aggregation columns... mysql implicitly does this under the hood).
-on a tangentially related note to the prior one, MySQL returns null instead of a divide by zero error when you divide by zero. Divide by zero errors are one of the few things that should ALWAYS RETURN AN ERROR NO MATTER WHAT
-mysql doesn't support EXCEPT clauses
-doesn't support FULL OUTER JOIN
-doesn't support generate_series,
-poor JSON support
-very limited, poor array/unnest support
-insert VALUES () (in postgres) not supported
-lack of consistent pipe operator concatenation,
-weird datatype suppport and in-query doesn't support ::cast
-doesn't support `select t1._* , t2.field1, t2.field2 from t1 join t2 on t1.id = t2.id` ; that is, you cannot select * from one table, and only certain fields from the other.
-case dependence in field and table names when not escape quoted (mysql uses backtick, postgres uses double quote for escaping names). What the fuck is this? SQL is a case-insensitive language, then the creators build-in case sensitivity?
-As I mentioned above, mysql uses backticks to escape names. This is abnormal for SQL databases.
-mysql LIKE is case-insensitive (what the hell, it's case-sensitive everywhere else). Postgres has LIKE, and ILIKE (insensitive-like).
-ugly and strange support for INTERVAL syntax (intervals, despite being strings, give a syntax error in mysql.
Example: In postgres or redshift etc you would right `select current_timestamp - interval '1 week'. In MySQL, you'd have to do `select current_timestamp - interval 1 week` (the '1 week' could be '7 month' or '2 day'... it's a string, and should be in single quotes. MySQL doesn't do this)
-mysql doesn't even support the normal SQL comment of `--`. It uses a `#` instead. No other database does that.
-probably the worst EXPLAIN/EXPLAIN ANALYZE plans I've ever seen from any database, ever
-this is encapsulated in the prior points but you can't do something simple like `select , row_number() as rownum from table`. Instead you have to declare variables and increment them in the query
-did I mention it's just straight up not SQL standard compliant?
At least MySQL 8.0 supports window functions and CTEs (seriously it's a death knell to a data analyst not to have these). They are the absolute #1 biggest piece of missing functionality to an analyst in my opinion.
This entire post focused on "mySQL have-nots", rather than "Postgres-haves" so I do think there are actually _even more_ advantages to using Postgres over MySQL. I understand MySQL is very fast for writes, but to my understanding it's not even like Postgres is slow for writes, and on the querying side of the coin, it's a universe of difference.
If you ever use MySQL in the future and there will be a data analyst existing somewhere downstream of you, I implore you to use MySQL v8.0 and nothing older, at any cost, for their sake.