Differences between MySQL and PostgreSQL with INSERT

So, recently when investigating an issue with a MySQL to PostgreSQL migration, I found a surprising difference in behaviour between the two databases.

The difference has to do with how each behave when explicitly passed NULL values in an INSERT statement to columns with defaults or auto-increments (called “serials” in Postgres)

What do I actually mean? To compare, in MySQL you can do this:

mysql> create table baz(whattimeisit TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table baz;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| baz   | CREATE TABLE `baz` (
  `whattimeisit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO baz (whattimeisit) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from baz;
+---------------------+
| whattimeisit        |
+---------------------+
| 2019-08-25 20:35:47 |
+---------------------+
1 row in set (0.00 sec)

Where it decides to revert to the “DEFAULT” value when passed a NULL.

In Postgres, we get the following error:

postgres=# CREATE TABLE baz (whatsthetime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL);
CREATE TABLE
postgres=# \d baz
                                 Table "public.baz"
    Column    |           Type           | Collation | Nullable |      Default      
--------------+--------------------------+-----------+----------+-------------------
 whatsthetime | timestamp with time zone |           | not null | CURRENT_TIMESTAMP

postgres=# INSERT INTO baz(whatsthetime) VALUES (NULL);
ERROR:  null value in column "whatsthetime" violates not-null constraint
DETAIL:  Failing row contains (null).

Funnily enough, when it comes to PostgreSQL if you don’t pass the column at all, the default is used and you don’t get the same error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.