5 -- Clean up in case a prior regression run failed
6 SET client_min_messages TO 'warning';
7 DROP ROLE IF EXISTS regress_alter_table_user1;
8 RESET client_min_messages;
10 CREATE USER regress_alter_table_user1;
16 CREATE TABLE attmp (initial int4);
18 COMMENT ON TABLE attmp_wrong IS 'table comment';
19 COMMENT ON TABLE attmp IS 'table comment';
20 COMMENT ON TABLE attmp IS NULL;
22 ALTER TABLE attmp ADD COLUMN xmin integer; -- fails
24 ALTER TABLE attmp ADD COLUMN a int4 default 3;
26 ALTER TABLE attmp ADD COLUMN b name;
28 ALTER TABLE attmp ADD COLUMN c text;
30 ALTER TABLE attmp ADD COLUMN d float8;
32 ALTER TABLE attmp ADD COLUMN e float4;
34 ALTER TABLE attmp ADD COLUMN f int2;
36 ALTER TABLE attmp ADD COLUMN g polygon;
38 ALTER TABLE attmp ADD COLUMN i char;
40 ALTER TABLE attmp ADD COLUMN k int4;
42 ALTER TABLE attmp ADD COLUMN l tid;
44 ALTER TABLE attmp ADD COLUMN m xid;
46 ALTER TABLE attmp ADD COLUMN n oidvector;
48 --ALTER TABLE attmp ADD COLUMN o lock;
49 ALTER TABLE attmp ADD COLUMN p boolean;
51 ALTER TABLE attmp ADD COLUMN q point;
53 ALTER TABLE attmp ADD COLUMN r lseg;
55 ALTER TABLE attmp ADD COLUMN s path;
57 ALTER TABLE attmp ADD COLUMN t box;
59 ALTER TABLE attmp ADD COLUMN v timestamp;
61 ALTER TABLE attmp ADD COLUMN w interval;
63 ALTER TABLE attmp ADD COLUMN x float8[];
65 ALTER TABLE attmp ADD COLUMN y float4[];
67 ALTER TABLE attmp ADD COLUMN z int2[];
69 INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t,
71 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
73 314159, '(1,1)', '512',
74 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
75 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)',
76 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
82 -- the wolf bug - schema mods caused inconsistent row descriptors
87 ALTER TABLE attmp ADD COLUMN a int4;
89 ALTER TABLE attmp ADD COLUMN b name;
91 ALTER TABLE attmp ADD COLUMN c text;
93 ALTER TABLE attmp ADD COLUMN d float8;
95 ALTER TABLE attmp ADD COLUMN e float4;
97 ALTER TABLE attmp ADD COLUMN f int2;
99 ALTER TABLE attmp ADD COLUMN g polygon;
101 ALTER TABLE attmp ADD COLUMN i char;
103 ALTER TABLE attmp ADD COLUMN k int4;
105 ALTER TABLE attmp ADD COLUMN l tid;
107 ALTER TABLE attmp ADD COLUMN m xid;
109 ALTER TABLE attmp ADD COLUMN n oidvector;
111 --ALTER TABLE attmp ADD COLUMN o lock;
112 ALTER TABLE attmp ADD COLUMN p boolean;
114 ALTER TABLE attmp ADD COLUMN q point;
116 ALTER TABLE attmp ADD COLUMN r lseg;
118 ALTER TABLE attmp ADD COLUMN s path;
120 ALTER TABLE attmp ADD COLUMN t box;
122 ALTER TABLE attmp ADD COLUMN v timestamp;
124 ALTER TABLE attmp ADD COLUMN w interval;
126 ALTER TABLE attmp ADD COLUMN x float8[];
128 ALTER TABLE attmp ADD COLUMN y float4[];
130 ALTER TABLE attmp ADD COLUMN z int2[];
132 INSERT INTO attmp (a, b, c, d, e, f, g, i, k, l, m, n, p, q, r, s, t,
134 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
136 314159, '(1,1)', '512',
137 '1 2 3 4 5 6 7 8', true, '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
138 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)',
139 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
143 CREATE INDEX attmp_idx ON attmp (a, (d + e), b);
145 ALTER INDEX attmp_idx ALTER COLUMN 0 SET STATISTICS 1000;
147 ALTER INDEX attmp_idx ALTER COLUMN 1 SET STATISTICS 1000;
149 ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS 1000;
153 ALTER INDEX attmp_idx ALTER COLUMN 3 SET STATISTICS 1000;
155 ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000;
157 ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1;
163 -- rename - check on both non-temp and temp tables
165 CREATE TABLE attmp (regtable int);
166 CREATE TEMP TABLE attmp (attmptable int);
168 ALTER TABLE attmp RENAME TO attmp_new;
171 SELECT * FROM attmp_new;
173 ALTER TABLE attmp RENAME TO attmp_new2;
175 SELECT * FROM attmp; -- should fail
176 SELECT * FROM attmp_new;
177 SELECT * FROM attmp_new2;
179 DROP TABLE attmp_new;
180 DROP TABLE attmp_new2;
182 -- check rename of partitioned tables and indexes also
183 CREATE TABLE part_attmp (a int primary key) partition by range (a);
184 CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100);
185 ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index;
186 ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index;
187 ALTER TABLE part_attmp RENAME TO part_at2tmp;
188 ALTER TABLE part_attmp1 RENAME TO part_at2tmp1;
189 SET ROLE regress_alter_table_user1;
190 ALTER INDEX part_attmp_index RENAME TO fail;
191 ALTER INDEX part_attmp1_index RENAME TO fail;
192 ALTER TABLE part_at2tmp RENAME TO fail;
193 ALTER TABLE part_at2tmp1 RENAME TO fail;
195 DROP TABLE part_at2tmp;
198 -- check renaming to a table's array type's autogenerated name
199 -- (the array type's name should get out of the way)
201 CREATE TABLE attmp_array (id int);
202 CREATE TABLE attmp_array2 (id int);
203 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
204 SELECT typname FROM pg_type WHERE oid = 'attmp_array2[]'::regtype;
205 ALTER TABLE attmp_array2 RENAME TO _attmp_array;
206 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
207 SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype;
208 DROP TABLE _attmp_array;
209 DROP TABLE attmp_array;
211 -- renaming to table's own array type's name is an interesting corner case
212 CREATE TABLE attmp_array (id int);
213 SELECT typname FROM pg_type WHERE oid = 'attmp_array[]'::regtype;
214 ALTER TABLE attmp_array RENAME TO _attmp_array;
215 SELECT typname FROM pg_type WHERE oid = '_attmp_array[]'::regtype;
216 DROP TABLE _attmp_array;
218 -- ALTER TABLE ... RENAME on non-table relations
219 -- renaming indexes (FIXME: this should probably test the index's functionality)
220 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO attmp_onek_unique1;
221 ALTER INDEX IF EXISTS __attmp_onek_unique1 RENAME TO onek_unique1;
223 ALTER INDEX onek_unique1 RENAME TO attmp_onek_unique1;
224 ALTER INDEX attmp_onek_unique1 RENAME TO onek_unique1;
226 SET ROLE regress_alter_table_user1;
227 ALTER INDEX onek_unique1 RENAME TO fail; -- permission denied
231 CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1;
232 ALTER TABLE attmp_view RENAME TO attmp_view_new;
234 SET ROLE regress_alter_table_user1;
235 ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied
238 -- hack to ensure we get an indexscan here
239 set enable_seqscan to off;
240 set enable_bitmapscan to off;
242 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
243 reset enable_seqscan;
244 reset enable_bitmapscan;
246 DROP VIEW attmp_view_new;
247 -- toast-like relation name
248 alter table stud_emp rename to pg_toast_stud_emp;
249 alter table pg_toast_stud_emp rename to stud_emp;
251 -- renaming index should rename constraint as well
252 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
253 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
254 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
256 -- renaming constraint
257 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
258 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
259 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
261 -- renaming constraint should rename index as well
262 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
263 DROP INDEX onek_unique1_constraint; -- to see whether it's there
264 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
265 DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
266 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
268 -- renaming constraints vs. inheritance
269 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
270 \d constraint_rename_test
271 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
272 \d constraint_rename_test2
273 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
274 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
275 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
276 \d constraint_rename_test
277 \d constraint_rename_test2
278 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
279 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
280 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
281 \d constraint_rename_test
282 \d constraint_rename_test2
283 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
284 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
285 \d constraint_rename_test
286 \d constraint_rename_test2
287 DROP TABLE constraint_rename_test2;
288 DROP TABLE constraint_rename_test;
289 ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok
290 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
292 -- renaming constraints with cache reset of target relation
293 CREATE TABLE constraint_rename_cache (a int,
294 CONSTRAINT chk_a CHECK (a > 0),
296 ALTER TABLE constraint_rename_cache
297 RENAME CONSTRAINT chk_a TO chk_a_new;
298 ALTER TABLE constraint_rename_cache
299 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new;
300 CREATE TABLE like_constraint_rename_cache
301 (LIKE constraint_rename_cache INCLUDING ALL);
302 \d like_constraint_rename_cache
303 DROP TABLE constraint_rename_cache;
304 DROP TABLE like_constraint_rename_cache;
306 -- FOREIGN KEY CONSTRAINT adding TEST
308 CREATE TABLE attmp2 (a int primary key);
310 CREATE TABLE attmp3 (a int, b int);
312 CREATE TABLE attmp4 (a int, b int, unique(a,b));
314 CREATE TABLE attmp5 (a int, b int);
316 -- Insert rows into attmp2 (pktable)
317 INSERT INTO attmp2 values (1);
318 INSERT INTO attmp2 values (2);
319 INSERT INTO attmp2 values (3);
320 INSERT INTO attmp2 values (4);
322 -- Insert rows into attmp3
323 INSERT INTO attmp3 values (1,10);
324 INSERT INTO attmp3 values (1,20);
325 INSERT INTO attmp3 values (5,50);
327 -- Try (and fail) to add constraint due to invalid source columns
328 ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full;
330 -- Try (and fail) to add constraint due to invalid destination columns explicitly given
331 ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full;
333 -- Try (and fail) to add constraint due to invalid data
334 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
336 -- Delete failing row
337 DELETE FROM attmp3 where a=5;
340 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
341 ALTER TABLE attmp3 drop constraint attmpconstr;
343 INSERT INTO attmp3 values (5,50);
345 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
346 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID;
347 ALTER TABLE attmp3 validate constraint attmpconstr;
349 -- Delete failing row
350 DELETE FROM attmp3 where a=5;
352 -- Try (and succeed) and repeat to show it works on already valid constraint
353 ALTER TABLE attmp3 validate constraint attmpconstr;
354 ALTER TABLE attmp3 validate constraint attmpconstr;
356 -- Try a non-verified CHECK constraint
357 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
358 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
359 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
360 DELETE FROM attmp3 WHERE NOT b > 10;
361 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
362 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
364 -- Test inherited NOT VALID CHECK constraints
365 select * from attmp3;
366 CREATE TABLE attmp6 () INHERITS (attmp3);
367 CREATE TABLE attmp7 () INHERITS (attmp3);
369 INSERT INTO attmp6 VALUES (6, 30), (7, 16);
370 ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
371 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails
372 DELETE FROM attmp6 WHERE b > 20;
373 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
375 -- An already validated constraint must not be revalidated
376 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
377 INSERT INTO attmp7 VALUES (8, 18);
378 ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
379 ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
380 ALTER TABLE attmp3 VALIDATE CONSTRAINT identity;
382 -- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT
383 create table parent_noinh_convalid (a int);
384 create table child_noinh_convalid () inherits (parent_noinh_convalid);
385 insert into parent_noinh_convalid values (1);
386 insert into child_noinh_convalid values (1);
387 alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid;
388 -- fail, because of the row in parent
389 alter table parent_noinh_convalid validate constraint check_a_is_2;
390 delete from only parent_noinh_convalid;
391 -- ok (parent itself contains no violating rows)
392 alter table parent_noinh_convalid validate constraint check_a_is_2;
393 select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2';
395 drop table parent_noinh_convalid, child_noinh_convalid;
397 -- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on
400 ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full;
414 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
415 -- exclusion until validated
416 set constraint_exclusion TO 'partition';
417 create table nv_parent (d date, check (false) no inherit not valid);
418 -- not valid constraint added at creation time should automatically become valid
421 create table nv_child_2010 () inherits (nv_parent);
422 create table nv_child_2011 () inherits (nv_parent);
423 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
424 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
425 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
426 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
427 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
428 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
429 -- after validation, the constraint should be used
430 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
431 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
433 -- add an inherited NOT VALID constraint
434 alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid;
436 -- we leave nv_parent and children around to help test pg_dump logic
438 -- Foreign key adding test with mixed types
440 -- Note: these tables are TEMP to avoid name conflicts when this test
441 -- is run in parallel with foreign_key.sql.
443 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
444 INSERT INTO PKTABLE VALUES(42);
445 CREATE TEMP TABLE FKTABLE (ftest1 inet);
446 -- This next should fail, because int=inet does not exist
447 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
448 -- This should also fail for the same reason, but here we
449 -- give the column name
450 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
452 -- This should succeed, even though they are different types,
453 -- because int=int8 exists and is a member of the integer opfamily
454 CREATE TEMP TABLE FKTABLE (ftest1 int8);
455 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
456 -- Check it actually works
457 INSERT INTO FKTABLE VALUES(42); -- should succeed
458 INSERT INTO FKTABLE VALUES(43); -- should fail
460 -- This should fail, because we'd have to cast numeric to int which is
461 -- not an implicit coercion (or use numeric=numeric, but that's not part
462 -- of the integer opfamily)
463 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
464 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
467 -- On the other hand, this should work because int implicitly promotes to
468 -- numeric, and we allow promotion on the FK side
469 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
470 INSERT INTO PKTABLE VALUES(42);
471 CREATE TEMP TABLE FKTABLE (ftest1 int);
472 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
473 -- Check it actually works
474 INSERT INTO FKTABLE VALUES(42); -- should succeed
475 INSERT INTO FKTABLE VALUES(43); -- should fail
479 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
480 PRIMARY KEY(ptest1, ptest2));
481 -- This should fail, because we just chose really odd types
482 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
483 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
485 -- Again, so should this...
486 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
487 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
488 references pktable(ptest1, ptest2);
490 -- This fails because we mixed up the column ordering
491 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
492 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
493 references pktable(ptest2, ptest1);
495 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
496 references pktable(ptest1, ptest2);
500 -- Test that ALTER CONSTRAINT updates trigger deferrability properly
502 CREATE TEMP TABLE PKTABLE (ptest1 int primary key);
503 CREATE TEMP TABLE FKTABLE (ftest1 int);
505 ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable
506 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
507 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable
508 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
509 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable
510 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;
512 ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable
513 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
514 ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE;
515 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable
516 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
517 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED;
518 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable
519 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
520 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE;
522 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
523 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
524 WHERE tgrelid = 'pktable'::regclass
526 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
527 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
528 WHERE tgrelid = 'fktable'::regclass
531 -- temp tables should go away by themselves, need not drop them.
533 -- test check constraint adding
535 create table atacc1 ( test int );
536 -- add a check constraint
537 alter table atacc1 add constraint atacc_test1 check (test>3);
539 insert into atacc1 (test) values (2);
541 insert into atacc1 (test) values (4);
544 -- let's do one where the check fails when added
545 create table atacc1 ( test int );
546 -- insert a soon to be failing row
547 insert into atacc1 (test) values (2);
548 -- add a check constraint (fails)
549 alter table atacc1 add constraint atacc_test1 check (test>3);
550 insert into atacc1 (test) values (4);
553 -- let's do one where the check fails because the column doesn't exist
554 create table atacc1 ( test int );
555 -- add a check constraint (fails)
556 alter table atacc1 add constraint atacc_test1 check (test1>3);
559 -- something a little more complicated
560 create table atacc1 ( test int, test2 int, test3 int);
561 -- add a check constraint (fails)
562 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
564 insert into atacc1 (test,test2,test3) values (4,4,2);
566 insert into atacc1 (test,test2,test3) values (4,4,5);
569 -- lets do some naming tests
570 create table atacc1 (test int check (test>3), test2 int);
571 alter table atacc1 add check (test2>test);
572 -- should fail for $2
573 insert into atacc1 (test2, test) values (3, 4);
576 -- inheritance related tests
577 create table atacc1 (test int);
578 create table atacc2 (test2 int);
579 create table atacc3 (test3 int) inherits (atacc1, atacc2);
580 alter table atacc2 add constraint foo check (test2>0);
581 -- fail and then succeed on atacc2
582 insert into atacc2 (test2) values (-3);
583 insert into atacc2 (test2) values (3);
584 -- fail and then succeed on atacc3
585 insert into atacc3 (test2) values (-3);
586 insert into atacc3 (test2) values (3);
591 -- same things with one created with INHERIT
592 create table atacc1 (test int);
593 create table atacc2 (test2 int);
594 create table atacc3 (test3 int) inherits (atacc1, atacc2);
595 alter table atacc3 no inherit atacc2;
597 alter table atacc3 no inherit atacc2;
598 -- make sure it really isn't a child
599 insert into atacc3 (test2) values (3);
600 select test2 from atacc2;
601 -- fail due to missing constraint
602 alter table atacc2 add constraint foo check (test2>0);
603 alter table atacc3 inherit atacc2;
604 -- fail due to missing column
605 alter table atacc3 rename test2 to testx;
606 alter table atacc3 inherit atacc2;
607 -- fail due to mismatched data type
608 alter table atacc3 add test2 bool;
609 alter table atacc3 inherit atacc2;
610 alter table atacc3 drop test2;
612 alter table atacc3 add test2 int;
613 update atacc3 set test2 = 4 where test2 is null;
614 alter table atacc3 add constraint foo check (test2>0);
615 alter table atacc3 inherit atacc2;
616 -- fail due to duplicates and circular inheritance
617 alter table atacc3 inherit atacc2;
618 alter table atacc2 inherit atacc3;
619 alter table atacc2 inherit atacc2;
620 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
621 select test2 from atacc2;
622 drop table atacc2 cascade;
625 -- adding only to a parent is allowed as of 9.2
627 create table atacc1 (test int);
628 create table atacc2 (test2 int) inherits (atacc1);
630 alter table atacc1 add constraint foo check (test>0) no inherit;
631 -- check constraint is not there on child
632 insert into atacc2 (test) values (-3);
633 -- check constraint is there on parent
634 insert into atacc1 (test) values (-3);
635 insert into atacc1 (test) values (3);
636 -- fail, violating row:
637 alter table atacc2 add constraint foo check (test>0) no inherit;
641 -- test unique constraint adding
643 create table atacc1 ( test int ) ;
644 -- add a unique constraint
645 alter table atacc1 add constraint atacc_test1 unique (test);
646 -- insert first value
647 insert into atacc1 (test) values (2);
649 insert into atacc1 (test) values (2);
651 insert into atacc1 (test) values (4);
652 -- try to create duplicates via alter table using - should fail
653 alter table atacc1 alter column test type integer using 0;
656 -- let's do one where the unique constraint fails when added
657 create table atacc1 ( test int );
658 -- insert soon to be failing rows
659 insert into atacc1 (test) values (2);
660 insert into atacc1 (test) values (2);
661 -- add a unique constraint (fails)
662 alter table atacc1 add constraint atacc_test1 unique (test);
663 insert into atacc1 (test) values (3);
666 -- let's do one where the unique constraint fails
667 -- because the column doesn't exist
668 create table atacc1 ( test int );
669 -- add a unique constraint (fails)
670 alter table atacc1 add constraint atacc_test1 unique (test1);
673 -- something a little more complicated
674 create table atacc1 ( test int, test2 int);
675 -- add a unique constraint
676 alter table atacc1 add constraint atacc_test1 unique (test, test2);
677 -- insert initial value
678 insert into atacc1 (test,test2) values (4,4);
680 insert into atacc1 (test,test2) values (4,4);
681 -- should all succeed
682 insert into atacc1 (test,test2) values (4,5);
683 insert into atacc1 (test,test2) values (5,4);
684 insert into atacc1 (test,test2) values (5,5);
687 -- lets do some naming tests
688 create table atacc1 (test int, test2 int, unique(test));
689 alter table atacc1 add unique (test2);
690 -- should fail for @@ second one @@
691 insert into atacc1 (test2, test) values (3, 3);
692 insert into atacc1 (test2, test) values (2, 3);
695 -- test primary key constraint adding
697 create table atacc1 ( id serial, test int) ;
698 -- add a primary key constraint
699 alter table atacc1 add constraint atacc_test1 primary key (test);
700 -- insert first value
701 insert into atacc1 (test) values (2);
703 insert into atacc1 (test) values (2);
705 insert into atacc1 (test) values (4);
706 -- inserting NULL should fail
707 insert into atacc1 (test) values(NULL);
708 -- try adding a second primary key (should fail)
709 alter table atacc1 add constraint atacc_oid1 primary key(id);
710 -- drop first primary key constraint
711 alter table atacc1 drop constraint atacc_test1 restrict;
712 -- try adding a primary key on oid (should succeed)
713 alter table atacc1 add constraint atacc_oid1 primary key(id);
716 -- let's do one where the primary key constraint fails when added
717 create table atacc1 ( test int );
718 -- insert soon to be failing rows
719 insert into atacc1 (test) values (2);
720 insert into atacc1 (test) values (2);
721 -- add a primary key (fails)
722 alter table atacc1 add constraint atacc_test1 primary key (test);
723 insert into atacc1 (test) values (3);
726 -- let's do another one where the primary key constraint fails when added
727 create table atacc1 ( test int );
728 -- insert soon to be failing row
729 insert into atacc1 (test) values (NULL);
730 -- add a primary key (fails)
731 alter table atacc1 add constraint atacc_test1 primary key (test);
732 insert into atacc1 (test) values (3);
735 -- let's do one where the primary key constraint fails
736 -- because the column doesn't exist
737 create table atacc1 ( test int );
738 -- add a primary key constraint (fails)
739 alter table atacc1 add constraint atacc_test1 primary key (test1);
742 -- adding a new column as primary key to a non-empty table.
743 -- should fail unless the column has a non-null default value.
744 create table atacc1 ( test int );
745 insert into atacc1 (test) values (0);
746 -- add a primary key column without a default (fails).
747 alter table atacc1 add column test2 int primary key;
748 -- now add a primary key column with a default (succeeds).
749 alter table atacc1 add column test2 int default 0 primary key;
752 -- this combination used to have order-of-execution problems (bug #15580)
753 create table atacc1 (a int);
754 insert into atacc1 values(1);
756 add column b float8 not null default random(),
760 -- something a little more complicated
761 create table atacc1 ( test int, test2 int);
762 -- add a primary key constraint
763 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
764 -- try adding a second primary key - should fail
765 alter table atacc1 add constraint atacc_test2 primary key (test);
766 -- insert initial value
767 insert into atacc1 (test,test2) values (4,4);
769 insert into atacc1 (test,test2) values (4,4);
770 insert into atacc1 (test,test2) values (NULL,3);
771 insert into atacc1 (test,test2) values (3, NULL);
772 insert into atacc1 (test,test2) values (NULL,NULL);
773 -- should all succeed
774 insert into atacc1 (test,test2) values (4,5);
775 insert into atacc1 (test,test2) values (5,4);
776 insert into atacc1 (test,test2) values (5,5);
779 -- lets do some naming tests
780 create table atacc1 (test int, test2 int, primary key(test));
781 -- only first should succeed
782 insert into atacc1 (test2, test) values (3, 3);
783 insert into atacc1 (test2, test) values (2, 3);
784 insert into atacc1 (test2, test) values (1, NULL);
787 -- alter table / alter column [set/drop] not null tests
788 -- try altering system catalogs, should fail
789 alter table pg_class alter column relname drop not null;
790 alter table pg_class alter relname set not null;
792 -- try altering non-existent table, should fail
793 alter table non_existent alter column bar set not null;
794 alter table non_existent alter column bar drop not null;
796 -- test setting columns to null and not null and vice versa
797 -- test checking for null values and primary key
798 create table atacc1 (test int not null);
799 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
800 alter table atacc1 alter column test drop not null;
801 alter table atacc1 drop constraint "atacc1_pkey";
802 alter table atacc1 alter column test drop not null;
803 insert into atacc1 values (null);
804 alter table atacc1 alter test set not null;
806 alter table atacc1 alter test set not null;
808 -- try altering a non-existent column, should fail
809 alter table atacc1 alter bar set not null;
810 alter table atacc1 alter bar drop not null;
812 -- try creating a view and altering that, should fail
813 create view myview as select * from atacc1;
814 alter table myview alter column test drop not null;
815 alter table myview alter column test set not null;
820 -- set not null verified by constraints
821 create table atacc1 (test_a int, test_b int);
822 insert into atacc1 values (null, 1);
823 -- constraint not cover all values, should fail
824 alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10);
825 alter table atacc1 alter test_a set not null;
826 alter table atacc1 drop constraint atacc1_constr_or;
827 -- not valid constraint, should fail
828 alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid;
829 alter table atacc1 alter test_a set not null;
830 alter table atacc1 drop constraint atacc1_constr_invalid;
831 -- with valid constraint
832 update atacc1 set test_a = 1;
833 alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);
834 alter table atacc1 alter test_a set not null;
837 insert into atacc1 values (2, null);
838 alter table atacc1 alter test_a drop not null;
839 -- test multiple set not null at same time
840 -- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan
841 alter table atacc1 alter test_a set not null, alter test_b set not null;
842 -- commands order has no importance
843 alter table atacc1 alter test_b set not null, alter test_a set not null;
845 -- valid one by table scan, one by check constraints
846 update atacc1 set test_b = 1;
847 alter table atacc1 alter test_b set not null, alter test_a set not null;
849 alter table atacc1 alter test_a drop not null, alter test_b drop not null;
850 -- both column has check constraints
851 alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
852 alter table atacc1 alter test_b set not null, alter test_a set not null;
856 create table parent (a int);
857 create table child (b varchar(255)) inherits (parent);
859 alter table parent alter a set not null;
860 insert into parent values (NULL);
861 insert into child (a, b) values (NULL, 'foo');
862 alter table parent alter a drop not null;
863 insert into parent values (NULL);
864 insert into child (a, b) values (NULL, 'foo');
865 alter table only parent alter a set not null;
866 alter table child alter a set not null;
868 alter table only parent alter a set not null;
869 insert into parent values (NULL);
870 alter table child alter a set not null;
871 insert into child (a, b) values (NULL, 'foo');
873 alter table child alter a set not null;
874 insert into child (a, b) values (NULL, 'foo');
878 -- test setting and removing default values
879 create table def_test (
881 c2 text default 'initial_default'
883 insert into def_test default values;
884 alter table def_test alter column c1 drop default;
885 insert into def_test default values;
886 alter table def_test alter column c2 drop default;
887 insert into def_test default values;
888 alter table def_test alter column c1 set default 10;
889 alter table def_test alter column c2 set default 'new_default';
890 insert into def_test default values;
891 select * from def_test;
893 -- set defaults to an incorrect type: this should fail
894 alter table def_test alter column c1 set default 'wrong_datatype';
895 alter table def_test alter column c2 set default 20;
897 -- set defaults on a non-existent column: this should fail
898 alter table def_test alter column c3 set default 30;
900 -- set defaults on views: we need to create a view, add a rule
901 -- to allow insertions into it, and then alter the view to add
903 create view def_view_test as select * from def_test;
904 create rule def_view_test_ins as
905 on insert to def_view_test
906 do instead insert into def_test select new.*;
907 insert into def_view_test default values;
908 alter table def_view_test alter column c1 set default 45;
909 insert into def_view_test default values;
910 alter table def_view_test alter column c2 set default 'view_default';
911 insert into def_view_test default values;
912 select * from def_view_test;
914 drop rule def_view_test_ins on def_view_test;
915 drop view def_view_test;
918 -- alter table / drop column tests
919 -- try altering system catalogs, should fail
920 alter table pg_class drop column relname;
922 -- try altering non-existent table, should fail
923 alter table nosuchtable drop column bar;
925 -- test dropping columns
926 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
927 insert into atacc1 values (1, 2, 3, 4);
928 alter table atacc1 drop a;
929 alter table atacc1 drop a;
932 select * from atacc1;
933 select * from atacc1 order by a;
934 select * from atacc1 order by "........pg.dropped.1........";
935 select * from atacc1 group by a;
936 select * from atacc1 group by "........pg.dropped.1........";
937 select atacc1.* from atacc1;
938 select a from atacc1;
939 select atacc1.a from atacc1;
940 select b,c,d from atacc1;
941 select a,b,c,d from atacc1;
942 select * from atacc1 where a = 1;
943 select "........pg.dropped.1........" from atacc1;
944 select atacc1."........pg.dropped.1........" from atacc1;
945 select "........pg.dropped.1........",b,c,d from atacc1;
946 select * from atacc1 where "........pg.dropped.1........" = 1;
949 update atacc1 set a = 3;
950 update atacc1 set b = 2 where a = 3;
951 update atacc1 set "........pg.dropped.1........" = 3;
952 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
955 insert into atacc1 values (10, 11, 12, 13);
956 insert into atacc1 values (default, 11, 12, 13);
957 insert into atacc1 values (11, 12, 13);
958 insert into atacc1 (a) values (10);
959 insert into atacc1 (a) values (default);
960 insert into atacc1 (a,b,c,d) values (10,11,12,13);
961 insert into atacc1 (a,b,c,d) values (default,11,12,13);
962 insert into atacc1 (b,c,d) values (11,12,13);
963 insert into atacc1 ("........pg.dropped.1........") values (10);
964 insert into atacc1 ("........pg.dropped.1........") values (default);
965 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
966 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
969 delete from atacc1 where a = 3;
970 delete from atacc1 where "........pg.dropped.1........" = 3;
973 -- try dropping a non-existent column, should fail
974 alter table atacc1 drop bar;
976 -- try removing an oid column, should succeed (as it's nonexistent)
977 alter table atacc1 SET WITHOUT OIDS;
979 -- try adding an oid column, should fail (not supported)
980 alter table atacc1 SET WITH OIDS;
982 -- try dropping the xmin column, should fail
983 alter table atacc1 drop xmin;
985 -- try creating a view and altering that, should fail
986 create view myview as select * from atacc1;
987 select * from myview;
988 alter table myview drop d;
991 -- test some commands to make sure they fail on the dropped column
993 analyze atacc1("........pg.dropped.1........");
994 vacuum analyze atacc1(a);
995 vacuum analyze atacc1("........pg.dropped.1........");
996 comment on column atacc1.a is 'testing';
997 comment on column atacc1."........pg.dropped.1........" is 'testing';
998 alter table atacc1 alter a set storage plain;
999 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
1000 alter table atacc1 alter a set statistics 0;
1001 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
1002 alter table atacc1 alter a set default 3;
1003 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1004 alter table atacc1 alter a drop default;
1005 alter table atacc1 alter "........pg.dropped.1........" drop default;
1006 alter table atacc1 alter a set not null;
1007 alter table atacc1 alter "........pg.dropped.1........" set not null;
1008 alter table atacc1 alter a drop not null;
1009 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1010 alter table atacc1 rename a to x;
1011 alter table atacc1 rename "........pg.dropped.1........" to x;
1012 alter table atacc1 add primary key(a);
1013 alter table atacc1 add primary key("........pg.dropped.1........");
1014 alter table atacc1 add unique(a);
1015 alter table atacc1 add unique("........pg.dropped.1........");
1016 alter table atacc1 add check (a > 3);
1017 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1018 create table atacc2 (id int4 unique);
1019 alter table atacc1 add foreign key (a) references atacc2(id);
1020 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1021 alter table atacc2 add foreign key (id) references atacc1(a);
1022 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1024 create index "testing_idx" on atacc1(a);
1025 create index "testing_idx" on atacc1("........pg.dropped.1........");
1027 -- test create as and select into
1028 insert into atacc1 values (21, 22, 23);
1029 create table attest1 as select * from atacc1;
1030 select * from attest1;
1032 select * into attest2 from atacc1;
1033 select * from attest2;
1036 -- try dropping all columns
1037 alter table atacc1 drop c;
1038 alter table atacc1 drop d;
1039 alter table atacc1 drop b;
1040 select * from atacc1;
1044 -- test constraint error reporting in presence of dropped columns
1045 create table atacc1 (id serial primary key, value int check (value < 10));
1046 insert into atacc1(value) values (100);
1047 alter table atacc1 drop column value;
1048 alter table atacc1 add column value int check (value < 10);
1049 insert into atacc1(value) values (100);
1050 insert into atacc1(id, value) values (null, 0);
1054 create table parent (a int, b int, c int);
1055 insert into parent values (1, 2, 3);
1056 alter table parent drop a;
1057 create table child (d varchar(255)) inherits (parent);
1058 insert into child values (12, 13, 'testing');
1060 select * from parent;
1061 select * from child;
1062 alter table parent drop c;
1063 select * from parent;
1064 select * from child;
1069 -- check error cases for inheritance column merging
1070 create table parent (a float8, b numeric(10,4), c text collate "C");
1072 create table child (a float4) inherits (parent); -- fail
1073 create table child (b decimal(10,7)) inherits (parent); -- fail
1074 create table child (c text collate "POSIX") inherits (parent); -- fail
1075 create table child (a double precision, b decimal(10,4)) inherits (parent);
1081 create table attest (a int4, b int4, c int4);
1082 insert into attest values (1,2,3);
1083 alter table attest drop a;
1084 copy attest to stdout;
1085 copy attest(a) to stdout;
1086 copy attest("........pg.dropped.1........") to stdout;
1087 copy attest from stdin;
1090 select * from attest;
1091 copy attest from stdin;
1094 select * from attest;
1095 copy attest(a) from stdin;
1096 copy attest("........pg.dropped.1........") from stdin;
1097 copy attest(b,c) from stdin;
1100 select * from attest;
1105 create table dropColumn (a int, b int, e int);
1106 create table dropColumnChild (c int) inherits (dropColumn);
1107 create table dropColumnAnother (d int) inherits (dropColumnChild);
1109 -- these two should fail
1110 alter table dropColumnchild drop column a;
1111 alter table only dropColumnChild drop column b;
1115 -- these three should work
1116 alter table only dropColumn drop column e;
1117 alter table dropColumnChild drop column c;
1118 alter table dropColumn drop column a;
1120 create table renameColumn (a int);
1121 create table renameColumnChild (b int) inherits (renameColumn);
1122 create table renameColumnAnother (c int) inherits (renameColumnChild);
1124 -- these three should fail
1125 alter table renameColumnChild rename column a to d;
1126 alter table only renameColumnChild rename column a to d;
1127 alter table only renameColumn rename column a to d;
1129 -- these should work
1130 alter table renameColumn rename column a to d;
1131 alter table renameColumnChild rename column b to a;
1133 -- these should work
1134 alter table if exists doesnt_exist_tab rename column a to d;
1135 alter table if exists doesnt_exist_tab rename column b to a;
1138 alter table renameColumn add column w int;
1141 alter table only renameColumn add column x int;
1144 -- Test corner cases in dropping of inherited columns
1146 create table p1 (f1 int, f2 int);
1147 create table c1 (f1 int not null) inherits(p1);
1149 -- should be rejected since c1.f1 is inherited
1150 alter table c1 drop column f1;
1152 alter table p1 drop column f1;
1153 -- c1.f1 is still there, but no longer inherited
1155 alter table c1 drop column f1;
1158 drop table p1 cascade;
1160 create table p1 (f1 int, f2 int);
1161 create table c1 () inherits(p1);
1163 -- should be rejected since c1.f1 is inherited
1164 alter table c1 drop column f1;
1165 alter table p1 drop column f1;
1166 -- c1.f1 is dropped now, since there is no local definition for it
1169 drop table p1 cascade;
1171 create table p1 (f1 int, f2 int);
1172 create table c1 () inherits(p1);
1174 -- should be rejected since c1.f1 is inherited
1175 alter table c1 drop column f1;
1176 alter table only p1 drop column f1;
1177 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1178 alter table c1 drop column f1;
1180 drop table p1 cascade;
1182 create table p1 (f1 int, f2 int);
1183 create table c1 (f1 int not null) inherits(p1);
1185 -- should be rejected since c1.f1 is inherited
1186 alter table c1 drop column f1;
1187 alter table only p1 drop column f1;
1188 -- c1.f1 is still there, but no longer inherited
1189 alter table c1 drop column f1;
1191 drop table p1 cascade;
1193 create table p1(id int, name text);
1194 create table p2(id2 int, name text, height int);
1195 create table c1(age int) inherits(p1,p2);
1196 create table gc1() inherits (c1);
1198 select relname, attname, attinhcount, attislocal
1199 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1200 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1201 order by relname, attnum;
1204 alter table only p1 drop column name;
1205 -- should work. Now c1.name is local and inhcount is 0.
1206 alter table p2 drop column name;
1207 -- should be rejected since its inherited
1208 alter table gc1 drop column name;
1209 -- should work, and drop gc1.name along
1210 alter table c1 drop column name;
1211 -- should fail: column does not exist
1212 alter table gc1 drop column name;
1213 -- should work and drop the attribute in all tables
1214 alter table p2 drop column height;
1217 create table dropColumnExists ();
1218 alter table dropColumnExists drop column non_existing; --fail
1219 alter table dropColumnExists drop column if exists non_existing; --succeed
1221 select relname, attname, attinhcount, attislocal
1222 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1223 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1224 order by relname, attnum;
1226 drop table p1, p2 cascade;
1228 -- test attinhcount tracking with merged columns
1230 create table depth0();
1231 create table depth1(c text) inherits (depth0);
1232 create table depth2() inherits (depth1);
1233 alter table depth0 add c text;
1235 select attrelid::regclass, attname, attinhcount, attislocal
1237 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1238 order by attrelid::regclass::text, attnum;
1240 -- test renumbering of child-table columns in inherited operations
1242 create table p1 (f1 int);
1243 create table c1 (f2 text, f3 int) inherits (p1);
1245 alter table p1 add column a1 int check (a1 > 0);
1246 alter table p1 add column f2 text;
1248 insert into p1 values (1,2,'abc');
1249 insert into c1 values(11,'xyz',33,0); -- should fail
1250 insert into c1 values(11,'xyz',33,22);
1253 update p1 set a1 = a1 + 1, f2 = upper(f2);
1256 drop table p1 cascade;
1258 -- test that operations with a dropped column do not try to reference
1261 create domain mytype as text;
1262 create temp table foo (f1 text, f2 mytype, f3 text);
1264 insert into foo values('bb','cc','dd');
1267 drop domain mytype cascade;
1270 insert into foo values('qq','rr');
1272 update foo set f3 = 'zz';
1274 select f3,max(f1) from foo group by f3;
1276 -- Simple tests for alter table column type
1277 alter table foo alter f1 TYPE integer; -- fails
1278 alter table foo alter f1 TYPE varchar(10);
1280 create table anothertab (atcol1 serial8, atcol2 boolean,
1281 constraint anothertab_chk check (atcol1 <= 3));
1283 insert into anothertab (atcol1, atcol2) values (default, true);
1284 insert into anothertab (atcol1, atcol2) values (default, false);
1285 select * from anothertab;
1287 alter table anothertab alter column atcol1 type boolean; -- fails
1288 alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
1289 alter table anothertab alter column atcol1 type integer;
1291 select * from anothertab;
1293 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1294 insert into anothertab (atcol1, atcol2) values (default, null);
1296 select * from anothertab;
1298 alter table anothertab alter column atcol2 type text
1299 using case when atcol2 is true then 'IT WAS TRUE'
1300 when atcol2 is false then 'IT WAS FALSE'
1301 else 'IT WAS NULL!' end;
1303 select * from anothertab;
1304 alter table anothertab alter column atcol1 type boolean
1305 using case when atcol1 % 2 = 0 then true else false end; -- fails
1306 alter table anothertab alter column atcol1 drop default;
1307 alter table anothertab alter column atcol1 type boolean
1308 using case when atcol1 % 2 = 0 then true else false end; -- fails
1309 alter table anothertab drop constraint anothertab_chk;
1310 alter table anothertab drop constraint anothertab_chk; -- fails
1311 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1313 alter table anothertab alter column atcol1 type boolean
1314 using case when atcol1 % 2 = 0 then true else false end;
1316 select * from anothertab;
1318 drop table anothertab;
1320 -- Test index handling in alter table column type (cf. bugs #15835, #15865)
1321 create table anothertab(f1 int primary key, f2 int unique,
1322 f3 int, f4 int, f5 int);
1323 alter table anothertab
1324 add exclude using btree (f3 with =);
1325 alter table anothertab
1326 add exclude using btree (f4 with =) where (f4 is not null);
1327 alter table anothertab
1328 add exclude using btree (f4 with =) where (f5 > 0);
1329 alter table anothertab
1331 create index on anothertab(f2,f3);
1332 create unique index on anothertab(f4);
1335 alter table anothertab alter column f1 type bigint;
1336 alter table anothertab
1337 alter column f2 type bigint,
1338 alter column f3 type bigint,
1339 alter column f4 type bigint;
1340 alter table anothertab alter column f5 type bigint;
1343 drop table anothertab;
1345 create table another (f1 int, f2 text);
1347 insert into another values(1, 'one');
1348 insert into another values(2, 'two');
1349 insert into another values(3, 'three');
1351 select * from another;
1354 alter f1 type text using f2 || ' more',
1355 alter f2 type bigint using f1 * 10;
1357 select * from another;
1362 create table tab1 (a int, b text);
1363 create table tab2 (x int, y tab1);
1364 alter table tab1 alter column b type varchar; -- fails
1366 -- Alter column type that's part of a partitioned index
1367 create table at_partitioned (a int, b text) partition by range (a);
1368 create table at_part_1 partition of at_partitioned for values from (0) to (1000);
1369 insert into at_partitioned values (512, '0.123');
1370 create table at_part_2 (b text, a int);
1371 insert into at_part_2 values ('1.234', 1024);
1372 create index on at_partitioned (b);
1373 create index on at_partitioned (a);
1376 alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000);
1378 alter table at_partitioned alter column b type numeric using b::numeric;
1381 drop table at_partitioned;
1383 -- Alter column type when no table rewrite is required
1384 -- Also check that comments are preserved
1385 create table at_partitioned(id int, name varchar(64), unique (id, name))
1386 partition by hash(id);
1387 comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint';
1388 comment on index at_partitioned_id_name_key is 'parent index';
1389 create table at_partitioned_0 partition of at_partitioned
1390 for values with (modulus 2, remainder 0);
1391 comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
1392 comment on index at_partitioned_0_id_name_key is 'child 0 index';
1393 create table at_partitioned_1 partition of at_partitioned
1394 for values with (modulus 2, remainder 1);
1395 comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
1396 comment on index at_partitioned_1_id_name_key is 'child 1 index';
1397 insert into at_partitioned values(1, 'foo');
1398 insert into at_partitioned values(3, 'bar');
1400 create temp table old_oids as
1401 select relname, oid as oldoid, relfilenode as oldfilenode
1402 from pg_class where relname like 'at_partitioned%';
1405 c.oid = oldoid as orig_oid,
1408 when c.oid then 'own'
1409 when oldfilenode then 'orig'
1412 obj_description(c.oid, 'pg_class') as desc
1413 from pg_class c left join old_oids using (relname)
1414 where relname like 'at_partitioned%'
1417 select conname, obj_description(oid, 'pg_constraint') as desc
1418 from pg_constraint where conname like 'at_partitioned%'
1421 alter table at_partitioned alter column name type varchar(127);
1423 -- Note: these tests currently show the wrong behavior for comments :-(
1426 c.oid = oldoid as orig_oid,
1429 when c.oid then 'own'
1430 when oldfilenode then 'orig'
1433 obj_description(c.oid, 'pg_class') as desc
1434 from pg_class c left join old_oids using (relname)
1435 where relname like 'at_partitioned%'
1438 select conname, obj_description(oid, 'pg_constraint') as desc
1439 from pg_constraint where conname like 'at_partitioned%'
1442 -- Don't remove this DROP, it exposes bug #15672
1443 drop table at_partitioned;
1445 -- disallow recursive containment of row types
1446 create temp table recur1 (f1 int);
1447 alter table recur1 add column f2 recur1; -- fails
1448 alter table recur1 add column f2 recur1[]; -- fails
1449 create domain array_of_recur1 as recur1[];
1450 alter table recur1 add column f2 array_of_recur1; -- fails
1451 create temp table recur2 (f1 int, f2 recur1);
1452 alter table recur1 add column f2 recur2; -- fails
1453 alter table recur1 add column f2 int;
1454 alter table recur1 alter column f2 type recur2; -- fails
1456 -- SET STORAGE may need to add a TOAST table
1457 create table test_storage (a text);
1458 alter table test_storage alter a set storage plain;
1459 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1460 alter table test_storage alter a set storage extended; -- re-add TOAST table
1462 select reltoastrelid <> 0 as has_toast_table
1464 where oid = 'test_storage'::regclass;
1466 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
1467 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
1468 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
1470 \d test_inh_check_child
1471 select relname, conname, coninhcount, conislocal, connoinherit
1472 from pg_constraint c, pg_class r
1473 where relname like 'test_inh_check%' and c.conrelid = r.oid
1475 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
1477 \d test_inh_check_child
1478 select relname, conname, coninhcount, conislocal, connoinherit
1479 from pg_constraint c, pg_class r
1480 where relname like 'test_inh_check%' and c.conrelid = r.oid
1482 -- also try noinherit, local, and local+inherited cases
1483 ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT;
1484 ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000);
1485 ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1);
1486 ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1);
1488 \d test_inh_check_child
1489 select relname, conname, coninhcount, conislocal, connoinherit
1490 from pg_constraint c, pg_class r
1491 where relname like 'test_inh_check%' and c.conrelid = r.oid
1493 ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric;
1495 \d test_inh_check_child
1496 select relname, conname, coninhcount, conislocal, connoinherit
1497 from pg_constraint c, pg_class r
1498 where relname like 'test_inh_check%' and c.conrelid = r.oid
1501 -- ALTER COLUMN TYPE with different schema in children
1502 -- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com
1503 CREATE TABLE test_type_diff (f1 int);
1504 CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff);
1505 ALTER TABLE test_type_diff ADD COLUMN f2 int;
1506 INSERT INTO test_type_diff_c VALUES (1, 2, 3);
1507 ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint;
1509 CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8);
1510 CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2);
1511 CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4);
1512 CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8);
1513 ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2;
1514 ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2;
1515 ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2;
1516 INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3);
1517 INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6);
1518 INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9);
1519 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8;
1520 -- whole-row references are disallowed
1521 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2));
1523 -- check for rollback of ANALYZE corrupting table property flags (bug #11638)
1524 CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text);
1525 CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text);
1527 ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
1528 ANALYZE check_fk_presence_2;
1530 \d check_fk_presence_2
1531 DROP TABLE check_fk_presence_1, check_fk_presence_2;
1533 -- check column addition within a view (bug #14876)
1534 create table at_base_table(id int, stuff text);
1535 insert into at_base_table values (23, 'skidoo');
1536 create view at_view_1 as select * from at_base_table bt;
1537 create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1;
1540 explain (verbose, costs off) select * from at_view_2;
1541 select * from at_view_2;
1543 create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt;
1546 explain (verbose, costs off) select * from at_view_2;
1547 select * from at_view_2;
1549 drop view at_view_2;
1550 drop view at_view_1;
1551 drop table at_base_table;
1553 -- check adding a column not iself requiring a rewrite, together with
1554 -- a column requiring a default (bug #16038)
1556 -- ensure that rewrites aren't silently optimized away, removing the
1557 -- value of the test
1558 CREATE OR REPLACE FUNCTION evtrig_rewrite_log() RETURNS event_trigger
1559 LANGUAGE plpgsql AS $$
1561 RAISE WARNING 'rewriting table %',
1562 pg_event_trigger_table_rewrite_oid()::regclass;
1565 CREATE EVENT TRIGGER evtrig_rewrite_log ON table_rewrite
1566 EXECUTE PROCEDURE evtrig_rewrite_log();
1568 CREATE TABLE rewrite_test(col text);
1569 INSERT INTO rewrite_test VALUES ('something');
1570 INSERT INTO rewrite_test VALUES (NULL);
1572 -- empty[12] doesn't need rewrite, but notempty[12]_rewrite will force one
1573 ALTER TABLE rewrite_test
1574 ADD COLUMN empty1 text,
1575 ADD COLUMN notempty1_rewrite serial;
1576 ALTER TABLE rewrite_test
1577 ADD COLUMN notempty2_rewrite serial,
1578 ADD COLUMN empty2 text;
1579 -- also check that fast defaults cause no problem, first without rewrite
1580 ALTER TABLE rewrite_test
1581 ADD COLUMN empty3 text,
1582 ADD COLUMN notempty3_norewrite int default 42;
1583 ALTER TABLE rewrite_test
1584 ADD COLUMN notempty4_norewrite int default 42,
1585 ADD COLUMN empty4 text;
1586 -- then with rewrite
1587 ALTER TABLE rewrite_test
1588 ADD COLUMN empty5 text,
1589 ADD COLUMN notempty5_norewrite int default 42,
1590 ADD COLUMN notempty5_rewrite serial;
1591 ALTER TABLE rewrite_test
1592 ADD COLUMN notempty6_rewrite serial,
1593 ADD COLUMN empty6 text,
1594 ADD COLUMN notempty6_norewrite int default 42;
1597 drop event trigger evtrig_rewrite_log;
1598 drop function evtrig_rewrite_log();
1599 DROP TABLE rewrite_test;
1604 drop type lockmodes;
1605 create type lockmodes as enum (
1610 ,'ShareUpdateExclusiveLock'
1612 ,'ShareRowExclusiveLock'
1614 ,'AccessExclusiveLock'
1618 create or replace view my_locks as
1619 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1620 from pg_locks l join pg_class c on l.relation = c.oid
1621 where virtualtransaction = (
1622 select virtualtransaction
1624 where transactionid = txid_current()::integer)
1625 and locktype = 'relation'
1626 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1627 and c.relname != 'my_locks'
1630 create table alterlock (f1 int primary key, f2 text);
1631 insert into alterlock values (1, 'foo');
1632 create table alterlock2 (f3 int primary key, f1 int);
1633 insert into alterlock2 values (1, 1);
1635 begin; alter table alterlock alter column f2 set statistics 150;
1636 select * from my_locks order by 1;
1639 begin; alter table alterlock cluster on alterlock_pkey;
1640 select * from my_locks order by 1;
1643 begin; alter table alterlock set without cluster;
1644 select * from my_locks order by 1;
1647 begin; alter table alterlock set (fillfactor = 100);
1648 select * from my_locks order by 1;
1651 begin; alter table alterlock reset (fillfactor);
1652 select * from my_locks order by 1;
1655 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1656 select * from my_locks order by 1;
1659 begin; alter table alterlock set (autovacuum_enabled = off);
1660 select * from my_locks order by 1;
1663 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1664 select * from my_locks order by 1;
1667 -- test that mixing options with different lock levels works as expected
1668 begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
1669 select * from my_locks order by 1;
1672 begin; alter table alterlock alter column f2 set storage extended;
1673 select * from my_locks order by 1;
1676 begin; alter table alterlock alter column f2 set default 'x';
1677 select * from my_locks order by 1;
1681 create trigger ttdummy
1682 before delete or update on alterlock
1686 select * from my_locks order by 1;
1690 select * from my_locks order by 1;
1691 alter table alterlock2 add foreign key (f1) references alterlock (f1);
1692 select * from my_locks order by 1;
1696 alter table alterlock2
1697 add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
1698 select * from my_locks order by 1;
1701 alter table alterlock2 validate constraint alterlock2nv;
1702 select * from my_locks order by 1;
1705 create or replace view my_locks as
1706 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1707 from pg_locks l join pg_class c on l.relation = c.oid
1708 where virtualtransaction = (
1709 select virtualtransaction
1711 where transactionid = txid_current()::integer)
1712 and locktype = 'relation'
1713 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1714 and c.relname = 'my_locks'
1718 alter table my_locks set (autovacuum_enabled = false);
1719 alter view my_locks set (autovacuum_enabled = false);
1720 alter table my_locks reset (autovacuum_enabled);
1721 alter view my_locks reset (autovacuum_enabled);
1724 alter view my_locks set (security_barrier=off);
1725 select * from my_locks order by 1;
1726 alter view my_locks reset (security_barrier);
1729 -- this test intentionally applies the ALTER TABLE command against a view, but
1730 -- uses a view option so we expect this to succeed. This form of SQL is
1731 -- accepted for historical reasons, as shown in the docs for ALTER VIEW
1733 alter table my_locks set (security_barrier=off);
1734 select * from my_locks order by 1;
1735 alter table my_locks reset (security_barrier);
1739 drop table alterlock2;
1740 drop table alterlock;
1742 drop type lockmodes;
1747 create function test_strict(text) returns text as
1748 'select coalesce($1, ''got passed a null'');'
1749 language sql returns null on null input;
1750 select test_strict(NULL);
1751 alter function test_strict(text) called on null input;
1752 select test_strict(NULL);
1754 create function non_strict(text) returns text as
1755 'select coalesce($1, ''got passed a null'');'
1756 language sql called on null input;
1757 select non_strict(NULL);
1758 alter function non_strict(text) returns null on null input;
1759 select non_strict(NULL);
1762 -- alter object set schema
1765 create schema alter1;
1766 create schema alter2;
1768 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1770 create view alter1.v1 as select * from alter1.t1;
1772 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1774 create domain alter1.posint integer check (value > 0);
1776 create type alter1.ctype as (f1 int, f2 text);
1778 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1779 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1781 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1783 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1784 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1786 create conversion alter1.latin1_to_utf8 for 'latin1' to 'utf8' from iso8859_1_to_utf8;
1788 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1789 create text search configuration alter1.cfg(parser = alter1.prs);
1790 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1791 create text search dictionary alter1.dict(template = alter1.tmpl);
1793 insert into alter1.t1(f2) values(11);
1794 insert into alter1.t1(f2) values(12);
1796 alter table alter1.t1 set schema alter1; -- no-op, same schema
1797 alter table alter1.t1 set schema alter2;
1798 alter table alter1.v1 set schema alter2;
1799 alter function alter1.plus1(int) set schema alter2;
1800 alter domain alter1.posint set schema alter2;
1801 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1802 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1803 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1804 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1805 alter type alter1.ctype set schema alter1; -- no-op, same schema
1806 alter type alter1.ctype set schema alter2;
1807 alter conversion alter1.latin1_to_utf8 set schema alter2;
1808 alter text search parser alter1.prs set schema alter2;
1809 alter text search configuration alter1.cfg set schema alter2;
1810 alter text search template alter1.tmpl set schema alter2;
1811 alter text search dictionary alter1.dict set schema alter2;
1813 -- this should succeed because nothing is left in alter1
1816 insert into alter2.t1(f2) values(13);
1817 insert into alter2.t1(f2) values(14);
1819 select * from alter2.t1;
1821 select * from alter2.v1;
1823 select alter2.plus1(41);
1826 drop schema alter2 cascade;
1832 CREATE TYPE test_type AS (a int);
1835 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1837 ALTER TYPE test_type ADD ATTRIBUTE b text;
1840 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1842 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1845 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1848 ALTER TYPE test_type DROP ATTRIBUTE b;
1851 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1853 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1855 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1858 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1859 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1862 DROP TYPE test_type;
1864 CREATE TYPE test_type1 AS (a int, b text);
1865 CREATE TABLE test_tbl1 (x int, y test_type1);
1866 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1868 CREATE TYPE test_type2 AS (a int, b text);
1869 CREATE TABLE test_tbl2 OF test_type2;
1870 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1874 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1875 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1879 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1880 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1884 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1885 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1889 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1890 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1893 \d test_tbl2_subclass
1895 DROP TABLE test_tbl2_subclass;
1897 CREATE TYPE test_typex AS (a int, b text);
1898 CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0));
1899 ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails
1900 ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE;
1902 DROP TABLE test_tblx;
1903 DROP TYPE test_typex;
1905 -- This test isn't that interesting on its own, but the purpose is to leave
1906 -- behind a table to test pg_upgrade with. The table has a composite type
1907 -- column in it, and the composite type has a dropped attribute.
1908 CREATE TYPE test_type3 AS (a int);
1909 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
1910 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
1912 CREATE TYPE test_type_empty AS ();
1913 DROP TYPE test_type_empty;
1916 -- typed tables: OF / NOT OF
1919 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
1920 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
1921 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
1922 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
1923 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
1924 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
1925 CREATE TABLE tt4 (x int); -- too few columns
1926 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
1927 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
1928 CREATE TABLE tt7 (x int, q text, y numeric(8,2));
1929 ALTER TABLE tt7 DROP q; -- OK
1931 ALTER TABLE tt0 OF tt_t0;
1932 ALTER TABLE tt1 OF tt_t0;
1933 ALTER TABLE tt2 OF tt_t0;
1934 ALTER TABLE tt3 OF tt_t0;
1935 ALTER TABLE tt4 OF tt_t0;
1936 ALTER TABLE tt5 OF tt_t0;
1937 ALTER TABLE tt6 OF tt_t0;
1938 ALTER TABLE tt7 OF tt_t0;
1940 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
1941 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
1942 ALTER TABLE tt7 NOT OF;
1945 -- make sure we can drop a constraint on the parent but it remains on the child
1946 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
1947 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
1948 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
1950 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
1951 DROP TABLE test_drop_constr_parent CASCADE;
1956 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1957 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1958 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1959 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1960 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1961 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1963 CREATE TABLE tt8(a int);
1964 CREATE SCHEMA alter2;
1966 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1967 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1968 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1969 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1970 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1971 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1975 DROP TABLE alter2.tt8;
1979 -- Check conflicts between index and CHECK constraint names
1981 CREATE TABLE tt9(c integer);
1982 ALTER TABLE tt9 ADD CHECK(c > 1);
1983 ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
1984 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
1985 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
1986 ALTER TABLE tt9 ADD UNIQUE(c);
1987 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
1988 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
1989 ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
1990 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
1991 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
1992 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
1997 -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
1998 CREATE TABLE comment_test (
2000 positive_col int CHECK (positive_col > 0),
2002 CONSTRAINT comment_test_pk PRIMARY KEY (id));
2003 CREATE INDEX comment_test_index ON comment_test(indexed_col);
2005 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
2006 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
2007 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
2008 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
2009 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
2011 SELECT col_description('comment_test'::regclass, 1) as comment;
2012 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
2013 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
2015 -- Change the datatype of all the columns. ALTER TABLE is optimized to not
2016 -- rebuild an index if the new data type is binary compatible with the old
2017 -- one. Check do a dummy ALTER TABLE that doesn't change the datatype
2018 -- first, to test that no-op codepath, and another one that does.
2019 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
2020 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
2021 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
2022 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
2023 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
2024 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
2026 -- Check that the comments are intact.
2027 SELECT col_description('comment_test'::regclass, 1) as comment;
2028 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
2029 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
2031 -- Check compatibility for foreign keys and comments. This is done
2032 -- separately as rebuilding the column type of the parent leads
2033 -- to an error and would reduce the test scope.
2034 CREATE TABLE comment_test_child (
2035 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test);
2036 CREATE INDEX comment_test_child_fk ON comment_test_child(id);
2037 COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child';
2038 COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child';
2039 COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child';
2041 -- Change column type of parent
2042 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
2043 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer;
2045 -- Comments should be intact
2046 SELECT col_description('comment_test_child'::regclass, 1) as comment;
2047 SELECT 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;
2048 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2;
2050 -- Check that we map relation oids to filenodes and back correctly. Only
2051 -- display bad mappings so the test output doesn't change all the time. A
2052 -- filenode function call can return NULL for a relation dropped concurrently
2053 -- with the call's surrounding query, so ignore a NULL mapped_oid for
2054 -- relations that no longer exist after all calls finish.
2055 CREATE TEMP TABLE filenode_mapping AS
2057 oid, mapped_oid, reltablespace, relfilenode, relname
2059 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid
2060 WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid;
2062 SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid
2063 WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
2065 -- Checks on creating and manipulation of user defined relations in
2068 -- XXX: It would be useful to add checks around trying to manipulate
2069 -- catalog tables, but that might have ugly consequences when run
2070 -- against an existing server with allow_system_table_mods = on.
2072 SHOW allow_system_table_mods;
2073 -- disallowed because of search_path issues with pg_dump
2074 CREATE TABLE pg_catalog.new_system_table();
2075 -- instead create in public first, move to catalog
2076 CREATE TABLE new_system_table(id serial primary key, othercol text);
2077 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2078 ALTER TABLE new_system_table SET SCHEMA public;
2079 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2080 -- will be ignored -- already there:
2081 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2082 ALTER TABLE new_system_table RENAME TO old_system_table;
2083 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
2084 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
2085 UPDATE old_system_table SET id = -id;
2086 DELETE FROM old_system_table WHERE othercol = 'somedata';
2087 TRUNCATE old_system_table;
2088 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
2089 ALTER TABLE old_system_table DROP COLUMN othercol;
2090 DROP TABLE old_system_table;
2093 CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
2094 -- check relpersistence of an unlogged table
2095 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
2097 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
2099 SELECT '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'
2101 CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
2102 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
2103 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
2104 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
2105 ALTER TABLE unlogged1 SET LOGGED;
2106 -- check relpersistence of an unlogged table after changing to permanent
2107 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
2109 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
2111 SELECT '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'
2113 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
2114 DROP TABLE unlogged3;
2115 DROP TABLE unlogged2;
2116 DROP TABLE unlogged1;
2118 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
2119 -- check relpersistence of a permanent table
2120 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
2122 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
2124 SELECT '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'
2126 CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
2127 CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
2128 ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
2129 ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
2130 ALTER TABLE logged2 SET UNLOGGED;
2131 ALTER TABLE logged1 SET UNLOGGED;
2132 -- check relpersistence of a permanent table after changing to unlogged
2133 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
2135 SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
2137 SELECT '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'
2139 ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
2144 -- test ADD COLUMN IF NOT EXISTS
2145 CREATE TABLE test_add_column(c1 integer);
2147 ALTER TABLE test_add_column
2148 ADD COLUMN c2 integer;
2150 ALTER TABLE test_add_column
2151 ADD COLUMN c2 integer; -- fail because c2 already exists
2152 ALTER TABLE ONLY test_add_column
2153 ADD COLUMN c2 integer; -- fail because c2 already exists
2155 ALTER TABLE test_add_column
2156 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
2157 ALTER TABLE ONLY test_add_column
2158 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
2160 ALTER TABLE test_add_column
2161 ADD COLUMN c2 integer, -- fail because c2 already exists
2162 ADD COLUMN c3 integer;
2164 ALTER TABLE test_add_column
2165 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2166 ADD COLUMN c3 integer; -- fail because c3 already exists
2168 ALTER TABLE test_add_column
2169 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2170 ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists
2172 ALTER TABLE test_add_column
2173 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2174 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
2175 ADD COLUMN c4 integer;
2177 DROP TABLE test_add_column;
2179 -- unsupported constraint types for partitioned tables
2180 CREATE TABLE partitioned (
2183 ) PARTITION BY RANGE (a, (a+b+1));
2184 ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
2186 -- cannot drop column that is part of the partition key
2187 ALTER TABLE partitioned DROP COLUMN a;
2188 ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
2189 ALTER TABLE partitioned DROP COLUMN b;
2190 ALTER TABLE partitioned ALTER COLUMN b TYPE char(5);
2192 -- partitioned table cannot participate in regular inheritance
2193 CREATE TABLE nonpartitioned (
2197 ALTER TABLE partitioned INHERIT nonpartitioned;
2198 ALTER TABLE nonpartitioned INHERIT partitioned;
2200 -- cannot add NO INHERIT constraint to partitioned tables
2201 ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT;
2203 DROP TABLE partitioned, nonpartitioned;
2209 -- check that target table is partitioned
2210 CREATE TABLE unparted (
2213 CREATE TABLE fail_part (like unparted);
2214 ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a');
2215 DROP TABLE unparted, fail_part;
2217 -- check that partition bound is compatible
2218 CREATE TABLE list_parted (
2220 b char(2) COLLATE "C",
2221 CONSTRAINT check_a CHECK (a > 0)
2222 ) PARTITION BY LIST (a);
2223 CREATE TABLE fail_part (LIKE list_parted);
2224 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10);
2225 DROP TABLE fail_part;
2227 -- check that the table being attached exists
2228 ALTER TABLE list_parted ATTACH PARTITION nonexistent FOR VALUES IN (1);
2230 -- check ownership of the source table
2231 CREATE ROLE regress_test_me;
2232 CREATE ROLE regress_test_not_me;
2233 CREATE TABLE not_owned_by_me (LIKE list_parted);
2234 ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me;
2235 SET SESSION AUTHORIZATION regress_test_me;
2236 CREATE TABLE owned_by_me (
2238 ) PARTITION BY LIST (a);
2239 ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1);
2240 RESET SESSION AUTHORIZATION;
2241 DROP TABLE owned_by_me, not_owned_by_me;
2242 DROP ROLE regress_test_not_me;
2243 DROP ROLE regress_test_me;
2245 -- check that the table being attached is not part of regular inheritance
2246 CREATE TABLE parent (LIKE list_parted);
2247 CREATE TABLE child () INHERITS (parent);
2248 ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1);
2249 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
2250 DROP TABLE parent CASCADE;
2252 -- check any TEMP-ness
2253 CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
2254 CREATE TABLE perm_part (a int);
2255 ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1);
2256 DROP TABLE temp_parted, perm_part;
2258 -- check that the table being attached is not a typed table
2259 CREATE TYPE mytype AS (a int);
2260 CREATE TABLE fail_part OF mytype;
2261 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2262 DROP TYPE mytype CASCADE;
2264 -- check that the table being attached has only columns present in the parent
2265 CREATE TABLE fail_part (like list_parted, c int);
2266 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2267 DROP TABLE fail_part;
2269 -- check that the table being attached has every column of the parent
2270 CREATE TABLE fail_part (a int NOT NULL);
2271 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2272 DROP TABLE fail_part;
2274 -- check that columns match in type, collation and NOT NULL status
2275 CREATE TABLE fail_part (
2279 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2280 ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX";
2281 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2282 DROP TABLE fail_part;
2284 -- check that the table being attached has all constraints of the parent
2285 CREATE TABLE fail_part (
2286 b char(2) COLLATE "C",
2289 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2291 -- check that the constraint matches in definition with parent's constraint
2292 ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0);
2293 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2294 DROP TABLE fail_part;
2296 -- check the attributes and constraints after partition is attached
2297 CREATE TABLE part_1 (
2299 b char(2) COLLATE "C",
2300 CONSTRAINT check_a CHECK (a > 0)
2302 ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1);
2303 -- attislocal and conislocal are always false for merged attributes and constraints respectively.
2304 SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0;
2305 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a';
2307 -- check that the new partition won't overlap with an existing partition
2308 CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
2309 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2310 DROP TABLE fail_part;
2311 -- check that an existing table can be attached as a default partition
2312 CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS);
2313 ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT;
2314 -- check attaching default partition fails if a default partition already
2316 CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS);
2317 ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT;
2319 -- check validation when attaching list partitions
2320 CREATE TABLE list_parted2 (
2323 ) PARTITION BY LIST (a);
2325 -- check that violating rows are correctly reported
2326 CREATE TABLE part_2 (LIKE list_parted2);
2327 INSERT INTO part_2 VALUES (3, 'a');
2328 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2330 -- should be ok after deleting the bad row
2332 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2334 -- check partition cannot be attached if default has some row for its values
2335 CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
2336 INSERT INTO list_parted2_def VALUES (11, 'z');
2337 CREATE TABLE part_3 (LIKE list_parted2);
2338 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
2339 -- should be ok after deleting the bad row
2340 DELETE FROM list_parted2_def WHERE a = 11;
2341 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
2343 -- adding constraints that describe the desired partition constraint
2344 -- (or more restrictive) will help skip the validation scan
2345 CREATE TABLE part_3_4 (
2347 CONSTRAINT check_a CHECK (a IN (3))
2350 -- however, if a list partition does not accept nulls, there should be
2351 -- an explicit NOT NULL constraint on the partition key column for the
2352 -- validation scan to be skipped;
2353 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
2355 -- adding a NOT NULL constraint will cause the scan to be skipped
2356 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
2357 ALTER TABLE part_3_4 ALTER a SET NOT NULL;
2358 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
2360 -- check if default partition scan skipped
2361 ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6));
2362 CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);
2364 -- check validation when attaching range partitions
2365 CREATE TABLE range_parted (
2368 ) PARTITION BY RANGE (a, b);
2370 -- check that violating rows are correctly reported
2371 CREATE TABLE part1 (
2372 a int NOT NULL CHECK (a = 1),
2373 b int NOT NULL CHECK (b >= 1 AND b <= 10)
2375 INSERT INTO part1 VALUES (1, 10);
2376 -- Remember the TO bound is exclusive
2377 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
2379 -- should be ok after deleting the bad row
2381 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
2383 -- adding constraints that describe the desired partition constraint
2384 -- (or more restrictive) will help skip the validation scan
2385 CREATE TABLE part2 (
2386 a int NOT NULL CHECK (a = 1),
2387 b int NOT NULL CHECK (b >= 10 AND b < 18)
2389 ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20);
2391 -- Create default partition
2392 CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
2394 -- Only one default partition is allowed, hence, following should give error
2395 CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS);
2396 ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT;
2398 -- Overlapping partitions cannot be attached, hence, following should give error
2399 INSERT INTO partr_def1 VALUES (2, 10);
2400 CREATE TABLE part3 (LIKE range_parted);
2401 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20);
2403 -- Attaching partitions should be successful when there are no overlapping rows
2404 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20);
2406 -- check that leaf partitions are scanned when attaching a partitioned
2408 CREATE TABLE part_5 (
2410 ) PARTITION BY LIST (b);
2412 -- check that violating rows are correctly reported
2413 CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a');
2414 INSERT INTO part_5_a (a, b) VALUES (6, 'a');
2415 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2417 -- delete the faulting row and also add a constraint to skip the scan
2418 DELETE FROM part_5_a WHERE a NOT IN (3);
2419 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);
2420 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2421 ALTER TABLE list_parted2 DETACH PARTITION part_5;
2422 ALTER TABLE part_5 DROP CONSTRAINT check_a;
2424 -- scan should again be skipped, even though NOT NULL is now a column property
2425 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL;
2426 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2428 -- Check the case where attnos of the partitioning columns in the table being
2429 -- attached differs from the parent. It should not affect the constraint-
2430 -- checking logic that allows to skip the scan.
2431 CREATE TABLE part_6 (
2434 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6)
2436 ALTER TABLE part_6 DROP c;
2437 ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);
2439 -- Similar to above, but the table being attached is a partitioned table
2440 -- whose partition has still different attnos for the root partitioning
2442 CREATE TABLE part_7 (
2444 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
2445 ) PARTITION BY LIST (b);
2446 CREATE TABLE part_7_a_null (
2450 LIKE list_parted2, -- 'a' will have attnum = 4
2451 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
2452 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
2454 ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;
2455 ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null);
2456 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
2458 -- Same example, but check this time that the constraint correctly detects
2460 ALTER TABLE list_parted2 DETACH PARTITION part_7;
2461 ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped
2462 INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a');
2463 SELECT tableoid::regclass, a, b FROM part_7 order by a;
2464 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
2466 -- check that leaf partitions of default partition are scanned when
2467 -- attaching a partitioned table.
2468 ALTER TABLE part_5 DROP CONSTRAINT check_a;
2469 CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a);
2470 CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5);
2471 INSERT INTO part5_def_p1 VALUES (5, 'y');
2472 CREATE TABLE part5_p1 (LIKE part_5);
2473 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
2474 -- should be ok after deleting the bad row
2475 DELETE FROM part5_def_p1 WHERE b = 'y';
2476 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
2478 -- check that the table being attached is not already a partition
2479 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2481 -- check that circular inheritance is not allowed
2482 ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b');
2483 ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
2485 -- If a partitioned table being created or an existing table being attached
2486 -- as a partition does not have a constraint that would allow validation scan
2487 -- to be skipped, but an individual partition does, then the partition's
2488 -- validation scan is skipped.
2489 CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a);
2490 CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b);
2491 CREATE TABLE quuux_default1 PARTITION OF quuux_default (
2492 CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1)
2493 ) FOR VALUES IN ('b');
2494 CREATE TABLE quuux1 (a int, b text);
2495 ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate!
2496 CREATE TABLE quuux2 (a int, b text);
2497 ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation
2498 DROP TABLE quuux1, quuux2;
2499 -- should validate for quuux1, but not for quuux2
2500 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);
2501 CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
2504 -- check validation when attaching hash partitions
2506 -- Use hand-rolled hash functions and operator class to get predictable result
2507 -- on different matchines. part_test_int4_ops is defined in insert.sql.
2509 -- check that the new partition won't overlap with an existing partition
2510 CREATE TABLE hash_parted (
2513 ) PARTITION BY HASH (a part_test_int4_ops);
2514 CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);
2515 CREATE TABLE fail_part (LIKE hpart_1);
2516 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);
2517 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0);
2518 DROP TABLE fail_part;
2520 -- check validation when attaching hash partitions
2522 -- check that violating rows are correctly reported
2523 CREATE TABLE hpart_2 (LIKE hash_parted);
2524 INSERT INTO hpart_2 VALUES (3, 0);
2525 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
2527 -- should be ok after deleting the bad row
2528 DELETE FROM hpart_2;
2529 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
2531 -- check that leaf partitions are scanned when attaching a partitioned
2533 CREATE TABLE hpart_5 (
2535 ) PARTITION BY LIST (b);
2537 -- check that violating rows are correctly reported
2538 CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
2539 INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
2540 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
2542 -- should be ok after deleting the bad row
2543 DELETE FROM hpart_5_a;
2544 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
2546 -- check that the table being attach is with valid modulus and remainder value
2547 CREATE TABLE fail_part(LIKE hash_parted);
2548 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1);
2549 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8);
2550 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
2551 DROP TABLE fail_part;
2557 -- check that the table is partitioned at all
2558 CREATE TABLE regular_table (a int);
2559 ALTER TABLE regular_table DETACH PARTITION any_name;
2560 DROP TABLE regular_table;
2562 -- check that the partition being detached exists at all
2563 ALTER TABLE list_parted2 DETACH PARTITION part_4;
2564 ALTER TABLE hash_parted DETACH PARTITION hpart_4;
2566 -- check that the partition being detached is actually a partition of the parent
2567 CREATE TABLE not_a_part (a int);
2568 ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
2569 ALTER TABLE list_parted2 DETACH PARTITION part_1;
2571 ALTER TABLE hash_parted DETACH PARTITION not_a_part;
2572 DROP TABLE not_a_part;
2574 -- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
2575 -- attislocal/conislocal is set to true
2576 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
2577 SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0;
2578 SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a';
2579 DROP TABLE part_3_4;
2581 -- check that a detached partition is not dropped on dropping a partitioned table
2582 CREATE TABLE range_parted2 (
2584 ) PARTITION BY RANGE(a);
2585 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
2586 ALTER TABLE range_parted2 DETACH PARTITION part_rp;
2587 DROP TABLE range_parted2;
2588 SELECT * from part_rp;
2591 -- Check ALTER TABLE commands for partitioned tables and partitions
2593 -- cannot add/drop column to/from *only* the parent
2594 ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
2595 ALTER TABLE ONLY list_parted2 DROP COLUMN b;
2597 -- cannot add a column to partition or drop an inherited one
2598 ALTER TABLE part_2 ADD COLUMN c text;
2599 ALTER TABLE part_2 DROP COLUMN b;
2601 -- Nor rename, alter type
2602 ALTER TABLE part_2 RENAME COLUMN b to c;
2603 ALTER TABLE part_2 ALTER COLUMN b TYPE text;
2605 -- cannot add/drop NOT NULL or check constraints to *only* the parent, when
2607 ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
2608 ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2610 ALTER TABLE list_parted2 ALTER b SET NOT NULL;
2611 ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
2612 ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2613 ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
2615 -- It's alright though, if no partitions are yet created
2616 CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
2617 ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
2618 ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
2619 ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL;
2620 ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a;
2621 DROP TABLE parted_no_parts;
2623 -- cannot drop inherited NOT NULL or check constraints from partition
2624 ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
2625 ALTER TABLE part_2 ALTER b DROP NOT NULL;
2626 ALTER TABLE part_2 DROP CONSTRAINT check_a2;
2628 -- Doesn't make sense to add NO INHERIT constraints on partitioned tables
2629 ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
2631 -- check that a partition cannot participate in regular inheritance
2632 CREATE TABLE inh_test () INHERITS (part_2);
2633 CREATE TABLE inh_test (LIKE part_2);
2634 ALTER TABLE inh_test INHERIT part_2;
2635 ALTER TABLE part_2 INHERIT inh_test;
2637 -- cannot drop or alter type of partition key columns of lower level
2638 -- partitioned tables; for example, part_5, which is list_parted2's
2639 -- partition, is partitioned on b;
2640 ALTER TABLE list_parted2 DROP COLUMN b;
2641 ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
2643 -- dropping non-partition key columns should be allowed on the parent table.
2644 ALTER TABLE list_parted DROP COLUMN b;
2645 SELECT * FROM list_parted;
2648 DROP TABLE list_parted, list_parted2, range_parted;
2649 DROP TABLE fail_def_part;
2650 DROP TABLE hash_parted;
2652 -- more tests for certain multi-level partitioning scenarios
2653 create table p (a int, b int) partition by range (a, b);
2654 create table p1 (b int, a int not null) partition by range (b);
2655 create table p11 (like p1);
2656 alter table p11 drop a;
2657 alter table p11 add a int;
2658 alter table p11 drop a;
2659 alter table p11 add a int not null;
2660 -- attnum for key attribute 'a' is different in p, p1, and p11
2661 select attrelid::regclass, attname, attnum
2664 and (attrelid = 'p'::regclass
2665 or attrelid = 'p1'::regclass
2666 or attrelid = 'p11'::regclass)
2667 order by attrelid::regclass::text;
2669 alter table p1 attach partition p11 for values from (2) to (5);
2671 insert into p1 (a, b) values (2, 3);
2672 -- check that partition validation scan correctly detects violating rows
2673 alter table p attach partition p1 for values from (1, 2) to (1, 10);
2679 -- validate constraint on partitioned tables should only scan leaf partitions
2680 create table parted_validate_test (a int) partition by list (a);
2681 create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1);
2682 alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
2683 alter table parted_validate_test validate constraint parted_validate_test_chka;
2684 drop table parted_validate_test;
2685 -- test alter column options
2686 CREATE TABLE attmp(i integer);
2687 INSERT INTO attmp VALUES (1);
2688 ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
2689 ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited);
2693 DROP USER regress_alter_table_user1;
2695 -- check that violating rows are correctly reported when attaching as the
2696 -- default partition
2697 create table defpart_attach_test (a int) partition by list (a);
2698 create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
2699 create table defpart_attach_test_d (b int, a int);
2700 alter table defpart_attach_test_d drop b;
2701 insert into defpart_attach_test_d values (1), (2);
2703 -- error because its constraint as the default partition would be violated
2704 -- by the row containing 1
2705 alter table defpart_attach_test attach partition defpart_attach_test_d default;
2706 delete from defpart_attach_test_d where a = 1;
2707 alter table defpart_attach_test_d add check (a > 1);
2709 -- should be attached successfully and without needing to be scanned
2710 alter table defpart_attach_test attach partition defpart_attach_test_d default;
2712 -- check that attaching a partition correctly reports any rows in the default
2713 -- partition that should not be there for the new partition to be attached
2715 create table defpart_attach_test_2 (like defpart_attach_test_d);
2716 alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
2718 drop table defpart_attach_test;
2720 -- check combinations of temporary and permanent relations when attaching
2722 create table perm_part_parent (a int) partition by list (a);
2723 create temp table temp_part_parent (a int) partition by list (a);
2724 create table perm_part_child (a int);
2725 create temp table temp_part_child (a int);
2726 alter table temp_part_parent attach partition perm_part_child default; -- error
2727 alter table perm_part_parent attach partition temp_part_child default; -- error
2728 alter table temp_part_parent attach partition temp_part_child default; -- ok
2729 drop table perm_part_parent cascade;
2730 drop table temp_part_parent cascade;
2732 -- check that attaching partitions to a table while it is being used is
2734 create table tab_part_attach (a int) partition by list (a);
2735 create or replace function func_part_attach() returns trigger
2736 language plpgsql as $$
2738 execute 'create table tab_part_attach_1 (a int)';
2739 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)';
2742 create trigger trig_part_attach before insert on tab_part_attach
2743 for each statement execute procedure func_part_attach();
2744 insert into tab_part_attach values (1);
2745 drop table tab_part_attach;
2746 drop function func_part_attach();
2748 -- test case where the partitioning operator is a SQL function whose
2749 -- evaluation results in the table's relcache being rebuilt partway through
2750 -- the execution of an ATTACH PARTITION command
2751 create function at_test_sql_partop (int4, int4) returns int language sql
2752 as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$;
2753 create operator class at_test_sql_partop for type int4 using btree as
2754 operator 1 < (int4, int4), operator 2 <= (int4, int4),
2755 operator 3 = (int4, int4), operator 4 >= (int4, int4),
2756 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4);
2757 create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop);
2758 create table at_test_sql_partop_1 (a int);
2759 alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10);
2760 drop table at_test_sql_partop;
2761 drop operator class at_test_sql_partop using btree;
2762 drop function at_test_sql_partop;