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

Commite7f7950

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 parentce54c02 commite7f7950

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
@@ -706,7 +706,17 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
706706

707707
column->identity=constraint->generated_when;
708708
saw_identity= true;
709+
710+
/* An identity column is implicitly NOT NULL */
711+
if (saw_nullable&& !column->is_not_null)
712+
ereport(ERROR,
713+
(errcode(ERRCODE_SYNTAX_ERROR),
714+
errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
715+
column->colname,cxt->relation->relname),
716+
parser_errposition(cxt->pstate,
717+
constraint->location)));
709718
column->is_not_null= true;
719+
saw_nullable= true;
710720
break;
711721
}
712722

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -399,3 +399,16 @@ CREATE TABLE itest_child PARTITION OF itest_parent (
399399
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
400400
ERROR: identity columns are not supported on partitions
401401
DROP TABLE itest_parent;
402+
-- Identity columns must be NOT NULL (cf bug #16913)
403+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail
404+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
405+
LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL);
406+
^
407+
CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail
408+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
409+
LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID...
410+
^
411+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
412+
DROP TABLE itest15;
413+
CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
414+
DROP TABLE itest15;

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,3 +254,12 @@ CREATE TABLE itest_child PARTITION OF itest_parent (
254254
f3 WITH OPTIONS GENERATED ALWAYSAS IDENTITY
255255
) FORVALUESFROM ('2016-07-01') TO ('2016-08-01');-- error
256256
DROPTABLE itest_parent;
257+
258+
-- Identity columns must be NOT NULL (cf bug #16913)
259+
260+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNULL);-- fail
261+
CREATETABLEitest15 (idintegerNULL GENERATED ALWAYSAS IDENTITY);-- fail
262+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNOT NULL);
263+
DROPTABLE itest15;
264+
CREATETABLEitest15 (idintegerNOT NULL GENERATED ALWAYSAS IDENTITY);
265+
DROPTABLE itest15;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp