Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
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>
Date: 2021-05-14T21:18:40Z
Lists: pgsql-bugs 
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

Commits

  1. Doc: correct erroneous entry in this week's minor release notes.

  2. Forbid marking an identity column as nullable.