diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/rewrite/rewriteHandler.c | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/identity.out | 41 | ||||
| -rw-r--r-- | src/test/regress/sql/identity.sql | 25 |
3 files changed, 54 insertions, 16 deletions
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 3b4f28874aa..fe777c3103d 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -808,7 +808,9 @@ 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)), diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 7322b287650..7ac9df767f5 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -118,53 +118,72 @@ SELECT * FROM itest3; (5 rows) -- OVERRIDING tests +-- GENERATED BY DEFAULT +-- This inserts the row as presented: INSERT INTO itest1 VALUES (10, 'xyz'); -INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); +-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed +-- by the standard, but we allow it as a no-op, since it is of use if +-- there are multiple identity columns in a table, which is also an +-- extension. +INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); +-- This ignores the 30 and uses the sequence value instead: +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) +-- GENERATED ALWAYS +-- This is an error: 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'); +-- This inserts the row as presented: +INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); +-- This ignores the 30 and uses the sequence value instead: +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 +-- GENERATED BY DEFAULT is not restricted. UPDATE itest1 SET a = 101 WHERE a = 1; UPDATE itest1 SET a = DEFAULT WHERE a = 2; 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; +-- GENERATED ALWAYS allows only DEFAULT. +UPDATE itest2 SET a = 101 WHERE a = 1; -- error ERROR: column "a" can only be updated to DEFAULT DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. -UPDATE itest2 SET a = DEFAULT WHERE a = 2; +UPDATE itest2 SET a = DEFAULT WHERE a = 2; -- ok 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); diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index b4cdd21bdd4..1bf2a976eb0 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -64,25 +64,42 @@ SELECT * FROM itest3; -- OVERRIDING tests +-- GENERATED BY DEFAULT + +-- This inserts the row as presented: INSERT INTO itest1 VALUES (10, 'xyz'); -INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); +-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed +-- by the standard, but we allow it as a no-op, since it is of use if +-- there are multiple identity columns in a table, which is also an +-- extension. +INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); +-- This ignores the 30 and uses the sequence value instead: +INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz'); SELECT * FROM itest1; +-- GENERATED ALWAYS + +-- This is an error: INSERT INTO itest2 VALUES (10, 'xyz'); -INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); +-- This inserts the row as presented: +INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); +-- This ignores the 30 and uses the sequence value instead: +INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz'); SELECT * FROM itest2; -- UPDATE tests +-- GENERATED BY DEFAULT is not restricted. UPDATE itest1 SET a = 101 WHERE a = 1; UPDATE itest1 SET a = DEFAULT WHERE a = 2; SELECT * FROM itest1; -UPDATE itest2 SET a = 101 WHERE a = 1; -UPDATE itest2 SET a = DEFAULT WHERE a = 2; +-- GENERATED ALWAYS allows only DEFAULT. +UPDATE itest2 SET a = 101 WHERE a = 1; -- error +UPDATE itest2 SET a = DEFAULT WHERE a = 2; -- ok SELECT * FROM itest2; |