diff options
Diffstat (limited to 'src/test/regress/expected/identity.out')
| -rw-r--r-- | src/test/regress/expected/identity.out | 506 |
1 files changed, 506 insertions, 0 deletions
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out new file mode 100644 index 0000000..069a42b --- /dev/null +++ b/src/test/regress/expected/identity.out @@ -0,0 +1,506 @@ +-- 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 +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 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 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); +-- add column to empty table +ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; +INSERT INTO itest13 VALUES (1), (2), (3); +-- add column to populated table +ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; +SELECT * 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 +-- 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; |