Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

From
Shay Rojansky
Date:
Hi all.

I can see the PG 13.3 change note about GENERATED ALWAYS AS IDENTITY no longer being compatible with an explicit NULL specification. However, it seems that GENERATED BY DEFAULT AS IDENTITY also is no longer compatible with null:

CREATE TABLE foo
(
    id INTEGER NULL GENERATED BY DEFAULT AS IDENTITY
);

Results in:

ERROR:  conflicting NULL/NOT NULL declarations for column "id" of table "foo"

Is this intended? It seems to make sense to allow NULL to be explicitly inserted into columns whith as GENERATED BY DEFAULT AS IDENTITY. If this is intended, maybe the release notes should be updated for this.

Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

From
Tom Lane
Date:
Shay Rojansky <roji@roji.org> writes:
> I can see the PG 13.3 change note about GENERATED ALWAYS AS IDENTITY no
> longer being compatible with an explicit NULL specification. However, it
> seems that GENERATED BY DEFAULT AS IDENTITY also is no longer compatible
> with null:

> CREATE TABLE foo
> (
>     id INTEGER NULL GENERATED BY DEFAULT AS IDENTITY
> );

> Results in:

> ERROR:  conflicting NULL/NOT NULL declarations for column "id" of table
> "foo"

> Is this intended?

GENERATED BY DEFAULT does create a NOT NULL constraint:

regression=# CREATE TABLE foo(id INTEGER GENERATED BY DEFAULT AS IDENTITY);
CREATE TABLE
regression=# \d foo
                             Table "public.foo"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity

so I think the patch is doing what it was intended to.  Whether GENERATED
BY DEFAULT *should* be forcing NOT NULL is a separate question, but
AFAIK it always has.

            regards, tom lane

Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

From
Tom Lane
Date:
I wrote:
> GENERATED BY DEFAULT does create a NOT NULL constraint:
> ...
> so I think the patch is doing what it was intended to.  Whether GENERATED
> BY DEFAULT *should* be forcing NOT NULL is a separate question, but
> AFAIK it always has.

Ah, found it.  SQL:2016 11.4 <column definition> syntax rule 16 saith:

  If <identity column specification> ICS is specified, then:
  ...
  d) The <column constraint definition> NOT NULL NOT DEFERRABLE is implicit.

The <identity column specification> production includes both cases:

  <identity column specification> ::=
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
        [ <left paren> <common sequence generator options> <right paren> ]

so the spec does clearly say that both alternatives force NOT NULL.

So, it was my error to write the release notes as though only
GENERATED ALWAYS is affected.  I'll go adjust that, though
it won't propagate to the website for another three months :-(

            regards, tom lane

Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

From
Shay Rojansky
Date:
> I'll go adjust that

Thanks Tom.

> so the spec does clearly say that both alternatives force NOT NULL.

For what it's worth, it's odd to disallow nullable columns which are also GENERATED BY DEFAULT AS IDENTITY - unless I'm missing something, it seems like quite an artificial restriction for a legitimate scenario. After all, we can do:

CREATE SEQUENCE foo_seq AS integer;
CREATE TABLE foo (
    bar INTEGER NULL DEFAULT nextval('foo_seq')
);

... which is logically very similar, and definitely seems useful. Would it make sense to allow nullable GENERATED BY DEFAULT AS IDENTITY as a PG extension?

Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties

From
Shay Rojansky
Date:
> I'll go adjust that

One last pedantic note: the CREATE TABLE page[1] doesn't seem to mention the syntactic incompatibility between GENERATED ... AS IDENTITY and NULL.