So I started looking into the bug noted in [1], but before getting to
multi-row inserts, I concluded that the current single-row behaviour
isn't spec-compliant.
In particular, Syntax Rule 11b of section 14.11 says that an INSERT
statement on a GENERATED ALWAYS identity column must specify an
overriding clause, but it doesn't place any restriction on the type of
overriding clause allowed. In other words it should be possible to use
either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE, but we
currently throw an error unless it's the former.
It's useful to allow OVERRIDING USER VALUE for precisely the example
use-case given in the INSERT docs:
This clause is useful for example when copying values between tables.
Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM
tbl1</literal> will copy from <literal>tbl1</literal> all columns that
are not identity columns in <literal>tbl2</literal> while values for
the identity columns in <literal>tbl2</literal> will be generated by
the sequences associated with <literal>tbl2</literal>.
which currently only works for a GENERATED BY DEFAULT identity column,
but should work equally well for a GENERATED ALWAYS identity column.
So I propose the attached patch.
Regards,
Dean
[1]
https://postgr.es/m/CAEZATCUmSp3-8nLOpgGcPkpUEXK9TJGM%3DiA6q4E2Sn%3D%2BbwkKNA%40mail.gmail.com
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
new file mode 100644
index 22dbc07..b48a29e
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -803,8 +803,8 @@ WITH ( MODULUS <replaceable class="param
<para>
This clause creates the column as an <firstterm>identity
column</firstterm>. It will have an implicit sequence attached to it
- and the column in new rows will automatically have values from the
- sequence assigned to it.
+ and new rows in the column will automatically have values from the
+ sequence assigned to them.
</para>
<para>
@@ -815,12 +815,22 @@ WITH ( MODULUS <replaceable class="param
only accepted if the <command>INSERT</command> statement
specifies <literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY
DEFAULT</literal> is specified, then the user-specified value takes
- precedence. See <xref linkend="sql-insert"/> for details. (In
+ precedence, unless the <command>INSERT</command> statement specifies
+ <literal>OVERRIDING USER VALUE</literal>.
+ See <xref linkend="sql-insert"/> for details. (In
the <command>COPY</command> command, user-specified values are always
used regardless of this setting.)
</para>
<para>
+ Additionally, if <literal>ALWAYS</literal> is specified, any attempt to
+ update the value of the column using an <command>UPDATE</command>
+ statement specifying any value other than <literal>DEFAULT</literal>
+ will be rejected. If <literal>BY DEFAULT</literal> is specified, the
+ system will allow values in the column to be updated.
+ </para>
+
+ <para>
The optional <replaceable>sequence_options</replaceable> clause can be
used to override the options of the sequence.
See <xref linkend="sql-createsequence"/> for details.
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
new file mode 100644
index 62e142f..8108f31
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -206,10 +206,16 @@ INSERT INTO <replaceable class="paramete
<term><literal>OVERRIDING SYSTEM VALUE</literal></term>
<listitem>
<para>
- Without this clause, it is an error to specify an explicit value
- (other than <literal>DEFAULT</literal>) for an identity column defined
- as <literal>GENERATED ALWAYS</literal>. This clause overrides that
- restriction.
+ If this clause is specified, then any values supplied for identity
+ columns will override the default sequence-generated values.
+ </para>
+
+ <para>
+ For an identity column defined as <literal>GENERATED ALWAYS</literal>,
+ it is an error to insert an explicit value (other than
+ <literal>DEFAULT</literal>) without specifying either
+ <literal>OVERRIDING SYSTEM VALUE</literal> or
+ <literal>OVERRIDING USER VALUE</literal>.
</para>
</listitem>
</varlistentry>
@@ -219,8 +225,8 @@ INSERT INTO <replaceable class="paramete
<listitem>
<para>
If this clause is specified, then any values supplied for identity
- columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
- and the default sequence-generated values are applied.
+ columns are ignored and the default sequence-generated values are
+ applied.
</para>
<para>
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index 7eb41ff..3dc27eb
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -821,13 +821,15 @@ rewriteTargetListIU(List *targetList,
{
if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default)
{
- if (override != OVERRIDING_SYSTEM_VALUE)
+ if (override == OVERRIDING_USER_VALUE)
+ apply_default = true;
+ else if (override != OVERRIDING_SYSTEM_VALUE)
ereport(ERROR,
(errcode(ERRCODE_GENERATED_ALWAYS),
errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)),
errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",
NameStr(att_tup->attname)),
- errhint("Use OVERRIDING SYSTEM VALUE to override.")));
+ errhint("You must specify either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE.")));
}
if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE)
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
new file mode 100644
index d7d5178..81044b2
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -119,28 +119,32 @@ SELECT * FROM itest3;
-- OVERRIDING tests
INSERT INTO itest1 VALUES (10, 'xyz');
-INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest1;
a | b
----+-----
1 |
2 |
10 | xyz
+ 20 | xyz
3 | xyz
-(4 rows)
+(5 rows)
INSERT INTO itest2 VALUES (10, 'xyz');
ERROR: cannot insert into column "a"
DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
-HINT: Use OVERRIDING SYSTEM VALUE to override.
-INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
+HINT: You must specify either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE.
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest2;
a | b
----+-----
1 |
2 |
- 10 | xyz
-(3 rows)
+ 20 | xyz
+ 3 | xyz
+(4 rows)
-- UPDATE tests
UPDATE itest1 SET a = 101 WHERE a = 1;
@@ -149,10 +153,11 @@ SELECT * FROM itest1;
a | b
-----+-----
10 | xyz
+ 20 | xyz
3 | xyz
101 |
4 |
-(4 rows)
+(5 rows)
UPDATE itest2 SET a = 101 WHERE a = 1;
ERROR: column "a" can only be updated to DEFAULT
@@ -162,9 +167,10 @@ SELECT * FROM itest2;
a | b
----+-----
1 |
- 10 | xyz
- 3 |
-(3 rows)
+ 20 | xyz
+ 3 | xyz
+ 4 |
+(4 rows)
-- COPY tests
CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint);
@@ -240,7 +246,7 @@ SELECT * FROM itestv10;
INSERT INTO itestv11 VALUES (10, 'xyz');
ERROR: cannot insert into column "a"
DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.
-HINT: Use OVERRIDING SYSTEM VALUE to override.
+HINT: You must specify either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE.
INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
SELECT * FROM itestv11;
a | b
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
new file mode 100644
index a35f331..f74353f
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -65,12 +65,14 @@ SELECT * FROM itest3;
-- OVERRIDING tests
INSERT INTO itest1 VALUES (10, 'xyz');
-INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
+INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest1;
INSERT INTO itest2 VALUES (10, 'xyz');
-INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');
+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
+INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest2;
