Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf52c5d6

Browse files
committed
Forbid marking an identity column as nullable.
GENERATED ALWAYS AS IDENTITY implies NOT NULL, but the code failedto complain if you overrode that with "GENERATED ALWAYS AS IDENTITYNULL". One might think the old behavior was a feature, but it wasinconsistent because the outcome varied depending on the order ofthe clauses, so it seems to have been just an oversight.Per bug #16913 from Pavel Boev. Back-patch to v10 where identitycolumns were introduced.Vik Fearing (minor tweaks by me)Discussion:https://postgr.es/m/16913-3b5198410f67d8c6@postgresql.org
1 parent1b88b89 commitf52c5d6

File tree

4 files changed

+33
-0
lines changed

4 files changed

+33
-0
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -840,6 +840,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
840840
column</firstterm>. It will have an implicit sequence attached to it
841841
and the column in new rows will automatically have values from the
842842
sequence assigned to it.
843+
Such a column is implicitly <literal>NOT NULL</literal>.
843844
</para>
844845

845846
<para>

‎src/backend/parser/parse_utilcmd.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -719,7 +719,17 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
719719

720720
column->identity=constraint->generated_when;
721721
saw_identity= true;
722+
723+
/* An identity column is implicitly NOT NULL */
724+
if (saw_nullable&& !column->is_not_null)
725+
ereport(ERROR,
726+
(errcode(ERRCODE_SYNTAX_ERROR),
727+
errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
728+
column->colname,cxt->relation->relname),
729+
parser_errposition(cxt->pstate,
730+
constraint->location)));
722731
column->is_not_null= true;
732+
saw_nullable= true;
723733
break;
724734
}
725735

‎src/test/regress/expected/identity.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -547,3 +547,16 @@ CREATE TABLE itest14 (id serial);
547547
ALTER TABLE itest14 ALTER id DROP DEFAULT;
548548
ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
549549
INSERT INTO itest14 (id) VALUES (DEFAULT);
550+
-- Identity columns must be NOT NULL (cf bug #16913)
551+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail
552+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
553+
LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL);
554+
^
555+
CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail
556+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
557+
LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID...
558+
^
559+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
560+
DROP TABLE itest15;
561+
CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
562+
DROP TABLE itest15;

‎src/test/regress/sql/identity.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -346,3 +346,12 @@ CREATE TABLE itest14 (id serial);
346346
ALTERTABLE itest14 ALTER id DROP DEFAULT;
347347
ALTERTABLE itest14 ALTER id ADD GENERATED BY DEFAULTAS IDENTITY;
348348
INSERT INTO itest14 (id)VALUES (DEFAULT);
349+
350+
-- Identity columns must be NOT NULL (cf bug #16913)
351+
352+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNULL);-- fail
353+
CREATETABLEitest15 (idintegerNULL GENERATED ALWAYSAS IDENTITY);-- fail
354+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNOT NULL);
355+
DROPTABLE itest15;
356+
CREATETABLEitest15 (idintegerNOT NULL GENERATED ALWAYSAS IDENTITY);
357+
DROPTABLE itest15;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp