src/test/regress/sql/identity.sql

   1 -- sanity check of system catalog
   2 SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd');
   3
   4
   5 CREATE TABLE itest1 (a int generated by default as identity, b text);
   6 CREATE TABLE itest2 (a bigint generated always as identity, b text);
   7 CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);
   8 ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error
   9
  10 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;
  11
  12 -- internal sequences should not be shown here
  13 SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
  14
  15 SELECT pg_get_serial_sequence('itest1', 'a');
  16
  17 \d itest1_a_seq
  18
  19 CREATE TABLE itest4 (a int, b text);
  20 ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, requires NOT NULL
  21 ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;
  22 ALTER TABLE itest4 ALTER COLUMN c ADD GENERATED ALWAYS AS IDENTITY;  -- error, column c does not exist
  23 ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- ok
  24 ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;  -- error, disallowed
  25 ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, already set
  26 ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY;  -- error, wrong data type
  27
  28 -- for later
  29 ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';
  30
  31 -- invalid column type
  32 CREATE TABLE itest_err_1 (a text generated by default as identity);
  33
  34 -- duplicate identity
  35 CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);
  36
  37 -- cannot have default and identity
  38 CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);
  39
  40 -- cannot combine serial and identity
  41 CREATE TABLE itest_err_4 (a serial generated by default as identity);
  42
  43 INSERT INTO itest1 DEFAULT VALUES;
  44 INSERT INTO itest1 DEFAULT VALUES;
  45 INSERT INTO itest2 DEFAULT VALUES;
  46 INSERT INTO itest2 DEFAULT VALUES;
  47 INSERT INTO itest3 DEFAULT VALUES;
  48 INSERT INTO itest3 DEFAULT VALUES;
  49 INSERT INTO itest4 DEFAULT VALUES;
  50 INSERT INTO itest4 DEFAULT VALUES;
  51
  52 SELECT * FROM itest1;
  53 SELECT * FROM itest2;
  54 SELECT * FROM itest3;
  55 SELECT * FROM itest4;
  56
  57
  58 -- VALUES RTEs
  59
  60 CREATE TABLE itest5 (a int generated always as identity, b text);
  61 INSERT INTO itest5 VALUES (1, 'a');  -- error
  62 INSERT INTO itest5 VALUES (DEFAULT, 'a');  -- ok
  63 INSERT INTO itest5 VALUES (2, 'b'), (3, 'c');  -- error
  64 INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c');  -- error
  65 INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c');  -- error
  66 INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');  -- ok
  67
  68 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa');
  69 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc');
  70 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee');
  71 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg');
  72 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii');
  73
  74 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa');
  75 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc');
  76 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee');
  77 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
  78 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
  79
  80 SELECT * FROM itest5;
  81 DROP TABLE itest5;
  82
  83 INSERT INTO itest3 VALUES (DEFAULT, 'a');
  84 INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
  85
  86 SELECT * FROM itest3;
  87
  88
  89 -- OVERRIDING tests
  90
  91 -- GENERATED BY DEFAULT
  92
  93 -- This inserts the row as presented:
  94 INSERT INTO itest1 VALUES (10, 'xyz');
  95 -- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
  96 -- by the standard, but we allow it as a no-op, since it is of use if
  97 -- there are multiple identity columns in a table, which is also an
  98 -- extension.
  99 INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
 100 -- This ignores the 30 and uses the sequence value instead:
 101 INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
 102
 103 SELECT * FROM itest1;
 104
 105 -- GENERATED ALWAYS
 106
 107 -- This is an error:
 108 INSERT INTO itest2 VALUES (10, 'xyz');
 109 -- This inserts the row as presented:
 110 INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
 111 -- This ignores the 30 and uses the sequence value instead:
 112 INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
 113
 114 SELECT * FROM itest2;
 115
 116
 117 -- UPDATE tests
 118
 119 -- GENERATED BY DEFAULT is not restricted.
 120 UPDATE itest1 SET a = 101 WHERE a = 1;
 121 UPDATE itest1 SET a = DEFAULT WHERE a = 2;
 122 SELECT * FROM itest1;
 123
 124 -- GENERATED ALWAYS allows only DEFAULT.
 125 UPDATE itest2 SET a = 101 WHERE a = 1;  -- error
 126 UPDATE itest2 SET a = DEFAULT WHERE a = 2;  -- ok
 127 SELECT * FROM itest2;
 128
 129
 130 -- COPY tests
 131
 132 CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint);
 133
 134 COPY itest9 FROM stdin;
 135 100     foo     200
 136 101     bar     201
 137 \.
 138
 139 COPY itest9 (b, c) FROM stdin;
 140 foo2    202
 141 bar2    203
 142 \.
 143
 144 SELECT * FROM itest9 ORDER BY c;
 145
 146
 147 -- DROP IDENTITY tests
 148
 149 ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;
 150 ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;  -- error
 151 ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS;  -- noop
 152
 153 INSERT INTO itest4 DEFAULT VALUES;  -- fails because NOT NULL is not dropped
 154 ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
 155 INSERT INTO itest4 DEFAULT VALUES;
 156 SELECT * FROM itest4;
 157
 158 -- check that sequence is removed
 159 SELECT sequence_name FROM itest4_a_seq;
 160
 161
 162 -- test views
 163
 164 CREATE TABLE itest10 (a int generated by default as identity, b text);
 165 CREATE TABLE itest11 (a int generated always as identity, b text);
 166
 167 CREATE VIEW itestv10 AS SELECT * FROM itest10;
 168 CREATE VIEW itestv11 AS SELECT * FROM itest11;
 169
 170 INSERT INTO itestv10 DEFAULT VALUES;
 171 INSERT INTO itestv10 DEFAULT VALUES;
 172
 173 INSERT INTO itestv11 DEFAULT VALUES;
 174 INSERT INTO itestv11 DEFAULT VALUES;
 175
 176 SELECT * FROM itestv10;
 177 SELECT * FROM itestv11;
 178
 179 INSERT INTO itestv10 VALUES (10, 'xyz');
 180 INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');
 181
 182 SELECT * FROM itestv10;
 183
 184 INSERT INTO itestv11 VALUES (10, 'xyz');
 185 INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
 186
 187 SELECT * FROM itestv11;
 188
 189 DROP VIEW itestv10, itestv11;
 190
 191
 192 -- ADD COLUMN
 193
 194 CREATE TABLE itest13 (a int);
 195 -- add column to empty table
 196 ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY;
 197 INSERT INTO itest13 VALUES (1), (2), (3);
 198 -- add column to populated table
 199 ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY;
 200 SELECT * FROM itest13;
 201
 202
 203 -- various ALTER COLUMN tests
 204
 205 -- fail, not allowed for identity columns
 206 ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
 207
 208 -- fail, not allowed, already has a default
 209 CREATE TABLE itest5 (a serial, b text);
 210 ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
 211
 212 ALTER TABLE itest3 ALTER COLUMN a TYPE int;
 213 SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;
 214 \d itest3
 215
 216 ALTER TABLE itest3 ALTER COLUMN a TYPE text;  -- error
 217
 218 -- check that unlogged propagates to sequence
 219 CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text);
 220 ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
 221 ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY;
 222 \d itest17
 223 \d itest17_a_seq
 224 \d itest17_c_seq
 225 CREATE TABLE itest18 (a int NOT NULL, b text);
 226 ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
 227 \d itest18
 228 \d itest18_a_seq
 229 ALTER TABLE itest18 SET LOGGED;
 230 \d itest18
 231 \d itest18_a_seq
 232 ALTER TABLE itest18 SET UNLOGGED;
 233 \d itest18
 234 \d itest18_a_seq
 235
 236 -- kinda silly to change property in the same command, but it should work
 237 ALTER TABLE itest3
 238   ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY,
 239   ALTER COLUMN c SET GENERATED ALWAYS;
 240 \d itest3
 241
 242
 243 -- ALTER COLUMN ... SET
 244
 245 CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
 246 INSERT INTO itest6 DEFAULT VALUES;
 247
 248 ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
 249 INSERT INTO itest6 DEFAULT VALUES;
 250 INSERT INTO itest6 DEFAULT VALUES;
 251 SELECT * FROM itest6;
 252
 253 SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2;
 254
 255 ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2;  -- fail, not identity
 256
 257
 258 -- prohibited direct modification of sequence
 259
 260 ALTER SEQUENCE itest6_a_seq OWNED BY NONE;
 261
 262
 263 -- inheritance
 264
 265 CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
 266 INSERT INTO itest7 DEFAULT VALUES;
 267 SELECT * FROM itest7;
 268
 269 -- identity property is not inherited
 270 CREATE TABLE itest7a (b text) INHERITS (itest7);
 271
 272 -- make column identity in child table
 273 CREATE TABLE itest7b (a int);
 274 CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);
 275 INSERT INTO itest7c DEFAULT VALUES;
 276 SELECT * FROM itest7c;
 277
 278 CREATE TABLE itest7d (a int not null);
 279 CREATE TABLE itest7e () INHERITS (itest7d);
 280 ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
 281 ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY;  -- error
 282
 283 SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;
 284
 285 -- These ALTER TABLE variants will not recurse.
 286 ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;
 287 ALTER TABLE itest7 ALTER COLUMN a RESTART;
 288 ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;
 289
 290 -- privileges
 291 CREATE USER regress_identity_user1;
 292 CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);
 293 GRANT SELECT, INSERT ON itest8 TO regress_identity_user1;
 294 SET ROLE regress_identity_user1;
 295 INSERT INTO itest8 DEFAULT VALUES;
 296 SELECT * FROM itest8;
 297 RESET ROLE;
 298 DROP TABLE itest8;
 299 DROP USER regress_identity_user1;
 300
 301 -- multiple steps in ALTER TABLE
 302 CREATE TABLE itest8 (f1 int);
 303
 304 ALTER TABLE itest8
 305   ADD COLUMN f2 int NOT NULL,
 306   ALTER COLUMN f2 ADD GENERATED ALWAYS AS IDENTITY;
 307
 308 ALTER TABLE itest8
 309   ADD COLUMN f3 int NOT NULL,
 310   ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY,
 311   ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT 10;
 312
 313 ALTER TABLE itest8
 314   ADD COLUMN f4 int;
 315
 316 ALTER TABLE itest8
 317   ALTER COLUMN f4 SET NOT NULL,
 318   ALTER COLUMN f4 ADD GENERATED ALWAYS AS IDENTITY,
 319   ALTER COLUMN f4 SET DATA TYPE bigint;
 320
 321 ALTER TABLE itest8
 322   ADD COLUMN f5 int GENERATED ALWAYS AS IDENTITY;
 323
 324 ALTER TABLE itest8
 325   ALTER COLUMN f5 DROP IDENTITY,
 326   ALTER COLUMN f5 DROP NOT NULL,
 327   ALTER COLUMN f5 SET DATA TYPE bigint;
 328
 329 INSERT INTO itest8 VALUES(0), (1);
 330
 331 -- This does not work when the table isn't empty.  That's intentional,
 332 -- since ADD GENERATED should only affect later insertions:
 333 ALTER TABLE itest8
 334   ADD COLUMN f22 int NOT NULL,
 335   ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY;
 336
 337 TABLE itest8;
 338 \d+ itest8
 339 \d itest8_f2_seq
 340 \d itest8_f3_seq
 341 \d itest8_f4_seq
 342 \d itest8_f5_seq
 343 DROP TABLE itest8;
 344
 345
 346 -- typed tables (currently not supported)
 347
 348 CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint);
 349 CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error
 350 DROP TYPE itest_type CASCADE;
 351
 352
 353 -- table partitions
 354
 355 -- partitions inherit identity column and share sequence
 356 CREATE TABLE pitest1 (f1 date NOT NULL, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1);
 357 -- new partition
 358 CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
 359 INSERT into pitest1(f1, f2) VALUES ('2016-07-2', 'from pitest1');
 360 INSERT into pitest1_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest1_p1');
 361 -- attached partition
 362 CREATE TABLE pitest1_p2 (f3 bigint, f2 text, f1 date NOT NULL);
 363 INSERT INTO pitest1_p2 (f1, f2, f3) VALUES ('2016-08-2', 'before attaching', 100);
 364 ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); -- requires NOT NULL constraint
 365 ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET NOT NULL;
 366 ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
 367 INSERT INTO pitest1_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest1_p2');
 368 INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-4', 'from pitest1');
 369 -- LIKE INCLUDING on partition
 370 CREATE TABLE pitest1_p1_like (LIKE pitest1_p1 INCLUDING IDENTITY);
 371 INSERT into pitest1_p1_like(f1, f2) VALUES ('2016-07-2', 'from pitest1_p1_like');
 372 SELECT tableoid::regclass, f1, f2, f3 FROM pitest1;
 373 SELECT tableoid::regclass, f1, f2, f3 FROM pitest1_p1_like;
 374 ALTER TABLE pitest1 ALTER COLUMN f3 SET DATA TYPE bigint;
 375 SELECT tableoid::regclass, f1, f2, f3, pg_typeof(f3) FROM pitest1;
 376 SELECT tableoid::regclass, f1, f2, f3, pg_typeof(f3) FROM pitest1_p2;
 377
 378 -- add identity column
 379 CREATE TABLE pitest2 (f1 date NOT NULL, f2 text) PARTITION BY RANGE (f1);
 380 CREATE TABLE pitest2_p1 PARTITION OF pitest2 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
 381 CREATE TABLE pitest2_p2 PARTITION OF pitest2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
 382 INSERT into pitest2(f1, f2) VALUES ('2016-07-2', 'from pitest2');
 383 INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-2', 'from pitest2');
 384 ALTER TABLE pitest2 ADD COLUMN f3 int GENERATED ALWAYS AS IDENTITY;
 385 INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest2_p1');
 386 INSERT INTO pitest2_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest2_p2');
 387 INSERT into pitest2(f1, f2) VALUES ('2016-07-4', 'from pitest2');
 388 INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-4', 'from pitest2');
 389 SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
 390
 391 -- SET identity column
 392 ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails
 393 ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails
 394 ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails
 395 ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART;
 396 INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200);
 397 INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2');
 398 INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1');
 399 INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300);
 400 SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
 401
 402 -- detaching a partition removes identity property
 403 ALTER TABLE pitest2 DETACH PARTITION pitest2_p1;
 404 INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2');
 405 INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error
 406 INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000);
 407 SELECT tableoid::regclass, f1, f2, f3 FROM pitest2;
 408 SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1;
 409
 410 DROP TABLE pitest2_p1;
 411
 412 -- changing a regular column to identity column in a partitioned table
 413 CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1);
 414 CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
 415 INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1);
 416 INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2);
 417 -- fails, changing only a partition not allowed
 418 ALTER TABLE pitest3_p1
 419             ALTER COLUMN f3 SET NOT NULL,
 420             ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
 421 -- fails, changing only the partitioned table not allowed
 422 BEGIN;
 423 ALTER TABLE pitest3_p1 ALTER COLUMN f3 SET NOT NULL;
 424 ALTER TABLE ONLY pitest3
 425             ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
 426 ROLLBACK;
 427 ALTER TABLE pitest3
 428             ALTER COLUMN f3 SET NOT NULL,
 429             ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3);
 430 INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3');
 431 INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1');
 432 SELECT tableoid::regclass, f1, f2, f3 FROM pitest3;
 433
 434 -- changing an identity column to a non-identity column in a partitioned table
 435 ALTER TABLE pitest3_p1 ALTER COLUMN f3 DROP IDENTITY; -- fails
 436 ALTER TABLE ONLY pitest3 ALTER COLUMN f3 DROP IDENTITY; -- fails
 437 ALTER TABLE pitest3 ALTER COLUMN f3 DROP IDENTITY;
 438 INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); -- fails
 439 INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); -- fails
 440 INSERT into pitest3(f1, f2, f3) VALUES ('2016-07-6', 'from pitest3', 5);
 441 INSERT into pitest3_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest3_p1', 6);
 442 SELECT tableoid::regclass, f1, f2, f3 FROM pitest3;
 443
 444 -- Changing NOT NULL constraint of identity columns is not allowed
 445 ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL;
 446 ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL;
 447 -- Identity columns have their own default
 448 ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000;
 449 ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000;
 450 -- Adding identity to an identity column is not allowed
 451 ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY;
 452 ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY;
 453
 454 -- partitions with their own identity columns are not allowed, even if the
 455 -- partitioned table does not have an identity column.
 456 CREATE TABLE pitest1_pfail PARTITION OF pitest1 (
 457     f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
 458 ) FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
 459
 460 CREATE TABLE pitest_pfail PARTITION OF pitest3 (
 461     f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
 462 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
 463
 464 CREATE TABLE pitest1_pfail (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS IDENTITY);
 465 ALTER TABLE pitest1 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
 466 ALTER TABLE pitest3 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01');
 467
 468 DROP TABLE pitest1_pfail;
 469 DROP TABLE pitest3;
 470
 471 -- test that sequence of half-dropped serial column is properly ignored
 472
 473 CREATE TABLE itest14 (id serial);
 474 ALTER TABLE itest14 ALTER id DROP DEFAULT;
 475 ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
 476 INSERT INTO itest14 (id) VALUES (DEFAULT);
 477
 478 -- Identity columns must be NOT NULL (cf bug #16913)
 479
 480 CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail
 481 CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail
 482 CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
 483 DROP TABLE itest15;
 484 CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
 485 DROP TABLE itest15;
 486
 487 -- MERGE tests
 488 CREATE TABLE itest15 (a int GENERATED ALWAYS AS IDENTITY, b text);
 489 CREATE TABLE itest16 (a int GENERATED BY DEFAULT AS IDENTITY, b text);
 490
 491 MERGE INTO itest15 t
 492 USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
 493 ON t.a = s.s_a
 494 WHEN NOT MATCHED THEN
 495         INSERT (a, b) VALUES (s.s_a, s.s_b);
 496
 497 -- Used to fail, but now it works and ignores the user supplied value
 498 MERGE INTO itest15 t
 499 USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
 500 ON t.a = s.s_a
 501 WHEN NOT MATCHED THEN
 502         INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
 503
 504 MERGE INTO itest15 t
 505 USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
 506 ON t.a = s.s_a
 507 WHEN NOT MATCHED THEN
 508         INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
 509
 510 MERGE INTO itest16 t
 511 USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
 512 ON t.a = s.s_a
 513 WHEN NOT MATCHED THEN
 514         INSERT (a, b) VALUES (s.s_a, s.s_b);
 515
 516 MERGE INTO itest16 t
 517 USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
 518 ON t.a = s.s_a
 519 WHEN NOT MATCHED THEN
 520         INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
 521
 522 MERGE INTO itest16 t
 523 USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
 524 ON t.a = s.s_a
 525 WHEN NOT MATCHED THEN
 526         INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
 527
 528 SELECT * FROM itest15;
 529 SELECT * FROM itest16;
 530 DROP TABLE itest15;
 531 DROP TABLE itest16;
 532
 533 -- For testing of pg_dump and pg_upgrade, leave behind some identity
 534 -- sequences whose logged-ness doesn't match their owning table's.
 535 CREATE TABLE identity_dump_logged (a INT GENERATED ALWAYS AS IDENTITY);
 536 ALTER SEQUENCE identity_dump_logged_a_seq SET UNLOGGED;
 537 CREATE UNLOGGED TABLE identity_dump_unlogged (a INT GENERATED ALWAYS AS IDENTITY);
 538 ALTER SEQUENCE identity_dump_unlogged_a_seq SET LOGGED;
 539 SELECT relname, relpersistence FROM pg_class
 540   WHERE relname ~ '^identity_dump_' ORDER BY 1;