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

Commit8a22977

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 parent3580b4a commit8a22977

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
@@ -839,6 +839,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
839839
column</firstterm>. It will have an implicit sequence attached to it
840840
and the column in new rows will automatically have values from the
841841
sequence assigned to it.
842+
Such a column is implicitly <literal>NOT NULL</literal>.
842843
</para>
843844

844845
<para>

‎src/backend/parser/parse_utilcmd.c

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

717717
column->identity=constraint->generated_when;
718718
saw_identity= true;
719+
720+
/* An identity column is implicitly NOT NULL */
721+
if (saw_nullable&& !column->is_not_null)
722+
ereport(ERROR,
723+
(errcode(ERRCODE_SYNTAX_ERROR),
724+
errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"",
725+
column->colname,cxt->relation->relname),
726+
parser_errposition(cxt->pstate,
727+
constraint->location)));
719728
column->is_not_null= true;
729+
saw_nullable= true;
720730
break;
721731
}
722732

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -491,3 +491,16 @@ CREATE TABLE itest14 (id serial);
491491
ALTER TABLE itest14 ALTER id DROP DEFAULT;
492492
ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
493493
INSERT INTO itest14 (id) VALUES (DEFAULT);
494+
-- Identity columns must be NOT NULL (cf bug #16913)
495+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail
496+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
497+
LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL);
498+
^
499+
CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail
500+
ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
501+
LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID...
502+
^
503+
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
504+
DROP TABLE itest15;
505+
CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
506+
DROP TABLE itest15;

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -323,3 +323,12 @@ CREATE TABLE itest14 (id serial);
323323
ALTERTABLE itest14 ALTER id DROP DEFAULT;
324324
ALTERTABLE itest14 ALTER id ADD GENERATED BY DEFAULTAS IDENTITY;
325325
INSERT INTO itest14 (id)VALUES (DEFAULT);
326+
327+
-- Identity columns must be NOT NULL (cf bug #16913)
328+
329+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNULL);-- fail
330+
CREATETABLEitest15 (idintegerNULL GENERATED ALWAYSAS IDENTITY);-- fail
331+
CREATETABLEitest15 (idinteger GENERATED ALWAYSAS IDENTITYNOT NULL);
332+
DROPTABLE itest15;
333+
CREATETABLEitest15 (idintegerNOT NULL GENERATED ALWAYSAS IDENTITY);
334+
DROPTABLE itest15;

0 commit comments

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