Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties - Mailing list pgsql-bugs

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