In this blog post, I’ll look at a comparison of PostgreSQL and MySQL.
I came across a post from Hans-Juergen Schoenig, a Postgres consultant at Cybertec. In it, he dismissed MySQL and showed Postgres as better. While his post ignores most of the reasons why MySQL is better, I will focus on where his post is less than accurate. Testing for MySQL was done with Percona Server 5.7, defaults.
Mr. Schoenig complains that MySQL changes data types automatically. He claims inserting 1234.5678 into a numeric(4, 2) column on Postgres produces an error, and that MySQL just rounds the number to fit. In my testing I found this to be a false claim:
1 2 3 4 5 6 7 | mysql> CREATE TABLE data ( -> id integer NOT NULL, -> data numeric(4, 2)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO data VALUES (1, 1234.5678); ERROR 1264 (22003): Out of range value for column 'data' at row 1 |
His next claim is that MySQL allows updating a key column to NULL and silently changes it to 0. This is also false:
1 2 3 4 5 | mysql> INSERT INTO data VALUES (1, 12); Query OK, 1 row affected (0.00 sec) mysql> UPDATE data SET id = NULL WHERE id = 1; ERROR 1048 (23000): Column 'id' cannot be null |
In the original post, we never see the warnings and so don’t have the full details of his environment. Since he didn’t specify which version he was testing on, I will point out that MySQL 5.7 does a far better job out-of-the-box handling your data than 5.6 does, and SQL Mode has existed in MySQL for ages. Any user could set it to STRICT_ALL|TRANS_TABLES and get the behavior that is now default in 5.7.
The author is also focusing on a narrow issue, using it to say Postgres is better. I feel this is misleading. I could point out factors in MySQL that are better than in Postgres as well.
This is another case of “don’t necessarily take our word for it”. A simple test of what you see on a blog can help you understand how things work in your environment and why.
Yep, that guy is an idiot, he’s incompetent to say anything.
Hi Manjot Singh,
Thanks for the post.
The post of Mr. Schoenig is the prove that we must filter all our reads. I believe in the “free speech” but some people just use it in the wrong way.
Maybe everyone should read why Uber ditched Postgres and moved to MySQL
https://eng.uber.com/mysql-migration/
Wow.
Most of the complaints about MySQL from the PostgreSQL world are based on MySQL 5.0 or worse.
I also once heard “MySQL doesn’t have FULL GROUP BY”. Come on guys, ONLY_FULL_GROUP_BY has existed as a SQL_MODE since MySQL 5.0…