inconsistent behavior with "GENERATED BY DEFAULT AS IDENTITY"

From: Himanshu Upadhyaya <upadhyaya(dot)himanshu(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: inconsistent behavior with "GENERATED BY DEFAULT AS IDENTITY"
Date: 2021-08-29 08:06:56
Message-ID: CAPF61jBCO-iUX+1p3Z1RHGYFdneyvq_SCfwFsXLDK3AzeGwurQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

It seems we have inconsistent behavior with the implementation of
"GENERATED BY DEFAULT AS IDENTITY" constraint on a table column.
Here we are not allowing(internally not replacing NULL with IDENTITY
DEFAULT) the "NULL" insertion into the table column.

postgres=# CREATE TABLE TEST_TBL_1(ID INTEGER GENERATED BY DEFAULT AS
IDENTITY ,ID1 INTEGER);
CREATE TABLE
postgres=# insert into TEST_TBL_1 values (NULL, 10);
ERROR: null value in column "id" of relation "test_tbl_1" violates
not-null constraint
DETAIL: Failing row contains (null, 10).
postgres=# insert into TEST_TBL_1(id1) values ( 10);
INSERT 0 1

However this is allowed on normal default column:
postgres=# create table TEST_TBL_2 (ID INTEGER DEFAULT 10 ,ID1 INTEGER);
CREATE TABLE
postgres=# insert into TEST_TBL_2 values (NULL, 10);
INSERT 0 1
postgres=# insert into TEST_TBL_2 (id1) values (20);
INSERT 0 1

if I understand it correctly, the use-case for supporting "GENERATED BY
DEFAULT AS IDENTITY" is to have an inbuilt sequence generated DEFAULT value
for a column.

IMHO below query should replace "NULL" value for ID column with the
GENERATED IDENTITY value (should insert 1,10 for ID and ID1 respectively in
below's example), similar to what we expect when we have DEFAULT constraint
on the column.

insert into TEST_TBL_1 values (NULL, 10);

TO Support the above query ORACLE is having "GENERATED BY DEFAULT ON NULL
AS IDENTITY" syntax. We can also think on similar lines and have similar
implementation
or allow it under "GENERATED BY DEFAULT AS IDENTITY" itself.

Any reason for disallowing NULL insertion?

Thoughts?

Thanks,
Himanshu

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gus Spier 2021-08-29 11:35:21 Re: Can we get rid of repeated queries from pg_dump?
Previous Message Andres Freund 2021-08-29 02:37:22 Re: archive status ".ready" files may be created too early