From: Tom Lane <tgl@sss.pgh.pa.us>
To: Shay Rojansky <roji@roji.org>
cc: Vik Fearing <vik@postgresfriends.org>,
        Amit Langote <amitlangote09@gmail.com>, pavel.boev@invitae.com,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected
 by order of column properties
In-reply-to: 
 <CADT4RqAwD3A=RvGiQU9AiTK-6VeuXcycwPHmJPv_OBCJFYOEww@mail.gmail.com>
References: <16913-3b5198410f67d8c6@postgresql.org>
 <7c02b352-a2e4-e71a-ae69-7e410daf5774@postgresfriends.org>
 <ce93e6a9-1a36-795b-cc1f-819107e82005@postgresfriends.org>
 <CA+HiwqG6V8=emTcBkZhAOU7bci_QSeSy5wVazEtitGtkEBKVgg@mail.gmail.com>
 <2441173.1615563048@sss.pgh.pa.us>
 <3fb6c576-182e-e664-f64a-a65a74da85de@postgresfriends.org>
 <CADT4RqAwD3A=RvGiQU9AiTK-6VeuXcycwPHmJPv_OBCJFYOEww@mail.gmail.com>
Comments: In-reply-to Shay Rojansky <roji@roji.org>
	message dated "Fri, 14 May 2021 22:06:05 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2191862.1621023482.1@sss.pgh.pa.us>
Date: Fri, 14 May 2021 16:18:02 -0400
Message-ID: <2191863.1621023482@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2191863.1621023482%40sss.pgh.pa.us>
Precedence: bulk

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