| -- sanity check of system catalog |
| SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd'); |
| attrelid | attname | attidentity |
| ----------+---------+------------- |
| (0 rows) |
| |
| CREATE TABLE itest1 (a int generated by default as identity, b text); |
| CREATE TABLE itest2 (a bigint generated always as identity, b text); |
| CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text); |
| ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error |
| ERROR: column "a" of relation "itest3" is already an identity column |
| SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2; |
| table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle |
| ------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+---------------- |
| itest1 | a | | NO | YES | BY DEFAULT | 1 | 1 | 2147483647 | 1 | NO |
| itest1 | b | | YES | NO | | | | | | NO |
| itest2 | a | | NO | YES | ALWAYS | 1 | 1 | 9223372036854775807 | 1 | NO |
| itest2 | b | | YES | NO | | | | | | NO |
| itest3 | a | | NO | YES | BY DEFAULT | 7 | 5 | 32767 | 1 | NO |
| itest3 | b | | YES | NO | | | | | | NO |
| (6 rows) |
| |
| -- internal sequences should not be shown here |
| SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; |
| sequence_name |
| --------------- |
| (0 rows) |
| |
| SELECT pg_get_serial_sequence('itest1', 'a'); |
| pg_get_serial_sequence |
| ------------------------ |
| public.itest1_a_seq |
| (1 row) |
| |
| \d itest1_a_seq |
| Sequence "public.itest1_a_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Sequence for identity column: public.itest1.a |
| |
| CREATE TABLE itest4 (a int, b text); |
| ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL |
| ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added |
| ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; |
| ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok |
| ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed |
| ERROR: column "a" of relation "itest4" is an identity column |
| ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set |
| ERROR: column "a" of relation "itest4" is already an identity column |
| ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type |
| ERROR: identity column type must be smallint, integer, or bigint |
| -- for later |
| ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; |
| -- invalid column type |
| CREATE TABLE itest_err_1 (a text generated by default as identity); |
| ERROR: identity column type must be smallint, integer, or bigint |
| -- duplicate identity |
| CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity); |
| ERROR: multiple identity specifications for column "a" of table "itest_err_2" |
| LINE 1: ...E itest_err_2 (a int generated always as identity generated ... |
| ^ |
| -- cannot have default and identity |
| CREATE TABLE itest_err_3 (a int default 5 generated by default as identity); |
| ERROR: both default and identity specified for column "a" of table "itest_err_3" |
| LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau... |
| ^ |
| -- cannot combine serial and identity |
| CREATE TABLE itest_err_4 (a serial generated by default as identity); |
| ERROR: both default and identity specified for column "a" of table "itest_err_4" |
| INSERT INTO itest1 DEFAULT VALUES; |
| INSERT INTO itest1 DEFAULT VALUES; |
| INSERT INTO itest2 DEFAULT VALUES; |
| INSERT INTO itest2 DEFAULT VALUES; |
| INSERT INTO itest3 DEFAULT VALUES; |
| INSERT INTO itest3 DEFAULT VALUES; |
| INSERT INTO itest4 DEFAULT VALUES; |
| INSERT INTO itest4 DEFAULT VALUES; |
| SELECT * FROM itest1; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| (2 rows) |
| |
| SELECT * FROM itest2; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| (2 rows) |
| |
| SELECT * FROM itest3; |
| a | b |
| ----+--- |
| 7 | |
| 12 | |
| (2 rows) |
| |
| SELECT * FROM itest4; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| (2 rows) |
| |
| -- VALUES RTEs |
| CREATE TABLE itest5 (a int generated always as identity, b text); |
| INSERT INTO itest5 VALUES (1, 'a'); -- error |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| INSERT INTO itest5 VALUES (DEFAULT, 'a'); -- ok |
| INSERT INTO itest5 VALUES (2, 'b'), (3, 'c'); -- error |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c'); -- error |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c'); -- error |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); -- ok |
| INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa'); |
| INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc'); |
| INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee'); |
| INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg'); |
| INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii'); |
| INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa'); |
| INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc'); |
| INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee'); |
| INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg'); |
| INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii'); |
| SELECT * FROM itest5; |
| a | b |
| ----+----- |
| 1 | a |
| 2 | b |
| 3 | c |
| -1 | aa |
| -2 | bb |
| -3 | cc |
| 4 | dd |
| -4 | ee |
| -5 | ff |
| 5 | gg |
| 6 | hh |
| 7 | ii |
| 8 | aaa |
| 9 | bbb |
| 10 | ccc |
| 11 | ddd |
| 12 | eee |
| 13 | fff |
| 14 | ggg |
| 15 | hhh |
| 16 | iii |
| (21 rows) |
| |
| DROP TABLE itest5; |
| INSERT INTO itest3 VALUES (DEFAULT, 'a'); |
| INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); |
| SELECT * FROM itest3; |
| a | b |
| ----+--- |
| 7 | |
| 12 | |
| 17 | a |
| 22 | b |
| 27 | c |
| (5 rows) |
| |
| -- OVERRIDING tests |
| -- GENERATED BY DEFAULT |
| -- This inserts the row as presented: |
| INSERT INTO itest1 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 |
| (5 rows) |
| |
| -- GENERATED ALWAYS |
| -- This is an error: |
| INSERT INTO itest2 VALUES (10, 'xyz'); |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| -- 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 | |
| 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 | |
| (5 rows) |
| |
| -- 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; -- ok |
| SELECT * FROM itest2; |
| a | b |
| ----+----- |
| 1 | |
| 20 | xyz |
| 3 | xyz |
| 4 | |
| (4 rows) |
| |
| -- COPY tests |
| CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint); |
| COPY itest9 FROM stdin; |
| COPY itest9 (b, c) FROM stdin; |
| SELECT * FROM itest9 ORDER BY c; |
| a | b | c |
| -----+------+----- |
| 100 | foo | 200 |
| 101 | bar | 201 |
| 1 | foo2 | 202 |
| 2 | bar2 | 203 |
| (4 rows) |
| |
| -- DROP IDENTITY tests |
| ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; |
| ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error |
| ERROR: column "a" of relation "itest4" is not an identity column |
| ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop |
| NOTICE: column "a" of relation "itest4" is not an identity column, skipping |
| INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped |
| ERROR: null value in column "a" of relation "itest4" violates not-null constraint |
| DETAIL: Failing row contains (null, ). |
| ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; |
| INSERT INTO itest4 DEFAULT VALUES; |
| SELECT * FROM itest4; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| | |
| (3 rows) |
| |
| -- check that sequence is removed |
| SELECT sequence_name FROM itest4_a_seq; |
| ERROR: relation "itest4_a_seq" does not exist |
| LINE 1: SELECT sequence_name FROM itest4_a_seq; |
| ^ |
| -- test views |
| CREATE TABLE itest10 (a int generated by default as identity, b text); |
| CREATE TABLE itest11 (a int generated always as identity, b text); |
| CREATE VIEW itestv10 AS SELECT * FROM itest10; |
| CREATE VIEW itestv11 AS SELECT * FROM itest11; |
| INSERT INTO itestv10 DEFAULT VALUES; |
| INSERT INTO itestv10 DEFAULT VALUES; |
| INSERT INTO itestv11 DEFAULT VALUES; |
| INSERT INTO itestv11 DEFAULT VALUES; |
| SELECT * FROM itestv10; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| (2 rows) |
| |
| SELECT * FROM itestv11; |
| a | b |
| ---+--- |
| 1 | |
| 2 | |
| (2 rows) |
| |
| INSERT INTO itestv10 VALUES (10, 'xyz'); |
| INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); |
| SELECT * FROM itestv10; |
| a | b |
| ----+----- |
| 1 | |
| 2 | |
| 10 | xyz |
| 3 | xyz |
| (4 rows) |
| |
| INSERT INTO itestv11 VALUES (10, 'xyz'); |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); |
| SELECT * FROM itestv11; |
| a | b |
| ----+----- |
| 1 | |
| 2 | |
| 11 | xyz |
| (3 rows) |
| |
| DROP VIEW itestv10, itestv11; |
| -- ADD COLUMN |
| CREATE TABLE itest13 (a int, dummy int) distributed by (dummy); |
| -- add column to empty table |
| ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; |
| INSERT INTO itest13 VALUES (1,1), (2,1), (3,1); |
| -- add column to populated table |
| ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; |
| SELECT a,b,c FROM itest13; |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| (3 rows) |
| |
| -- various ALTER COLUMN tests |
| -- fail, not allowed for identity columns |
| ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; |
| ERROR: column "a" of relation "itest1" is an identity column |
| -- fail, not allowed, already has a default |
| CREATE TABLE itest5 (a serial, b text); |
| ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; |
| ERROR: column "a" of relation "itest5" already has a default value |
| ALTER TABLE itest3 ALTER COLUMN a TYPE int; |
| SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; |
| seqtypid |
| ---------- |
| integer |
| (1 row) |
| |
| \d itest3 |
| Table "public.itest3" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+---------------------------------- |
| a | integer | | not null | generated by default as identity |
| b | text | | | |
| |
| ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error |
| ERROR: identity column type must be smallint, integer, or bigint |
| -- check that unlogged propagates to sequence |
| CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text); |
| ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; |
| ERROR: unlogged sequences are not supported |
| ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY; |
| ERROR: unlogged sequences are not supported |
| \d itest17 |
| Unlogged table "public.itest17" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+--------- |
| a | integer | | not null | |
| b | text | | | |
| Distributed by: (a) |
| |
| \d itest17_a_seq |
| \d itest17_c_seq |
| CREATE TABLE itest18 (a int NOT NULL, b text); |
| ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; |
| \d itest18 |
| Unlogged table "public.itest18" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+------------------------------ |
| a | integer | | not null | generated always as identity |
| b | text | | | |
| |
| \d itest18_a_seq |
| Unlogged sequence "public.itest18_a_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Sequence for identity column: public.itest18.a |
| |
| ALTER TABLE itest18 SET LOGGED; |
| \d itest18 |
| Table "public.itest18" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+------------------------------ |
| a | integer | | not null | generated always as identity |
| b | text | | | |
| |
| \d itest18_a_seq |
| Sequence "public.itest18_a_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Sequence for identity column: public.itest18.a |
| |
| ALTER TABLE itest18 SET UNLOGGED; |
| \d itest18 |
| Unlogged table "public.itest18" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+------------------------------ |
| a | integer | | not null | generated always as identity |
| b | text | | | |
| |
| \d itest18_a_seq |
| Unlogged sequence "public.itest18_a_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Sequence for identity column: public.itest18.a |
| |
| -- kinda silly to change property in the same command, but it should work |
| ALTER TABLE itest3 |
| ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, |
| ALTER COLUMN c SET GENERATED ALWAYS; |
| \d itest3 |
| Table "public.itest3" |
| Column | Type | Collation | Nullable | Default |
| --------+---------+-----------+----------+---------------------------------- |
| a | integer | | not null | generated by default as identity |
| b | text | | | |
| c | integer | | not null | generated always as identity |
| |
| -- ALTER COLUMN ... SET |
| CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); |
| INSERT INTO itest6 DEFAULT VALUES; |
| ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; |
| INSERT INTO itest6 DEFAULT VALUES; |
| INSERT INTO itest6 DEFAULT VALUES; |
| SELECT * FROM itest6; |
| a | b |
| -----+--- |
| 1 | |
| 100 | |
| 102 | |
| (3 rows) |
| |
| SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2; |
| table_name | column_name | is_identity | identity_generation |
| ------------+-------------+-------------+--------------------- |
| itest6 | a | YES | BY DEFAULT |
| itest6 | b | NO | |
| (2 rows) |
| |
| ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity |
| ERROR: column "b" of relation "itest6" is not an identity column |
| -- prohibited direct modification of sequence |
| ALTER SEQUENCE itest6_a_seq OWNED BY NONE; |
| ERROR: cannot change ownership of identity sequence |
| DETAIL: Sequence "itest6_a_seq" is linked to table "itest6". |
| -- inheritance |
| CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); |
| INSERT INTO itest7 DEFAULT VALUES; |
| SELECT * FROM itest7; |
| a |
| --- |
| 1 |
| (1 row) |
| |
| -- identity property is not inherited |
| CREATE TABLE itest7a (b text) INHERITS (itest7); |
| -- make column identity in child table |
| CREATE TABLE itest7b (a int); |
| CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); |
| NOTICE: merging column "a" with inherited definition |
| INSERT INTO itest7c DEFAULT VALUES; |
| SELECT * FROM itest7c; |
| a |
| --- |
| 1 |
| (1 row) |
| |
| CREATE TABLE itest7d (a int not null); |
| CREATE TABLE itest7e () INHERITS (itest7d); |
| ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; |
| ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error |
| ERROR: cannot recursively add identity column to table that has child tables |
| SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; |
| table_name | column_name | is_nullable | is_identity | identity_generation |
| ------------+-------------+-------------+-------------+--------------------- |
| itest7 | a | NO | YES | ALWAYS |
| itest7a | a | NO | NO | |
| itest7a | b | YES | NO | |
| itest7b | a | YES | NO | |
| itest7c | a | NO | YES | ALWAYS |
| itest7d | a | NO | YES | ALWAYS |
| itest7e | a | NO | NO | |
| (7 rows) |
| |
| -- These ALTER TABLE variants will not recurse. |
| ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; |
| ALTER TABLE itest7 ALTER COLUMN a RESTART; |
| ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; |
| -- privileges |
| CREATE USER regress_identity_user1; |
| CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); |
| GRANT SELECT, INSERT ON itest8 TO regress_identity_user1; |
| SET ROLE regress_identity_user1; |
| INSERT INTO itest8 DEFAULT VALUES; |
| SELECT * FROM itest8; |
| a | b |
| ---+--- |
| 1 | |
| (1 row) |
| |
| RESET ROLE; |
| DROP TABLE itest8; |
| DROP USER regress_identity_user1; |
| -- multiple steps in ALTER TABLE |
| CREATE TABLE itest8 (f1 int); |
| ALTER TABLE itest8 |
| ADD COLUMN f2 int NOT NULL, |
| ALTER COLUMN f2 ADD GENERATED ALWAYS AS IDENTITY; |
| ALTER TABLE itest8 |
| ADD COLUMN f3 int NOT NULL, |
| ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY, |
| ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT 10; |
| ALTER TABLE itest8 |
| ADD COLUMN f4 int; |
| ALTER TABLE itest8 |
| ALTER COLUMN f4 SET NOT NULL, |
| ALTER COLUMN f4 ADD GENERATED ALWAYS AS IDENTITY, |
| ALTER COLUMN f4 SET DATA TYPE bigint; |
| ALTER TABLE itest8 |
| ADD COLUMN f5 int GENERATED ALWAYS AS IDENTITY; |
| ALTER TABLE itest8 |
| ALTER COLUMN f5 DROP IDENTITY, |
| ALTER COLUMN f5 DROP NOT NULL, |
| ALTER COLUMN f5 SET DATA TYPE bigint; |
| INSERT INTO itest8 VALUES(0), (1); |
| -- This does not work when the table isn't empty. That's intentional, |
| -- since ADD GENERATED should only affect later insertions: |
| ALTER TABLE itest8 |
| ADD COLUMN f22 int NOT NULL, |
| ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY; |
| ERROR: column "f22" of relation "itest8" contains null values |
| TABLE itest8; |
| f1 | f2 | f3 | f4 | f5 |
| ----+----+----+----+---- |
| 0 | 1 | 1 | 1 | |
| 1 | 2 | 11 | 2 | |
| (2 rows) |
| |
| \d+ itest8 |
| Table "public.itest8" |
| Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| --------+---------+-----------+----------+----------------------------------+---------+--------------+------------- |
| f1 | integer | | | | plain | | |
| f2 | integer | | not null | generated always as identity | plain | | |
| f3 | integer | | not null | generated by default as identity | plain | | |
| f4 | bigint | | not null | generated always as identity | plain | | |
| f5 | bigint | | | | plain | | |
| |
| \d itest8_f2_seq |
| Sequence "public.itest8_f2_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Sequence for identity column: public.itest8.f2 |
| |
| \d itest8_f3_seq |
| Sequence "public.itest8_f3_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 10 | no | 1 |
| Sequence for identity column: public.itest8.f3 |
| |
| \d itest8_f4_seq |
| Sequence "public.itest8_f4_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| --------+-------+---------+---------------------+-----------+---------+------- |
| bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 |
| Sequence for identity column: public.itest8.f4 |
| |
| \d itest8_f5_seq |
| DROP TABLE itest8; |
| -- typed tables (currently not supported) |
| CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); |
| CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error |
| ERROR: identity columns are not supported on typed tables |
| DROP TYPE itest_type CASCADE; |
| -- table partitions (currently not supported) |
| CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); |
| CREATE TABLE itest_child PARTITION OF itest_parent ( |
| f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY |
| ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error |
| ERROR: identity columns are not supported on partitions |
| DROP TABLE itest_parent; |
| -- test that sequence of half-dropped serial column is properly ignored |
| CREATE TABLE itest14 (id serial); |
| ALTER TABLE itest14 ALTER id DROP DEFAULT; |
| ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY; |
| INSERT INTO itest14 (id) VALUES (DEFAULT); |
| -- Identity columns must be NOT NULL (cf bug #16913) |
| CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail |
| ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15" |
| LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); |
| ^ |
| CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail |
| ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15" |
| LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID... |
| ^ |
| CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL); |
| DROP TABLE itest15; |
| CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY); |
| DROP TABLE itest15; |
| -- MERGE tests |
| CREATE TABLE itest15 (a int GENERATED ALWAYS AS IDENTITY, b text); |
| CREATE TABLE itest16 (a int GENERATED BY DEFAULT AS IDENTITY, b text); |
| MERGE INTO itest15 t |
| USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) VALUES (s.s_a, s.s_b); |
| ERROR: cannot insert a non-DEFAULT value into column "a" |
| DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. |
| HINT: Use OVERRIDING SYSTEM VALUE to override. |
| -- Used to fail, but now it works and ignores the user supplied value |
| MERGE INTO itest15 t |
| USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b); |
| MERGE INTO itest15 t |
| USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b); |
| MERGE INTO itest16 t |
| USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) VALUES (s.s_a, s.s_b); |
| MERGE INTO itest16 t |
| USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b); |
| MERGE INTO itest16 t |
| USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s |
| ON t.a = s.s_a |
| WHEN NOT MATCHED THEN |
| INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b); |
| SELECT * FROM itest15; |
| a | b |
| ----+------------------- |
| 1 | inserted by merge |
| 30 | inserted by merge |
| (2 rows) |
| |
| SELECT * FROM itest16; |
| a | b |
| ----+------------------- |
| 10 | inserted by merge |
| 1 | inserted by merge |
| 30 | inserted by merge |
| (3 rows) |
| |
| DROP TABLE itest15; |
| DROP TABLE itest16; |
| -- For testing of pg_dump and pg_upgrade, leave behind some identity |
| -- sequences whose logged-ness doesn't match their owning table's. |
| CREATE TABLE identity_dump_logged (a INT GENERATED ALWAYS AS IDENTITY); |
| ALTER SEQUENCE identity_dump_logged_a_seq SET UNLOGGED; |
| CREATE UNLOGGED TABLE identity_dump_unlogged (a INT GENERATED ALWAYS AS IDENTITY); |
| ERROR: unlogged sequences are not supported |
| ALTER SEQUENCE identity_dump_unlogged_a_seq SET LOGGED; |
| ERROR: relation "identity_dump_unlogged_a_seq" does not exist |
| SELECT relname, relpersistence FROM pg_class |
| WHERE relname ~ '^identity_dump_' ORDER BY 1; |
| relname | relpersistence |
| ----------------------------+---------------- |
| identity_dump_logged | p |
| identity_dump_logged_a_seq | u |
| (2 rows) |
| |
