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(<field>)
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 <fields>, 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.