1-- sanity check of system catalog 2SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd'); 3 4 5CREATE TABLE itest1 (a int generated by default as identity, b text); 6CREATE TABLE itest2 (a bigint generated always as identity, b text); 7CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text); 8ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error 9 10SELECT 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; 11 12-- internal sequences should not be shown here 13SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; 14 15SELECT pg_get_serial_sequence('itest1', 'a'); 16 17\d itest1_a_seq 18 19CREATE TABLE itest4 (a int, b text); 20ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL 21ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; 22ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok 23ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed 24ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set 25ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type 26 27-- for later 28ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; 29 30-- invalid column type 31CREATE TABLE itest_err_1 (a text generated by default as identity); 32 33-- duplicate identity 34CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity); 35 36-- cannot have default and identity 37CREATE TABLE itest_err_3 (a int default 5 generated by default as identity); 38 39-- cannot combine serial and identity 40CREATE TABLE itest_err_4 (a serial generated by default as identity); 41 42INSERT INTO itest1 DEFAULT VALUES; 43INSERT INTO itest1 DEFAULT VALUES; 44INSERT INTO itest2 DEFAULT VALUES; 45INSERT INTO itest2 DEFAULT VALUES; 46INSERT INTO itest3 DEFAULT VALUES; 47INSERT INTO itest3 DEFAULT VALUES; 48INSERT INTO itest4 DEFAULT VALUES; 49INSERT INTO itest4 DEFAULT VALUES; 50 51SELECT * FROM itest1; 52SELECT * FROM itest2; 53SELECT * FROM itest3; 54SELECT * FROM itest4; 55 56 57-- VALUES RTEs 58 59INSERT INTO itest3 VALUES (DEFAULT, 'a'); 60INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); 61 62SELECT * FROM itest3; 63 64 65-- OVERRIDING tests 66 67INSERT INTO itest1 VALUES (10, 'xyz'); 68INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); 69 70SELECT * FROM itest1; 71 72INSERT INTO itest2 VALUES (10, 'xyz'); 73INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); 74 75SELECT * FROM itest2; 76 77 78-- UPDATE tests 79 80UPDATE itest1 SET a = 101 WHERE a = 1; 81UPDATE itest1 SET a = DEFAULT WHERE a = 2; 82SELECT * FROM itest1; 83 84UPDATE itest2 SET a = 101 WHERE a = 1; 85UPDATE itest2 SET a = DEFAULT WHERE a = 2; 86SELECT * FROM itest2; 87 88 89-- COPY tests 90 91CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint); 92 93COPY itest9 FROM stdin; 94100 foo 200 95101 bar 201 96\. 97 98COPY itest9 (b, c) FROM stdin; 99foo2 202 100bar2 203 101\. 102 103SELECT * FROM itest9 ORDER BY c; 104 105 106-- DROP IDENTITY tests 107 108ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; 109ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error 110ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop 111 112INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped 113ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; 114INSERT INTO itest4 DEFAULT VALUES; 115SELECT * FROM itest4; 116 117-- check that sequence is removed 118SELECT sequence_name FROM itest4_a_seq; 119 120 121-- test views 122 123CREATE TABLE itest10 (a int generated by default as identity, b text); 124CREATE TABLE itest11 (a int generated always as identity, b text); 125 126CREATE VIEW itestv10 AS SELECT * FROM itest10; 127CREATE VIEW itestv11 AS SELECT * FROM itest11; 128 129INSERT INTO itestv10 DEFAULT VALUES; 130INSERT INTO itestv10 DEFAULT VALUES; 131 132INSERT INTO itestv11 DEFAULT VALUES; 133INSERT INTO itestv11 DEFAULT VALUES; 134 135SELECT * FROM itestv10; 136SELECT * FROM itestv11; 137 138INSERT INTO itestv10 VALUES (10, 'xyz'); 139INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); 140 141SELECT * FROM itestv10; 142 143INSERT INTO itestv11 VALUES (10, 'xyz'); 144INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); 145 146SELECT * FROM itestv11; 147 148 149-- ADD COLUMN 150 151CREATE TABLE itest13 (a int); 152-- add column to empty table 153ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; 154INSERT INTO itest13 VALUES (1), (2), (3); 155-- add column to populated table 156ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; 157SELECT * FROM itest13; 158 159 160-- various ALTER COLUMN tests 161 162-- fail, not allowed for identity columns 163ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; 164 165-- fail, not allowed, already has a default 166CREATE TABLE itest5 (a serial, b text); 167ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 168 169ALTER TABLE itest3 ALTER COLUMN a TYPE int; 170SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; 171\d itest3 172 173ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error 174 175 176-- ALTER COLUMN ... SET 177 178CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); 179INSERT INTO itest6 DEFAULT VALUES; 180 181ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; 182INSERT INTO itest6 DEFAULT VALUES; 183INSERT INTO itest6 DEFAULT VALUES; 184SELECT * FROM itest6; 185 186SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2; 187 188ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity 189 190 191-- prohibited direct modification of sequence 192 193ALTER SEQUENCE itest6_a_seq OWNED BY NONE; 194 195 196-- inheritance 197 198CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); 199INSERT INTO itest7 DEFAULT VALUES; 200SELECT * FROM itest7; 201 202-- identity property is not inherited 203CREATE TABLE itest7a (b text) INHERITS (itest7); 204 205-- make column identity in child table 206CREATE TABLE itest7b (a int); 207CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); 208INSERT INTO itest7c DEFAULT VALUES; 209SELECT * FROM itest7c; 210 211CREATE TABLE itest7d (a int not null); 212CREATE TABLE itest7e () INHERITS (itest7d); 213ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 214ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error 215 216SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; 217 218-- These ALTER TABLE variants will not recurse. 219ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; 220ALTER TABLE itest7 ALTER COLUMN a RESTART; 221ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; 222 223-- privileges 224CREATE USER regress_identity_user1; 225CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); 226GRANT SELECT, INSERT ON itest8 TO regress_identity_user1; 227SET ROLE regress_identity_user1; 228INSERT INTO itest8 DEFAULT VALUES; 229SELECT * FROM itest8; 230RESET ROLE; 231DROP TABLE itest8; 232DROP USER regress_identity_user1; 233 234 235-- typed tables (currently not supported) 236 237CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); 238CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error 239DROP TYPE itest_type CASCADE; 240 241 242-- table partitions (currently not supported) 243 244CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); 245CREATE TABLE itest_child PARTITION OF itest_parent ( 246 f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY 247) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error 248DROP TABLE itest_parent; 249 250-- Identity columns must be NOT NULL (cf bug #16913) 251 252CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail 253CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail 254CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL); 255DROP TABLE itest15; 256CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY); 257DROP TABLE itest15; 258