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 59CREATE TABLE itest5 (a int generated always as identity, b text); 60INSERT INTO itest5 VALUES (1, 'a'); -- error 61INSERT INTO itest5 VALUES (DEFAULT, 'a'); -- ok 62INSERT INTO itest5 VALUES (2, 'b'), (3, 'c'); -- error 63INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c'); -- error 64INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c'); -- error 65INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); -- ok 66 67INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa'); 68INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc'); 69INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee'); 70INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg'); 71INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii'); 72 73INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa'); 74INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc'); 75INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee'); 76INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg'); 77INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii'); 78 79SELECT * FROM itest5; 80DROP TABLE itest5; 81 82INSERT INTO itest3 VALUES (DEFAULT, 'a'); 83INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); 84 85SELECT * FROM itest3; 86 87 88-- OVERRIDING tests 89 90-- GENERATED BY DEFAULT 91 92-- This inserts the row as presented: 93INSERT INTO itest1 VALUES (10, 'xyz'); 94-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed 95-- by the standard, but we allow it as a no-op, since it is of use if 96-- there are multiple identity columns in a table, which is also an 97-- extension. 98INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); 99-- This ignores the 30 and uses the sequence value instead: 100INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz'); 101 102SELECT * FROM itest1; 103 104-- GENERATED ALWAYS 105 106-- This is an error: 107INSERT INTO itest2 VALUES (10, 'xyz'); 108-- This inserts the row as presented: 109INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz'); 110-- This ignores the 30 and uses the sequence value instead: 111INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz'); 112 113SELECT * FROM itest2; 114 115 116-- UPDATE tests 117 118-- GENERATED BY DEFAULT is not restricted. 119UPDATE itest1 SET a = 101 WHERE a = 1; 120UPDATE itest1 SET a = DEFAULT WHERE a = 2; 121SELECT * FROM itest1; 122 123-- GENERATED ALWAYS allows only DEFAULT. 124UPDATE itest2 SET a = 101 WHERE a = 1; -- error 125UPDATE itest2 SET a = DEFAULT WHERE a = 2; -- ok 126SELECT * FROM itest2; 127 128 129-- COPY tests 130 131CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint); 132 133COPY itest9 FROM stdin; 134100 foo 200 135101 bar 201 136\. 137 138COPY itest9 (b, c) FROM stdin; 139foo2 202 140bar2 203 141\. 142 143SELECT * FROM itest9 ORDER BY c; 144 145 146-- DROP IDENTITY tests 147 148ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; 149ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error 150ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop 151 152INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped 153ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; 154INSERT INTO itest4 DEFAULT VALUES; 155SELECT * FROM itest4; 156 157-- check that sequence is removed 158SELECT sequence_name FROM itest4_a_seq; 159 160 161-- test views 162 163CREATE TABLE itest10 (a int generated by default as identity, b text); 164CREATE TABLE itest11 (a int generated always as identity, b text); 165 166CREATE VIEW itestv10 AS SELECT * FROM itest10; 167CREATE VIEW itestv11 AS SELECT * FROM itest11; 168 169INSERT INTO itestv10 DEFAULT VALUES; 170INSERT INTO itestv10 DEFAULT VALUES; 171 172INSERT INTO itestv11 DEFAULT VALUES; 173INSERT INTO itestv11 DEFAULT VALUES; 174 175SELECT * FROM itestv10; 176SELECT * FROM itestv11; 177 178INSERT INTO itestv10 VALUES (10, 'xyz'); 179INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); 180 181SELECT * FROM itestv10; 182 183INSERT INTO itestv11 VALUES (10, 'xyz'); 184INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); 185 186SELECT * FROM itestv11; 187 188DROP VIEW itestv10, itestv11; 189 190 191-- ADD COLUMN 192 193CREATE TABLE itest13 (a int); 194-- add column to empty table 195ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY; 196INSERT INTO itest13 VALUES (1), (2), (3); 197-- add column to populated table 198ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY; 199SELECT * FROM itest13; 200 201 202-- various ALTER COLUMN tests 203 204-- fail, not allowed for identity columns 205ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; 206 207-- fail, not allowed, already has a default 208CREATE TABLE itest5 (a serial, b text); 209ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 210 211ALTER TABLE itest3 ALTER COLUMN a TYPE int; 212SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; 213\d itest3 214 215ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error 216 217-- kinda silly to change property in the same command, but it should work 218ALTER TABLE itest3 219 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, 220 ALTER COLUMN c SET GENERATED ALWAYS; 221\d itest3 222 223 224-- ALTER COLUMN ... SET 225 226CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); 227INSERT INTO itest6 DEFAULT VALUES; 228 229ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; 230INSERT INTO itest6 DEFAULT VALUES; 231INSERT INTO itest6 DEFAULT VALUES; 232SELECT * FROM itest6; 233 234SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2; 235 236ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity 237 238 239-- prohibited direct modification of sequence 240 241ALTER SEQUENCE itest6_a_seq OWNED BY NONE; 242 243 244-- inheritance 245 246CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); 247INSERT INTO itest7 DEFAULT VALUES; 248SELECT * FROM itest7; 249 250-- identity property is not inherited 251CREATE TABLE itest7a (b text) INHERITS (itest7); 252 253-- make column identity in child table 254CREATE TABLE itest7b (a int); 255CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); 256INSERT INTO itest7c DEFAULT VALUES; 257SELECT * FROM itest7c; 258 259CREATE TABLE itest7d (a int not null); 260CREATE TABLE itest7e () INHERITS (itest7d); 261ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; 262ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error 263 264SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; 265 266-- These ALTER TABLE variants will not recurse. 267ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; 268ALTER TABLE itest7 ALTER COLUMN a RESTART; 269ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; 270 271-- privileges 272CREATE USER regress_identity_user1; 273CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); 274GRANT SELECT, INSERT ON itest8 TO regress_identity_user1; 275SET ROLE regress_identity_user1; 276INSERT INTO itest8 DEFAULT VALUES; 277SELECT * FROM itest8; 278RESET ROLE; 279DROP TABLE itest8; 280DROP USER regress_identity_user1; 281 282-- multiple steps in ALTER TABLE 283CREATE TABLE itest8 (f1 int); 284 285ALTER TABLE itest8 286 ADD COLUMN f2 int NOT NULL, 287 ALTER COLUMN f2 ADD GENERATED ALWAYS AS IDENTITY; 288 289ALTER TABLE itest8 290 ADD COLUMN f3 int NOT NULL, 291 ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY, 292 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT 10; 293 294ALTER TABLE itest8 295 ADD COLUMN f4 int; 296 297ALTER TABLE itest8 298 ALTER COLUMN f4 SET NOT NULL, 299 ALTER COLUMN f4 ADD GENERATED ALWAYS AS IDENTITY, 300 ALTER COLUMN f4 SET DATA TYPE bigint; 301 302ALTER TABLE itest8 303 ADD COLUMN f5 int GENERATED ALWAYS AS IDENTITY; 304 305ALTER TABLE itest8 306 ALTER COLUMN f5 DROP IDENTITY, 307 ALTER COLUMN f5 DROP NOT NULL, 308 ALTER COLUMN f5 SET DATA TYPE bigint; 309 310INSERT INTO itest8 VALUES(0), (1); 311 312-- This does not work when the table isn't empty. That's intentional, 313-- since ADD GENERATED should only affect later insertions: 314ALTER TABLE itest8 315 ADD COLUMN f22 int NOT NULL, 316 ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY; 317 318TABLE itest8; 319\d+ itest8 320\d itest8_f2_seq 321\d itest8_f3_seq 322\d itest8_f4_seq 323\d itest8_f5_seq 324DROP TABLE itest8; 325 326 327-- typed tables (currently not supported) 328 329CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); 330CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error 331DROP TYPE itest_type CASCADE; 332 333 334-- table partitions (currently not supported) 335 336CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); 337CREATE TABLE itest_child PARTITION OF itest_parent ( 338 f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY 339) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error 340DROP TABLE itest_parent; 341 342 343-- test that sequence of half-dropped serial column is properly ignored 344 345CREATE TABLE itest14 (id serial); 346ALTER TABLE itest14 ALTER id DROP DEFAULT; 347ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY; 348INSERT INTO itest14 (id) VALUES (DEFAULT); 349 350-- Identity columns must be NOT NULL (cf bug #16913) 351 352CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail 353CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail 354CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL); 355DROP TABLE itest15; 356CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY); 357DROP TABLE itest15; 358