Should we support new definition for Identity column : GENERATED BY DEFAULT ON NULL?
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
On 11/2/21 12:19 PM, Himanshu Upadhyaya wrote:
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).
What could possibly be the use case for this?
Thoughts?
I don't like it.
--
Vik Fearing
On Wed, Nov 03, 2021 at 11:07:58AM +0100, Vik Fearing wrote:
On 11/2/21 12:19 PM, Himanshu Upadhyaya wrote:
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).What could possibly be the use case for this?
Unfortunately, the PREPARE/EXECUTE infrastructure, and not just for
PostgreSQL, has no way of passing along DEFAULT explicitly, i.e. it
can only explicitly pass literals and NULL, so people come up with
workarounds like this. I keep saying "explicitly" because one of the
workarounds is to pass DEFAULT by omission in the target list, which
is not a great way to handle anything, being a communication by
silence and all. I'm thinking that lack is the problem we should
actually address.
Thoughts?
I don't like it.
Neither do I, but I do get some of the motivation behind it. I don't
suppose the standard actually says much about PREPARE, or whether we
should care about anything it happens to say that gets in the way of
doing something more helpful.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, 3 Nov 2021 at 14:39, David Fetter <david@fetter.org> wrote:
Unfortunately, the PREPARE/EXECUTE infrastructure, and not just for
PostgreSQL, has no way of passing along DEFAULT explicitly, i.e. it
My $.02: I'd be much happier with the idea of changing those
obviously-deficient interfaces to allow explicit DEFAULT than of having an
explicit NULL treated differently depending on the column type.
Geoff