1-- sanity check of system catalog 2SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd'); 3 attrelid | attname | attidentity 4----------+---------+------------- 5(0 rows) 6 7CREATE TABLE itest1 (a int generated by default as identity, b text); 8CREATE TABLE itest2 (a bigint generated always as identity, b text); 9CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text); 10ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error 11ERROR: column "a" of relation "itest3" is already an identity column 12SELECT 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; 13 table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle 14------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+---------------- 15 itest1 | a | | NO | YES | BY DEFAULT | 1 | 1 | 2147483647 | 1 | NO 16 itest1 | b | | YES | NO | | | | | | NO 17 itest2 | a | | NO | YES | ALWAYS | 1 | 1 | 9223372036854775807 | 1 | NO 18 itest2 | b | | YES | NO | | | | | | NO 19 itest3 | a | | NO | YES | BY DEFAULT | 7 | 5 | 32767 | 1 | NO 20 itest3 | b | | YES | NO | | | | | | NO 21(6 rows) 22 23-- internal sequences should not be shown here 24SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; 25 sequence_name 26--------------- 27(0 rows) 28 29SELECT pg_get_serial_sequence('itest1', 'a'); 30 pg_get_serial_sequence 31------------------------ 32 public.itest1_a_seq 33(1 row) 34 35\d itest1_a_seq 36 Sequence "public.itest1_a_seq" 37 Type | Start | Minimum | Maximum | Increment | Cycles? | Cache 38---------+-------+---------+------------+-----------+---------+------- 39 integer | 1 | 1 | 2147483647 | 1 | no | 1 40Sequence for identity column: public.itest1.a 41 42CREATE TABLE itest4 (a int, b text); 43ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL 44ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added 45ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; 46ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok 47ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed 48ERROR: column "a" of relation "itest4" is an identity column 49ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set 50ERROR: column "a" of relation "itest4" is already an identity column 51ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type 52ERROR: identity column type must be smallint, integer, or bigint 53-- for later 54ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; 55-- invalid column type 56CREATE TABLE itest_err_1 (a text generated by default as identity); 57ERROR: identity column type must be smallint, integer, or bigint 58-- duplicate identity 59CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity); 60ERROR: multiple identity specifications for column "a" of table "itest_err_2" 61LINE 1: ...E itest_err_2 (a int generated always as identity generated ... 62 ^ 63-- cannot have default and identity 64CREATE TABLE itest_err_3 (a int default 5 generated by default as identity); 65ERROR: both default and identity specified for column "a" of table "itest_err_3" 66LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau... 67 ^ 68-- cannot combine serial and identity 69CREATE TABLE itest_err_4 (a serial generated by default as identity); 70ERROR: both default and identity specified for column "a" of table "itest_err_4" 71INSERT INTO itest1 DEFAULT VALUES; 72INSERT INTO itest1 DEFAULT VALUES; 73INSERT INTO itest2 DEFAULT VALUES; 74INSERT INTO itest2 DEFAULT VALUES; 75INSERT INTO itest3 DEFAULT VALUES; 76INSERT INTO itest3 DEFAULT VALUES; 77INSERT INTO itest4 DEFAULT VALUES; 78INSERT INTO itest4 DEFAULT VALUES; 79SELECT * FROM itest1; 80 a | b 81---+--- 82 1 | 83 2 | 84(2 rows) 85 86SELECT * FROM itest2; 87 a | b 88---+--- 89 1 | 90 2 | 91(2 rows) 92 93SELECT * FROM itest3; 94 a | b 95----+--- 96 7 | 97 12 | 98(2 rows) 99 100SELECT * FROM itest4; 101 a | b 102---+--- 103 1 | 104 2 | 105(2 rows) 106 107-- VALUES RTEs 108INSERT INTO itest3 VALUES (DEFAULT, 'a'); 109INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); 110SELECT * FROM itest3; 111 a | b 112----+--- 113 7 | 114 12 | 115 17 | a 116 22 | b 117 27 | c 118(5 rows) 119 120-- OVERRIDING tests 121INSERT INTO itest1 VALUES (10, 'xyz'); 122INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); 123SELECT * FROM itest1; 124 a | b 125----+----- 126 1 | 127 2 | 128 10 | xyz 129 3 | xyz 130(4 rows) 131 132INSERT INTO itest2 VALUES (10, 'xyz'); 133ERROR: cannot insert into column "a" 134DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. 135HINT: Use OVERRIDING SYSTEM VALUE to override. 136INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); 137SELECT * FROM itest2; 138 a | b 139----+----- 140 1 | 141 2 | 142 10 | xyz 143(3 rows) 144 145-- UPDATE tests 146UPDATE itest1 SET a = 101 WHERE a = 1; 147UPDATE itest1 SET a = DEFAULT WHERE a = 2; 148SELECT * FROM itest1; 149 a | b 150-----+----- 151 10 | xyz 152 3 | xyz 153 101 | 154 4 | 155(4 rows) 156 157UPDATE itest2 SET a = 101 WHERE a = 1; 158ERROR: column "a" can only be updated to DEFAULT 159DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. 160UPDATE itest2 SET a = DEFAULT WHERE a = 2; 161SELECT * FROM itest2; 162 a | b 163----+----- 164 1 | 165 10 | xyz 166 3 | 167(3 rows) 168 169-- COPY tests 170CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint); 171COPY itest9 FROM stdin; 172COPY itest9 (b, c) FROM stdin; 173SELECT * FROM itest9 ORDER BY c; 174 a | b | c 175-----+------+----- 176 100 | foo | 200 177 101 | bar | 201 178 1 | foo2 | 202 179 2 | bar2 | 203 180(4 rows) 181 182-- DROP IDENTITY tests 183ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; 184ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error 185ERROR: column "a" of relation "itest4" is not an identity column 186ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop 187NOTICE: column "a" of relation "itest4" is not an identity column, skipping 188INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped 189ERROR: null value in column "a" violates not-null constraint 190DETAIL: Failing row contains (null, ). 191ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; 192INSERT INTO itest4 DEFAULT VALUES; 193SELECT * FROM itest4; 194 a | b 195---+--- 196 1 | 197 2 | 198 | 199(3 rows) 200 201-- check that sequence is removed 202SELECT sequence_name FROM itest4_a_seq; 203ERROR: relation "itest4_a_seq" does not exist 204LINE 1: SELECT sequence_name FROM itest4_a_seq; 205 ^ 206-- test views 207CREATE TABLE itest10 (a int generated by default as identity, b text); 208CREATE TABLE itest11 (a int generated always as identity, b text); 209CREATE VIEW itestv10 AS SELECT * FROM itest10; 210CREATE VIEW itestv11 AS SELECT * FROM itest11; 211INSERT INTO itestv10 DEFAULT VALUES; 212INSERT INTO itestv10 DEFAULT VALUES; 213INSERT INTO itestv11 DEFAULT VALUES; 214INSERT INTO itestv11 DEFAULT VALUES; 215SELECT * FROM itestv10; 216 a | b 217---+--- 218 1 | 219 2 | 220(2 rows) 221 222SELECT * FROM itestv11; 223 a | b 224---+--- 225 1 | 226 2 | 227(2 rows) 228 229INSERT INTO itestv10 VALUES (10, 'xyz'); 230INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); 231SELECT * FROM itestv10; 232 a | b 233----+----- 234 1 | 235 2 | 236 10 | xyz 237 3 | xyz 238(4 rows) 239 240INSERT INTO itestv11 VALUES (10, 'xyz'); 241ERROR: cannot insert into column "a" 242DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. 243HINT: Use OVERRIDING SYSTEM VALUE to override. 244INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); 245SELECT * FROM itestv11; 246 a | b 247----+----- 248 1 | 249 2 | 250 11 | xyz 251(3 rows) 252 253-- ADD COLUMN 254CREATE TABLE itest13 (a int); 255-- add column to empty table 256ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; 257INSERT INTO itest13 VALUES (1), (2), (3); 258-- add column to populated table 259ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; 260SELECT * FROM itest13; 261 a | b | c 262---+---+--- 263 1 | 1 | 1 264 2 | 2 | 2 265 3 | 3 | 3 266(3 rows) 267 268-- various ALTER COLUMN tests 269-- fail, not allowed for identity columns 270ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; 271ERROR: column "a" of relation "itest1" is an identity column 272-- fail, not allowed, already has a default 273CREATE TABLE itest5 (a serial, b text); 274ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 275ERROR: column "a" of relation "itest5" already has a default value 276ALTER TABLE itest3 ALTER COLUMN a TYPE int; 277SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; 278 seqtypid 279---------- 280 integer 281(1 row) 282 283\d itest3 284 Table "public.itest3" 285 Column | Type | Collation | Nullable | Default 286--------+---------+-----------+----------+---------------------------------- 287 a | integer | | not null | generated by default as identity 288 b | text | | | 289 290ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error 291ERROR: identity column type must be smallint, integer, or bigint 292-- ALTER COLUMN ... SET 293CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); 294INSERT INTO itest6 DEFAULT VALUES; 295ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; 296INSERT INTO itest6 DEFAULT VALUES; 297INSERT INTO itest6 DEFAULT VALUES; 298SELECT * FROM itest6; 299 a | b 300-----+--- 301 1 | 302 100 | 303 102 | 304(3 rows) 305 306SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2; 307 table_name | column_name | is_identity | identity_generation 308------------+-------------+-------------+--------------------- 309 itest6 | a | YES | BY DEFAULT 310 itest6 | b | NO | 311(2 rows) 312 313ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity 314ERROR: column "b" of relation "itest6" is not an identity column 315-- prohibited direct modification of sequence 316ALTER SEQUENCE itest6_a_seq OWNED BY NONE; 317ERROR: cannot change ownership of identity sequence 318DETAIL: Sequence "itest6_a_seq" is linked to table "itest6". 319-- inheritance 320CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); 321INSERT INTO itest7 DEFAULT VALUES; 322SELECT * FROM itest7; 323 a 324--- 325 1 326(1 row) 327 328-- identity property is not inherited 329CREATE TABLE itest7a (b text) INHERITS (itest7); 330-- make column identity in child table 331CREATE TABLE itest7b (a int); 332CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); 333NOTICE: merging column "a" with inherited definition 334INSERT INTO itest7c DEFAULT VALUES; 335SELECT * FROM itest7c; 336 a 337--- 338 1 339(1 row) 340 341CREATE TABLE itest7d (a int not null); 342CREATE TABLE itest7e () INHERITS (itest7d); 343ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 344ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error 345ERROR: cannot recursively add identity column to table that has child tables 346SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; 347 table_name | column_name | is_nullable | is_identity | identity_generation 348------------+-------------+-------------+-------------+--------------------- 349 itest7 | a | NO | YES | ALWAYS 350 itest7a | a | NO | NO | 351 itest7a | b | YES | NO | 352 itest7b | a | YES | NO | 353 itest7c | a | NO | YES | ALWAYS 354 itest7d | a | NO | YES | ALWAYS 355 itest7e | a | NO | NO | 356(7 rows) 357 358-- These ALTER TABLE variants will not recurse. 359ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; 360ALTER TABLE itest7 ALTER COLUMN a RESTART; 361ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; 362-- privileges 363CREATE USER regress_user1; 364CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); 365GRANT SELECT, INSERT ON itest8 TO regress_user1; 366SET ROLE regress_user1; 367INSERT INTO itest8 DEFAULT VALUES; 368SELECT * FROM itest8; 369 a | b 370---+--- 371 1 | 372(1 row) 373 374RESET ROLE; 375DROP TABLE itest8; 376DROP USER regress_user1; 377-- typed tables (currently not supported) 378CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); 379CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error 380ERROR: identity columns are not supported on typed tables 381DROP TYPE itest_type CASCADE; 382-- table partitions (currently not supported) 383CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); 384CREATE TABLE itest_child PARTITION OF itest_parent ( 385 f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY 386) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error 387ERROR: identity columns are not supported on partitions 388DROP TABLE itest_parent; 389-- Identity columns must be NOT NULL (cf bug #16913) 390CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail 391ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15" 392LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); 393 ^ 394CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail 395ERROR: conflicting NULL/NOT NULL declarations for column "id" of table "itest15" 396LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID... 397 ^ 398CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL); 399DROP TABLE itest15; 400CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY); 401DROP TABLE itest15; 402