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

Commitde3bbfc

Browse files
committed
Fix INSERT OVERRIDING USER VALUE behavior
The original implementation disallowed using OVERRIDING USER VALUE onidentity columns defined as GENERATED ALWAYS, which is not perstandard. So allow that now.Expand documentation and tests around this.Author: Dean Rasheed <dean.a.rasheed@gmail.com>Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>Reviewed-by: Vik Fearing <vik@postgresfriends.org>Discussion:https://www.postgresql.org/message-id/flat/CAEZATCVrh2ufCwmzzM%3Dk_OfuLhTTPBJCdFkimst2kry4oHepuQ%40mail.gmail.com
1 parent616ae3d commitde3bbfc

File tree

6 files changed

+102
-36
lines changed

6 files changed

+102
-36
lines changed

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

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -842,15 +842,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
842842

843843
<para>
844844
The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
845-
determine how the sequence value is given precedence over a
846-
user-specified value in an <command>INSERT</command> statement.
847-
If <literal>ALWAYS</literal> is specified, a user-specified value is
848-
only accepted if the <command>INSERT</command> statement
849-
specifies <literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY
850-
DEFAULT</literal> is specified, then the user-specified value takes
851-
precedence. See <xref linkend="sql-insert"/> for details. (In
852-
the <command>COPY</command> command, user-specified values are always
853-
used regardless of this setting.)
845+
determine how explicitly user-specified values are handled in
846+
<command>INSERT</command> and <command>UPDATE</command> commands.
847+
</para>
848+
849+
<para>
850+
In an <command>INSERT</command> command, if <literal>ALWAYS</literal> is
851+
selected, a user-specified value is only accepted if the
852+
<command>INSERT</command> statement specifies <literal>OVERRIDING SYSTEM
853+
VALUE</literal>. If <literal>BY DEFAULT</literal> is selected, then the
854+
user-specified value takes precedence. See <xref linkend="sql-insert"/>
855+
for details. (In the <command>COPY</command> command, user-specified
856+
values are always used regardless of this setting.)
857+
</para>
858+
859+
<para>
860+
In an <command>UPDATE</command> command, if <literal>ALWAYS</literal> is
861+
selected, any update of the column to any value other than
862+
<literal>DEFAULT</literal> will be rejected. If <literal>BY
863+
DEFAULT</literal> is selected, the column can be updated normally.
864+
(There is no <literal>OVERRIDING</literal> clause for the
865+
<command>UPDATE</command> command.)
854866
</para>
855867

856868
<para>

‎doc/src/sgml/ref/insert.sgml

Lines changed: 22 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -206,10 +206,19 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
206206
<term><literal>OVERRIDING SYSTEM VALUE</literal></term>
207207
<listitem>
208208
<para>
209-
Without this clause, it is an error to specify an explicit value
210-
(other than <literal>DEFAULT</literal>) for an identity column defined
211-
as <literal>GENERATED ALWAYS</literal>. This clause overrides that
212-
restriction.
209+
If this clause is specified, then any values supplied for identity
210+
columns will override the default sequence-generated values.
211+
</para>
212+
213+
<para>
214+
For an identity column defined as <literal>GENERATED ALWAYS</literal>,
215+
it is an error to insert an explicit value (other than
216+
<literal>DEFAULT</literal>) without specifying either
217+
<literal>OVERRIDING SYSTEM VALUE</literal> or <literal>OVERRIDING USER
218+
VALUE</literal>. (For an identity column defined as
219+
<literal>GENERATED BY DEFAULT</literal>, <literal>OVERRIDING SYSTEM
220+
VALUE</literal> is the normal behavior and specifying it does nothing,
221+
but <productname>PostgreSQL</productname> allows it as an extension.)
213222
</para>
214223
</listitem>
215224
</varlistentry>
@@ -219,8 +228,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
219228
<listitem>
220229
<para>
221230
If this clause is specified, then any values supplied for identity
222-
columnsdefined as <literal>GENERATED BY DEFAULT</literal> are ignored
223-
and the default sequence-generated values areapplied.
231+
columnsare ignored and the default sequence-generated values are
232+
applied.
224233
</para>
225234

226235
<para>
@@ -238,7 +247,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
238247
<term><literal>DEFAULT VALUES</literal></term>
239248
<listitem>
240249
<para>
241-
All columns will be filled with their default values.
250+
All columns will be filled with their default values, as if
251+
<literal>DEFAULT</literal> were explicitly specified for each column.
242252
(An <literal>OVERRIDING</literal> clause is not permitted in this
243253
form.)
244254
</para>
@@ -258,8 +268,11 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
258268
<term><literal>DEFAULT</literal></term>
259269
<listitem>
260270
<para>
261-
The corresponding column will be filled with
262-
its default value.
271+
The corresponding column will be filled with its default value. An
272+
identity column will be filled with a new value generated by the
273+
associated sequence. For a generated column, specifying this is
274+
permitted but merely specifies the normal behavior of computing the
275+
column from its generation expression.
263276
</para>
264277
</listitem>
265278
</varlistentry>

