Search code examples
postgresqlnullsql-insert

inserting null date in postgres


I am inserting a null date with a INSERT ... SELECT FROM statement in sql

CREATE TABLE null_date (
  id         bigserial PRIMARY KEY
, some_date  date
);

WITH date_data (some_date) AS (
  VALUES (null)
)
INSERT INTO null_date (some_date)
SELECT some_date
FROM date_data;

and it fails with

ERROR:  column "some_date" is of type date but expression is of type text
LINE 5: SELECT some_date
               ^
HINT:  You will need to rewrite or cast the expression.

However, if I try to insert it directly, it works

INSERT INTO null_date (some_date)
VALUES (null)

can somebody please help me understand what's happening here? Here is the link to db<>fiddle. Thanks


Solution

  • The problem is that the VALUES statement and consequently the WITH clause will treat the NULL value as type text, because PostgreSQL doesn't know which data type the NULL should be. You don't have that problem with INSERT INTO ... VALUES (...), because here PostgreSQL knows right away that the NULL value with unknown type will be inserted into a certain column, so it will resolve it to the target data type.

    In cases where PostgreSQL cannot guess the data type from context, you had better use an explicit type cast:

    WITH date_data (some_date) AS (
      VALUES (CAST(null AS date))
    )
    INSERT INTO null_date (some_date)
    SELECT some_date
    FROM date_data;
    

    PostgreSQL used to behave differently in cases like this, but commit 1e7c4bb0049 changed that in 2017. Read the commit message for an explanation.