PostgreSQL: how to insert null value to uuid

Question

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)

Dumpish

Please provide your table definition (what you get with \d tbl in psql). And also the verbatim INSERT command you are using. And always your version of Postgres. – Heartburn

You should leave the table definition in the question. It's the key to the answer (and should be part of any such question). – Heartburn

Answer 1

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.

Heartburn

@user4265223: Turns out it is defined NOT NULL after all. – Heartburn

Answer 2

In Postgres use uuid_nil() function to simulate empty uuid (same as 00000000-0000-0000-0000-000000000000)

INSERT INTO inv_location (address_id)
VALUES (uuid_nil())

You might need to have uuid extension (not sure), if you do, run this (only once):

create extension if not exists "uuid-ossp";

Bonina

In my use case I'm not able to easily install the extension and I've thought minutes and minutes about how to do so. I'm not a smart person. Thank you anyways, I'll just copy the string :-) – Purity

Answer 3

INSERT INTO inv_location (address_id) VALUES ('00000000-0000-0000-0000-000000000000'::UUID)

Schoenberg