src/test/regress/expected/identity.out - master - githubmirror/postgres

src/test/regress/expected/identity.out
928 строк39 KB
Michael Paquier
Add some regression tests for missing DDL patterns
12 дек 2024, 05:16
12 дек 2024, 05:160172b4c
-- 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 c ADD GENERATED ALWAYS AS IDENTITY;  -- error, column c does not exist
ERROR:  column "c" of relation "itest4" does not exist
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);
-- 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
-- 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;
ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY;
\d itest17
                    Unlogged table "public.itest17"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 a      | integer |           | not null | generated always as identity
 b      | text    |           |          | 
 c      | integer |           | not null | generated always as identity

\d itest17_a_seq
               Unlogged sequence "public.itest17_a_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.itest17.a

\d itest17_c_seq
               Unlogged sequence "public.itest17_c_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.itest17.c

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   |              | 
Not-null constraints:
    "itest8_f2_not_null" NOT NULL "f2"
    "itest8_f3_not_null" NOT NULL "f3"
    "itest8_f4_not_null" NOT NULL "f4"

\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
-- partitions inherit identity column and share sequence
CREATE TABLE pitest1 (f1 date NOT NULL, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1);
-- new partition
CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
INSERT into pitest1(f1, f2) VALUES ('2016-07-2', 'from pitest1');
INSERT into pitest1_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest1_p1');
-- attached partition
CREATE TABLE pitest1_p2 (f3 bigint, f2 text, f1 date NOT NULL);
INSERT INTO pitest1_p2 (f1, f2, f3) VALUES ('2016-08-2', 'before attaching', 100);
ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); -- requires NOT NULL constraint
ERROR:  column "f3" in child table "pitest1_p2" must be marked NOT NULL
ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET NOT NULL;
ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
INSERT INTO pitest1_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest1_p2');
INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-4', 'from pitest1');
-- LIKE INCLUDING on partition
CREATE TABLE pitest1_p1_like (LIKE pitest1_p1 INCLUDING IDENTITY);
INSERT into pitest1_p1_like(f1, f2) VALUES ('2016-07-2', 'from pitest1_p1_like');
SELECT tableoid::regclass, f1, f2, f3 FROM pitest1;
  tableoid  |     f1     |        f2        | f3  
------------+------------+------------------+-----
 pitest1_p1 | 07-02-2016 | from pitest1     |   1
 pitest1_p1 | 07-03-2016 | from pitest1_p1  |   2
 pitest1_p2 | 08-02-2016 | before attaching | 100
 pitest1_p2 | 08-03-2016 | from pitest1_p2  |   3
 pitest1_p2 | 08-04-2016 | from pitest1     |   4
(5 rows)

SELECT tableoid::regclass, f1, f2, f3 FROM pitest1_p1_like;
    tableoid     |     f1     |          f2          | f3 
-----------------+------------+----------------------+----
 pitest1_p1_like | 07-02-2016 | from pitest1_p1_like |  1
(1 row)

ALTER TABLE pitest1 ALTER COLUMN f3 SET DATA TYPE bigint;
SELECT tableoid::regclass, f1, f2, f3, pg_typeof(f3) FROM pitest1;
  tableoid  |     f1     |        f2        | f3  | pg_typeof 
------------+------------+------------------+-----+-----------
 pitest1_p1 | 07-02-2016 | from pitest1     |   1 | bigint
 pitest1_p1 | 07-03-2016 | from pitest1_p1  |   2 | bigint
 pitest1_p2 | 08-02-2016 | before attaching | 100 | bigint
 pitest1_p2 | 08-03-2016 | from pitest1_p2  |   3 | bigint
 pitest1_p2 | 08-04-2016 | from pitest1     |   4 | bigint
(5 rows)

SELECT tableoid::regclass, f1, f2, f3, pg_typeof(f3) FROM pitest1_p2;
  tableoid  |     f1     |        f2        | f3  | pg_typeof 
------------+------------+------------------+-----+-----------
 pitest1_p2 | 08-02-2016 | before attaching | 100 | bigint
 pitest1_p2 | 08-03-2016 | from pitest1_p2  |   3 | bigint
 pitest1_p2 | 08-04-2016 | from pitest1     |   4 | bigint
(3 rows)

