Horton

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

Shay Rojansky <roji@roji.org>

View thread View on postgr.es
From: Shay Rojansky <roji@roji.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
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-15T15:43:21Z
Lists: pgsql-bugs 
> I'll go adjust that

Thanks Tom.

> so the spec does clearly say that both alternatives force NOT NULL.

For what it's worth, it's odd to disallow nullable columns which are also
GENERATED BY DEFAULT AS IDENTITY - unless I'm missing something, it seems
like quite an artificial restriction for a legitimate scenario. After all,
we can do:

CREATE SEQUENCE foo_seq AS integer;
CREATE TABLE foo (
    bar INTEGER NULL DEFAULT nextval('foo_seq')
);

... which is logically very similar, and definitely seems useful. Would it
make sense to allow nullable GENERATED BY DEFAULT AS IDENTITY as a PG
extension?

Commits

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

  2. Forbid marking an identity column as nullable.