Should we support new definition for Identity column : GENERATED BY DEFAULT ON NULL?

[Himanshu Upadhyaya]

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
================================================================

I think it is good to have support for GENERATED BY DEFAULT ON NULL in
Postgres.

Thoughts?

Thanks,
Himanshu