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
