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

Shay Rojansky <roji(at)roji(dot)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