-- add identity column
CREATE TABLE pitest2 (f1 date NOT NULL, f2 text) PARTITION BY RANGE (f1);
CREATE TABLE pitest2_p1 PARTITION OF pitest2 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
CREATE TABLE pitest2_p2 PARTITION OF pitest2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
INSERT into pitest2(f1, f2) VALUES ('2016-07-2', 'from pitest2');
INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-2', 'from pitest2');
ALTER TABLE pitest2 ADD COLUMN f3 int GENERATED ALWAYS AS IDENTITY;
INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest2_p1');
INSERT INTO pitest2_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest2_p2');
INSERT into pitest2(f1, f2) VALUES ('2016-07-4', 'from pitest2');
INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-4', 'from pitest2');
SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
  tableoid  |     f1     |       f2        | f3 
------------+------------+-----------------+----
 pitest2_p1 | 07-02-2016 | from pitest2    |  1
 pitest2_p1 | 07-03-2016 | from pitest2_p1 |  3
 pitest2_p1 | 07-04-2016 | from pitest2    |  5
 pitest2_p2 | 08-02-2016 | from pitest2    |  2
 pitest2_p2 | 08-03-2016 | from pitest2_p2 |  4
 pitest2_p2 | 08-04-2016 | from pitest2    |  6
(6 rows)

-- SET identity column
ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails
ERROR:  cannot change identity column of a partition
ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails
ERROR:  cannot change identity column of a partition
ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails
ERROR:  cannot change identity column of only the partitioned table
HINT:  Do not specify the ONLY keyword.
ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART;
INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200);
INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2');
INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1');
INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300);
SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
  tableoid  |     f1     |       f2        |  f3  
------------+------------+-----------------+------
 pitest2_p1 | 07-02-2016 | from pitest2    |    1
 pitest2_p1 | 07-03-2016 | from pitest2_p1 |    3
 pitest2_p1 | 07-04-2016 | from pitest2    |    5
 pitest2_p1 | 07-05-2016 | from pitest2    |  200
 pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002
 pitest2_p2 | 08-02-2016 | from pitest2    |    2
 pitest2_p2 | 08-03-2016 | from pitest2_p2 |    4
 pitest2_p2 | 08-04-2016 | from pitest2    |    6
 pitest2_p2 | 08-05-2016 | from pitest2    | 1000
 pitest2_p2 | 08-06-2016 | from pitest2_p2 |  300
(10 rows)

-- detaching a partition removes identity property
ALTER TABLE pitest2 DETACH PARTITION pitest2_p1;
INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2');
INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error
ERROR:  null value in column "f3" of relation "pitest2_p1" violates not-null constraint
DETAIL:  Failing row contains (07-07-2016, from pitest2_p1, null).
INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000);
SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
  tableoid  |     f1     |       f2        |  f3  
------------+------------+-----------------+------
 pitest2_p2 | 08-02-2016 | from pitest2    |    2
 pitest2_p2 | 08-03-2016 | from pitest2_p2 |    4
 pitest2_p2 | 08-04-2016 | from pitest2    |    6
 pitest2_p2 | 08-05-2016 | from pitest2    | 1000
 pitest2_p2 | 08-06-2016 | from pitest2_p2 |  300
 pitest2_p2 | 08-07-2016 | from pitest2    | 1004
(6 rows)

SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1;
  tableoid  |     f1     |       f2        |  f3  
------------+------------+-----------------+------
 pitest2_p1 | 07-02-2016 | from pitest2    |    1
 pitest2_p1 | 07-03-2016 | from pitest2_p1 |    3
 pitest2_p1 | 07-04-2016 | from pitest2    |    5
 pitest2_p1 | 07-05-2016 | from pitest2    |  200
 pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002
 pitest2_p1 | 07-07-2016 | from pitest2_p1 | 2000
(6 rows)

DROP TABLE pitest2_p1;
-- changing a regular column to identity column in a partitioned table
CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1);
CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1);
INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2);
-- fails, changing only a partition not allowed
ALTER TABLE pitest3_p1
            ALTER COLUMN f3 SET NOT NULL,
            ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
ERROR:  cannot add identity to a column of a partition
-- fails, changing only the partitioned table not allowed
BEGIN;
ALTER TABLE pitest3_p1 ALTER COLUMN f3 SET NOT NULL;
ALTER TABLE ONLY pitest3
            ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
