1-- 2-- ALTER_TABLE 3-- add attribute 4-- 5 6CREATE TABLE tmp (initial int4); 7 8COMMENT ON TABLE tmp_wrong IS 'table comment'; 9COMMENT ON TABLE tmp IS 'table comment'; 10COMMENT ON TABLE tmp IS NULL; 11 12ALTER TABLE tmp ADD COLUMN xmin integer; -- fails 13 14ALTER TABLE tmp ADD COLUMN a int4 default 3; 15 16ALTER TABLE tmp ADD COLUMN b name; 17 18ALTER TABLE tmp ADD COLUMN c text; 19 20ALTER TABLE tmp ADD COLUMN d float8; 21 22ALTER TABLE tmp ADD COLUMN e float4; 23 24ALTER TABLE tmp ADD COLUMN f int2; 25 26ALTER TABLE tmp ADD COLUMN g polygon; 27 28ALTER TABLE tmp ADD COLUMN h abstime; 29 30ALTER TABLE tmp ADD COLUMN i char; 31 32ALTER TABLE tmp ADD COLUMN j abstime[]; 33 34ALTER TABLE tmp ADD COLUMN k int4; 35 36ALTER TABLE tmp ADD COLUMN l tid; 37 38ALTER TABLE tmp ADD COLUMN m xid; 39 40ALTER TABLE tmp ADD COLUMN n oidvector; 41 42--ALTER TABLE tmp ADD COLUMN o lock; 43ALTER TABLE tmp ADD COLUMN p smgr; 44 45ALTER TABLE tmp ADD COLUMN q point; 46 47ALTER TABLE tmp ADD COLUMN r lseg; 48 49ALTER TABLE tmp ADD COLUMN s path; 50 51ALTER TABLE tmp ADD COLUMN t box; 52 53ALTER TABLE tmp ADD COLUMN u tinterval; 54 55ALTER TABLE tmp ADD COLUMN v timestamp; 56 57ALTER TABLE tmp ADD COLUMN w interval; 58 59ALTER TABLE tmp ADD COLUMN x float8[]; 60 61ALTER TABLE tmp ADD COLUMN y float4[]; 62 63ALTER TABLE tmp ADD COLUMN z int2[]; 64 65INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, 66 v, w, x, y, z) 67 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 68 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', 69 314159, '(1,1)', '512', 70 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', 71 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]', 72 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); 73 74SELECT * FROM tmp; 75 76DROP TABLE tmp; 77 78-- the wolf bug - schema mods caused inconsistent row descriptors 79CREATE TABLE tmp ( 80 initial int4 81); 82 83ALTER TABLE tmp ADD COLUMN a int4; 84 85ALTER TABLE tmp ADD COLUMN b name; 86 87ALTER TABLE tmp ADD COLUMN c text; 88 89ALTER TABLE tmp ADD COLUMN d float8; 90 91ALTER TABLE tmp ADD COLUMN e float4; 92 93ALTER TABLE tmp ADD COLUMN f int2; 94 95ALTER TABLE tmp ADD COLUMN g polygon; 96 97ALTER TABLE tmp ADD COLUMN h abstime; 98 99ALTER TABLE tmp ADD COLUMN i char; 100 101ALTER TABLE tmp ADD COLUMN j abstime[]; 102 103ALTER TABLE tmp ADD COLUMN k int4; 104 105ALTER TABLE tmp ADD COLUMN l tid; 106 107ALTER TABLE tmp ADD COLUMN m xid; 108 109ALTER TABLE tmp ADD COLUMN n oidvector; 110 111--ALTER TABLE tmp ADD COLUMN o lock; 112ALTER TABLE tmp ADD COLUMN p smgr; 113 114ALTER TABLE tmp ADD COLUMN q point; 115 116ALTER TABLE tmp ADD COLUMN r lseg; 117 118ALTER TABLE tmp ADD COLUMN s path; 119 120ALTER TABLE tmp ADD COLUMN t box; 121 122ALTER TABLE tmp ADD COLUMN u tinterval; 123 124ALTER TABLE tmp ADD COLUMN v timestamp; 125 126ALTER TABLE tmp ADD COLUMN w interval; 127 128ALTER TABLE tmp ADD COLUMN x float8[]; 129 130ALTER TABLE tmp ADD COLUMN y float4[]; 131 132ALTER TABLE tmp ADD COLUMN z int2[]; 133 134INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, 135 v, w, x, y, z) 136 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 137 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', 138 314159, '(1,1)', '512', 139 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', 140 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]', 141 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); 142 143SELECT * FROM tmp; 144 145DROP TABLE tmp; 146 147 148-- 149-- rename - check on both non-temp and temp tables 150-- 151CREATE TABLE tmp (regtable int); 152CREATE TEMP TABLE tmp (tmptable int); 153 154ALTER TABLE tmp RENAME TO tmp_new; 155 156SELECT * FROM tmp; 157SELECT * FROM tmp_new; 158 159ALTER TABLE tmp RENAME TO tmp_new2; 160 161SELECT * FROM tmp; -- should fail 162SELECT * FROM tmp_new; 163SELECT * FROM tmp_new2; 164 165DROP TABLE tmp_new; 166DROP TABLE tmp_new2; 167 168-- 169-- check renaming to a table's array type's autogenerated name 170-- (the array type's name should get out of the way) 171-- 172CREATE TABLE tmp_array (id int); 173CREATE TABLE tmp_array2 (id int); 174SELECT typname FROM pg_type WHERE oid = 'tmp_array[]'::regtype; 175SELECT typname FROM pg_type WHERE oid = 'tmp_array2[]'::regtype; 176ALTER TABLE tmp_array2 RENAME TO _tmp_array; 177SELECT typname FROM pg_type WHERE oid = 'tmp_array[]'::regtype; 178SELECT typname FROM pg_type WHERE oid = '_tmp_array[]'::regtype; 179DROP TABLE _tmp_array; 180DROP TABLE tmp_array; 181 182-- renaming to table's own array type's name is an interesting corner case 183CREATE TABLE tmp_array (id int); 184SELECT typname FROM pg_type WHERE oid = 'tmp_array[]'::regtype; 185ALTER TABLE tmp_array RENAME TO _tmp_array; 186SELECT typname FROM pg_type WHERE oid = '_tmp_array[]'::regtype; 187DROP TABLE _tmp_array; 188 189-- ALTER TABLE ... RENAME on non-table relations 190-- renaming indexes (FIXME: this should probably test the index's functionality) 191ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1; 192ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1; 193 194ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1; 195ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1; 196-- renaming views 197CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; 198ALTER TABLE tmp_view RENAME TO tmp_view_new; 199 200-- hack to ensure we get an indexscan here 201set enable_seqscan to off; 202set enable_bitmapscan to off; 203-- 5 values, sorted 204SELECT unique1 FROM tenk1 WHERE unique1 < 5; 205reset enable_seqscan; 206reset enable_bitmapscan; 207 208DROP VIEW tmp_view_new; 209-- toast-like relation name 210alter table stud_emp rename to pg_toast_stud_emp; 211alter table pg_toast_stud_emp rename to stud_emp; 212 213-- renaming index should rename constraint as well 214ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); 215ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo; 216ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; 217 218-- renaming constraint 219ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0); 220ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo; 221ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo; 222 223-- renaming constraint should rename index as well 224ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1); 225DROP INDEX onek_unique1_constraint; -- to see whether it's there 226ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo; 227DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there 228ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo; 229 230-- renaming constraints vs. inheritance 231CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int); 232\d constraint_rename_test 233CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test); 234\d constraint_rename_test2 235ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail 236ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail 237ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok 238\d constraint_rename_test 239\d constraint_rename_test2 240ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT; 241ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok 242ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok 243\d constraint_rename_test 244\d constraint_rename_test2 245ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a); 246ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok 247\d constraint_rename_test 248\d constraint_rename_test2 249DROP TABLE constraint_rename_test2; 250DROP TABLE constraint_rename_test; 251ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok 252ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a); 253 254-- renaming constraints with cache reset of target relation 255CREATE TABLE constraint_rename_cache (a int, 256 CONSTRAINT chk_a CHECK (a > 0), 257 PRIMARY KEY (a)); 258ALTER TABLE constraint_rename_cache 259 RENAME CONSTRAINT chk_a TO chk_a_new; 260ALTER TABLE constraint_rename_cache 261 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new; 262CREATE TABLE like_constraint_rename_cache 263 (LIKE constraint_rename_cache INCLUDING ALL); 264\d like_constraint_rename_cache 265DROP TABLE constraint_rename_cache; 266DROP TABLE like_constraint_rename_cache; 267 268-- FOREIGN KEY CONSTRAINT adding TEST 269 270CREATE TABLE tmp2 (a int primary key); 271 272CREATE TABLE tmp3 (a int, b int); 273 274CREATE TABLE tmp4 (a int, b int, unique(a,b)); 275 276CREATE TABLE tmp5 (a int, b int); 277 278-- Insert rows into tmp2 (pktable) 279INSERT INTO tmp2 values (1); 280INSERT INTO tmp2 values (2); 281INSERT INTO tmp2 values (3); 282INSERT INTO tmp2 values (4); 283 284-- Insert rows into tmp3 285INSERT INTO tmp3 values (1,10); 286INSERT INTO tmp3 values (1,20); 287INSERT INTO tmp3 values (5,50); 288 289-- Try (and fail) to add constraint due to invalid source columns 290ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full; 291 292-- Try (and fail) to add constraint due to invalid destination columns explicitly given 293ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full; 294 295-- Try (and fail) to add constraint due to invalid data 296ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; 297 298-- Delete failing row 299DELETE FROM tmp3 where a=5; 300 301-- Try (and succeed) 302ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full; 303ALTER TABLE tmp3 drop constraint tmpconstr; 304 305INSERT INTO tmp3 values (5,50); 306 307-- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate 308ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID; 309ALTER TABLE tmp3 validate constraint tmpconstr; 310 311-- Delete failing row 312DELETE FROM tmp3 where a=5; 313 314-- Try (and succeed) and repeat to show it works on already valid constraint 315ALTER TABLE tmp3 validate constraint tmpconstr; 316ALTER TABLE tmp3 validate constraint tmpconstr; 317 318-- Try a non-verified CHECK constraint 319ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail 320ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds 321ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails 322DELETE FROM tmp3 WHERE NOT b > 10; 323ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds 324ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds 325 326-- Test inherited NOT VALID CHECK constraints 327select * from tmp3; 328CREATE TABLE tmp6 () INHERITS (tmp3); 329CREATE TABLE tmp7 () INHERITS (tmp3); 330 331INSERT INTO tmp6 VALUES (6, 30), (7, 16); 332ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID; 333ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails 334DELETE FROM tmp6 WHERE b > 20; 335ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds 336 337-- An already validated constraint must not be revalidated 338CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$; 339INSERT INTO tmp7 VALUES (8, 18); 340ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b)); 341ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID; 342ALTER TABLE tmp3 VALIDATE CONSTRAINT identity; 343 344-- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT 345create table parent_noinh_convalid (a int); 346create table child_noinh_convalid () inherits (parent_noinh_convalid); 347insert into parent_noinh_convalid values (1); 348insert into child_noinh_convalid values (1); 349alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid; 350-- fail, because of the row in parent 351alter table parent_noinh_convalid validate constraint check_a_is_2; 352delete from only parent_noinh_convalid; 353-- ok (parent itself contains no violating rows) 354alter table parent_noinh_convalid validate constraint check_a_is_2; 355select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2'; 356-- cleanup 357drop table parent_noinh_convalid, child_noinh_convalid; 358 359-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on 360-- tmp4 is a,b 361 362ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full; 363 364DROP TABLE tmp7; 365 366DROP TABLE tmp6; 367 368DROP TABLE tmp5; 369 370DROP TABLE tmp4; 371 372DROP TABLE tmp3; 373 374DROP TABLE tmp2; 375 376-- NOT VALID with plan invalidation -- ensure we don't use a constraint for 377-- exclusion until validated 378set constraint_exclusion TO 'partition'; 379create table nv_parent (d date, check (false) no inherit not valid); 380-- not valid constraint added at creation time should automatically become valid 381\d nv_parent 382 383create table nv_child_2010 () inherits (nv_parent); 384create table nv_child_2011 () inherits (nv_parent); 385alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; 386alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid; 387explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31'; 388create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent); 389explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date; 390explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; 391-- after validation, the constraint should be used 392alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check; 393explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date; 394 395-- add an inherited NOT VALID constraint 396alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid; 397\d nv_child_2009 398-- we leave nv_parent and children around to help test pg_dump logic 399 400-- Foreign key adding test with mixed types 401 402-- Note: these tables are TEMP to avoid name conflicts when this test 403-- is run in parallel with foreign_key.sql. 404 405CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); 406INSERT INTO PKTABLE VALUES(42); 407CREATE TEMP TABLE FKTABLE (ftest1 inet); 408-- This next should fail, because int=inet does not exist 409ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 410-- This should also fail for the same reason, but here we 411-- give the column name 412ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); 413DROP TABLE FKTABLE; 414-- This should succeed, even though they are different types, 415-- because int=int8 exists and is a member of the integer opfamily 416CREATE TEMP TABLE FKTABLE (ftest1 int8); 417ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 418-- Check it actually works 419INSERT INTO FKTABLE VALUES(42); -- should succeed 420INSERT INTO FKTABLE VALUES(43); -- should fail 421DROP TABLE FKTABLE; 422-- This should fail, because we'd have to cast numeric to int which is 423-- not an implicit coercion (or use numeric=numeric, but that's not part 424-- of the integer opfamily) 425CREATE TEMP TABLE FKTABLE (ftest1 numeric); 426ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 427DROP TABLE FKTABLE; 428DROP TABLE PKTABLE; 429-- On the other hand, this should work because int implicitly promotes to 430-- numeric, and we allow promotion on the FK side 431CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); 432INSERT INTO PKTABLE VALUES(42); 433CREATE TEMP TABLE FKTABLE (ftest1 int); 434ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; 435-- Check it actually works 436INSERT INTO FKTABLE VALUES(42); -- should succeed 437INSERT INTO FKTABLE VALUES(43); -- should fail 438DROP TABLE FKTABLE; 439DROP TABLE PKTABLE; 440 441CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, 442 PRIMARY KEY(ptest1, ptest2)); 443-- This should fail, because we just chose really odd types 444CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); 445ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; 446DROP TABLE FKTABLE; 447-- Again, so should this... 448CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); 449ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) 450 references pktable(ptest1, ptest2); 451DROP TABLE FKTABLE; 452-- This fails because we mixed up the column ordering 453CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); 454ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) 455 references pktable(ptest2, ptest1); 456-- As does this... 457ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) 458 references pktable(ptest1, ptest2); 459DROP TABLE FKTABLE; 460DROP TABLE PKTABLE; 461 462-- Test that ALTER CONSTRAINT updates trigger deferrability properly 463 464CREATE TEMP TABLE PKTABLE (ptest1 int primary key); 465CREATE TEMP TABLE FKTABLE (ftest1 int); 466 467ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable 468 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 469ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable 470 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; 471ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable 472 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; 473 474ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable 475 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; 476ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE; 477ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable 478 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 479ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED; 480ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable 481 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; 482ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE; 483 484SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred 485FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint 486WHERE tgrelid = 'pktable'::regclass 487ORDER BY 1,2,3; 488SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred 489FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint 490WHERE tgrelid = 'fktable'::regclass 491ORDER BY 1,2,3; 492 493-- temp tables should go away by themselves, need not drop them. 494 495-- test check constraint adding 496 497create table atacc1 ( test int ); 498-- add a check constraint 499alter table atacc1 add constraint atacc_test1 check (test>3); 500-- should fail 501insert into atacc1 (test) values (2); 502-- should succeed 503insert into atacc1 (test) values (4); 504drop table atacc1; 505 506-- let's do one where the check fails when added 507create table atacc1 ( test int ); 508-- insert a soon to be failing row 509insert into atacc1 (test) values (2); 510-- add a check constraint (fails) 511alter table atacc1 add constraint atacc_test1 check (test>3); 512insert into atacc1 (test) values (4); 513drop table atacc1; 514 515-- let's do one where the check fails because the column doesn't exist 516create table atacc1 ( test int ); 517-- add a check constraint (fails) 518alter table atacc1 add constraint atacc_test1 check (test1>3); 519drop table atacc1; 520 521-- something a little more complicated 522create table atacc1 ( test int, test2 int, test3 int); 523-- add a check constraint (fails) 524alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4); 525-- should fail 526insert into atacc1 (test,test2,test3) values (4,4,2); 527-- should succeed 528insert into atacc1 (test,test2,test3) values (4,4,5); 529drop table atacc1; 530 531-- lets do some naming tests 532create table atacc1 (test int check (test>3), test2 int); 533alter table atacc1 add check (test2>test); 534-- should fail for $2 535insert into atacc1 (test2, test) values (3, 4); 536drop table atacc1; 537 538-- inheritance related tests 539create table atacc1 (test int); 540create table atacc2 (test2 int); 541create table atacc3 (test3 int) inherits (atacc1, atacc2); 542alter table atacc2 add constraint foo check (test2>0); 543-- fail and then succeed on atacc2 544insert into atacc2 (test2) values (-3); 545insert into atacc2 (test2) values (3); 546-- fail and then succeed on atacc3 547insert into atacc3 (test2) values (-3); 548insert into atacc3 (test2) values (3); 549drop table atacc3; 550drop table atacc2; 551drop table atacc1; 552 553-- same things with one created with INHERIT 554create table atacc1 (test int); 555create table atacc2 (test2 int); 556create table atacc3 (test3 int) inherits (atacc1, atacc2); 557alter table atacc3 no inherit atacc2; 558-- fail 559alter table atacc3 no inherit atacc2; 560-- make sure it really isn't a child 561insert into atacc3 (test2) values (3); 562select test2 from atacc2; 563-- fail due to missing constraint 564alter table atacc2 add constraint foo check (test2>0); 565alter table atacc3 inherit atacc2; 566-- fail due to missing column 567alter table atacc3 rename test2 to testx; 568alter table atacc3 inherit atacc2; 569-- fail due to mismatched data type 570alter table atacc3 add test2 bool; 571alter table atacc3 inherit atacc2; 572alter table atacc3 drop test2; 573-- succeed 574alter table atacc3 add test2 int; 575update atacc3 set test2 = 4 where test2 is null; 576alter table atacc3 add constraint foo check (test2>0); 577alter table atacc3 inherit atacc2; 578-- fail due to duplicates and circular inheritance 579alter table atacc3 inherit atacc2; 580alter table atacc2 inherit atacc3; 581alter table atacc2 inherit atacc2; 582-- test that we really are a child now (should see 4 not 3 and cascade should go through) 583select test2 from atacc2; 584drop table atacc2 cascade; 585drop table atacc1; 586 587-- adding only to a parent is allowed as of 9.2 588 589create table atacc1 (test int); 590create table atacc2 (test2 int) inherits (atacc1); 591-- ok: 592alter table atacc1 add constraint foo check (test>0) no inherit; 593-- check constraint is not there on child 594insert into atacc2 (test) values (-3); 595-- check constraint is there on parent 596insert into atacc1 (test) values (-3); 597insert into atacc1 (test) values (3); 598-- fail, violating row: 599alter table atacc2 add constraint foo check (test>0) no inherit; 600drop table atacc2; 601drop table atacc1; 602 603-- test unique constraint adding 604 605create table atacc1 ( test int ) with oids; 606-- add a unique constraint 607alter table atacc1 add constraint atacc_test1 unique (test); 608-- insert first value 609insert into atacc1 (test) values (2); 610-- should fail 611insert into atacc1 (test) values (2); 612-- should succeed 613insert into atacc1 (test) values (4); 614-- try adding a unique oid constraint 615alter table atacc1 add constraint atacc_oid1 unique(oid); 616-- try to create duplicates via alter table using - should fail 617alter table atacc1 alter column test type integer using 0; 618drop table atacc1; 619 620-- let's do one where the unique constraint fails when added 621create table atacc1 ( test int ); 622-- insert soon to be failing rows 623insert into atacc1 (test) values (2); 624insert into atacc1 (test) values (2); 625-- add a unique constraint (fails) 626alter table atacc1 add constraint atacc_test1 unique (test); 627insert into atacc1 (test) values (3); 628drop table atacc1; 629 630-- let's do one where the unique constraint fails 631-- because the column doesn't exist 632create table atacc1 ( test int ); 633-- add a unique constraint (fails) 634alter table atacc1 add constraint atacc_test1 unique (test1); 635drop table atacc1; 636 637-- something a little more complicated 638create table atacc1 ( test int, test2 int); 639-- add a unique constraint 640alter table atacc1 add constraint atacc_test1 unique (test, test2); 641-- insert initial value 642insert into atacc1 (test,test2) values (4,4); 643-- should fail 644insert into atacc1 (test,test2) values (4,4); 645-- should all succeed 646insert into atacc1 (test,test2) values (4,5); 647insert into atacc1 (test,test2) values (5,4); 648insert into atacc1 (test,test2) values (5,5); 649drop table atacc1; 650 651-- lets do some naming tests 652create table atacc1 (test int, test2 int, unique(test)); 653alter table atacc1 add unique (test2); 654-- should fail for @@ second one @@ 655insert into atacc1 (test2, test) values (3, 3); 656insert into atacc1 (test2, test) values (2, 3); 657drop table atacc1; 658 659-- test primary key constraint adding 660 661create table atacc1 ( test int ) with oids; 662-- add a primary key constraint 663alter table atacc1 add constraint atacc_test1 primary key (test); 664-- insert first value 665insert into atacc1 (test) values (2); 666-- should fail 667insert into atacc1 (test) values (2); 668-- should succeed 669insert into atacc1 (test) values (4); 670-- inserting NULL should fail 671insert into atacc1 (test) values(NULL); 672-- try adding a second primary key (should fail) 673alter table atacc1 add constraint atacc_oid1 primary key(oid); 674-- drop first primary key constraint 675alter table atacc1 drop constraint atacc_test1 restrict; 676-- try adding a primary key on oid (should succeed) 677alter table atacc1 add constraint atacc_oid1 primary key(oid); 678drop table atacc1; 679 680-- let's do one where the primary key constraint fails when added 681create table atacc1 ( test int ); 682-- insert soon to be failing rows 683insert into atacc1 (test) values (2); 684insert into atacc1 (test) values (2); 685-- add a primary key (fails) 686alter table atacc1 add constraint atacc_test1 primary key (test); 687insert into atacc1 (test) values (3); 688drop table atacc1; 689 690-- let's do another one where the primary key constraint fails when added 691create table atacc1 ( test int ); 692-- insert soon to be failing row 693insert into atacc1 (test) values (NULL); 694-- add a primary key (fails) 695alter table atacc1 add constraint atacc_test1 primary key (test); 696insert into atacc1 (test) values (3); 697drop table atacc1; 698 699-- let's do one where the primary key constraint fails 700-- because the column doesn't exist 701create table atacc1 ( test int ); 702-- add a primary key constraint (fails) 703alter table atacc1 add constraint atacc_test1 primary key (test1); 704drop table atacc1; 705 706-- adding a new column as primary key to a non-empty table. 707-- should fail unless the column has a non-null default value. 708create table atacc1 ( test int ); 709insert into atacc1 (test) values (0); 710-- add a primary key column without a default (fails). 711alter table atacc1 add column test2 int primary key; 712-- now add a primary key column with a default (succeeds). 713alter table atacc1 add column test2 int default 0 primary key; 714drop table atacc1; 715 716-- additionally, we've seen issues with foreign key validation not being 717-- properly delayed until after a table rewrite. Check that works ok. 718create table atacc1 (a int primary key); 719alter table atacc1 add constraint atacc1_fkey foreign key (a) references atacc1 (a) not valid; 720alter table atacc1 validate constraint atacc1_fkey, alter a type bigint; 721drop table atacc1; 722 723-- we've also seen issues with check constraints being validated at the wrong 724-- time when there's a pending table rewrite. 725create table atacc1 (a bigint, b int); 726insert into atacc1 values(1,1); 727alter table atacc1 add constraint atacc1_chk check(b = 1) not valid; 728alter table atacc1 validate constraint atacc1_chk, alter a type int; 729drop table atacc1; 730 731-- same as above, but ensure the constraint violation is detected 732create table atacc1 (a bigint, b int); 733insert into atacc1 values(1,2); 734alter table atacc1 add constraint atacc1_chk check(b = 1) not valid; 735alter table atacc1 validate constraint atacc1_chk, alter a type int; 736drop table atacc1; 737 738-- something a little more complicated 739create table atacc1 ( test int, test2 int); 740-- add a primary key constraint 741alter table atacc1 add constraint atacc_test1 primary key (test, test2); 742-- try adding a second primary key - should fail 743alter table atacc1 add constraint atacc_test2 primary key (test); 744-- insert initial value 745insert into atacc1 (test,test2) values (4,4); 746-- should fail 747insert into atacc1 (test,test2) values (4,4); 748insert into atacc1 (test,test2) values (NULL,3); 749insert into atacc1 (test,test2) values (3, NULL); 750insert into atacc1 (test,test2) values (NULL,NULL); 751-- should all succeed 752insert into atacc1 (test,test2) values (4,5); 753insert into atacc1 (test,test2) values (5,4); 754insert into atacc1 (test,test2) values (5,5); 755drop table atacc1; 756 757-- lets do some naming tests 758create table atacc1 (test int, test2 int, primary key(test)); 759-- only first should succeed 760insert into atacc1 (test2, test) values (3, 3); 761insert into atacc1 (test2, test) values (2, 3); 762insert into atacc1 (test2, test) values (1, NULL); 763drop table atacc1; 764 765-- alter table / alter column [set/drop] not null tests 766-- try altering system catalogs, should fail 767alter table pg_class alter column relname drop not null; 768alter table pg_class alter relname set not null; 769 770-- try altering non-existent table, should fail 771alter table non_existent alter column bar set not null; 772alter table non_existent alter column bar drop not null; 773 774-- test setting columns to null and not null and vice versa 775-- test checking for null values and primary key 776create table atacc1 (test int not null) with oids; 777alter table atacc1 add constraint "atacc1_pkey" primary key (test); 778alter table atacc1 alter column test drop not null; 779alter table atacc1 drop constraint "atacc1_pkey"; 780alter table atacc1 alter column test drop not null; 781insert into atacc1 values (null); 782alter table atacc1 alter test set not null; 783delete from atacc1; 784alter table atacc1 alter test set not null; 785 786-- try altering a non-existent column, should fail 787alter table atacc1 alter bar set not null; 788alter table atacc1 alter bar drop not null; 789 790-- try altering the oid column, should fail 791alter table atacc1 alter oid set not null; 792alter table atacc1 alter oid drop not null; 793 794-- try creating a view and altering that, should fail 795create view myview as select * from atacc1; 796alter table myview alter column test drop not null; 797alter table myview alter column test set not null; 798drop view myview; 799 800drop table atacc1; 801 802-- test inheritance 803create table parent (a int); 804create table child (b varchar(255)) inherits (parent); 805 806alter table parent alter a set not null; 807insert into parent values (NULL); 808insert into child (a, b) values (NULL, 'foo'); 809alter table parent alter a drop not null; 810insert into parent values (NULL); 811insert into child (a, b) values (NULL, 'foo'); 812alter table only parent alter a set not null; 813alter table child alter a set not null; 814delete from parent; 815alter table only parent alter a set not null; 816insert into parent values (NULL); 817alter table child alter a set not null; 818insert into child (a, b) values (NULL, 'foo'); 819delete from child; 820alter table child alter a set not null; 821insert into child (a, b) values (NULL, 'foo'); 822drop table child; 823drop table parent; 824 825-- test setting and removing default values 826create table def_test ( 827 c1 int4 default 5, 828 c2 text default 'initial_default' 829); 830insert into def_test default values; 831alter table def_test alter column c1 drop default; 832insert into def_test default values; 833alter table def_test alter column c2 drop default; 834insert into def_test default values; 835alter table def_test alter column c1 set default 10; 836alter table def_test alter column c2 set default 'new_default'; 837insert into def_test default values; 838select * from def_test; 839 840-- set defaults to an incorrect type: this should fail 841alter table def_test alter column c1 set default 'wrong_datatype'; 842alter table def_test alter column c2 set default 20; 843 844-- set defaults on a non-existent column: this should fail 845alter table def_test alter column c3 set default 30; 846 847-- set defaults on views: we need to create a view, add a rule 848-- to allow insertions into it, and then alter the view to add 849-- a default 850create view def_view_test as select * from def_test; 851create rule def_view_test_ins as 852 on insert to def_view_test 853 do instead insert into def_test select new.*; 854insert into def_view_test default values; 855alter table def_view_test alter column c1 set default 45; 856insert into def_view_test default values; 857alter table def_view_test alter column c2 set default 'view_default'; 858insert into def_view_test default values; 859select * from def_view_test; 860 861drop rule def_view_test_ins on def_view_test; 862drop view def_view_test; 863drop table def_test; 864 865-- alter table / drop column tests 866-- try altering system catalogs, should fail 867alter table pg_class drop column relname; 868 869-- try altering non-existent table, should fail 870alter table nosuchtable drop column bar; 871 872-- test dropping columns 873create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; 874insert into atacc1 values (1, 2, 3, 4); 875alter table atacc1 drop a; 876alter table atacc1 drop a; 877 878-- SELECTs 879select * from atacc1; 880select * from atacc1 order by a; 881select * from atacc1 order by "........pg.dropped.1........"; 882select * from atacc1 group by a; 883select * from atacc1 group by "........pg.dropped.1........"; 884select atacc1.* from atacc1; 885select a from atacc1; 886select atacc1.a from atacc1; 887select b,c,d from atacc1; 888select a,b,c,d from atacc1; 889select * from atacc1 where a = 1; 890select "........pg.dropped.1........" from atacc1; 891select atacc1."........pg.dropped.1........" from atacc1; 892select "........pg.dropped.1........",b,c,d from atacc1; 893select * from atacc1 where "........pg.dropped.1........" = 1; 894 895-- UPDATEs 896update atacc1 set a = 3; 897update atacc1 set b = 2 where a = 3; 898update atacc1 set "........pg.dropped.1........" = 3; 899update atacc1 set b = 2 where "........pg.dropped.1........" = 3; 900 901-- INSERTs 902insert into atacc1 values (10, 11, 12, 13); 903insert into atacc1 values (default, 11, 12, 13); 904insert into atacc1 values (11, 12, 13); 905insert into atacc1 (a) values (10); 906insert into atacc1 (a) values (default); 907insert into atacc1 (a,b,c,d) values (10,11,12,13); 908insert into atacc1 (a,b,c,d) values (default,11,12,13); 909insert into atacc1 (b,c,d) values (11,12,13); 910insert into atacc1 ("........pg.dropped.1........") values (10); 911insert into atacc1 ("........pg.dropped.1........") values (default); 912insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13); 913insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13); 914 915-- DELETEs 916delete from atacc1 where a = 3; 917delete from atacc1 where "........pg.dropped.1........" = 3; 918delete from atacc1; 919 920-- try dropping a non-existent column, should fail 921alter table atacc1 drop bar; 922 923-- try dropping the oid column, should succeed 924alter table atacc1 drop oid; 925 926-- try dropping the xmin column, should fail 927alter table atacc1 drop xmin; 928 929-- try creating a view and altering that, should fail 930create view myview as select * from atacc1; 931select * from myview; 932alter table myview drop d; 933drop view myview; 934 935-- test some commands to make sure they fail on the dropped column 936analyze atacc1(a); 937analyze atacc1("........pg.dropped.1........"); 938vacuum analyze atacc1(a); 939vacuum analyze atacc1("........pg.dropped.1........"); 940comment on column atacc1.a is 'testing'; 941comment on column atacc1."........pg.dropped.1........" is 'testing'; 942alter table atacc1 alter a set storage plain; 943alter table atacc1 alter "........pg.dropped.1........" set storage plain; 944alter table atacc1 alter a set statistics 0; 945alter table atacc1 alter "........pg.dropped.1........" set statistics 0; 946alter table atacc1 alter a set default 3; 947alter table atacc1 alter "........pg.dropped.1........" set default 3; 948alter table atacc1 alter a drop default; 949alter table atacc1 alter "........pg.dropped.1........" drop default; 950alter table atacc1 alter a set not null; 951alter table atacc1 alter "........pg.dropped.1........" set not null; 952alter table atacc1 alter a drop not null; 953alter table atacc1 alter "........pg.dropped.1........" drop not null; 954alter table atacc1 rename a to x; 955alter table atacc1 rename "........pg.dropped.1........" to x; 956alter table atacc1 add primary key(a); 957alter table atacc1 add primary key("........pg.dropped.1........"); 958alter table atacc1 add unique(a); 959alter table atacc1 add unique("........pg.dropped.1........"); 960alter table atacc1 add check (a > 3); 961alter table atacc1 add check ("........pg.dropped.1........" > 3); 962create table atacc2 (id int4 unique); 963alter table atacc1 add foreign key (a) references atacc2(id); 964alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); 965alter table atacc2 add foreign key (id) references atacc1(a); 966alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........"); 967drop table atacc2; 968create index "testing_idx" on atacc1(a); 969create index "testing_idx" on atacc1("........pg.dropped.1........"); 970 971-- test create as and select into 972insert into atacc1 values (21, 22, 23); 973create table test1 as select * from atacc1; 974select * from test1; 975drop table test1; 976select * into test2 from atacc1; 977select * from test2; 978drop table test2; 979 980-- try dropping all columns 981alter table atacc1 drop c; 982alter table atacc1 drop d; 983alter table atacc1 drop b; 984select * from atacc1; 985 986drop table atacc1; 987 988-- test constraint error reporting in presence of dropped columns 989create table atacc1 (id serial primary key, value int check (value < 10)); 990insert into atacc1(value) values (100); 991alter table atacc1 drop column value; 992alter table atacc1 add column value int check (value < 10); 993insert into atacc1(value) values (100); 994insert into atacc1(id, value) values (null, 0); 995drop table atacc1; 996 997-- test inheritance 998create table parent (a int, b int, c int); 999insert into parent values (1, 2, 3); 1000alter table parent drop a; 1001create table child (d varchar(255)) inherits (parent); 1002insert into child values (12, 13, 'testing'); 1003 1004select * from parent; 1005select * from child; 1006alter table parent drop c; 1007select * from parent; 1008select * from child; 1009 1010drop table child; 1011drop table parent; 1012 1013-- check error cases for inheritance column merging 1014create table parent (a float8, b numeric(10,4), c text collate "C"); 1015 1016create table child (a float4) inherits (parent); -- fail 1017create table child (b decimal(10,7)) inherits (parent); -- fail 1018create table child (c text collate "POSIX") inherits (parent); -- fail 1019create table child (a double precision, b decimal(10,4)) inherits (parent); 1020 1021drop table child; 1022drop table parent; 1023 1024-- test copy in/out 1025create table test (a int4, b int4, c int4); 1026insert into test values (1,2,3); 1027alter table test drop a; 1028copy test to stdout; 1029copy test(a) to stdout; 1030copy test("........pg.dropped.1........") to stdout; 1031copy test from stdin; 103210 11 12 1033\. 1034select * from test; 1035copy test from stdin; 103621 22 1037\. 1038select * from test; 1039copy test(a) from stdin; 1040copy test("........pg.dropped.1........") from stdin; 1041copy test(b,c) from stdin; 104231 32 1043\. 1044select * from test; 1045drop table test; 1046 1047-- test inheritance 1048 1049create table dropColumn (a int, b int, e int); 1050create table dropColumnChild (c int) inherits (dropColumn); 1051create table dropColumnAnother (d int) inherits (dropColumnChild); 1052 1053-- these two should fail 1054alter table dropColumnchild drop column a; 1055alter table only dropColumnChild drop column b; 1056 1057 1058 1059-- these three should work 1060alter table only dropColumn drop column e; 1061alter table dropColumnChild drop column c; 1062alter table dropColumn drop column a; 1063 1064create table renameColumn (a int); 1065create table renameColumnChild (b int) inherits (renameColumn); 1066create table renameColumnAnother (c int) inherits (renameColumnChild); 1067 1068-- these three should fail 1069alter table renameColumnChild rename column a to d; 1070alter table only renameColumnChild rename column a to d; 1071alter table only renameColumn rename column a to d; 1072 1073-- these should work 1074alter table renameColumn rename column a to d; 1075alter table renameColumnChild rename column b to a; 1076 1077-- these should work 1078alter table if exists doesnt_exist_tab rename column a to d; 1079alter table if exists doesnt_exist_tab rename column b to a; 1080 1081-- this should work 1082alter table renameColumn add column w int; 1083 1084-- this should fail 1085alter table only renameColumn add column x int; 1086 1087 1088-- Test corner cases in dropping of inherited columns 1089 1090create table p1 (f1 int, f2 int); 1091create table c1 (f1 int not null) inherits(p1); 1092 1093-- should be rejected since c1.f1 is inherited 1094alter table c1 drop column f1; 1095-- should work 1096alter table p1 drop column f1; 1097-- c1.f1 is still there, but no longer inherited 1098select f1 from c1; 1099alter table c1 drop column f1; 1100select f1 from c1; 1101 1102drop table p1 cascade; 1103 1104create table p1 (f1 int, f2 int); 1105create table c1 () inherits(p1); 1106 1107-- should be rejected since c1.f1 is inherited 1108alter table c1 drop column f1; 1109alter table p1 drop column f1; 1110-- c1.f1 is dropped now, since there is no local definition for it 1111select f1 from c1; 1112 1113drop table p1 cascade; 1114 1115create table p1 (f1 int, f2 int); 1116create table c1 () inherits(p1); 1117 1118-- should be rejected since c1.f1 is inherited 1119alter table c1 drop column f1; 1120alter table only p1 drop column f1; 1121-- c1.f1 is NOT dropped, but must now be considered non-inherited 1122alter table c1 drop column f1; 1123 1124drop table p1 cascade; 1125 1126create table p1 (f1 int, f2 int); 1127create table c1 (f1 int not null) inherits(p1); 1128 1129-- should be rejected since c1.f1 is inherited 1130alter table c1 drop column f1; 1131alter table only p1 drop column f1; 1132-- c1.f1 is still there, but no longer inherited 1133alter table c1 drop column f1; 1134 1135drop table p1 cascade; 1136 1137create table p1(id int, name text); 1138create table p2(id2 int, name text, height int); 1139create table c1(age int) inherits(p1,p2); 1140create table gc1() inherits (c1); 1141 1142select relname, attname, attinhcount, attislocal 1143from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) 1144where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped 1145order by relname, attnum; 1146 1147-- should work 1148alter table only p1 drop column name; 1149-- should work. Now c1.name is local and inhcount is 0. 1150alter table p2 drop column name; 1151-- should be rejected since its inherited 1152alter table gc1 drop column name; 1153-- should work, and drop gc1.name along 1154alter table c1 drop column name; 1155-- should fail: column does not exist 1156alter table gc1 drop column name; 1157-- should work and drop the attribute in all tables 1158alter table p2 drop column height; 1159 1160-- IF EXISTS test 1161create table dropColumnExists (); 1162alter table dropColumnExists drop column non_existing; --fail 1163alter table dropColumnExists drop column if exists non_existing; --succeed 1164 1165select relname, attname, attinhcount, attislocal 1166from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) 1167where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped 1168order by relname, attnum; 1169 1170drop table p1, p2 cascade; 1171 1172-- test attinhcount tracking with merged columns 1173 1174create table depth0(); 1175create table depth1(c text) inherits (depth0); 1176create table depth2() inherits (depth1); 1177alter table depth0 add c text; 1178 1179select attrelid::regclass, attname, attinhcount, attislocal 1180from pg_attribute 1181where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2') 1182order by attrelid::regclass::text, attnum; 1183 1184-- 1185-- Test the ALTER TABLE SET WITH/WITHOUT OIDS command 1186-- 1187create table altstartwith (col integer) with oids; 1188 1189insert into altstartwith values (1); 1190 1191select oid > 0, * from altstartwith; 1192 1193alter table altstartwith set without oids; 1194 1195select oid > 0, * from altstartwith; -- fails 1196select * from altstartwith; 1197 1198alter table altstartwith set with oids; 1199 1200select oid > 0, * from altstartwith; 1201 1202drop table altstartwith; 1203 1204-- Check inheritance cases 1205create table altwithoid (col integer) with oids; 1206 1207-- Inherits parents oid column anyway 1208create table altinhoid () inherits (altwithoid) without oids; 1209 1210insert into altinhoid values (1); 1211 1212select oid > 0, * from altwithoid; 1213select oid > 0, * from altinhoid; 1214 1215alter table altwithoid set without oids; 1216 1217select oid > 0, * from altwithoid; -- fails 1218select oid > 0, * from altinhoid; -- fails 1219select * from altwithoid; 1220select * from altinhoid; 1221 1222alter table altwithoid set with oids; 1223 1224select oid > 0, * from altwithoid; 1225select oid > 0, * from altinhoid; 1226 1227drop table altwithoid cascade; 1228 1229create table altwithoid (col integer) without oids; 1230 1231-- child can have local oid column 1232create table altinhoid () inherits (altwithoid) with oids; 1233 1234insert into altinhoid values (1); 1235 1236select oid > 0, * from altwithoid; -- fails 1237select oid > 0, * from altinhoid; 1238 1239alter table altwithoid set with oids; 1240 1241select oid > 0, * from altwithoid; 1242select oid > 0, * from altinhoid; 1243 1244-- the child's local definition should remain 1245alter table altwithoid set without oids; 1246 1247select oid > 0, * from altwithoid; -- fails 1248select oid > 0, * from altinhoid; 1249 1250drop table altwithoid cascade; 1251 1252-- test renumbering of child-table columns in inherited operations 1253 1254create table p1 (f1 int); 1255create table c1 (f2 text, f3 int) inherits (p1); 1256 1257alter table p1 add column a1 int check (a1 > 0); 1258alter table p1 add column f2 text; 1259 1260insert into p1 values (1,2,'abc'); 1261insert into c1 values(11,'xyz',33,0); -- should fail 1262insert into c1 values(11,'xyz',33,22); 1263 1264select * from p1; 1265update p1 set a1 = a1 + 1, f2 = upper(f2); 1266select * from p1; 1267 1268drop table p1 cascade; 1269 1270-- test that operations with a dropped column do not try to reference 1271-- its datatype 1272 1273create domain mytype as text; 1274create temp table foo (f1 text, f2 mytype, f3 text); 1275 1276insert into foo values('bb','cc','dd'); 1277select * from foo; 1278 1279drop domain mytype cascade; 1280 1281select * from foo; 1282insert into foo values('qq','rr'); 1283select * from foo; 1284update foo set f3 = 'zz'; 1285select * from foo; 1286select f3,max(f1) from foo group by f3; 1287 1288-- Simple tests for alter table column type 1289alter table foo alter f1 TYPE integer; -- fails 1290alter table foo alter f1 TYPE varchar(10); 1291 1292create table anothertab (atcol1 serial8, atcol2 boolean, 1293 constraint anothertab_chk check (atcol1 <= 3)); 1294 1295insert into anothertab (atcol1, atcol2) values (default, true); 1296insert into anothertab (atcol1, atcol2) values (default, false); 1297select * from anothertab; 1298 1299alter table anothertab alter column atcol1 type boolean; -- fails 1300alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails 1301alter table anothertab alter column atcol1 type integer; 1302 1303select * from anothertab; 1304 1305insert into anothertab (atcol1, atcol2) values (45, null); -- fails 1306insert into anothertab (atcol1, atcol2) values (default, null); 1307 1308select * from anothertab; 1309 1310alter table anothertab alter column atcol2 type text 1311 using case when atcol2 is true then 'IT WAS TRUE' 1312 when atcol2 is false then 'IT WAS FALSE' 1313 else 'IT WAS NULL!' end; 1314 1315select * from anothertab; 1316alter table anothertab alter column atcol1 type boolean 1317 using case when atcol1 % 2 = 0 then true else false end; -- fails 1318alter table anothertab alter column atcol1 drop default; 1319alter table anothertab alter column atcol1 type boolean 1320 using case when atcol1 % 2 = 0 then true else false end; -- fails 1321alter table anothertab drop constraint anothertab_chk; 1322alter table anothertab drop constraint anothertab_chk; -- fails 1323alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds 1324 1325alter table anothertab alter column atcol1 type boolean 1326 using case when atcol1 % 2 = 0 then true else false end; 1327 1328select * from anothertab; 1329 1330drop table anothertab; 1331 1332-- Test index handling in alter table column type (cf. bugs #15835, #15865) 1333create table anothertab(f1 int primary key, f2 int unique, 1334 f3 int, f4 int, f5 int); 1335alter table anothertab 1336 add exclude using btree (f3 with =); 1337alter table anothertab 1338 add exclude using btree (f4 with =) where (f4 is not null); 1339alter table anothertab 1340 add exclude using btree (f4 with =) where (f5 > 0); 1341alter table anothertab 1342 add unique(f1,f4); 1343create index on anothertab(f2,f3); 1344create unique index on anothertab(f4); 1345 1346\d anothertab 1347alter table anothertab alter column f1 type bigint; 1348alter table anothertab 1349 alter column f2 type bigint, 1350 alter column f3 type bigint, 1351 alter column f4 type bigint; 1352alter table anothertab alter column f5 type bigint; 1353\d anothertab 1354 1355drop table anothertab; 1356 1357create table another (f1 int, f2 text); 1358 1359insert into another values(1, 'one'); 1360insert into another values(2, 'two'); 1361insert into another values(3, 'three'); 1362 1363select * from another; 1364 1365alter table another 1366 alter f1 type text using f2 || ' more', 1367 alter f2 type bigint using f1 * 10; 1368 1369select * from another; 1370 1371drop table another; 1372 1373-- table's row type 1374create table tab1 (a int, b text); 1375create table tab2 (x int, y tab1); 1376alter table tab1 alter column b type varchar; -- fails 1377 1378-- disallow recursive containment of row types 1379create temp table recur1 (f1 int); 1380alter table recur1 add column f2 recur1; -- fails 1381alter table recur1 add column f2 recur1[]; -- fails 1382create domain array_of_recur1 as recur1[]; 1383alter table recur1 add column f2 array_of_recur1; -- fails 1384create temp table recur2 (f1 int, f2 recur1); 1385alter table recur1 add column f2 recur2; -- fails 1386alter table recur1 add column f2 int; 1387alter table recur1 alter column f2 type recur2; -- fails 1388 1389-- SET STORAGE may need to add a TOAST table 1390create table test_storage (a text); 1391alter table test_storage alter a set storage plain; 1392alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table 1393alter table test_storage alter a set storage extended; -- re-add TOAST table 1394 1395select reltoastrelid <> 0 as has_toast_table 1396from pg_class 1397where oid = 'test_storage'::regclass; 1398 1399-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779) 1400CREATE TABLE test_inh_check (a float check (a > 10.2), b float); 1401CREATE TABLE test_inh_check_child() INHERITS(test_inh_check); 1402\d test_inh_check 1403\d test_inh_check_child 1404select relname, conname, coninhcount, conislocal, connoinherit 1405 from pg_constraint c, pg_class r 1406 where relname like 'test_inh_check%' and c.conrelid = r.oid 1407 order by 1, 2; 1408ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric; 1409\d test_inh_check 1410\d test_inh_check_child 1411select relname, conname, coninhcount, conislocal, connoinherit 1412 from pg_constraint c, pg_class r 1413 where relname like 'test_inh_check%' and c.conrelid = r.oid 1414 order by 1, 2; 1415-- also try noinherit, local, and local+inherited cases 1416ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT; 1417ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000); 1418ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1); 1419ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1); 1420\d test_inh_check 1421\d test_inh_check_child 1422select relname, conname, coninhcount, conislocal, connoinherit 1423 from pg_constraint c, pg_class r 1424 where relname like 'test_inh_check%' and c.conrelid = r.oid 1425 order by 1, 2; 1426ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric; 1427\d test_inh_check 1428\d test_inh_check_child 1429select relname, conname, coninhcount, conislocal, connoinherit 1430 from pg_constraint c, pg_class r 1431 where relname like 'test_inh_check%' and c.conrelid = r.oid 1432 order by 1, 2; 1433 1434-- ALTER COLUMN TYPE with different schema in children 1435-- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com 1436CREATE TABLE test_type_diff (f1 int); 1437CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff); 1438ALTER TABLE test_type_diff ADD COLUMN f2 int; 1439INSERT INTO test_type_diff_c VALUES (1, 2, 3); 1440ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint; 1441 1442CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8); 1443CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2); 1444CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4); 1445CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8); 1446ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2; 1447ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2; 1448ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2; 1449INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3); 1450INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6); 1451INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9); 1452ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8; 1453-- whole-row references are disallowed 1454ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2)); 1455 1456-- check for rollback of ANALYZE corrupting table property flags (bug #11638) 1457CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text); 1458CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text); 1459BEGIN; 1460ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey; 1461ANALYZE check_fk_presence_2; 1462ROLLBACK; 1463\d check_fk_presence_2 1464DROP TABLE check_fk_presence_1, check_fk_presence_2; 1465 1466-- check column addition within a view (bug #14876) 1467create table at_base_table(id int, stuff text); 1468insert into at_base_table values (23, 'skidoo'); 1469create view at_view_1 as select * from at_base_table bt; 1470create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1; 1471\d+ at_view_1 1472\d+ at_view_2 1473explain (verbose, costs off) select * from at_view_2; 1474select * from at_view_2; 1475 1476create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt; 1477\d+ at_view_1 1478\d+ at_view_2 1479explain (verbose, costs off) select * from at_view_2; 1480select * from at_view_2; 1481 1482drop view at_view_2; 1483drop view at_view_1; 1484drop table at_base_table; 1485 1486-- 1487-- lock levels 1488-- 1489drop type lockmodes; 1490create type lockmodes as enum ( 1491 'SIReadLock' 1492,'AccessShareLock' 1493,'RowShareLock' 1494,'RowExclusiveLock' 1495,'ShareUpdateExclusiveLock' 1496,'ShareLock' 1497,'ShareRowExclusiveLock' 1498,'ExclusiveLock' 1499,'AccessExclusiveLock' 1500); 1501 1502drop view my_locks; 1503create or replace view my_locks as 1504select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode 1505from pg_locks l join pg_class c on l.relation = c.oid 1506where virtualtransaction = ( 1507 select virtualtransaction 1508 from pg_locks 1509 where transactionid = txid_current()::integer) 1510and locktype = 'relation' 1511and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') 1512and c.relname != 'my_locks' 1513group by c.relname; 1514 1515create table alterlock (f1 int primary key, f2 text); 1516insert into alterlock values (1, 'foo'); 1517create table alterlock2 (f3 int primary key, f1 int); 1518insert into alterlock2 values (1, 1); 1519 1520begin; alter table alterlock alter column f2 set statistics 150; 1521select * from my_locks order by 1; 1522rollback; 1523 1524begin; alter table alterlock cluster on alterlock_pkey; 1525select * from my_locks order by 1; 1526commit; 1527 1528begin; alter table alterlock set without cluster; 1529select * from my_locks order by 1; 1530commit; 1531 1532begin; alter table alterlock set (fillfactor = 100); 1533select * from my_locks order by 1; 1534commit; 1535 1536begin; alter table alterlock reset (fillfactor); 1537select * from my_locks order by 1; 1538commit; 1539 1540begin; alter table alterlock set (toast.autovacuum_enabled = off); 1541select * from my_locks order by 1; 1542commit; 1543 1544begin; alter table alterlock set (autovacuum_enabled = off); 1545select * from my_locks order by 1; 1546commit; 1547 1548begin; alter table alterlock alter column f2 set (n_distinct = 1); 1549select * from my_locks order by 1; 1550rollback; 1551 1552-- test that mixing options with different lock levels works as expected 1553begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80); 1554select * from my_locks order by 1; 1555commit; 1556 1557begin; alter table alterlock alter column f2 set storage extended; 1558select * from my_locks order by 1; 1559rollback; 1560 1561begin; alter table alterlock alter column f2 set default 'x'; 1562select * from my_locks order by 1; 1563rollback; 1564 1565begin; 1566create trigger ttdummy 1567 before delete or update on alterlock 1568 for each row 1569 execute procedure 1570 ttdummy (1, 1); 1571select * from my_locks order by 1; 1572rollback; 1573 1574begin; 1575select * from my_locks order by 1; 1576alter table alterlock2 add foreign key (f1) references alterlock (f1); 1577select * from my_locks order by 1; 1578rollback; 1579 1580begin; 1581alter table alterlock2 1582add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID; 1583select * from my_locks order by 1; 1584commit; 1585begin; 1586alter table alterlock2 validate constraint alterlock2nv; 1587select * from my_locks order by 1; 1588rollback; 1589 1590create or replace view my_locks as 1591select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode 1592from pg_locks l join pg_class c on l.relation = c.oid 1593where virtualtransaction = ( 1594 select virtualtransaction 1595 from pg_locks 1596 where transactionid = txid_current()::integer) 1597and locktype = 'relation' 1598and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog') 1599and c.relname = 'my_locks' 1600group by c.relname; 1601 1602-- raise exception 1603alter table my_locks set (autovacuum_enabled = false); 1604alter view my_locks set (autovacuum_enabled = false); 1605alter table my_locks reset (autovacuum_enabled); 1606alter view my_locks reset (autovacuum_enabled); 1607 1608begin; 1609alter view my_locks set (security_barrier=off); 1610select * from my_locks order by 1; 1611alter view my_locks reset (security_barrier); 1612rollback; 1613 1614-- this test intentionally applies the ALTER TABLE command against a view, but 1615-- uses a view option so we expect this to succeed. This form of SQL is 1616-- accepted for historical reasons, as shown in the docs for ALTER VIEW 1617begin; 1618alter table my_locks set (security_barrier=off); 1619select * from my_locks order by 1; 1620alter table my_locks reset (security_barrier); 1621rollback; 1622 1623-- cleanup 1624drop table alterlock2; 1625drop table alterlock; 1626drop view my_locks; 1627drop type lockmodes; 1628 1629-- 1630-- alter function 1631-- 1632create function test_strict(text) returns text as 1633 'select coalesce($1, ''got passed a null'');' 1634 language sql returns null on null input; 1635select test_strict(NULL); 1636alter function test_strict(text) called on null input; 1637select test_strict(NULL); 1638 1639create function non_strict(text) returns text as 1640 'select coalesce($1, ''got passed a null'');' 1641 language sql called on null input; 1642select non_strict(NULL); 1643alter function non_strict(text) returns null on null input; 1644select non_strict(NULL); 1645 1646-- 1647-- alter object set schema 1648-- 1649 1650create schema alter1; 1651create schema alter2; 1652 1653create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); 1654 1655create view alter1.v1 as select * from alter1.t1; 1656 1657create function alter1.plus1(int) returns int as 'select $1+1' language sql; 1658 1659create domain alter1.posint integer check (value > 0); 1660 1661create type alter1.ctype as (f1 int, f2 text); 1662 1663create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql 1664as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2'; 1665 1666create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype); 1667 1668create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as 1669 operator 1 alter1.=(alter1.ctype, alter1.ctype); 1670 1671create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8; 1672 1673create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 1674create text search configuration alter1.cfg(parser = alter1.prs); 1675create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize); 1676create text search dictionary alter1.dict(template = alter1.tmpl); 1677 1678insert into alter1.t1(f2) values(11); 1679insert into alter1.t1(f2) values(12); 1680 1681alter table alter1.t1 set schema alter1; -- no-op, same schema 1682alter table alter1.t1 set schema alter2; 1683alter table alter1.v1 set schema alter2; 1684alter function alter1.plus1(int) set schema alter2; 1685alter domain alter1.posint set schema alter2; 1686alter operator class alter1.ctype_hash_ops using hash set schema alter2; 1687alter operator family alter1.ctype_hash_ops using hash set schema alter2; 1688alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2; 1689alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2; 1690alter type alter1.ctype set schema alter1; -- no-op, same schema 1691alter type alter1.ctype set schema alter2; 1692alter conversion alter1.ascii_to_utf8 set schema alter2; 1693alter text search parser alter1.prs set schema alter2; 1694alter text search configuration alter1.cfg set schema alter2; 1695alter text search template alter1.tmpl set schema alter2; 1696alter text search dictionary alter1.dict set schema alter2; 1697 1698-- this should succeed because nothing is left in alter1 1699drop schema alter1; 1700 1701insert into alter2.t1(f2) values(13); 1702insert into alter2.t1(f2) values(14); 1703 1704select * from alter2.t1; 1705 1706select * from alter2.v1; 1707 1708select alter2.plus1(41); 1709 1710-- clean up 1711drop schema alter2 cascade; 1712 1713-- 1714-- composite types 1715-- 1716 1717CREATE TYPE test_type AS (a int); 1718\d test_type 1719 1720ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails 1721 1722ALTER TYPE test_type ADD ATTRIBUTE b text; 1723\d test_type 1724 1725ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails 1726 1727ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar; 1728\d test_type 1729 1730ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer; 1731\d test_type 1732 1733ALTER TYPE test_type DROP ATTRIBUTE b; 1734\d test_type 1735 1736ALTER TYPE test_type DROP ATTRIBUTE c; -- fails 1737 1738ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c; 1739 1740ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean; 1741\d test_type 1742 1743ALTER TYPE test_type RENAME ATTRIBUTE a TO aa; 1744ALTER TYPE test_type RENAME ATTRIBUTE d TO dd; 1745\d test_type 1746 1747DROP TYPE test_type; 1748 1749CREATE TYPE test_type1 AS (a int, b text); 1750CREATE TABLE test_tbl1 (x int, y test_type1); 1751ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails 1752 1753CREATE TYPE test_type2 AS (a int, b text); 1754CREATE TABLE test_tbl2 OF test_type2; 1755CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2); 1756\d test_type2 1757\d test_tbl2 1758 1759ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails 1760ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE; 1761\d test_type2 1762\d test_tbl2 1763 1764ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails 1765ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE; 1766\d test_type2 1767\d test_tbl2 1768 1769ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails 1770ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE; 1771\d test_type2 1772\d test_tbl2 1773 1774ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails 1775ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE; 1776\d test_type2 1777\d test_tbl2 1778\d test_tbl2_subclass 1779 1780DROP TABLE test_tbl2_subclass; 1781 1782CREATE TYPE test_typex AS (a int, b text); 1783CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0)); 1784ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails 1785ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE; 1786\d test_tblx 1787DROP TABLE test_tblx; 1788DROP TYPE test_typex; 1789 1790-- This test isn't that interesting on its own, but the purpose is to leave 1791-- behind a table to test pg_upgrade with. The table has a composite type 1792-- column in it, and the composite type has a dropped attribute. 1793CREATE TYPE test_type3 AS (a int); 1794CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3; 1795ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int; 1796 1797CREATE TYPE test_type_empty AS (); 1798DROP TYPE test_type_empty; 1799 1800-- 1801-- typed tables: OF / NOT OF 1802-- 1803 1804CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2)); 1805ALTER TYPE tt_t0 DROP ATTRIBUTE z; 1806CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK 1807CREATE TABLE tt1 (x int, y bigint); -- wrong base type 1808CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod 1809CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order 1810CREATE TABLE tt4 (x int); -- too few columns 1811CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns 1812CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent 1813CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS; 1814ALTER TABLE tt7 DROP q; -- OK 1815 1816ALTER TABLE tt0 OF tt_t0; 1817ALTER TABLE tt1 OF tt_t0; 1818ALTER TABLE tt2 OF tt_t0; 1819ALTER TABLE tt3 OF tt_t0; 1820ALTER TABLE tt4 OF tt_t0; 1821ALTER TABLE tt5 OF tt_t0; 1822ALTER TABLE tt6 OF tt_t0; 1823ALTER TABLE tt7 OF tt_t0; 1824 1825CREATE TYPE tt_t1 AS (x int, y numeric(8,2)); 1826ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table 1827ALTER TABLE tt7 NOT OF; 1828\d tt7 1829 1830-- make sure we can drop a constraint on the parent but it remains on the child 1831CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL)); 1832CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent); 1833ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check"; 1834-- should fail 1835INSERT INTO test_drop_constr_child (c) VALUES (NULL); 1836DROP TABLE test_drop_constr_parent CASCADE; 1837 1838-- 1839-- IF EXISTS test 1840-- 1841ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; 1842ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); 1843ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); 1844ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; 1845ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; 1846ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; 1847 1848CREATE TABLE tt8(a int); 1849CREATE SCHEMA alter2; 1850 1851ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; 1852ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f); 1853ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10); 1854ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0; 1855ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1; 1856ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2; 1857 1858\d alter2.tt8 1859 1860DROP TABLE alter2.tt8; 1861DROP SCHEMA alter2; 1862 1863 1864-- Check that comments on constraints and indexes are not lost at ALTER TABLE. 1865CREATE TABLE comment_test ( 1866 id int, 1867 positive_col int CHECK (positive_col > 0), 1868 indexed_col int, 1869 CONSTRAINT comment_test_pk PRIMARY KEY (id)); 1870CREATE INDEX comment_test_index ON comment_test(indexed_col); 1871 1872COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test'; 1873COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test'; 1874COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col'; 1875COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test'; 1876COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test'; 1877 1878SELECT col_description('comment_test'::regclass, 1) as comment; 1879SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; 1880SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; 1881 1882-- Change the datatype of all the columns. ALTER TABLE is optimized to not 1883-- rebuild an index if the new data type is binary compatible with the old 1884-- one. Check do a dummy ALTER TABLE that doesn't change the datatype 1885-- first, to test that no-op codepath, and another one that does. 1886ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int; 1887ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text; 1888ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int; 1889ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; 1890ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int; 1891ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint; 1892 1893-- Check that the comments are intact. 1894SELECT col_description('comment_test'::regclass, 1) as comment; 1895SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2; 1896SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2; 1897 1898-- Check compatibility for foreign keys and comments. This is done 1899-- separately as rebuilding the column type of the parent leads 1900-- to an error and would reduce the test scope. 1901CREATE TABLE comment_test_child ( 1902 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test); 1903CREATE INDEX comment_test_child_fk ON comment_test_child(id); 1904COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child'; 1905COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child'; 1906COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child'; 1907 1908-- Change column type of parent 1909ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text; 1910ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer; 1911 1912-- Comments should be intact 1913SELECT col_description('comment_test_child'::regclass, 1) as comment; 1914SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test_child'::regclass ORDER BY 1, 2; 1915SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2; 1916 1917-- Check that we map relation oids to filenodes and back correctly. Only 1918-- display bad mappings so the test output doesn't change all the time. A 1919-- filenode function call can return NULL for a relation dropped concurrently 1920-- with the call's surrounding query, so ignore a NULL mapped_oid for 1921-- relations that no longer exist after all calls finish. 1922CREATE TEMP TABLE filenode_mapping AS 1923SELECT 1924 oid, mapped_oid, reltablespace, relfilenode, relname 1925FROM pg_class, 1926 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid 1927WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid; 1928 1929SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid 1930WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL; 1931 1932-- Checks on creating and manipulation of user defined relations in 1933-- pg_catalog. 1934-- 1935-- XXX: It would be useful to add checks around trying to manipulate 1936-- catalog tables, but that might have ugly consequences when run 1937-- against an existing server with allow_system_table_mods = on. 1938 1939SHOW allow_system_table_mods; 1940-- disallowed because of search_path issues with pg_dump 1941CREATE TABLE pg_catalog.new_system_table(); 1942-- instead create in public first, move to catalog 1943CREATE TABLE new_system_table(id serial primary key, othercol text); 1944ALTER TABLE new_system_table SET SCHEMA pg_catalog; 1945 1946-- XXX: it's currently impossible to move relations out of pg_catalog 1947ALTER TABLE new_system_table SET SCHEMA public; 1948-- move back, will be ignored -- already there 1949ALTER TABLE new_system_table SET SCHEMA pg_catalog; 1950ALTER TABLE new_system_table RENAME TO old_system_table; 1951CREATE INDEX old_system_table__othercol ON old_system_table (othercol); 1952INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata'); 1953UPDATE old_system_table SET id = -id; 1954DELETE FROM old_system_table WHERE othercol = 'somedata'; 1955TRUNCATE old_system_table; 1956ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey; 1957ALTER TABLE old_system_table DROP COLUMN othercol; 1958DROP TABLE old_system_table; 1959 1960-- set logged 1961CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT); 1962-- check relpersistence of an unlogged table 1963SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' 1964UNION ALL 1965SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' 1966UNION ALL 1967SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' 1968ORDER BY relname; 1969CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key 1970CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key 1971ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key 1972ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists 1973ALTER TABLE unlogged1 SET LOGGED; 1974-- check relpersistence of an unlogged table after changing to permanent 1975SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1' 1976UNION ALL 1977SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1' 1978UNION ALL 1979SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1' 1980ORDER BY relname; 1981ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing 1982DROP TABLE unlogged3; 1983DROP TABLE unlogged2; 1984DROP TABLE unlogged1; 1985-- set unlogged 1986CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); 1987-- check relpersistence of a permanent table 1988SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' 1989UNION ALL 1990SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' 1991UNION ALL 1992SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' 1993ORDER BY relname; 1994CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key 1995CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key 1996ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists 1997ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key 1998ALTER TABLE logged2 SET UNLOGGED; 1999ALTER TABLE logged1 SET UNLOGGED; 2000-- check relpersistence of a permanent table after changing to unlogged 2001SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1' 2002UNION ALL 2003SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1' 2004UNION ALL 2005SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^logged1' 2006ORDER BY relname; 2007ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing 2008DROP TABLE logged3; 2009DROP TABLE logged2; 2010DROP TABLE logged1; 2011 2012-- test ADD COLUMN IF NOT EXISTS 2013CREATE TABLE test_add_column(c1 integer); 2014\d test_add_column 2015ALTER TABLE test_add_column 2016 ADD COLUMN c2 integer; 2017\d test_add_column 2018ALTER TABLE test_add_column 2019 ADD COLUMN c2 integer; -- fail because c2 already exists 2020ALTER TABLE ONLY test_add_column 2021 ADD COLUMN c2 integer; -- fail because c2 already exists 2022\d test_add_column 2023ALTER TABLE test_add_column 2024 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists 2025ALTER TABLE ONLY test_add_column 2026 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists 2027\d test_add_column 2028ALTER TABLE test_add_column 2029 ADD COLUMN c2 integer, -- fail because c2 already exists 2030 ADD COLUMN c3 integer; 2031\d test_add_column 2032ALTER TABLE test_add_column 2033 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2034 ADD COLUMN c3 integer; -- fail because c3 already exists 2035\d test_add_column 2036ALTER TABLE test_add_column 2037 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2038 ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists 2039\d test_add_column 2040ALTER TABLE test_add_column 2041 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists 2042 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists 2043 ADD COLUMN c4 integer; 2044\d test_add_column 2045DROP TABLE test_add_column; 2046 2047-- unsupported constraint types for partitioned tables 2048CREATE TABLE partitioned ( 2049 a int, 2050 b int 2051) PARTITION BY RANGE (a, (a+b+1)); 2052ALTER TABLE partitioned ADD UNIQUE (a); 2053ALTER TABLE partitioned ADD PRIMARY KEY (a); 2054ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah; 2055ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&); 2056 2057-- cannot drop column that is part of the partition key 2058ALTER TABLE partitioned DROP COLUMN a; 2059ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); 2060ALTER TABLE partitioned DROP COLUMN b; 2061ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); 2062 2063-- partitioned table cannot participate in regular inheritance 2064CREATE TABLE nonpartitioned ( 2065 a int, 2066 b int 2067); 2068ALTER TABLE partitioned INHERIT nonpartitioned; 2069ALTER TABLE nonpartitioned INHERIT partitioned; 2070 2071-- cannot add NO INHERIT constraint to partitioned tables 2072ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT; 2073 2074DROP TABLE partitioned, nonpartitioned; 2075 2076-- 2077-- ATTACH PARTITION 2078-- 2079 2080-- check that target table is partitioned 2081CREATE TABLE unparted ( 2082 a int 2083); 2084CREATE TABLE fail_part (like unparted); 2085ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a'); 2086DROP TABLE unparted, fail_part; 2087 2088-- check that partition bound is compatible 2089CREATE TABLE list_parted ( 2090 a int NOT NULL, 2091 b char(2) COLLATE "C", 2092 CONSTRAINT check_a CHECK (a > 0) 2093) PARTITION BY LIST (a); 2094CREATE TABLE fail_part (LIKE list_parted); 2095ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10); 2096DROP TABLE fail_part; 2097 2098-- check that the table being attached exists 2099ALTER TABLE list_parted ATTACH PARTITION nonexistant FOR VALUES IN (1); 2100 2101-- check ownership of the source table 2102CREATE ROLE regress_test_me; 2103CREATE ROLE regress_test_not_me; 2104CREATE TABLE not_owned_by_me (LIKE list_parted); 2105ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me; 2106SET SESSION AUTHORIZATION regress_test_me; 2107CREATE TABLE owned_by_me ( 2108 a int 2109) PARTITION BY LIST (a); 2110ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1); 2111RESET SESSION AUTHORIZATION; 2112DROP TABLE owned_by_me, not_owned_by_me; 2113DROP ROLE regress_test_not_me; 2114DROP ROLE regress_test_me; 2115 2116-- check that the table being attached is not part of regular inheritance 2117CREATE TABLE parent (LIKE list_parted); 2118CREATE TABLE child () INHERITS (parent); 2119ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1); 2120ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); 2121DROP TABLE parent CASCADE; 2122 2123-- check any TEMP-ness 2124CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); 2125CREATE TABLE perm_part (a int); 2126ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1); 2127DROP TABLE temp_parted, perm_part; 2128 2129-- check that the table being attached is not a typed table 2130CREATE TYPE mytype AS (a int); 2131CREATE TABLE fail_part OF mytype; 2132ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2133DROP TYPE mytype CASCADE; 2134 2135-- check existence (or non-existence) of oid column 2136ALTER TABLE list_parted SET WITH OIDS; 2137CREATE TABLE fail_part (a int); 2138ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2139 2140ALTER TABLE list_parted SET WITHOUT OIDS; 2141ALTER TABLE fail_part SET WITH OIDS; 2142ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2143DROP TABLE fail_part; 2144 2145-- check that the table being attached has only columns present in the parent 2146CREATE TABLE fail_part (like list_parted, c int); 2147ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2148DROP TABLE fail_part; 2149 2150-- check that the table being attached has every column of the parent 2151CREATE TABLE fail_part (a int NOT NULL); 2152ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2153DROP TABLE fail_part; 2154 2155-- check that columns match in type, collation and NOT NULL status 2156CREATE TABLE fail_part ( 2157 b char(3), 2158 a int NOT NULL 2159); 2160ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2161ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX"; 2162ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2163DROP TABLE fail_part; 2164 2165-- check that the table being attached has all constraints of the parent 2166CREATE TABLE fail_part ( 2167 b char(2) COLLATE "C", 2168 a int NOT NULL 2169); 2170ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2171 2172-- check that the constraint matches in definition with parent's constraint 2173ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0); 2174ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2175DROP TABLE fail_part; 2176 2177-- check the attributes and constraints after partition is attached 2178CREATE TABLE part_1 ( 2179 a int NOT NULL, 2180 b char(2) COLLATE "C", 2181 CONSTRAINT check_a CHECK (a > 0) 2182); 2183ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); 2184-- attislocal and conislocal are always false for merged attributes and constraints respectively. 2185SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0; 2186SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a'; 2187 2188-- check that the new partition won't overlap with an existing partition 2189CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS); 2190ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); 2191 2192-- check validation when attaching list partitions 2193CREATE TABLE list_parted2 ( 2194 a int, 2195 b char 2196) PARTITION BY LIST (a); 2197 2198-- check that violating rows are correctly reported 2199CREATE TABLE part_2 (LIKE list_parted2); 2200INSERT INTO part_2 VALUES (3, 'a'); 2201ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2202 2203-- should be ok after deleting the bad row 2204DELETE FROM part_2; 2205ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2206 2207-- adding constraints that describe the desired partition constraint 2208-- (or more restrictive) will help skip the validation scan 2209CREATE TABLE part_3_4 ( 2210 LIKE list_parted2, 2211 CONSTRAINT check_a CHECK (a IN (3)) 2212); 2213 2214-- however, if a list partition does not accept nulls, there should be 2215-- an explicit NOT NULL constraint on the partition key column for the 2216-- validation scan to be skipped; 2217ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); 2218 2219-- adding a NOT NULL constraint will cause the scan to be skipped 2220ALTER TABLE list_parted2 DETACH PARTITION part_3_4; 2221ALTER TABLE part_3_4 ALTER a SET NOT NULL; 2222ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); 2223 2224 2225-- check validation when attaching range partitions 2226CREATE TABLE range_parted ( 2227 a int, 2228 b int 2229) PARTITION BY RANGE (a, b); 2230 2231-- check that violating rows are correctly reported 2232CREATE TABLE part1 ( 2233 a int NOT NULL CHECK (a = 1), 2234 b int NOT NULL CHECK (b >= 1 AND b <= 10) 2235); 2236INSERT INTO part1 VALUES (1, 10); 2237-- Remember the TO bound is exclusive 2238ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); 2239 2240-- should be ok after deleting the bad row 2241DELETE FROM part1; 2242ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); 2243 2244-- adding constraints that describe the desired partition constraint 2245-- (or more restrictive) will help skip the validation scan 2246CREATE TABLE part2 ( 2247 a int NOT NULL CHECK (a = 1), 2248 b int NOT NULL CHECK (b >= 10 AND b < 18) 2249); 2250ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20); 2251 2252-- check that leaf partitions are scanned when attaching a partitioned 2253-- table 2254CREATE TABLE part_5 ( 2255 LIKE list_parted2 2256) PARTITION BY LIST (b); 2257 2258-- check that violating rows are correctly reported 2259CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a'); 2260INSERT INTO part_5_a (a, b) VALUES (6, 'a'); 2261ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2262 2263-- delete the faulting row and also add a constraint to skip the scan 2264DELETE FROM part_5_a WHERE a NOT IN (3); 2265ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5); 2266ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2267ALTER TABLE list_parted2 DETACH PARTITION part_5; 2268ALTER TABLE part_5 DROP CONSTRAINT check_a; 2269 2270-- scan should again be skipped, even though NOT NULL is now a column property 2271ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL; 2272ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); 2273 2274-- Check the case where attnos of the partitioning columns in the table being 2275-- attached differs from the parent. It should not affect the constraint- 2276-- checking logic that allows to skip the scan. 2277CREATE TABLE part_6 ( 2278 c int, 2279 LIKE list_parted2, 2280 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6) 2281); 2282ALTER TABLE part_6 DROP c; 2283ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6); 2284 2285-- Similar to above, but the table being attached is a partitioned table 2286-- whose partition has still different attnos for the root partitioning 2287-- columns. 2288CREATE TABLE part_7 ( 2289 LIKE list_parted2, 2290 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) 2291) PARTITION BY LIST (b); 2292CREATE TABLE part_7_a_null ( 2293 c int, 2294 d int, 2295 e int, 2296 LIKE list_parted2, -- 'a' will have attnum = 4 2297 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'), 2298 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7) 2299); 2300ALTER TABLE part_7_a_null DROP c, DROP d, DROP e; 2301ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null); 2302ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); 2303 2304-- Same example, but check this time that the constraint correctly detects 2305-- violating rows 2306ALTER TABLE list_parted2 DETACH PARTITION part_7; 2307ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped 2308INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a'); 2309SELECT tableoid::regclass, a, b FROM part_7 order by a; 2310ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7); 2311 2312-- check that the table being attached is not already a partition 2313ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); 2314 2315-- check that circular inheritance is not allowed 2316ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); 2317ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); 2318 2319-- 2320-- DETACH PARTITION 2321-- 2322 2323-- check that the table is partitioned at all 2324CREATE TABLE regular_table (a int); 2325ALTER TABLE regular_table DETACH PARTITION any_name; 2326DROP TABLE regular_table; 2327 2328-- check that the partition being detached exists at all 2329ALTER TABLE list_parted2 DETACH PARTITION part_4; 2330 2331-- check that the partition being detached is actually a partition of the parent 2332CREATE TABLE not_a_part (a int); 2333ALTER TABLE list_parted2 DETACH PARTITION not_a_part; 2334ALTER TABLE list_parted2 DETACH PARTITION part_1; 2335 2336-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and 2337-- attislocal/conislocal is set to true 2338ALTER TABLE list_parted2 DETACH PARTITION part_3_4; 2339SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0; 2340SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a'; 2341DROP TABLE part_3_4; 2342 2343-- check that a detached partition is not dropped on dropping a partitioned table 2344CREATE TABLE range_parted2 ( 2345 a int 2346) PARTITION BY RANGE(a); 2347CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100); 2348ALTER TABLE range_parted2 DETACH PARTITION part_rp; 2349DROP TABLE range_parted2; 2350SELECT * from part_rp; 2351DROP TABLE part_rp; 2352 2353-- Check ALTER TABLE commands for partitioned tables and partitions 2354 2355-- cannot add/drop column to/from *only* the parent 2356ALTER TABLE ONLY list_parted2 ADD COLUMN c int; 2357ALTER TABLE ONLY list_parted2 DROP COLUMN b; 2358 2359-- cannot add a column to partition or drop an inherited one 2360ALTER TABLE part_2 ADD COLUMN c text; 2361ALTER TABLE part_2 DROP COLUMN b; 2362 2363-- Nor rename, alter type 2364ALTER TABLE part_2 RENAME COLUMN b to c; 2365ALTER TABLE part_2 ALTER COLUMN b TYPE text; 2366 2367-- cannot add/drop NOT NULL or check constraints to *only* the parent, when 2368-- partitions exist 2369ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; 2370ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); 2371 2372ALTER TABLE list_parted2 ALTER b SET NOT NULL; 2373ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL; 2374ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz'); 2375ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b; 2376 2377-- It's alright though, if no partitions are yet created 2378CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a); 2379ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL; 2380ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0); 2381ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL; 2382ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a; 2383DROP TABLE parted_no_parts; 2384 2385-- cannot drop inherited NOT NULL or check constraints from partition 2386ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0); 2387ALTER TABLE part_2 ALTER b DROP NOT NULL; 2388ALTER TABLE part_2 DROP CONSTRAINT check_a2; 2389 2390-- Doesn't make sense to add NO INHERIT constraints on partitioned tables 2391ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT; 2392 2393-- check that a partition cannot participate in regular inheritance 2394CREATE TABLE inh_test () INHERITS (part_2); 2395CREATE TABLE inh_test (LIKE part_2); 2396ALTER TABLE inh_test INHERIT part_2; 2397ALTER TABLE part_2 INHERIT inh_test; 2398 2399-- cannot drop or alter type of partition key columns of lower level 2400-- partitioned tables; for example, part_5, which is list_parted2's 2401-- partition, is partitioned on b; 2402ALTER TABLE list_parted2 DROP COLUMN b; 2403ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; 2404 2405-- cleanup 2406DROP TABLE list_parted, list_parted2, range_parted; 2407 2408-- more tests for certain multi-level partitioning scenarios 2409create table p (a int, b int) partition by range (a, b); 2410create table p1 (b int, a int not null) partition by range (b); 2411create table p11 (like p1); 2412alter table p11 drop a; 2413alter table p11 add a int; 2414alter table p11 drop a; 2415alter table p11 add a int not null; 2416-- attnum for key attribute 'a' is different in p, p1, and p11 2417select attrelid::regclass, attname, attnum 2418from pg_attribute 2419where attname = 'a' 2420 and (attrelid = 'p'::regclass 2421 or attrelid = 'p1'::regclass 2422 or attrelid = 'p11'::regclass) 2423order by attrelid::regclass::text; 2424 2425alter table p1 attach partition p11 for values from (2) to (5); 2426 2427insert into p1 (a, b) values (2, 3); 2428-- check that partition validation scan correctly detects violating rows 2429alter table p attach partition p1 for values from (1, 2) to (1, 10); 2430 2431-- cleanup 2432drop table p; 2433drop table p1; 2434 2435-- validate constraint on partitioned tables should only scan leaf partitions 2436create table parted_validate_test (a int) partition by list (a); 2437create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1); 2438alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid; 2439alter table parted_validate_test validate constraint parted_validate_test_chka; 2440drop table parted_validate_test; 2441 2442-- check combinations of temporary and permanent relations when attaching 2443-- partitions. 2444create table perm_part_parent (a int) partition by list (a); 2445create temp table temp_part_parent (a int) partition by list (a); 2446create table perm_part_child (a int); 2447create temp table temp_part_child (a int); 2448alter table temp_part_parent attach partition perm_part_child 2449 for values in (1, 2); -- error 2450alter table perm_part_parent attach partition temp_part_child 2451 for values in (1, 2); -- error 2452alter table temp_part_parent attach partition temp_part_child 2453 for values in (1, 2); -- ok 2454drop table perm_part_parent cascade; 2455drop table temp_part_parent cascade; 2456 2457-- check that attaching partitions to a table while it is being used is 2458-- prevented 2459create table tab_part_attach (a int) partition by list (a); 2460create or replace function func_part_attach() returns trigger 2461 language plpgsql as $$ 2462 begin 2463 execute 'create table tab_part_attach_1 (a int)'; 2464 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)'; 2465 return null; 2466 end $$; 2467create trigger trig_part_attach before insert on tab_part_attach 2468 for each statement execute procedure func_part_attach(); 2469insert into tab_part_attach values (1); 2470drop table tab_part_attach; 2471drop function func_part_attach(); 2472 2473-- test case where the partitioning operator is a SQL function whose 2474-- evaluation results in the table's relcache being rebuilt partway through 2475-- the execution of an ATTACH PARTITION command 2476create function at_test_sql_partop (int4, int4) returns int language sql 2477as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$; 2478create operator class at_test_sql_partop for type int4 using btree as 2479 operator 1 < (int4, int4), operator 2 <= (int4, int4), 2480 operator 3 = (int4, int4), operator 4 >= (int4, int4), 2481 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4); 2482create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop); 2483create table at_test_sql_partop_1 (a int); 2484alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10); 2485drop table at_test_sql_partop; 2486drop operator class at_test_sql_partop using btree; 2487drop function at_test_sql_partop; 2488 2489-- Test that ALTER TABLE rewrite preserves a clustered index 2490-- for normal indexes and indexes on constraints. 2491create table alttype_cluster (a int); 2492alter table alttype_cluster add primary key (a); 2493create index alttype_cluster_ind on alttype_cluster (a); 2494alter table alttype_cluster cluster on alttype_cluster_ind; 2495-- Normal index remains clustered. 2496select indexrelid::regclass, indisclustered from pg_index 2497 where indrelid = 'alttype_cluster'::regclass 2498 order by indexrelid::regclass::text; 2499alter table alttype_cluster alter a type bigint; 2500select indexrelid::regclass, indisclustered from pg_index 2501 where indrelid = 'alttype_cluster'::regclass 2502 order by indexrelid::regclass::text; 2503-- Constraint index remains clustered. 2504alter table alttype_cluster cluster on alttype_cluster_pkey; 2505select indexrelid::regclass, indisclustered from pg_index 2506 where indrelid = 'alttype_cluster'::regclass 2507 order by indexrelid::regclass::text; 2508alter table alttype_cluster alter a type int; 2509select indexrelid::regclass, indisclustered from pg_index 2510 where indrelid = 'alttype_cluster'::regclass 2511 order by indexrelid::regclass::text; 2512drop table alttype_cluster; 2513 2514-- 2515-- Check that attaching or detaching a partitioned partition correctly leads 2516-- to its partitions' constraint being updated to reflect the parent's 2517-- newly added/removed constraint 2518create table target_parted (a int, b int) partition by list (a); 2519create table attach_parted (a int, b int) partition by list (b); 2520create table attach_parted_part1 partition of attach_parted for values in (1); 2521-- insert a row directly into the leaf partition so that its partition 2522-- constraint is built and stored in the relcache 2523insert into attach_parted_part1 values (1, 1); 2524-- the following better invalidate the partition constraint of the leaf 2525-- partition too... 2526alter table target_parted attach partition attach_parted for values in (1); 2527-- ...such that the following insert fails 2528insert into attach_parted_part1 values (2, 1); 2529-- ...and doesn't when the partition is detached along with its own partition 2530alter table target_parted detach partition attach_parted; 2531insert into attach_parted_part1 values (2, 1); 2532