Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1lhfCo-0005fY-Ne
	for pgsql-bugs@arkaria.postgresql.org; Fri, 14 May 2021 21:18:46 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1lhfCn-0006Oh-Dq
	for pgsql-bugs@arkaria.postgresql.org; Fri, 14 May 2021 21:18:45 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lhfCn-0006OY-7O
	for pgsql-bugs@lists.postgresql.org; Fri, 14 May 2021 21:18:45 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lhfCl-00079g-4X
	for pgsql-bugs@lists.postgresql.org; Fri, 14 May 2021 21:18:44 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 14ELIeX52194412;
	Fri, 14 May 2021 17:18:40 -0400
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: <2191863.1621023482@sss.pgh.pa.us>
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>
 <2191863.1621023482@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 14 May 2021 16:18:02 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2194410.1621027120.1@sss.pgh.pa.us>
Date: Fri, 14 May 2021 17:18:40 -0400
Message-ID: <2194411.1621027120@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/2194411.1621027120%40sss.pgh.pa.us>
Precedence: bulk

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