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 93SELECT * FROM itest9 ORDER BY c; 94 95 96-- DROP IDENTITY tests 97 98ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; 99ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error 100ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop 101 102INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped 103ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; 104INSERT INTO itest4 DEFAULT VALUES; 105SELECT * FROM itest4; 106 107-- check that sequence is removed 108SELECT sequence_name FROM itest4_a_seq; 109 110 111-- test views 112 113CREATE TABLE itest10 (a int generated by default as identity, b text); 114CREATE TABLE itest11 (a int generated always as identity, b text); 115 116CREATE VIEW itestv10 AS SELECT * FROM itest10; 117CREATE VIEW itestv11 AS SELECT * FROM itest11; 118 119INSERT INTO itestv10 DEFAULT VALUES; 120INSERT INTO itestv10 DEFAULT VALUES; 121 122INSERT INTO itestv11 DEFAULT VALUES; 123INSERT INTO itestv11 DEFAULT VALUES; 124 125SELECT * FROM itestv10; 126SELECT * FROM itestv11; 127 128INSERT INTO itestv10 VALUES (10, 'xyz'); 129INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); 130 131SELECT * FROM itestv10; 132 133INSERT INTO itestv11 VALUES (10, 'xyz'); 134INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); 135 136SELECT * FROM itestv11; 137 138DROP VIEW itestv10, itestv11; 139 140 141-- ADD COLUMN 142 143CREATE TABLE itest13 (a int); 144-- add column to empty table 145ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; 146INSERT INTO itest13 VALUES (1), (2), (3); 147-- add column to populated table 148ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; 149SELECT * FROM itest13; 150 151 152-- various ALTER COLUMN tests 153 154-- fail, not allowed for identity columns 155ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; 156 157-- fail, not allowed, already has a default 158CREATE TABLE itest5 (a serial, b text); 159ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 160 161ALTER TABLE itest3 ALTER COLUMN a TYPE int; 162SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; 163\d itest3 164 165ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error 166 167-- kinda silly to change property in the same command, but it should work 168ALTER TABLE itest3 169 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, 170 ALTER COLUMN c SET GENERATED ALWAYS; 171\d itest3 172 173 174-- ALTER COLUMN ... SET 175 176CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); 177INSERT INTO itest6 DEFAULT VALUES; 178 179ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; 180INSERT INTO itest6 DEFAULT VALUES; 181INSERT INTO itest6 DEFAULT VALUES; 182SELECT * FROM itest6; 183 184SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6'; 185 186ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity 187 188 189-- prohibited direct modification of sequence 190 191ALTER SEQUENCE itest6_a_seq OWNED BY NONE; 192 193 194-- inheritance 195 196CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); 197INSERT INTO itest7 DEFAULT VALUES; 198SELECT * FROM itest7; 199 200-- identity property is not inherited 201CREATE TABLE itest7a (b text) INHERITS (itest7); 202 203-- make column identity in child table 204CREATE TABLE itest7b (a int); 205CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); 206INSERT INTO itest7c DEFAULT VALUES; 207SELECT * FROM itest7c; 208 209CREATE TABLE itest7d (a int not null); 210CREATE TABLE itest7e () INHERITS (itest7d); 211ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 212ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error 213 214SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; 215 216-- These ALTER TABLE variants will not recurse. 217ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; 218ALTER TABLE itest7 ALTER COLUMN a RESTART; 219ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; 220 221-- privileges 222CREATE USER regress_identity_user1; 223CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); 224GRANT SELECT, INSERT ON itest8 TO regress_identity_user1; 225SET ROLE regress_identity_user1; 226INSERT INTO itest8 DEFAULT VALUES; 227SELECT * FROM itest8; 228RESET ROLE; 229DROP TABLE itest8; 230DROP USER regress_identity_user1; 231 232 233-- typed tables (currently not supported) 234 235CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); 236CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error 237DROP TYPE itest_type CASCADE; 238 239 240-- table partitions (currently not supported) 241 242CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); 243CREATE TABLE itest_child PARTITION OF itest_parent ( 244 f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY 245) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error 246DROP TABLE itest_parent; 247