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

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