Search code examples
postgresqlnullsql-insert

PostgreSQL: how to insert null value to uuid

Need to insert null value to field with uuid type without NOT NULL specification (not primary key).

When I try insert '', this return:

ERROR:  invalid input syntax for uuid: ""

When I try insert null, this return:

ERROR:  null value in column "uuid" violates not-null constraint

How to do it?

psql 9.3.5

SQL:

INSERT INTO inv_location (address_id)
VALUES (null)
Solution

  • If the column is defined NOT NULL, you cannot enter a NULL value. Period.

    In this error message:

    ERROR: null value in column "uuid" violates not-null constraint

    "uuid" is the name of the column, not the data type of address_id. And this column is defined NOT NULL:

    uuid         | character varying(36) | not null

    Your INSERT statement does not include "uuid" in the target list, so NULL is defaults to NULL in absence of a different column default. Boom.

    Goes to show how basic type names (ab)used as identifier lead to confusing error messages.