ERROR:  cannot add identity to a column of only the partitioned table
HINT:  Do not specify the ONLY keyword.
ROLLBACK;
ALTER TABLE pitest3
            ALTER COLUMN f3 SET NOT NULL,
            ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3');
INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1');
SELECT tableoid::regclass, f1, f2, f3 FROM pitest3;
  tableoid  |     f1     |       f2        | f3 
------------+------------+-----------------+----
 pitest3_p1 | 07-02-2016 | from pitest3    |  1
 pitest3_p1 | 07-03-2016 | from pitest3_p1 |  2
 pitest3_p1 | 07-04-2016 | from pitest3    |  3
 pitest3_p1 | 07-05-2016 | from pitest3_p1 |  4
(4 rows)

-- changing an identity column to a non-identity column in a partitioned table
ALTER TABLE pitest3_p1 ALTER COLUMN f3 DROP IDENTITY; -- fails
ERROR:  cannot drop identity from a column of a partition
ALTER TABLE ONLY pitest3 ALTER COLUMN f3 DROP IDENTITY; -- fails
ERROR:  cannot drop identity from a column of only the partitioned table
HINT:  Do not specify the ONLY keyword.
ALTER TABLE pitest3 ALTER COLUMN f3 DROP IDENTITY;
INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); -- fails
ERROR:  null value in column "f3" of relation "pitest3_p1" violates not-null constraint
DETAIL:  Failing row contains (07-04-2016, from pitest3, null).
INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); -- fails
ERROR:  null value in column "f3" of relation "pitest3_p1" violates not-null constraint
DETAIL:  Failing row contains (07-05-2016, from pitest3_p1, null).
INSERT into pitest3(f1, f2, f3) VALUES ('2016-07-6', 'from pitest3', 5);
INSERT into pitest3_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest3_p1', 6);
SELECT tableoid::regclass, f1, f2, f3 FROM pitest3;
  tableoid  |     f1     |       f2        | f3 
------------+------------+-----------------+----
 pitest3_p1 | 07-02-2016 | from pitest3    |  1
 pitest3_p1 | 07-03-2016 | from pitest3_p1 |  2
 pitest3_p1 | 07-04-2016 | from pitest3    |  3
 pitest3_p1 | 07-05-2016 | from pitest3_p1 |  4
 pitest3_p1 | 07-06-2016 | from pitest3    |  5
 pitest3_p1 | 07-07-2016 | from pitest3_p1 |  6
(6 rows)

-- Changing NOT NULL constraint of identity columns is not allowed
ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL;
ERROR:  column "f3" of relation "pitest1_p1" is an identity column
ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL;
ERROR:  column "f3" of relation "pitest1" is an identity column
-- Identity columns have their own default
ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000;
ERROR:  column "f3" of relation "pitest1_p2" is an identity column
ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000;
ERROR:  column "f3" of relation "pitest1" is an identity column
-- Adding identity to an identity column is not allowed
ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY;
ERROR:  cannot add identity to a column of a partition
ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY;
ERROR:  column "f3" of relation "pitest1" is already an identity column
-- partitions with their own identity columns are not allowed, even if the
-- partitioned table does not have an identity column.
CREATE TABLE pitest1_pfail PARTITION OF pitest1 (
    f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
) FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
ERROR:  identity columns are not supported on partitions
CREATE TABLE pitest_pfail PARTITION OF pitest3 (
    f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
ERROR:  identity columns are not supported on partitions
CREATE TABLE pitest1_pfail (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS IDENTITY);
ALTER TABLE pitest1 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
ERROR:  table "pitest1_pfail" being attached contains an identity column "f3"
DETAIL:  The new partition may not contain an identity column.
ALTER TABLE pitest3 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
ERROR:  table "pitest1_pfail" being attached contains an identity column "f3"
DETAIL:  The new partition may not contain an identity column.
DROP TABLE pitest1_pfail;
DROP TABLE pitest3;
-- 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);
ALTER SEQUENCE identity_dump_unlogged_a_seq SET LOGGED;
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
 identity_dump_unlogged       | u
 identity_dump_unlogged_a_seq | p
(4 rows)