path: root/src/test/regress/expected/identity.out

-- 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)

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 ORDER BY a;
 a  | b 
----+---
  7 | 
 12 | 
(2 rows)

SELECT * FROM itest4;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

-- OVERRIDING tests
INSERT INTO itest1 VALUES (10, 'xyz');
INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');
SELECT * FROM itest1 ORDER BY a;
 a  |  b  
----+-----
  1 | 
  2 | 
  3 | xyz
 10 | xyz
(4 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');
SELECT * FROM itest2 ORDER BY a;
 a  |  b  
----+-----
  1 | 
  2 | 
 10 | xyz
(3 rows)

-- UPDATE tests
UPDATE itest1 SET a = 101 WHERE a = 1;
ERROR:  could not plan this distributed update
DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
UPDATE itest1 SET a = DEFAULT WHERE a = 2;
ERROR:  could not plan this distributed update
DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
SELECT * FROM itest1 ORDER BY a;
 a  |  b  
----+-----
  1 | 
  2 | 
  3 | xyz
 10 | xyz
(4 rows)

UPDATE itest2 SET a = 101 WHERE a = 1;
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;
ERROR:  could not plan this distributed update
DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
SELECT * FROM itest2 ORDER BY a;
 a  |  b  
----+-----
  1 | 
  2 | 
 10 | xyz
(3 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" 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 ORDER BY a;
 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)

-- 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
-- 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 ORDER BY a;
  a  | b 
-----+---
   1 | 
 100 | 
 102 | 
(3 rows)

SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';
 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_user1;
CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);
GRANT SELECT, INSERT ON itest8 TO regress_user1;
SET ROLE regress_user1;
INSERT INTO itest8 DEFAULT VALUES;
SELECT * FROM itest8;
 a | b 
---+---
 1 | 
(1 row)

RESET ROLE;
DROP TABLE itest8;
DROP USER regress_user1;