From: Tom Lane <tgl@sss.pgh.pa.us>
To: Shay Rojansky <roji@roji.org>
Cc: Vik Fearing <vik@postgresfriends.org>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: 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
Date: Fri, 14 May 2021 17:18:40 -0400
Message-ID: <2194411.1621027120@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, roji@roji.org, vik@postgresfriends.org, amitlangote09@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16913: GENERATED AS IDENTITY column nullability is affected by order of column properties
In-Reply-To: <2194411.1621027120@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox