Should we support new definition for Identity column : GENERATED BY DEFAULT ON NULL? - Mailing list pgsql-hackers

Hi,

Trying to insert NULL value to the Identity column defined by "GENERATED BY DEFAULT" is disallowed, but there can be use cases where the user would like to have an identity column where manual NULL insertion is required(and it should not error-out by Postgres).

How about having a new type for the Identity column as "GENERATED BY DEFAULT ON NULL", which will allow manual NULL insertion and internally NULL value will be replaced by Sequence NextValue?

ORACLE is supporting this feature by having a similar Identity column type as below:

=======================================================================

SQL> CREATE TABLE itest1 (id1 INTEGER GENERATED BY DEFAULT ON NULL

AS IDENTITY, id2 INTEGER);

Table created.

SQL> INSERT INTO itest1 VALUES (NULL, 10);    --Supported with GENERATED BY DEFAULT ON NULL

1 row created.

SQL> INSERT INTO itest1 VALUES (1,30);

1 row created.

SQL> INSERT INTO itest1 (id2) VALUES (20);

1 row created.

SQL> SELECT * FROM itest1;

       ID1        ID2
---------- ----------
         1         10
         1         30
         2         20

Thoughts?

Thanks,

Himanshu