‎doc/src/sgml/ref/update.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -142,8 +142,11 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
142142
<term><literal>DEFAULT</literal></term>
143143
<listitem>
144144
<para>
145-
Set the column to its default value (which will be NULL if no
146-
specific default expression has been assigned to it).
145+
Set the column to its default value (which will be NULL if no specific
146+
default expression has been assigned to it). An identity column will be
147+
set to a new value generated by the associated sequence. For a
148+
generated column, specifying this is permitted but merely specifies the
149+
normal behavior of computing the column from its generation expression.
147150
</para>
148151
</listitem>
149152
</varlistentry>

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -808,7 +808,9 @@ rewriteTargetListIU(List *targetList,
808808
{
809809
if (att_tup->attidentity==ATTRIBUTE_IDENTITY_ALWAYS&& !apply_default)
810810
{
811-
if (override!=OVERRIDING_SYSTEM_VALUE)
811+
if (override==OVERRIDING_USER_VALUE)
812+
apply_default= true;
813+
elseif (override!=OVERRIDING_SYSTEM_VALUE)
812814
ereport(ERROR,
813815
(errcode(ERRCODE_GENERATED_ALWAYS),
814816
errmsg("cannot insert into column \"%s\"",NameStr(att_tup->attname)),

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

Lines changed: 30 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -118,53 +118,72 @@ SELECT * FROM itest3;
118118
(5 rows)
119119

120120
-- OVERRIDING tests
121+
-- GENERATED BY DEFAULT
122+
-- This inserts the row as presented:
121123
INSERT INTO itest1 VALUES (10, 'xyz');
122-
INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
124+
-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
125+
-- by the standard, but we allow it as a no-op, since it is of use if
126+
-- there are multiple identity columns in a table, which is also an
127+
-- extension.
128+
INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
129+
-- This ignores the 30 and uses the sequence value instead:
130+
INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
123131
SELECT * FROM itest1;
124132
a | b
125133
----+-----
126134
1 |
127135
2 |
128136
10 | xyz
137+
20 | xyz
129138
3 | xyz
130-
(4 rows)
139+
(5 rows)
131140

141+
-- GENERATED ALWAYS
142+
-- This is an error:
132143
INSERT INTO itest2 VALUES (10, 'xyz');
133144
ERROR: cannot insert into column "a"
134145
DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
135146
HINT: Use OVERRIDING SYSTEM VALUE to override.
136-
INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
147+
-- This inserts the row as presented:
148+
INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
149+
-- This ignores the 30 and uses the sequence value instead:
150+
INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
137151
SELECT * FROM itest2;
138152
a | b
139153
----+-----
140154
1 |
141155
2 |
142-
10 | xyz
143-
(3 rows)
156+
20 | xyz
157+
3 | xyz
158+
(4 rows)
144159

145160
-- UPDATE tests
161+
-- GENERATED BY DEFAULT is not restricted.
146162
UPDATE itest1 SET a = 101 WHERE a = 1;
147163
UPDATE itest1 SET a = DEFAULT WHERE a = 2;
148164
SELECT * FROM itest1;
149165
a | b
150166
-----+-----
151167
10 | xyz
168+
20 | xyz
152169
3 | xyz
153170
101 |
154171
4 |
155-
(4 rows)
172+
(5 rows)
156173

157-
UPDATE itest2 SET a = 101 WHERE a = 1;
174+
-- GENERATED ALWAYS allows only DEFAULT.
175+
UPDATE itest2 SET a = 101 WHERE a = 1; -- error
158176
ERROR: column "a" can only be updated to DEFAULT
159177
DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
160-
UPDATE itest2 SET a = DEFAULT WHERE a = 2;
178+
UPDATE itest2 SET a = DEFAULT WHERE a = 2; -- ok
161179
SELECT * FROM itest2;
162180
a | b
163181
----+-----
164182
1 |
165-
10 | xyz
166-
3 |
167-
(3 rows)
183+
20 | xyz
184+
3 | xyz
185+
4 |
186+
(4 rows)
168187

169188
-- COPY tests
170189
CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint);

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

Lines changed: 21 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -64,25 +64,42 @@ SELECT * FROM itest3;
6464

6565
-- OVERRIDING tests
6666

67+
-- GENERATED BY DEFAULT
68+
69+
-- This inserts the row as presented:
6770
INSERT INTO itest1VALUES (10,'xyz');
68-
INSERT INTO itest1 OVERRIDING USER VALUEVALUES (10,'xyz');
71+
-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
72+
-- by the standard, but we allow it as a no-op, since it is of use if
73+
-- there are multiple identity columns in a table, which is also an
74+
-- extension.
75+
INSERT INTO itest1 OVERRIDING SYSTEM VALUEVALUES (20,'xyz');
76+
-- This ignores the 30 and uses the sequence value instead:
77+
INSERT INTO itest1 OVERRIDING USER VALUEVALUES (30,'xyz');
6978

7079
SELECT*FROM itest1;
7180

81+
-- GENERATED ALWAYS
82+
83+
-- This is an error:
7284
INSERT INTO itest2VALUES (10,'xyz');
73-
INSERT INTO itest2 OVERRIDING SYSTEM VALUEVALUES (10,'xyz');
85+
-- This inserts the row as presented:
86+
INSERT INTO itest2 OVERRIDING SYSTEM VALUEVALUES (20,'xyz');
87+
-- This ignores the 30 and uses the sequence value instead:
88+
INSERT INTO itest2 OVERRIDING USER VALUEVALUES (30,'xyz');
7489

7590
SELECT*FROM itest2;
7691

7792

7893
-- UPDATE tests
7994

95+
-- GENERATED BY DEFAULT is not restricted.
8096
UPDATE itest1SET a=101WHERE a=1;
8197
UPDATE itest1SET a= DEFAULTWHERE a=2;
8298
SELECT*FROM itest1;
8399

84-
UPDATE itest2SET a=101WHERE a=1;
85-
UPDATE itest2SET a= DEFAULTWHERE a=2;
100+
-- GENERATED ALWAYS allows only DEFAULT.
101+
UPDATE itest2SET a=101WHERE a=1;-- error
102+
UPDATE itest2SET a= DEFAULTWHERE a=2;-- ok
86103
SELECT*FROM itest2;
87104

88105

0 commit comments

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