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 16:18:02 -0400
Message-ID: <2191863.1621023482@sss.pgh.pa.us> (raw)
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>
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
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: <2191863.1621023482@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