MySQL and PostgreSQL are very commonly used databases that share a lot of characteristics, while maintaining some distinct traits. As for basic similarities, both use MySQL and PostgreSQL use SQL, or Structured Query Language. Both are also relational databases that have tabular data (data in tables), and they have similar CLI's (Command Line Interface). This is only the surface, however, and as you look deeper, the differences may seem more drastic. In this post, we'll take a closer look at the differences between PostgreSQL and MySQL's treatment of data types.
First off, the concept of a schema is fundamentally different between these two databases. MySQL essentially considers a schema to be the same as a database (or it is at least the basic format that theoretically constitutes a database). PostgreSQL, on the other hand, considers a schema to be a configuration of settings to combine and use various elements of a single database without the need for multiple databases. In the pictures below, the red represents a MySQL schema (top) and a possible PostgreSQL schema (bottom).
As for basic data types, these two databases tend to sort commonly-used types into different numbers of categories based on size. MySQL, for example, has a large range of sizes for integers, such as TINYINT, SMALLINT, MEDIUMINT, INTEGER, and BIGINT, whereas PostgreSQL only uses three, SMALLINT, INTEGER, and BIGINT. Likewise, MySQL has more options for text-based VARCHAR field sizes than PostgreSQL.
PostgreSQL and MySQL also have different data types to handle binary (1, 0) data: BYTEA for PostgreSQL and BLOB for MySQL. And PostgreSQL can actually generate and store UUID's (Universally Unique Identifiers), a sort of random, encryption-style text. MySQL currently has no equivalent to PostgreSQL's UUID.
Booloeans are drastically different between the two databases. Whereas PostgreSQL handles booleans as any proper programming language, MySQL stores them only as binaries which are actually one-digit TINYINTs, such that the value of a boolean could theoretically be many other numbers as well. Take a look at the example below as an illustration:
mysql> CREATE TABLE test (
-> id INT AUTO_INCREMENT,
-> bool BOOLEAN,
-> PRIMARY KEY (id)
-> );
Query OK, 0 row affected (0.02 sec)
mysql> INSERT INTO test (bool) VALUES (0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (bool) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (bool) VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from bool \G
*************************** 1. row ***************************
id: 1
bool: 0
*************************** 2. row ***************************
id: 2
bool: 1
*************************** 3. row ***************************
id: 3
bool: 2
3 rows in set (0.00 sec)
Another perk of PostgreSQL's is its flexible foreign data wrapper, which makes a "foreign table" (like a foreign key) and can represent a variety of configurations outside of a PostgreSQL table. Similarly useful are Postgres's Materialized Views, which save the result of a query as concrete data for later use, providing a time-efficient means of performing intensive queries when convenient. A separate issue is also MySQL's lack of the CHECK constraint, which is a flexible form of data validation. Likewise, PostgreSQL supports arrays and provides means of customizing your own data types with a variety of constraints, both of which are unavailable features in MySQL.
This post pains me a bit to write, actually. As I churn through these differences, consistently checking off reasons PostgreSQL is better than MySQL, I feel like I'm turning my back on my roots. MySQL is like home to me. It was MySQL that brought me my first database encounters—just a few short weeks ago—when I first discovered who I really am: a database weirdo. It's also just plain simpler, which is quite apparent considering the bevy of features it lacks compared to Postgres. But in databases, as in life, we must continue to evolve, while remembering where we came from—right? Even though this post has been bittersweet, I'm looking forward to sharing even more of Postgres's superior features in next week's post comparing Postgres's data handling to MySQL's. Until then, happy databasing!
Top comments (1)
There is another alternative solution to compare data. This tool helps to save time comparing and synchronizing PostgreSQL database data and has a wide range of useful features.