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
230 -- rename statements with mismatching statement and object types
231 CREATE TABLE alter_idx_rename_test (a INT);
232 CREATE INDEX alter_idx_rename_test_idx ON alter_idx_rename_test (a);
233 CREATE TABLE alter_idx_rename_test_parted (a INT) PARTITION BY LIST (a);
234 CREATE INDEX alter_idx_rename_test_parted_idx ON alter_idx_rename_test_parted (a);
236 ALTER INDEX alter_idx_rename_test RENAME TO alter_idx_rename_test_2;
237 ALTER INDEX alter_idx_rename_test_parted RENAME TO alter_idx_rename_test_parted_2;
238 SELECT relation::regclass, mode FROM pg_locks
239 WHERE pid = pg_backend_pid() AND locktype = 'relation'
240 AND relation::regclass::text LIKE 'alter\_idx%'
241 ORDER BY relation::regclass::text COLLATE "C";
244 ALTER INDEX alter_idx_rename_test_idx RENAME TO alter_idx_rename_test_idx_2;
245 ALTER INDEX alter_idx_rename_test_parted_idx RENAME TO alter_idx_rename_test_parted_idx_2;
246 SELECT relation::regclass, mode FROM pg_locks
247 WHERE pid = pg_backend_pid() AND locktype = 'relation'
248 AND relation::regclass::text LIKE 'alter\_idx%'
249 ORDER BY relation::regclass::text COLLATE "C";
252 ALTER TABLE alter_idx_rename_test_idx_2 RENAME TO alter_idx_rename_test_idx_3;
253 ALTER TABLE alter_idx_rename_test_parted_idx_2 RENAME TO alter_idx_rename_test_parted_idx_3;
254 SELECT relation::regclass, mode FROM pg_locks
255 WHERE pid = pg_backend_pid() AND locktype = 'relation'
256 AND relation::regclass::text LIKE 'alter\_idx%'
257 ORDER BY relation::regclass::text COLLATE "C";
259 DROP TABLE alter_idx_rename_test_2;
262 CREATE VIEW attmp_view (unique1) AS SELECT unique1 FROM tenk1;
263 ALTER TABLE attmp_view RENAME TO attmp_view_new;
265 SET ROLE regress_alter_table_user1;
266 ALTER VIEW attmp_view_new RENAME TO fail; -- permission denied
269 -- hack to ensure we get an indexscan here
270 set enable_seqscan to off;
271 set enable_bitmapscan to off;
273 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
274 reset enable_seqscan;
275 reset enable_bitmapscan;
277 DROP VIEW attmp_view_new;
278 -- toast-like relation name
279 alter table stud_emp rename to pg_toast_stud_emp;
280 alter table pg_toast_stud_emp rename to stud_emp;
282 -- renaming index should rename constraint as well
283 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
284 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
285 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
287 -- renaming constraint
288 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
289 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
290 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
292 -- renaming constraint should rename index as well
293 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
294 DROP INDEX onek_unique1_constraint; -- to see whether it's there
295 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
296 DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
297 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
299 -- renaming constraints vs. inheritance
300 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
301 \d constraint_rename_test
302 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
303 \d constraint_rename_test2
304 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
305 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
306 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
307 \d constraint_rename_test
308 \d constraint_rename_test2
309 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
310 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
311 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
312 \d constraint_rename_test
313 \d constraint_rename_test2
314 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
315 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
316 \d constraint_rename_test
317 \d constraint_rename_test2
318 DROP TABLE constraint_rename_test2;
319 DROP TABLE constraint_rename_test;
320 ALTER TABLE IF EXISTS constraint_not_exist RENAME CONSTRAINT con3 TO con3foo; -- ok
321 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
323 -- renaming constraints with cache reset of target relation
324 CREATE TABLE constraint_rename_cache (a int,
325 CONSTRAINT chk_a CHECK (a > 0),
327 ALTER TABLE constraint_rename_cache
328 RENAME CONSTRAINT chk_a TO chk_a_new;
329 ALTER TABLE constraint_rename_cache
330 RENAME CONSTRAINT constraint_rename_cache_pkey TO constraint_rename_pkey_new;
331 CREATE TABLE like_constraint_rename_cache
332 (LIKE constraint_rename_cache INCLUDING ALL);
333 \d like_constraint_rename_cache
334 DROP TABLE constraint_rename_cache;
335 DROP TABLE like_constraint_rename_cache;
337 -- FOREIGN KEY CONSTRAINT adding TEST
339 CREATE TABLE attmp2 (a int primary key);
341 CREATE TABLE attmp3 (a int, b int);
343 CREATE TABLE attmp4 (a int, b int, unique(a,b));
345 CREATE TABLE attmp5 (a int, b int);
347 -- Insert rows into attmp2 (pktable)
348 INSERT INTO attmp2 values (1);
349 INSERT INTO attmp2 values (2);
350 INSERT INTO attmp2 values (3);
351 INSERT INTO attmp2 values (4);
353 -- Insert rows into attmp3
354 INSERT INTO attmp3 values (1,10);
355 INSERT INTO attmp3 values (1,20);
356 INSERT INTO attmp3 values (5,50);
358 -- Try (and fail) to add constraint due to invalid source columns
359 ALTER TABLE attmp3 add constraint attmpconstr foreign key(c) references attmp2 match full;
361 -- Try (and fail) to add constraint due to invalid destination columns explicitly given
362 ALTER TABLE attmp3 add constraint attmpconstr foreign key(a) references attmp2(b) match full;
364 -- Try (and fail) to add constraint due to invalid data
365 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
367 -- Delete failing row
368 DELETE FROM attmp3 where a=5;
371 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full;
372 ALTER TABLE attmp3 drop constraint attmpconstr;
374 INSERT INTO attmp3 values (5,50);
376 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
377 ALTER TABLE attmp3 add constraint attmpconstr foreign key (a) references attmp2 match full NOT VALID;
378 ALTER TABLE attmp3 validate constraint attmpconstr;
380 -- Delete failing row
381 DELETE FROM attmp3 where a=5;
383 -- Try (and succeed) and repeat to show it works on already valid constraint
384 ALTER TABLE attmp3 validate constraint attmpconstr;
385 ALTER TABLE attmp3 validate constraint attmpconstr;
387 -- Try a non-verified CHECK constraint
388 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
389 ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
390 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
391 DELETE FROM attmp3 WHERE NOT b > 10;
392 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
393 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
395 -- Test inherited NOT VALID CHECK constraints
396 select * from attmp3;
397 CREATE TABLE attmp6 () INHERITS (attmp3);
398 CREATE TABLE attmp7 () INHERITS (attmp3);
400 INSERT INTO attmp6 VALUES (6, 30), (7, 16);
401 ALTER TABLE attmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
402 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- fails
403 DELETE FROM attmp6 WHERE b > 20;
404 ALTER TABLE attmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
406 -- An already validated constraint must not be revalidated
407 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
408 INSERT INTO attmp7 VALUES (8, 18);
409 ALTER TABLE attmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
410 ALTER TABLE attmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
411 ALTER TABLE attmp3 VALIDATE CONSTRAINT identity;
413 -- A NO INHERIT constraint should not be looked for in children during VALIDATE CONSTRAINT
414 create table parent_noinh_convalid (a int);
415 create table child_noinh_convalid () inherits (parent_noinh_convalid);
416 insert into parent_noinh_convalid values (1);
417 insert into child_noinh_convalid values (1);
418 alter table parent_noinh_convalid add constraint check_a_is_2 check (a = 2) no inherit not valid;
419 -- fail, because of the row in parent
420 alter table parent_noinh_convalid validate constraint check_a_is_2;
421 delete from only parent_noinh_convalid;
422 -- ok (parent itself contains no violating rows)
423 alter table parent_noinh_convalid validate constraint check_a_is_2;
424 select convalidated from pg_constraint where conrelid = 'parent_noinh_convalid'::regclass and conname = 'check_a_is_2';
426 drop table parent_noinh_convalid, child_noinh_convalid;
428 -- Try (and fail) to create constraint from attmp5(a) to attmp4(a) - unique constraint on
431 ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full;
445 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
446 -- exclusion until validated
447 set constraint_exclusion TO 'partition';
448 create table nv_parent (d date, check (false) no inherit not valid);
449 -- not valid constraint added at creation time should automatically become valid
452 create table nv_child_2010 () inherits (nv_parent);
453 create table nv_child_2011 () inherits (nv_parent);
454 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
455 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
456 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
457 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
458 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
459 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
460 -- after validation, the constraint should be used
461 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
462 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
464 -- add an inherited NOT VALID constraint
465 alter table nv_parent add check (d between '2001-01-01'::date and '2099-12-31'::date) not valid;
467 -- we leave nv_parent and children around to help test pg_dump logic
469 -- Foreign key adding test with mixed types
471 -- Note: these tables are TEMP to avoid name conflicts when this test
472 -- is run in parallel with foreign_key.sql.
474 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
475 INSERT INTO PKTABLE VALUES(42);
476 CREATE TEMP TABLE FKTABLE (ftest1 inet);
477 -- This next should fail, because int=inet does not exist
478 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
479 -- This should also fail for the same reason, but here we
480 -- give the column name
481 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
483 -- This should succeed, even though they are different types,
484 -- because int=int8 exists and is a member of the integer opfamily
485 CREATE TEMP TABLE FKTABLE (ftest1 int8);
486 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
487 -- Check it actually works
488 INSERT INTO FKTABLE VALUES(42); -- should succeed
489 INSERT INTO FKTABLE VALUES(43); -- should fail
491 -- This should fail, because we'd have to cast numeric to int which is
492 -- not an implicit coercion (or use numeric=numeric, but that's not part
493 -- of the integer opfamily)
494 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
495 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
498 -- On the other hand, this should work because int implicitly promotes to
499 -- numeric, and we allow promotion on the FK side
500 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
501 INSERT INTO PKTABLE VALUES(42);
502 CREATE TEMP TABLE FKTABLE (ftest1 int);
503 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
504 -- Check it actually works
505 INSERT INTO FKTABLE VALUES(42); -- should succeed
506 INSERT INTO FKTABLE VALUES(43); -- should fail
510 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
511 PRIMARY KEY(ptest1, ptest2));
512 -- This should fail, because we just chose really odd types
513 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
514 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
516 -- Again, so should this...
517 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
518 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
519 references pktable(ptest1, ptest2);
521 -- This fails because we mixed up the column ordering
522 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
523 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
524 references pktable(ptest2, ptest1);
526 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
527 references pktable(ptest1, ptest2);
531 -- Test that ALTER CONSTRAINT updates trigger deferrability properly
533 CREATE TEMP TABLE PKTABLE (ptest1 int primary key);
534 CREATE TEMP TABLE FKTABLE (ftest1 int);
536 ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable
537 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
538 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable
539 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
540 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable
541 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;
543 ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable
544 ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;
545 ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE;
546 ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable
547 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
548 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED;
549 ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable
550 ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
551 ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE;
553 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
554 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
555 WHERE tgrelid = 'pktable'::regclass
557 SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred
558 FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
559 WHERE tgrelid = 'fktable'::regclass
562 -- temp tables should go away by themselves, need not drop them.
564 -- test check constraint adding
566 create table atacc1 ( test int );
567 -- add a check constraint
568 alter table atacc1 add constraint atacc_test1 check (test>3);
570 insert into atacc1 (test) values (2);
572 insert into atacc1 (test) values (4);
575 -- let's do one where the check fails when added
576 create table atacc1 ( test int );
577 -- insert a soon to be failing row
578 insert into atacc1 (test) values (2);
579 -- add a check constraint (fails)
580 alter table atacc1 add constraint atacc_test1 check (test>3);
581 insert into atacc1 (test) values (4);
584 -- let's do one where the check fails because the column doesn't exist
585 create table atacc1 ( test int );
586 -- add a check constraint (fails)
587 alter table atacc1 add constraint atacc_test1 check (test1>3);
590 -- something a little more complicated
591 create table atacc1 ( test int, test2 int, test3 int);
592 -- add a check constraint (fails)
593 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
595 insert into atacc1 (test,test2,test3) values (4,4,2);
597 insert into atacc1 (test,test2,test3) values (4,4,5);
600 -- lets do some naming tests
601 create table atacc1 (test int check (test>3), test2 int);
602 alter table atacc1 add check (test2>test);
603 -- should fail for $2
604 insert into atacc1 (test2, test) values (3, 4);
607 -- inheritance related tests
608 create table atacc1 (test int);
609 create table atacc2 (test2 int);
610 create table atacc3 (test3 int) inherits (atacc1, atacc2);
611 alter table atacc2 add constraint foo check (test2>0);
612 -- fail and then succeed on atacc2
613 insert into atacc2 (test2) values (-3);
614 insert into atacc2 (test2) values (3);
615 -- fail and then succeed on atacc3
616 insert into atacc3 (test2) values (-3);
617 insert into atacc3 (test2) values (3);
622 -- same things with one created with INHERIT
623 create table atacc1 (test int);
624 create table atacc2 (test2 int);
625 create table atacc3 (test3 int) inherits (atacc1, atacc2);
626 alter table atacc3 no inherit atacc2;
628 alter table atacc3 no inherit atacc2;
629 -- make sure it really isn't a child
630 insert into atacc3 (test2) values (3);
631 select test2 from atacc2;
632 -- fail due to missing constraint
633 alter table atacc2 add constraint foo check (test2>0);
634 alter table atacc3 inherit atacc2;
635 -- fail due to missing column
636 alter table atacc3 rename test2 to testx;
637 alter table atacc3 inherit atacc2;
638 -- fail due to mismatched data type
639 alter table atacc3 add test2 bool;
640 alter table atacc3 inherit atacc2;
641 alter table atacc3 drop test2;
643 alter table atacc3 add test2 int;
644 update atacc3 set test2 = 4 where test2 is null;
645 alter table atacc3 add constraint foo check (test2>0);
646 alter table atacc3 inherit atacc2;
647 -- fail due to duplicates and circular inheritance
648 alter table atacc3 inherit atacc2;
649 alter table atacc2 inherit atacc3;
650 alter table atacc2 inherit atacc2;
651 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
652 select test2 from atacc2;
653 drop table atacc2 cascade;
656 -- adding only to a parent is allowed as of 9.2
658 create table atacc1 (test int);
659 create table atacc2 (test2 int) inherits (atacc1);
661 alter table atacc1 add constraint foo check (test>0) no inherit;
662 -- check constraint is not there on child
663 insert into atacc2 (test) values (-3);
664 -- check constraint is there on parent
665 insert into atacc1 (test) values (-3);
666 insert into atacc1 (test) values (3);
667 -- fail, violating row:
668 alter table atacc2 add constraint foo check (test>0) no inherit;
672 -- test unique constraint adding
674 create table atacc1 ( test int ) ;
675 -- add a unique constraint
676 alter table atacc1 add constraint atacc_test1 unique (test);
677 -- insert first value
678 insert into atacc1 (test) values (2);
680 insert into atacc1 (test) values (2);
682 insert into atacc1 (test) values (4);
683 -- try to create duplicates via alter table using - should fail
684 alter table atacc1 alter column test type integer using 0;
687 -- let's do one where the unique constraint fails when added
688 create table atacc1 ( test int );
689 -- insert soon to be failing rows
690 insert into atacc1 (test) values (2);
691 insert into atacc1 (test) values (2);
692 -- add a unique constraint (fails)
693 alter table atacc1 add constraint atacc_test1 unique (test);
694 insert into atacc1 (test) values (3);
697 -- let's do one where the unique constraint fails
698 -- because the column doesn't exist
699 create table atacc1 ( test int );
700 -- add a unique constraint (fails)
701 alter table atacc1 add constraint atacc_test1 unique (test1);
704 -- something a little more complicated
705 create table atacc1 ( test int, test2 int);
706 -- add a unique constraint
707 alter table atacc1 add constraint atacc_test1 unique (test, test2);
708 -- insert initial value
709 insert into atacc1 (test,test2) values (4,4);
711 insert into atacc1 (test,test2) values (4,4);
712 -- should all succeed
713 insert into atacc1 (test,test2) values (4,5);
714 insert into atacc1 (test,test2) values (5,4);
715 insert into atacc1 (test,test2) values (5,5);
718 -- lets do some naming tests
719 create table atacc1 (test int, test2 int, unique(test));
720 alter table atacc1 add unique (test2);
721 -- should fail for @@ second one @@
722 insert into atacc1 (test2, test) values (3, 3);
723 insert into atacc1 (test2, test) values (2, 3);
726 -- test primary key constraint adding
728 create table atacc1 ( id serial, test int) ;
729 -- add a primary key constraint
730 alter table atacc1 add constraint atacc_test1 primary key (test);
731 -- insert first value
732 insert into atacc1 (test) values (2);
734 insert into atacc1 (test) values (2);
736 insert into atacc1 (test) values (4);
737 -- inserting NULL should fail
738 insert into atacc1 (test) values(NULL);
739 -- try adding a second primary key (should fail)
740 alter table atacc1 add constraint atacc_oid1 primary key(id);
741 -- drop first primary key constraint
742 alter table atacc1 drop constraint atacc_test1 restrict;
743 -- try adding a primary key on oid (should succeed)
744 alter table atacc1 add constraint atacc_oid1 primary key(id);
747 -- let's do one where the primary key constraint fails when added
748 create table atacc1 ( test int );
749 -- insert soon to be failing rows
750 insert into atacc1 (test) values (2);
751 insert into atacc1 (test) values (2);
752 -- add a primary key (fails)
753 alter table atacc1 add constraint atacc_test1 primary key (test);
754 insert into atacc1 (test) values (3);
757 -- let's do another one where the primary key constraint fails when added
758 create table atacc1 ( test int );
759 -- insert soon to be failing row
760 insert into atacc1 (test) values (NULL);
761 -- add a primary key (fails)
762 alter table atacc1 add constraint atacc_test1 primary key (test);
763 insert into atacc1 (test) values (3);
766 -- let's do one where the primary key constraint fails
767 -- because the column doesn't exist
768 create table atacc1 ( test int );
769 -- add a primary key constraint (fails)
770 alter table atacc1 add constraint atacc_test1 primary key (test1);
773 -- adding a new column as primary key to a non-empty table.
774 -- should fail unless the column has a non-null default value.
775 create table atacc1 ( test int );
776 insert into atacc1 (test) values (0);
777 -- add a primary key column without a default (fails).
778 alter table atacc1 add column test2 int primary key;
779 -- now add a primary key column with a default (succeeds).
780 alter table atacc1 add column test2 int default 0 primary key;
783 -- this combination used to have order-of-execution problems (bug #15580)
784 create table atacc1 (a int);
785 insert into atacc1 values(1);
787 add column b float8 not null default random(),
791 -- additionally, we've seen issues with foreign key validation not being
792 -- properly delayed until after a table rewrite. Check that works ok.
793 create table atacc1 (a int primary key);
794 alter table atacc1 add constraint atacc1_fkey foreign key (a) references atacc1 (a) not valid;
795 alter table atacc1 validate constraint atacc1_fkey, alter a type bigint;
798 -- we've also seen issues with check constraints being validated at the wrong
799 -- time when there's a pending table rewrite.
800 create table atacc1 (a bigint, b int);
801 insert into atacc1 values(1,1);
802 alter table atacc1 add constraint atacc1_chk check(b = 1) not valid;
803 alter table atacc1 validate constraint atacc1_chk, alter a type int;
806 -- same as above, but ensure the constraint violation is detected
807 create table atacc1 (a bigint, b int);
808 insert into atacc1 values(1,2);
809 alter table atacc1 add constraint atacc1_chk check(b = 1) not valid;
810 alter table atacc1 validate constraint atacc1_chk, alter a type int;
813 -- something a little more complicated
814 create table atacc1 ( test int, test2 int);
815 -- add a primary key constraint
816 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
817 -- try adding a second primary key - should fail
818 alter table atacc1 add constraint atacc_test2 primary key (test);
819 -- insert initial value
820 insert into atacc1 (test,test2) values (4,4);
822 insert into atacc1 (test,test2) values (4,4);
823 insert into atacc1 (test,test2) values (NULL,3);
824 insert into atacc1 (test,test2) values (3, NULL);
825 insert into atacc1 (test,test2) values (NULL,NULL);
826 -- should all succeed
827 insert into atacc1 (test,test2) values (4,5);
828 insert into atacc1 (test,test2) values (5,4);
829 insert into atacc1 (test,test2) values (5,5);
832 -- lets do some naming tests
833 create table atacc1 (test int, test2 int, primary key(test));
834 -- only first should succeed
835 insert into atacc1 (test2, test) values (3, 3);
836 insert into atacc1 (test2, test) values (2, 3);
837 insert into atacc1 (test2, test) values (1, NULL);
840 -- alter table / alter column [set/drop] not null tests
841 -- try altering system catalogs, should fail
842 alter table pg_class alter column relname drop not null;
843 alter table pg_class alter relname set not null;
845 -- try altering non-existent table, should fail
846 alter table non_existent alter column bar set not null;
847 alter table non_existent alter column bar drop not null;
849 -- test setting columns to null and not null and vice versa
850 -- test checking for null values and primary key
851 create table atacc1 (test int not null);
852 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
854 alter table atacc1 alter column test drop not null;
856 alter table atacc1 drop constraint "atacc1_pkey";
857 alter table atacc1 alter column test drop not null;
859 insert into atacc1 values (null);
860 alter table atacc1 alter test set not null;
862 alter table atacc1 alter test set not null;
864 -- try altering a non-existent column, should fail
865 alter table atacc1 alter bar set not null;
866 alter table atacc1 alter bar drop not null;
868 -- try creating a view and altering that, should fail
869 create view myview as select * from atacc1;
870 alter table myview alter column test drop not null;
871 alter table myview alter column test set not null;
876 -- set not null verified by constraints
877 create table atacc1 (test_a int, test_b int);
878 insert into atacc1 values (null, 1);
879 -- constraint not cover all values, should fail
880 alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10);
881 alter table atacc1 alter test_a set not null;
882 alter table atacc1 drop constraint atacc1_constr_or;
883 -- not valid constraint, should fail
884 alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid;
885 alter table atacc1 alter test_a set not null;
886 alter table atacc1 drop constraint atacc1_constr_invalid;
887 -- with valid constraint
888 update atacc1 set test_a = 1;
889 alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);
890 alter table atacc1 alter test_a set not null;
893 insert into atacc1 values (2, null);
894 alter table atacc1 alter test_a drop not null;
895 -- test multiple set not null at same time
896 -- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan
897 alter table atacc1 alter test_a set not null, alter test_b set not null;
898 -- commands order has no importance
899 alter table atacc1 alter test_b set not null, alter test_a set not null;
901 -- valid one by table scan, one by check constraints
902 update atacc1 set test_b = 1;
903 alter table atacc1 alter test_b set not null, alter test_a set not null;
905 alter table atacc1 alter test_a drop not null, alter test_b drop not null;
906 -- both column has check constraints
907 alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
908 alter table atacc1 alter test_b set not null, alter test_a set not null;
912 create table parent (a int);
913 create table child (b varchar(255)) inherits (parent);
915 alter table parent alter a set not null;
916 insert into parent values (NULL);
917 insert into child (a, b) values (NULL, 'foo');
918 alter table parent alter a drop not null;
919 insert into parent values (NULL);
920 insert into child (a, b) values (NULL, 'foo');
921 alter table only parent alter a set not null;
922 alter table child alter a set not null;
926 -- test setting and removing default values
927 create table def_test (
929 c2 text default 'initial_default'
931 insert into def_test default values;
932 alter table def_test alter column c1 drop default;
933 insert into def_test default values;
934 alter table def_test alter column c2 drop default;
935 insert into def_test default values;
936 alter table def_test alter column c1 set default 10;
937 alter table def_test alter column c2 set default 'new_default';
938 insert into def_test default values;
939 select * from def_test;
941 -- set defaults to an incorrect type: this should fail
942 alter table def_test alter column c1 set default 'wrong_datatype';
943 alter table def_test alter column c2 set default 20;
945 -- set defaults on a non-existent column: this should fail
946 alter table def_test alter column c3 set default 30;
948 -- set defaults on views: we need to create a view, add a rule
949 -- to allow insertions into it, and then alter the view to add
951 create view def_view_test as select * from def_test;
952 create rule def_view_test_ins as
953 on insert to def_view_test
954 do instead insert into def_test select new.*;
955 insert into def_view_test default values;
956 alter table def_view_test alter column c1 set default 45;
957 insert into def_view_test default values;
958 alter table def_view_test alter column c2 set default 'view_default';
959 insert into def_view_test default values;
960 select * from def_view_test;
962 drop rule def_view_test_ins on def_view_test;
963 drop view def_view_test;
966 -- alter table / drop column tests
967 -- try altering system catalogs, should fail
968 alter table pg_class drop column relname;
970 -- try altering non-existent table, should fail
971 alter table nosuchtable drop column bar;
973 -- test dropping columns
974 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
975 insert into atacc1 values (1, 2, 3, 4);
976 alter table atacc1 drop a;
977 alter table atacc1 drop a;
980 select * from atacc1;
981 select * from atacc1 order by a;
982 select * from atacc1 order by "........pg.dropped.1........";
983 select * from atacc1 group by a;
984 select * from atacc1 group by "........pg.dropped.1........";
985 select atacc1.* from atacc1;
986 select a from atacc1;
987 select atacc1.a from atacc1;
988 select b,c,d from atacc1;
989 select a,b,c,d from atacc1;
990 select * from atacc1 where a = 1;
991 select "........pg.dropped.1........" from atacc1;
992 select atacc1."........pg.dropped.1........" from atacc1;
993 select "........pg.dropped.1........",b,c,d from atacc1;
994 select * from atacc1 where "........pg.dropped.1........" = 1;
997 update atacc1 set a = 3;
998 update atacc1 set b = 2 where a = 3;
999 update atacc1 set "........pg.dropped.1........" = 3;
1000 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
1003 insert into atacc1 values (10, 11, 12, 13);
1004 insert into atacc1 values (default, 11, 12, 13);
1005 insert into atacc1 values (11, 12, 13);
1006 insert into atacc1 (a) values (10);
1007 insert into atacc1 (a) values (default);
1008 insert into atacc1 (a,b,c,d) values (10,11,12,13);
1009 insert into atacc1 (a,b,c,d) values (default,11,12,13);
1010 insert into atacc1 (b,c,d) values (11,12,13);
1011 insert into atacc1 ("........pg.dropped.1........") values (10);
1012 insert into atacc1 ("........pg.dropped.1........") values (default);
1013 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
1014 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
1017 delete from atacc1 where a = 3;
1018 delete from atacc1 where "........pg.dropped.1........" = 3;
1021 -- try dropping a non-existent column, should fail
1022 alter table atacc1 drop bar;
1024 -- try removing an oid column, should succeed (as it's nonexistent)
1025 alter table atacc1 SET WITHOUT OIDS;
1027 -- try adding an oid column, should fail (not supported)
1028 alter table atacc1 SET WITH OIDS;
1030 -- try dropping the xmin column, should fail
1031 alter table atacc1 drop xmin;
1033 -- try creating a view and altering that, should fail
1034 create view myview as select * from atacc1;
1035 select * from myview;
1036 alter table myview drop d;
1039 -- test some commands to make sure they fail on the dropped column
1041 analyze atacc1("........pg.dropped.1........");
1042 vacuum analyze atacc1(a);
1043 vacuum analyze atacc1("........pg.dropped.1........");
1044 comment on column atacc1.a is 'testing';
1045 comment on column atacc1."........pg.dropped.1........" is 'testing';
1046 alter table atacc1 alter a set storage plain;
1047 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
1048 alter table atacc1 alter a set statistics 0;
1049 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
1050 alter table atacc1 alter a set default 3;
1051 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1052 alter table atacc1 alter a drop default;
1053 alter table atacc1 alter "........pg.dropped.1........" drop default;
1054 alter table atacc1 alter a set not null;
1055 alter table atacc1 alter "........pg.dropped.1........" set not null;
1056 alter table atacc1 alter a drop not null;
1057 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1058 alter table atacc1 rename a to x;
1059 alter table atacc1 rename "........pg.dropped.1........" to x;
1060 alter table atacc1 add primary key(a);
1061 alter table atacc1 add primary key("........pg.dropped.1........");
1062 alter table atacc1 add unique(a);
1063 alter table atacc1 add unique("........pg.dropped.1........");
1064 alter table atacc1 add check (a > 3);
1065 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1066 create table atacc2 (id int4 unique);
1067 alter table atacc1 add foreign key (a) references atacc2(id);
1068 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1069 alter table atacc2 add foreign key (id) references atacc1(a);
1070 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1072 create index "testing_idx" on atacc1(a);
1073 create index "testing_idx" on atacc1("........pg.dropped.1........");
1075 -- test create as and select into
1076 insert into atacc1 values (21, 22, 23);
1077 create table attest1 as select * from atacc1;
1078 select * from attest1;
1080 select * into attest2 from atacc1;
1081 select * from attest2;
1084 -- try dropping all columns
1085 alter table atacc1 drop c;
1086 alter table atacc1 drop d;
1087 alter table atacc1 drop b;
1088 select * from atacc1;
1092 -- test constraint error reporting in presence of dropped columns
1093 create table atacc1 (id serial primary key, value int check (value < 10));
1094 insert into atacc1(value) values (100);
1095 alter table atacc1 drop column value;
1096 alter table atacc1 add column value int check (value < 10);
1097 insert into atacc1(value) values (100);
1098 insert into atacc1(id, value) values (null, 0);
1102 create table parent (a int, b int, c int);
1103 insert into parent values (1, 2, 3);
1104 alter table parent drop a;
1105 create table child (d varchar(255)) inherits (parent);
1106 insert into child values (12, 13, 'testing');
1108 select * from parent;
1109 select * from child;
1110 alter table parent drop c;
1111 select * from parent;
1112 select * from child;
1117 -- check error cases for inheritance column merging
1118 create table parent (a float8, b numeric(10,4), c text collate "C");
1120 create table child (a float4) inherits (parent); -- fail
1121 create table child (b decimal(10,7)) inherits (parent); -- fail
1122 create table child (c text collate "POSIX") inherits (parent); -- fail
1123 create table child (a double precision, b decimal(10,4)) inherits (parent);
1129 create table attest (a int4, b int4, c int4);
1130 insert into attest values (1,2,3);
1131 alter table attest drop a;
1132 copy attest to stdout;
1133 copy attest(a) to stdout;
1134 copy attest("........pg.dropped.1........") to stdout;
1135 copy attest from stdin;
1138 select * from attest;
1139 copy attest from stdin;
1142 select * from attest;
1143 copy attest(a) from stdin;
1144 copy attest("........pg.dropped.1........") from stdin;
1145 copy attest(b,c) from stdin;
1148 select * from attest;
1153 create table dropColumn (a int, b int, e int);
1154 create table dropColumnChild (c int) inherits (dropColumn);
1155 create table dropColumnAnother (d int) inherits (dropColumnChild);
1157 -- these two should fail
1158 alter table dropColumnchild drop column a;
1159 alter table only dropColumnChild drop column b;
1163 -- these three should work
1164 alter table only dropColumn drop column e;
1165 alter table dropColumnChild drop column c;
1166 alter table dropColumn drop column a;
1168 create table renameColumn (a int);
1169 create table renameColumnChild (b int) inherits (renameColumn);
1170 create table renameColumnAnother (c int) inherits (renameColumnChild);
1172 -- these three should fail
1173 alter table renameColumnChild rename column a to d;
1174 alter table only renameColumnChild rename column a to d;
1175 alter table only renameColumn rename column a to d;
1177 -- these should work
1178 alter table renameColumn rename column a to d;
1179 alter table renameColumnChild rename column b to a;
1181 -- these should work
1182 alter table if exists doesnt_exist_tab rename column a to d;
1183 alter table if exists doesnt_exist_tab rename column b to a;
1186 alter table renameColumn add column w int;
1189 alter table only renameColumn add column x int;
1192 -- Test corner cases in dropping of inherited columns
1194 create table p1 (f1 int, f2 int);
1195 create table c1 (f1 int not null) inherits(p1);
1197 -- should be rejected since c1.f1 is inherited
1198 alter table c1 drop column f1;
1200 alter table p1 drop column f1;
1201 -- c1.f1 is still there, but no longer inherited
1203 alter table c1 drop column f1;
1206 drop table p1 cascade;
1208 create table p1 (f1 int, f2 int);
1209 create table c1 () inherits(p1);
1211 -- should be rejected since c1.f1 is inherited
1212 alter table c1 drop column f1;
1213 alter table p1 drop column f1;
1214 -- c1.f1 is dropped now, since there is no local definition for it
1217 drop table p1 cascade;
1219 create table p1 (f1 int, f2 int);
1220 create table c1 () inherits(p1);
1222 -- should be rejected since c1.f1 is inherited
1223 alter table c1 drop column f1;
1224 alter table only p1 drop column f1;
1225 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1226 alter table c1 drop column f1;
1228 drop table p1 cascade;
1230 create table p1 (f1 int, f2 int);
1231 create table c1 (f1 int not null) inherits(p1);
1233 -- should be rejected since c1.f1 is inherited
1234 alter table c1 drop column f1;
1235 alter table only p1 drop column f1;
1236 -- c1.f1 is still there, but no longer inherited
1237 alter table c1 drop column f1;
1239 drop table p1 cascade;
1241 create table p1(id int, name text);
1242 create table p2(id2 int, name text, height int);
1243 create table c1(age int) inherits(p1,p2);
1244 create table gc1() inherits (c1);
1246 select relname, attname, attinhcount, attislocal
1247 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1248 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1249 order by relname, attnum;
1252 alter table only p1 drop column name;
1253 -- should work. Now c1.name is local and inhcount is 0.
1254 alter table p2 drop column name;
1255 -- should be rejected since its inherited
1256 alter table gc1 drop column name;
1257 -- should work, and drop gc1.name along
1258 alter table c1 drop column name;
1259 -- should fail: column does not exist
1260 alter table gc1 drop column name;
1261 -- should work and drop the attribute in all tables
1262 alter table p2 drop column height;
1265 create table dropColumnExists ();
1266 alter table dropColumnExists drop column non_existing; --fail
1267 alter table dropColumnExists drop column if exists non_existing; --succeed
1269 select relname, attname, attinhcount, attislocal
1270 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1271 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1272 order by relname, attnum;
1274 drop table p1, p2 cascade;
1276 -- test attinhcount tracking with merged columns
1278 create table depth0();
1279 create table depth1(c text) inherits (depth0);
1280 create table depth2() inherits (depth1);
1281 alter table depth0 add c text;
1283 select attrelid::regclass, attname, attinhcount, attislocal
1285 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1286 order by attrelid::regclass::text, attnum;
1288 -- test renumbering of child-table columns in inherited operations
1290 create table p1 (f1 int);
1291 create table c1 (f2 text, f3 int) inherits (p1);
1293 alter table p1 add column a1 int check (a1 > 0);
1294 alter table p1 add column f2 text;
1296 insert into p1 values (1,2,'abc');
1297 insert into c1 values(11,'xyz',33,0); -- should fail
1298 insert into c1 values(11,'xyz',33,22);
1301 update p1 set a1 = a1 + 1, f2 = upper(f2);
1304 drop table p1 cascade;
1306 -- test that operations with a dropped column do not try to reference
1309 create domain mytype as text;
1310 create temp table foo (f1 text, f2 mytype, f3 text);
1312 insert into foo values('bb','cc','dd');
1315 drop domain mytype cascade;
1318 insert into foo values('qq','rr');
1320 update foo set f3 = 'zz';
1322 select f3,max(f1) from foo group by f3;
1324 -- Simple tests for alter table column type
1325 alter table foo alter f1 TYPE integer; -- fails
1326 alter table foo alter f1 TYPE varchar(10);
1328 create table anothertab (atcol1 serial8, atcol2 boolean,
1329 constraint anothertab_chk check (atcol1 <= 3));
1331 insert into anothertab (atcol1, atcol2) values (default, true);
1332 insert into anothertab (atcol1, atcol2) values (default, false);
1333 select * from anothertab;
1335 alter table anothertab alter column atcol1 type boolean; -- fails
1336 alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
1337 alter table anothertab alter column atcol1 type integer;
1339 select * from anothertab;
1341 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1342 insert into anothertab (atcol1, atcol2) values (default, null);
1344 select * from anothertab;
1346 alter table anothertab alter column atcol2 type text
1347 using case when atcol2 is true then 'IT WAS TRUE'
1348 when atcol2 is false then 'IT WAS FALSE'
1349 else 'IT WAS NULL!' end;
1351 select * from anothertab;
1352 alter table anothertab alter column atcol1 type boolean
1353 using case when atcol1 % 2 = 0 then true else false end; -- fails
1354 alter table anothertab alter column atcol1 drop default;
1355 alter table anothertab alter column atcol1 type boolean
1356 using case when atcol1 % 2 = 0 then true else false end; -- fails
1357 alter table anothertab drop constraint anothertab_chk;
1358 alter table anothertab drop constraint anothertab_chk; -- fails
1359 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1361 alter table anothertab alter column atcol1 type boolean
1362 using case when atcol1 % 2 = 0 then true else false end;
1364 select * from anothertab;
1366 drop table anothertab;
1368 -- Test index handling in alter table column type (cf. bugs #15835, #15865)
1369 create table anothertab(f1 int primary key, f2 int unique,
1370 f3 int, f4 int, f5 int);
1371 alter table anothertab
1372 add exclude using btree (f3 with =);
1373 alter table anothertab
1374 add exclude using btree (f4 with =) where (f4 is not null);
1375 alter table anothertab
1376 add exclude using btree (f4 with =) where (f5 > 0);
1377 alter table anothertab
1379 create index on anothertab(f2,f3);
1380 create unique index on anothertab(f4);
1383 alter table anothertab alter column f1 type bigint;
1384 alter table anothertab
1385 alter column f2 type bigint,
1386 alter column f3 type bigint,
1387 alter column f4 type bigint;
1388 alter table anothertab alter column f5 type bigint;
1391 drop table anothertab;
1393 -- test that USING expressions are parsed before column alter type / drop steps
1394 create table another (f1 int, f2 text, f3 text);
1396 insert into another values(1, 'one', 'uno');
1397 insert into another values(2, 'two', 'due');
1398 insert into another values(3, 'three', 'tre');
1400 select * from another;
1403 alter f1 type text using f2 || ' and ' || f3 || ' more',
1404 alter f2 type bigint using f1 * 10,
1407 select * from another;
1411 -- Create an index that skips WAL, then perform a SET DATA TYPE that skips
1412 -- rewriting the index.
1414 create table skip_wal_skip_rewrite_index (c varchar(10) primary key);
1415 alter table skip_wal_skip_rewrite_index alter c type varchar(20);
1418 -- We disallow changing table's row type if it's used for storage
1419 create table at_tab1 (a int, b text);
1420 create table at_tab2 (x int, y at_tab1);
1421 alter table at_tab1 alter column b type varchar; -- fails
1423 -- Use of row type in an expression is defended differently
1424 create table at_tab2 (x int, y text, check((x,y)::at_tab1 = (1,'42')::at_tab1));
1425 alter table at_tab1 alter column b type varchar; -- allowed, but ...
1426 insert into at_tab2 values(1,'42'); -- ... this will fail
1427 drop table at_tab1, at_tab2;
1428 -- Check it for a partitioned table, too
1429 create table at_tab1 (a int, b text) partition by list(a);
1430 create table at_tab2 (x int, y at_tab1);
1431 alter table at_tab1 alter column b type varchar; -- fails
1432 drop table at_tab1, at_tab2;
1434 -- Alter column type that's part of a partitioned index
1435 create table at_partitioned (a int, b text) partition by range (a);
1436 create table at_part_1 partition of at_partitioned for values from (0) to (1000);
1437 insert into at_partitioned values (512, '0.123');
1438 create table at_part_2 (b text, a int);
1439 insert into at_part_2 values ('1.234', 1024);
1440 create index on at_partitioned (b);
1441 create index on at_partitioned (a);
1444 alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000);
1446 alter table at_partitioned alter column b type numeric using b::numeric;
1449 drop table at_partitioned;
1451 -- Alter column type when no table rewrite is required
1452 -- Also check that comments are preserved
1453 create table at_partitioned(id int, name varchar(64), unique (id, name))
1454 partition by hash(id);
1455 comment on constraint at_partitioned_id_name_key on at_partitioned is 'parent constraint';
1456 comment on index at_partitioned_id_name_key is 'parent index';
1457 create table at_partitioned_0 partition of at_partitioned
1458 for values with (modulus 2, remainder 0);
1459 comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
1460 comment on index at_partitioned_0_id_name_key is 'child 0 index';
1461 create table at_partitioned_1 partition of at_partitioned
1462 for values with (modulus 2, remainder 1);
1463 comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
1464 comment on index at_partitioned_1_id_name_key is 'child 1 index';
1465 insert into at_partitioned values(1, 'foo');
1466 insert into at_partitioned values(3, 'bar');
1468 create temp table old_oids as
1469 select relname, oid as oldoid, relfilenode as oldfilenode
1470 from pg_class where relname like 'at_partitioned%';
1473 c.oid = oldoid as orig_oid,
1476 when c.oid then 'own'
1477 when oldfilenode then 'orig'
1480 obj_description(c.oid, 'pg_class') as desc
1481 from pg_class c left join old_oids using (relname)
1482 where relname like 'at_partitioned%'
1485 select conname, obj_description(oid, 'pg_constraint') as desc
1486 from pg_constraint where conname like 'at_partitioned%'
1489 alter table at_partitioned alter column name type varchar(127);
1492 c.oid = oldoid as orig_oid,
1495 when c.oid then 'own'
1496 when oldfilenode then 'orig'
1499 obj_description(c.oid, 'pg_class') as desc
1500 from pg_class c left join old_oids using (relname)
1501 where relname like 'at_partitioned%'
1504 select conname, obj_description(oid, 'pg_constraint') as desc
1505 from pg_constraint where conname like 'at_partitioned%'
1508 -- Don't remove this DROP, it exposes bug #15672
1509 drop table at_partitioned;
1511 -- disallow recursive containment of row types
1512 create temp table recur1 (f1 int);
1513 alter table recur1 add column f2 recur1; -- fails
1514 alter table recur1 add column f2 recur1[]; -- fails
1515 create domain array_of_recur1 as recur1[];
1516 alter table recur1 add column f2 array_of_recur1; -- fails
1517 create temp table recur2 (f1 int, f2 recur1);
1518 alter table recur1 add column f2 recur2; -- fails
1519 alter table recur1 add column f2 int;
1520 alter table recur1 alter column f2 type recur2; -- fails
1522 -- SET STORAGE may need to add a TOAST table
1523 create table test_storage (a text, c text storage plain);
1524 select reltoastrelid <> 0 as has_toast_table
1525 from pg_class where oid = 'test_storage'::regclass;
1526 alter table test_storage alter a set storage plain;
1527 -- rewrite table to remove its TOAST table; need a non-constant column default
1528 alter table test_storage add b int default random()::int;
1529 select reltoastrelid <> 0 as has_toast_table
1530 from pg_class where oid = 'test_storage'::regclass;
1531 alter table test_storage alter a set storage default; -- re-add TOAST table
1532 select reltoastrelid <> 0 as has_toast_table
1533 from pg_class where oid = 'test_storage'::regclass;
1535 -- check STORAGE correctness
1536 create table test_storage_failed (a text, b int storage extended);
1538 -- test that SET STORAGE propagates to index correctly
1539 create index test_storage_idx on test_storage (b, a);
1540 alter table test_storage alter column a set storage external;
1542 \d+ test_storage_idx
1544 -- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
1545 CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
1546 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
1548 \d test_inh_check_child
1549 select relname, conname, coninhcount, conislocal, connoinherit
1550 from pg_constraint c, pg_class r
1551 where relname like 'test_inh_check%' and c.conrelid = r.oid
1553 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
1555 \d test_inh_check_child
1556 select relname, conname, coninhcount, conislocal, connoinherit
1557 from pg_constraint c, pg_class r
1558 where relname like 'test_inh_check%' and c.conrelid = r.oid
1560 -- also try noinherit, local, and local+inherited cases
1561 ALTER TABLE test_inh_check ADD CONSTRAINT bnoinherit CHECK (b > 100) NO INHERIT;
1562 ALTER TABLE test_inh_check_child ADD CONSTRAINT blocal CHECK (b < 1000);
1563 ALTER TABLE test_inh_check_child ADD CONSTRAINT bmerged CHECK (b > 1);
1564 ALTER TABLE test_inh_check ADD CONSTRAINT bmerged CHECK (b > 1);
1566 \d test_inh_check_child
1567 select relname, conname, coninhcount, conislocal, connoinherit
1568 from pg_constraint c, pg_class r
1569 where relname like 'test_inh_check%' and c.conrelid = r.oid
1571 ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric;
1573 \d test_inh_check_child
1574 select relname, conname, coninhcount, conislocal, connoinherit
1575 from pg_constraint c, pg_class r
1576 where relname like 'test_inh_check%' and c.conrelid = r.oid
1579 -- ALTER COLUMN TYPE with different schema in children
1580 -- Bug at https://postgr.es/m/20170102225618.GA10071@telsasoft.com
1581 CREATE TABLE test_type_diff (f1 int);
1582 CREATE TABLE test_type_diff_c (extra smallint) INHERITS (test_type_diff);
1583 ALTER TABLE test_type_diff ADD COLUMN f2 int;
1584 INSERT INTO test_type_diff_c VALUES (1, 2, 3);
1585 ALTER TABLE test_type_diff ALTER COLUMN f2 TYPE bigint USING f2::bigint;
1587 CREATE TABLE test_type_diff2 (int_two int2, int_four int4, int_eight int8);
1588 CREATE TABLE test_type_diff2_c1 (int_four int4, int_eight int8, int_two int2);
1589 CREATE TABLE test_type_diff2_c2 (int_eight int8, int_two int2, int_four int4);
1590 CREATE TABLE test_type_diff2_c3 (int_two int2, int_four int4, int_eight int8);
1591 ALTER TABLE test_type_diff2_c1 INHERIT test_type_diff2;
1592 ALTER TABLE test_type_diff2_c2 INHERIT test_type_diff2;
1593 ALTER TABLE test_type_diff2_c3 INHERIT test_type_diff2;
1594 INSERT INTO test_type_diff2_c1 VALUES (1, 2, 3);
1595 INSERT INTO test_type_diff2_c2 VALUES (4, 5, 6);
1596 INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9);
1597 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8;
1598 -- whole-row references are disallowed
1599 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2));
1601 -- check for rollback of ANALYZE corrupting table property flags (bug #11638)
1602 CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text);
1603 CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text);
1605 ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
1606 ANALYZE check_fk_presence_2;
1608 \d check_fk_presence_2
1609 DROP TABLE check_fk_presence_1, check_fk_presence_2;
1611 -- check column addition within a view (bug #14876)
1612 create table at_base_table(id int, stuff text);
1613 insert into at_base_table values (23, 'skidoo');
1614 create view at_view_1 as select * from at_base_table bt;
1615 create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1;
1618 explain (verbose, costs off) select * from at_view_2;
1619 select * from at_view_2;
1621 create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt;
1624 explain (verbose, costs off) select * from at_view_2;
1625 select * from at_view_2;
1627 drop view at_view_2;
1628 drop view at_view_1;
1629 drop table at_base_table;
1631 -- related case (bug #17811)
1633 create temp table t1 as select * from int8_tbl;
1634 create temp view v1 as select 1::int8 as q1;
1635 create temp view v2 as select * from v1;
1636 create or replace temp view v1 with (security_barrier = true)
1637 as select * from t1;
1639 create temp table log (q1 int8, q2 int8);
1640 create rule v1_upd_rule as on update to v1
1641 do also insert into log values (new.*);
1643 update v2 set q1 = q1 + 1 where q1 = 123;
1649 -- check adding a column not itself requiring a rewrite, together with
1650 -- a column requiring a default (bug #16038)
1652 -- ensure that rewrites aren't silently optimized away, removing the
1653 -- value of the test
1654 CREATE FUNCTION check_ddl_rewrite(p_tablename regclass, p_ddl text)
1656 LANGUAGE plpgsql AS $$
1660 v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename;
1664 RETURN v_relfilenode <> (SELECT relfilenode FROM pg_class WHERE oid = p_tablename);
1668 CREATE TABLE rewrite_test(col text);
1669 INSERT INTO rewrite_test VALUES ('something');
1670 INSERT INTO rewrite_test VALUES (NULL);
1672 -- empty[12] don't need rewrite, but notempty[12]_rewrite will force one
1673 SELECT check_ddl_rewrite('rewrite_test', $$
1674 ALTER TABLE rewrite_test
1675 ADD COLUMN empty1 text,
1676 ADD COLUMN notempty1_rewrite serial;
1678 SELECT check_ddl_rewrite('rewrite_test', $$
1679 ALTER TABLE rewrite_test
1680 ADD COLUMN notempty2_rewrite serial,
1681 ADD COLUMN empty2 text;
1683 -- also check that fast defaults cause no problem, first without rewrite
1684 SELECT check_ddl_rewrite('rewrite_test', $$
1685 ALTER TABLE rewrite_test
1686 ADD COLUMN empty3 text,
1687 ADD COLUMN notempty3_norewrite int default 42;
1689 SELECT check_ddl_rewrite('rewrite_test', $$
1690 ALTER TABLE rewrite_test
1691 ADD COLUMN notempty4_norewrite int default 42,
1692 ADD COLUMN empty4 text;
1694 -- then with rewrite
1695 SELECT check_ddl_rewrite('rewrite_test', $$
1696 ALTER TABLE rewrite_test
1697 ADD COLUMN empty5 text,
1698 ADD COLUMN notempty5_norewrite int default 42,
1699 ADD COLUMN notempty5_rewrite serial;
1701 SELECT check_ddl_rewrite('rewrite_test', $$
1702 ALTER TABLE rewrite_test
1703 ADD COLUMN notempty6_rewrite serial,
1704 ADD COLUMN empty6 text,
1705 ADD COLUMN notempty6_norewrite int default 42;
1709 DROP FUNCTION check_ddl_rewrite(regclass, text);
1710 DROP TABLE rewrite_test;
1715 drop type lockmodes;
1716 create type lockmodes as enum (
1721 ,'ShareUpdateExclusiveLock'
1723 ,'ShareRowExclusiveLock'
1725 ,'AccessExclusiveLock'
1729 create or replace view my_locks as
1730 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1731 from pg_locks l join pg_class c on l.relation = c.oid
1732 where virtualtransaction = (
1733 select virtualtransaction
1735 where transactionid = pg_current_xact_id()::xid)
1736 and locktype = 'relation'
1737 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1738 and c.relname != 'my_locks'
1741 create table alterlock (f1 int primary key, f2 text);
1742 insert into alterlock values (1, 'foo');
1743 create table alterlock2 (f3 int primary key, f1 int);
1744 insert into alterlock2 values (1, 1);
1746 begin; alter table alterlock alter column f2 set statistics 150;
1747 select * from my_locks order by 1;
1750 begin; alter table alterlock cluster on alterlock_pkey;
1751 select * from my_locks order by 1;
1754 begin; alter table alterlock set without cluster;
1755 select * from my_locks order by 1;
1758 begin; alter table alterlock set (fillfactor = 100);
1759 select * from my_locks order by 1;
1762 begin; alter table alterlock reset (fillfactor);
1763 select * from my_locks order by 1;
1766 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1767 select * from my_locks order by 1;
1770 begin; alter table alterlock set (autovacuum_enabled = off);
1771 select * from my_locks order by 1;
1774 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1775 select * from my_locks order by 1;
1778 -- test that mixing options with different lock levels works as expected
1779 begin; alter table alterlock set (autovacuum_enabled = off, fillfactor = 80);
1780 select * from my_locks order by 1;
1783 begin; alter table alterlock alter column f2 set storage extended;
1784 select * from my_locks order by 1;
1787 begin; alter table alterlock alter column f2 set default 'x';
1788 select * from my_locks order by 1;
1792 create trigger ttdummy
1793 before delete or update on alterlock
1797 select * from my_locks order by 1;
1801 select * from my_locks order by 1;
1802 alter table alterlock2 add foreign key (f1) references alterlock (f1);
1803 select * from my_locks order by 1;
1807 alter table alterlock2
1808 add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
1809 select * from my_locks order by 1;
1812 alter table alterlock2 validate constraint alterlock2nv;
1813 select * from my_locks order by 1;
1816 create or replace view my_locks as
1817 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1818 from pg_locks l join pg_class c on l.relation = c.oid
1819 where virtualtransaction = (
1820 select virtualtransaction
1822 where transactionid = pg_current_xact_id()::xid)
1823 and locktype = 'relation'
1824 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1825 and c.relname = 'my_locks'
1829 alter table my_locks set (autovacuum_enabled = false);
1830 alter view my_locks set (autovacuum_enabled = false);
1831 alter table my_locks reset (autovacuum_enabled);
1832 alter view my_locks reset (autovacuum_enabled);
1835 alter view my_locks set (security_barrier=off);
1836 select * from my_locks order by 1;
1837 alter view my_locks reset (security_barrier);
1840 -- this test intentionally applies the ALTER TABLE command against a view, but
1841 -- uses a view option so we expect this to succeed. This form of SQL is
1842 -- accepted for historical reasons, as shown in the docs for ALTER VIEW
1844 alter table my_locks set (security_barrier=off);
1845 select * from my_locks order by 1;
1846 alter table my_locks reset (security_barrier);
1850 drop table alterlock2;
1851 drop table alterlock;
1853 drop type lockmodes;
1858 create function test_strict(text) returns text as
1859 'select coalesce($1, ''got passed a null'');'
1860 language sql returns null on null input;
1861 select test_strict(NULL);
1862 alter function test_strict(text) called on null input;
1863 select test_strict(NULL);
1865 create function non_strict(text) returns text as
1866 'select coalesce($1, ''got passed a null'');'
1867 language sql called on null input;
1868 select non_strict(NULL);
1869 alter function non_strict(text) returns null on null input;
1870 select non_strict(NULL);
1873 -- alter object set schema
1876 create schema alter1;
1877 create schema alter2;
1879 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1881 create view alter1.v1 as select * from alter1.t1;
1883 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1885 create domain alter1.posint integer check (value > 0);
1887 create type alter1.ctype as (f1 int, f2 text);
1889 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1890 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1892 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1894 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1895 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1897 create conversion alter1.latin1_to_utf8 for 'latin1' to 'utf8' from iso8859_1_to_utf8;
1899 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1900 create text search configuration alter1.cfg(parser = alter1.prs);
1901 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1902 create text search dictionary alter1.dict(template = alter1.tmpl);
1904 insert into alter1.t1(f2) values(11);
1905 insert into alter1.t1(f2) values(12);
1907 alter table alter1.t1 set schema alter1; -- no-op, same schema
1908 alter table alter1.t1 set schema alter2;
1909 alter table alter1.v1 set schema alter2;
1910 alter function alter1.plus1(int) set schema alter2;
1911 alter domain alter1.posint set schema alter2;
1912 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1913 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1914 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1915 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1916 alter type alter1.ctype set schema alter1; -- no-op, same schema
1917 alter type alter1.ctype set schema alter2;
1918 alter conversion alter1.latin1_to_utf8 set schema alter2;
1919 alter text search parser alter1.prs set schema alter2;
1920 alter text search configuration alter1.cfg set schema alter2;
1921 alter text search template alter1.tmpl set schema alter2;
1922 alter text search dictionary alter1.dict set schema alter2;
1924 -- this should succeed because nothing is left in alter1
1927 insert into alter2.t1(f2) values(13);
1928 insert into alter2.t1(f2) values(14);
1930 select * from alter2.t1;
1932 select * from alter2.v1;
1934 select alter2.plus1(41);
1937 drop schema alter2 cascade;
1943 CREATE TYPE test_type AS (a int);
1946 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1948 ALTER TYPE test_type ADD ATTRIBUTE b text;
1951 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1953 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1956 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1959 ALTER TYPE test_type DROP ATTRIBUTE b;
1962 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1964 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1966 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1969 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1970 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1973 DROP TYPE test_type;
1975 CREATE TYPE test_type1 AS (a int, b text);
1976 CREATE TABLE test_tbl1 (x int, y test_type1);
1977 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1979 DROP TABLE test_tbl1;
1980 CREATE TABLE test_tbl1 (x int, y text);
1981 CREATE INDEX test_tbl1_idx ON test_tbl1((row(x,y)::test_type1));
1982 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1984 DROP TABLE test_tbl1;
1985 DROP TYPE test_type1;
1987 CREATE TYPE test_type2 AS (a int, b text);
1988 CREATE TABLE test_tbl2 OF test_type2;
1989 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1993 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1994 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1998 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1999 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
2003 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
2004 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
2008 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
2009 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
2012 \d test_tbl2_subclass
2014 DROP TABLE test_tbl2_subclass, test_tbl2;
2015 DROP TYPE test_type2;
2017 CREATE TYPE test_typex AS (a int, b text);
2018 CREATE TABLE test_tblx (x int, y test_typex check ((y).a > 0));
2019 ALTER TYPE test_typex DROP ATTRIBUTE a; -- fails
2020 ALTER TYPE test_typex DROP ATTRIBUTE a CASCADE;
2022 DROP TABLE test_tblx;
2023 DROP TYPE test_typex;
2025 -- This test isn't that interesting on its own, but the purpose is to leave
2026 -- behind a table to test pg_upgrade with. The table has a composite type
2027 -- column in it, and the composite type has a dropped attribute.
2028 CREATE TYPE test_type3 AS (a int);
2029 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
2030 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
2032 CREATE TYPE test_type_empty AS ();
2033 DROP TYPE test_type_empty;
2036 -- typed tables: OF / NOT OF
2039 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
2040 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
2041 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
2042 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
2043 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
2044 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
2045 CREATE TABLE tt4 (x int); -- too few columns
2046 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
2047 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
2048 CREATE TABLE tt7 (x int, q text, y numeric(8,2));
2049 ALTER TABLE tt7 DROP q; -- OK
2051 ALTER TABLE tt0 OF tt_t0;
2052 ALTER TABLE tt1 OF tt_t0;
2053 ALTER TABLE tt2 OF tt_t0;
2054 ALTER TABLE tt3 OF tt_t0;
2055 ALTER TABLE tt4 OF tt_t0;
2056 ALTER TABLE tt5 OF tt_t0;
2057 ALTER TABLE tt6 OF tt_t0;
2058 ALTER TABLE tt7 OF tt_t0;
2060 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
2061 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
2062 ALTER TABLE tt7 NOT OF;
2065 -- make sure we can drop a constraint on the parent but it remains on the child
2066 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
2067 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
2068 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
2070 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
2071 DROP TABLE test_drop_constr_parent CASCADE;
2076 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
2077 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
2078 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
2079 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
2080 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
2081 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
2083 CREATE TABLE tt8(a int);
2084 CREATE SCHEMA alter2;
2086 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
2087 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
2088 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
2089 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
2090 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
2091 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
2095 DROP TABLE alter2.tt8;
2099 -- Check conflicts between index and CHECK constraint names
2101 CREATE TABLE tt9(c integer);
2102 ALTER TABLE tt9 ADD CHECK(c > 1);
2103 ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
2104 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
2105 ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
2106 ALTER TABLE tt9 ADD UNIQUE(c);
2107 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
2108 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
2109 ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
2110 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
2111 ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
2112 ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
2117 -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
2118 CREATE TABLE comment_test (
2120 constraint id_notnull_constraint not null id,
2121 positive_col int CHECK (positive_col > 0),
2123 CONSTRAINT comment_test_pk PRIMARY KEY (id));
2124 CREATE INDEX comment_test_index ON comment_test(indexed_col);
2126 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
2127 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
2128 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
2129 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
2130 COMMENT ON CONSTRAINT id_notnull_constraint ON comment_test IS 'NOT NULL constraint of comment_test';
2131 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
2133 SELECT col_description('comment_test'::regclass, 1) as comment;
2134 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;
2135 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
2137 -- Change the datatype of all the columns. ALTER TABLE is optimized to not
2138 -- rebuild an index if the new data type is binary compatible with the old
2139 -- one. Check do a dummy ALTER TABLE that doesn't change the datatype
2140 -- first, to test that no-op codepath, and another one that does.
2141 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE int;
2142 ALTER TABLE comment_test ALTER COLUMN indexed_col SET DATA TYPE text;
2143 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int;
2144 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
2145 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE int;
2146 ALTER TABLE comment_test ALTER COLUMN positive_col SET DATA TYPE bigint;
2148 -- Some error cases.
2149 ALTER TABLE comment_test ALTER COLUMN xmin SET DATA TYPE x;
2150 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE x;
2151 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int COLLATE "C";
2153 -- Check that the comments are intact.
2154 SELECT col_description('comment_test'::regclass, 1) as comment;
2155 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;
2156 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
2158 -- Check compatibility for foreign keys and comments. This is done
2159 -- separately as rebuilding the column type of the parent leads
2160 -- to an error and would reduce the test scope.
2161 CREATE TABLE comment_test_child (
2162 id text CONSTRAINT comment_test_child_fk REFERENCES comment_test);
2163 CREATE INDEX comment_test_child_fk ON comment_test_child(id);
2164 COMMENT ON COLUMN comment_test_child.id IS 'Column ''id'' on comment_test_child';
2165 COMMENT ON INDEX comment_test_child_fk IS 'Index backing the FOREIGN KEY of comment_test_child';
2166 COMMENT ON CONSTRAINT comment_test_child_fk ON comment_test_child IS 'FOREIGN KEY constraint of comment_test_child';
2168 -- Change column type of parent
2169 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE text;
2170 ALTER TABLE comment_test ALTER COLUMN id SET DATA TYPE int USING id::integer;
2172 -- Comments should be intact
2173 SELECT col_description('comment_test_child'::regclass, 1) as comment;
2174 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;
2175 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test_child'::regclass ORDER BY 1, 2;
2177 -- Check that we map relation oids to filenodes and back correctly. Only
2178 -- display bad mappings so the test output doesn't change all the time. A
2179 -- filenode function call can return NULL for a relation dropped concurrently
2180 -- with the call's surrounding query, so ignore a NULL mapped_oid for
2181 -- relations that no longer exist after all calls finish.
2182 CREATE TEMP TABLE filenode_mapping AS
2184 oid, mapped_oid, reltablespace, relfilenode, relname
2186 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) AS mapped_oid
2187 WHERE relkind IN ('r', 'i', 'S', 't', 'm') AND mapped_oid IS DISTINCT FROM oid;
2189 SELECT m.* FROM filenode_mapping m LEFT JOIN pg_class c ON c.oid = m.oid
2190 WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
2192 -- Checks on creating and manipulation of user defined relations in
2195 SHOW allow_system_table_mods;
2196 -- disallowed because of search_path issues with pg_dump
2197 CREATE TABLE pg_catalog.new_system_table();
2198 -- instead create in public first, move to catalog
2199 CREATE TABLE new_system_table(id serial primary key, othercol text);
2200 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2201 ALTER TABLE new_system_table SET SCHEMA public;
2202 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2203 -- will be ignored -- already there:
2204 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2205 ALTER TABLE new_system_table RENAME TO old_system_table;
2206 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
2207 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
2208 UPDATE old_system_table SET id = -id;
2209 DELETE FROM old_system_table WHERE othercol = 'somedata';
2210 TRUNCATE old_system_table;
2211 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
2212 ALTER TABLE old_system_table DROP COLUMN othercol;
2213 DROP TABLE old_system_table;
2216 CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast
2217 -- check relpersistence of an unlogged table
2218 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
2220 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
2222 SELECT r.relname || ' 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'
2224 CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
2225 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
2226 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
2227 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
2228 ALTER TABLE unlogged1 SET LOGGED;
2229 -- check relpersistence of an unlogged table after changing to permanent
2230 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
2232 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
2234 SELECT r.relname || ' 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'
2236 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
2237 DROP TABLE unlogged3;
2238 DROP TABLE unlogged2;
2239 DROP TABLE unlogged1;
2242 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT); -- has sequence, toast
2243 -- check relpersistence of a permanent table
2244 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
2246 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
2248 SELECT r.relname ||' 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'
2250 CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); -- foreign key
2251 CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); -- self-referencing foreign key
2252 ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a permanent table exists
2253 ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
2254 ALTER TABLE logged2 SET UNLOGGED;
2255 ALTER TABLE logged1 SET UNLOGGED;
2256 -- check relpersistence of a permanent table after changing to unlogged
2257 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^logged1'
2259 SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
2261 SELECT r.relname || ' 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'
2263 ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
2268 -- test ADD COLUMN IF NOT EXISTS
2269 CREATE TABLE test_add_column(c1 integer);
2271 ALTER TABLE test_add_column
2272 ADD COLUMN c2 integer;
2274 ALTER TABLE test_add_column
2275 ADD COLUMN c2 integer; -- fail because c2 already exists
2276 ALTER TABLE ONLY test_add_column
2277 ADD COLUMN c2 integer; -- fail because c2 already exists
2279 ALTER TABLE test_add_column
2280 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
2281 ALTER TABLE ONLY test_add_column
2282 ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
2284 ALTER TABLE test_add_column
2285 ADD COLUMN c2 integer, -- fail because c2 already exists
2286 ADD COLUMN c3 integer primary key;
2288 ALTER TABLE test_add_column
2289 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2290 ADD COLUMN c3 integer primary key;
2292 ALTER TABLE test_add_column
2293 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2294 ADD COLUMN IF NOT EXISTS c3 integer primary key; -- skipping because c3 already exists
2296 ALTER TABLE test_add_column
2297 ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2298 ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
2299 ADD COLUMN c4 integer REFERENCES test_add_column;
2301 ALTER TABLE test_add_column
2302 ADD COLUMN IF NOT EXISTS c4 integer REFERENCES test_add_column;
2304 ALTER TABLE test_add_column
2305 ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 8);
2307 ALTER TABLE test_add_column
2308 ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10);
2310 DROP TABLE test_add_column;
2313 -- assorted cases with multiple ALTER TABLE steps
2314 CREATE TABLE ataddindex(f1 INT);
2315 INSERT INTO ataddindex VALUES (42), (43);
2316 CREATE UNIQUE INDEX ataddindexi0 ON ataddindex(f1);
2317 ALTER TABLE ataddindex
2318 ADD PRIMARY KEY USING INDEX ataddindexi0,
2319 ALTER f1 TYPE BIGINT;
2321 DROP TABLE ataddindex;
2323 CREATE TABLE ataddindex(f1 VARCHAR(10));
2324 INSERT INTO ataddindex(f1) VALUES ('foo'), ('a');
2325 ALTER TABLE ataddindex
2326 ALTER f1 SET DATA TYPE TEXT,
2327 ADD EXCLUDE ((f1 LIKE 'a') WITH =);
2329 DROP TABLE ataddindex;
2331 CREATE TABLE ataddindex(id int, ref_id int);
2332 ALTER TABLE ataddindex
2333 ADD PRIMARY KEY (id),
2334 ADD FOREIGN KEY (ref_id) REFERENCES ataddindex;
2336 DROP TABLE ataddindex;
2338 CREATE TABLE ataddindex(id int, ref_id int);
2339 ALTER TABLE ataddindex
2341 ADD FOREIGN KEY (ref_id) REFERENCES ataddindex (id);
2343 DROP TABLE ataddindex;
2345 CREATE TABLE atnotnull1 ();
2346 ALTER TABLE atnotnull1
2348 ALTER a SET NOT NULL;
2349 ALTER TABLE atnotnull1
2352 ALTER TABLE atnotnull1
2354 ADD PRIMARY KEY (c);
2357 -- cannot drop column that is part of the partition key
2358 CREATE TABLE partitioned (
2361 ) PARTITION BY RANGE (a, (a+b+1));
2362 ALTER TABLE partitioned DROP COLUMN a;
2363 ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
2364 ALTER TABLE partitioned DROP COLUMN b;
2365 ALTER TABLE partitioned ALTER COLUMN b TYPE char(5);
2367 -- specifying storage parameters for partitioned tables is not supported
2368 ALTER TABLE partitioned SET (fillfactor=100);
2370 -- partitioned table cannot participate in regular inheritance
2371 CREATE TABLE nonpartitioned (
2375 ALTER TABLE partitioned INHERIT nonpartitioned;
2376 ALTER TABLE nonpartitioned INHERIT partitioned;
2378 -- cannot add NO INHERIT constraint to partitioned tables
2379 ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT;
2381 DROP TABLE partitioned, nonpartitioned;
2387 -- check that target table is partitioned
2388 CREATE TABLE unparted (
2391 CREATE TABLE fail_part (like unparted);
2392 ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a');
2393 DROP TABLE unparted, fail_part;
2395 -- check that partition bound is compatible
2396 CREATE TABLE list_parted (
2398 b char(2) COLLATE "C",
2399 CONSTRAINT check_a CHECK (a > 0)
2400 ) PARTITION BY LIST (a);
2401 CREATE TABLE fail_part (LIKE list_parted);
2402 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10);
2403 DROP TABLE fail_part;
2405 -- check that the table being attached exists
2406 ALTER TABLE list_parted ATTACH PARTITION nonexistent FOR VALUES IN (1);
2408 -- check ownership of the source table
2409 CREATE ROLE regress_test_me;
2410 CREATE ROLE regress_test_not_me;
2411 CREATE TABLE not_owned_by_me (LIKE list_parted);
2412 ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me;
2413 SET SESSION AUTHORIZATION regress_test_me;
2414 CREATE TABLE owned_by_me (
2416 ) PARTITION BY LIST (a);
2417 ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1);
2418 RESET SESSION AUTHORIZATION;
2419 DROP TABLE owned_by_me, not_owned_by_me;
2420 DROP ROLE regress_test_not_me;
2421 DROP ROLE regress_test_me;
2423 -- check that the table being attached is not part of regular inheritance
2424 CREATE TABLE parent (LIKE list_parted);
2425 CREATE TABLE child () INHERITS (parent);
2426 ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1);
2427 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
2429 -- now it should work, with a little tweak
2430 ALTER TABLE parent ADD CONSTRAINT check_a CHECK (a > 0);
2431 ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1);
2432 -- test insert/update, per bug #18550
2433 INSERT INTO parent VALUES (1);
2434 UPDATE parent SET a = 2 WHERE a = 1;
2435 DROP TABLE parent CASCADE;
2437 -- check any TEMP-ness
2438 CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
2439 CREATE TABLE perm_part (a int);
2440 ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1);
2441 DROP TABLE temp_parted, perm_part;
2443 -- check that the table being attached is not a typed table
2444 CREATE TYPE mytype AS (a int);
2445 CREATE TABLE fail_part OF mytype;
2446 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2447 DROP TYPE mytype CASCADE;
2449 -- check that the table being attached has only columns present in the parent
2450 CREATE TABLE fail_part (like list_parted, c int);
2451 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2452 DROP TABLE fail_part;
2454 -- check that the table being attached has every column of the parent
2455 CREATE TABLE fail_part (a int NOT NULL);
2456 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2457 DROP TABLE fail_part;
2459 -- check that columns match in type, collation and NOT NULL status
2460 CREATE TABLE fail_part (
2464 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2465 ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "POSIX";
2466 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2467 DROP TABLE fail_part;
2469 -- check that the table being attached has all constraints of the parent
2470 CREATE TABLE fail_part (
2471 b char(2) COLLATE "C",
2474 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2476 -- check that the constraint matches in definition with parent's constraint
2477 ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0);
2478 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2479 DROP TABLE fail_part;
2481 -- check the attributes and constraints after partition is attached
2482 CREATE TABLE part_1 (
2484 b char(2) COLLATE "C",
2485 CONSTRAINT check_a CHECK (a > 0)
2487 ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1);
2488 -- attislocal and conislocal are always false for merged attributes and constraints respectively.
2489 SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0;
2490 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a';
2492 -- check that NOT NULL NO INHERIT cannot be merged to a normal NOT NULL
2493 CREATE TABLE part_fail (a int NOT NULL NO INHERIT,
2494 b char(2) COLLATE "C",
2495 CONSTRAINT check_a CHECK (a > 0)
2497 ALTER TABLE list_parted ATTACH PARTITION part_fail FOR VALUES IN (2);
2498 DROP TABLE part_fail;
2500 -- check that the new partition won't overlap with an existing partition
2501 CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
2502 ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
2503 DROP TABLE fail_part;
2504 -- check that an existing table can be attached as a default partition
2505 CREATE TABLE def_part (LIKE list_parted INCLUDING CONSTRAINTS);
2506 ALTER TABLE list_parted ATTACH PARTITION def_part DEFAULT;
2507 -- check attaching default partition fails if a default partition already
2509 CREATE TABLE fail_def_part (LIKE part_1 INCLUDING CONSTRAINTS);
2510 ALTER TABLE list_parted ATTACH PARTITION fail_def_part DEFAULT;
2512 -- check validation when attaching list partitions
2513 CREATE TABLE list_parted2 (
2516 ) PARTITION BY LIST (a);
2518 -- check that violating rows are correctly reported
2519 CREATE TABLE part_2 (LIKE list_parted2);
2520 INSERT INTO part_2 VALUES (3, 'a');
2521 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2523 -- should be ok after deleting the bad row
2525 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2527 -- check partition cannot be attached if default has some row for its values
2528 CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
2529 INSERT INTO list_parted2_def VALUES (11, 'z');
2530 CREATE TABLE part_3 (LIKE list_parted2);
2531 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
2532 -- should be ok after deleting the bad row
2533 DELETE FROM list_parted2_def WHERE a = 11;
2534 ALTER TABLE list_parted2 ATTACH PARTITION part_3 FOR VALUES IN (11);
2536 -- adding constraints that describe the desired partition constraint
2537 -- (or more restrictive) will help skip the validation scan
2538 CREATE TABLE part_3_4 (
2540 CONSTRAINT check_a CHECK (a IN (3))
2543 -- however, if a list partition does not accept nulls, there should be
2544 -- an explicit NOT NULL constraint on the partition key column for the
2545 -- validation scan to be skipped;
2546 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
2548 -- adding a NOT NULL constraint will cause the scan to be skipped
2549 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
2550 ALTER TABLE part_3_4 ALTER a SET NOT NULL;
2551 ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);
2553 -- check if default partition scan skipped
2554 ALTER TABLE list_parted2_def ADD CONSTRAINT check_a CHECK (a IN (5, 6));
2555 CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);
2557 -- check validation when attaching range partitions
2558 CREATE TABLE range_parted (
2561 ) PARTITION BY RANGE (a, b);
2563 -- check that violating rows are correctly reported
2564 CREATE TABLE part1 (
2565 a int NOT NULL CHECK (a = 1),
2566 b int NOT NULL CHECK (b >= 1 AND b <= 10)
2568 INSERT INTO part1 VALUES (1, 10);
2569 -- Remember the TO bound is exclusive
2570 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
2572 -- should be ok after deleting the bad row
2574 ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10);
2576 -- adding constraints that describe the desired partition constraint
2577 -- (or more restrictive) will help skip the validation scan
2578 CREATE TABLE part2 (
2579 a int NOT NULL CHECK (a = 1),
2580 b int NOT NULL CHECK (b >= 10 AND b < 18)
2582 ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20);
2584 -- Create default partition
2585 CREATE TABLE partr_def1 PARTITION OF range_parted DEFAULT;
2587 -- Only one default partition is allowed, hence, following should give error
2588 CREATE TABLE partr_def2 (LIKE part1 INCLUDING CONSTRAINTS);
2589 ALTER TABLE range_parted ATTACH PARTITION partr_def2 DEFAULT;
2591 -- Overlapping partitions cannot be attached, hence, following should give error
2592 INSERT INTO partr_def1 VALUES (2, 10);
2593 CREATE TABLE part3 (LIKE range_parted);
2594 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (2, 10) TO (2, 20);
2596 -- Attaching partitions should be successful when there are no overlapping rows
2597 ALTER TABLE range_parted ATTACH partition part3 FOR VALUES FROM (3, 10) TO (3, 20);
2599 -- check that leaf partitions are scanned when attaching a partitioned
2601 CREATE TABLE part_5 (
2603 ) PARTITION BY LIST (b);
2605 -- check that violating rows are correctly reported
2606 CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a');
2607 INSERT INTO part_5_a (a, b) VALUES (6, 'a');
2608 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2610 -- delete the faulting row and also add a constraint to skip the scan
2611 DELETE FROM part_5_a WHERE a NOT IN (3);
2612 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);
2613 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2614 ALTER TABLE list_parted2 DETACH PARTITION part_5;
2615 ALTER TABLE part_5 DROP CONSTRAINT check_a;
2617 -- scan should again be skipped, even though NOT NULL is now a column property
2618 ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL;
2619 ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);
2621 -- Check the case where attnos of the partitioning columns in the table being
2622 -- attached differs from the parent. It should not affect the constraint-
2623 -- checking logic that allows to skip the scan.
2624 CREATE TABLE part_6 (
2627 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6)
2629 ALTER TABLE part_6 DROP c;
2630 ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);
2632 -- Similar to above, but the table being attached is a partitioned table
2633 -- whose partition has still different attnos for the root partitioning
2635 CREATE TABLE part_7 (
2637 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
2638 ) PARTITION BY LIST (b);
2639 CREATE TABLE part_7_a_null (
2643 LIKE list_parted2, -- 'a' will have attnum = 4
2644 CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
2645 CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
2647 ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;
2648 ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN ('a', null);
2649 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
2651 -- Same example, but check this time that the constraint correctly detects
2653 ALTER TABLE list_parted2 DETACH PARTITION part_7;
2654 ALTER TABLE part_7 DROP CONSTRAINT check_a; -- thusly, scan won't be skipped
2655 INSERT INTO part_7 (a, b) VALUES (8, null), (9, 'a');
2656 SELECT tableoid::regclass, a, b FROM part_7 order by a;
2657 ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);
2659 -- check that leaf partitions of default partition are scanned when
2660 -- attaching a partitioned table.
2661 ALTER TABLE part_5 DROP CONSTRAINT check_a;
2662 CREATE TABLE part5_def PARTITION OF part_5 DEFAULT PARTITION BY LIST(a);
2663 CREATE TABLE part5_def_p1 PARTITION OF part5_def FOR VALUES IN (5);
2664 INSERT INTO part5_def_p1 VALUES (5, 'y');
2665 CREATE TABLE part5_p1 (LIKE part_5);
2666 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
2667 -- should be ok after deleting the bad row
2668 DELETE FROM part5_def_p1 WHERE b = 'y';
2669 ALTER TABLE part_5 ATTACH PARTITION part5_p1 FOR VALUES IN ('y');
2671 -- check that the table being attached is not already a partition
2672 ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
2674 -- check that circular inheritance is not allowed
2675 ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b');
2676 ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
2678 -- If a partitioned table being created or an existing table being attached
2679 -- as a partition does not have a constraint that would allow validation scan
2680 -- to be skipped, but an individual partition does, then the partition's
2681 -- validation scan is skipped.
2682 CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a);
2683 CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b);
2684 CREATE TABLE quuux_default1 PARTITION OF quuux_default (
2685 CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1)
2686 ) FOR VALUES IN ('b');
2687 CREATE TABLE quuux1 (a int, b text);
2688 ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate!
2689 CREATE TABLE quuux2 (a int, b text);
2690 ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation
2691 DROP TABLE quuux1, quuux2;
2692 -- should validate for quuux1, but not for quuux2
2693 CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);
2694 CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);
2697 -- check validation when attaching hash partitions
2699 -- Use hand-rolled hash functions and operator class to get predictable result
2700 -- on different machines. part_test_int4_ops is defined in test_setup.sql.
2702 -- check that the new partition won't overlap with an existing partition
2703 CREATE TABLE hash_parted (
2706 ) PARTITION BY HASH (a part_test_int4_ops);
2707 CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 4, REMAINDER 0);
2708 CREATE TABLE fail_part (LIKE hpart_1);
2709 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 4);
2710 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 0);
2711 DROP TABLE fail_part;
2713 -- check validation when attaching hash partitions
2715 -- check that violating rows are correctly reported
2716 CREATE TABLE hpart_2 (LIKE hash_parted);
2717 INSERT INTO hpart_2 VALUES (3, 0);
2718 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
2720 -- should be ok after deleting the bad row
2721 DELETE FROM hpart_2;
2722 ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
2724 -- check that leaf partitions are scanned when attaching a partitioned
2726 CREATE TABLE hpart_5 (
2728 ) PARTITION BY LIST (b);
2730 -- check that violating rows are correctly reported
2731 CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
2732 INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
2733 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
2735 -- should be ok after deleting the bad row
2736 DELETE FROM hpart_5_a;
2737 ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
2739 -- check that the table being attach is with valid modulus and remainder value
2740 CREATE TABLE fail_part(LIKE hash_parted);
2741 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 0, REMAINDER 1);
2742 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 8, REMAINDER 8);
2743 ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (MODULUS 3, REMAINDER 2);
2744 DROP TABLE fail_part;
2750 -- check that the table is partitioned at all
2751 CREATE TABLE regular_table (a int);
2752 ALTER TABLE regular_table DETACH PARTITION any_name;
2753 ALTER TABLE regular_table DETACH PARTITION any_name CONCURRENTLY;
2754 ALTER TABLE regular_table DETACH PARTITION any_name FINALIZE;
2755 DROP TABLE regular_table;
2757 -- check that the partition being detached exists at all
2758 ALTER TABLE list_parted2 DETACH PARTITION part_4;
2759 ALTER TABLE hash_parted DETACH PARTITION hpart_4;
2761 -- check that the partition being detached is actually a partition of the parent
2762 CREATE TABLE not_a_part (a int);
2763 ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
2764 ALTER TABLE list_parted2 DETACH PARTITION part_1;
2766 ALTER TABLE hash_parted DETACH PARTITION not_a_part;
2767 DROP TABLE not_a_part;
2769 -- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
2770 -- attislocal/conislocal is set to true
2771 ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
2772 SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0;
2773 SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a';
2774 DROP TABLE part_3_4;
2776 -- check that a detached partition is not dropped on dropping a partitioned table
2777 CREATE TABLE range_parted2 (
2779 ) PARTITION BY RANGE(a);
2780 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
2781 ALTER TABLE range_parted2 DETACH PARTITION part_rp;
2782 DROP TABLE range_parted2;
2783 SELECT * from part_rp;
2786 -- concurrent detach
2787 CREATE TABLE range_parted2 (
2789 ) PARTITION BY RANGE(a);
2790 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
2792 -- doesn't work in a partition block
2793 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
2795 CREATE TABLE part_rpd PARTITION OF range_parted2 DEFAULT;
2796 -- doesn't work if there's a default partition
2797 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
2798 -- doesn't work for the default partition
2799 ALTER TABLE range_parted2 DETACH PARTITION part_rpd CONCURRENTLY;
2800 DROP TABLE part_rpd;
2802 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
2804 -- constraint should be created
2806 CREATE TABLE part_rp100 PARTITION OF range_parted2 (CHECK (a>=123 AND a<133 AND a IS NOT NULL)) FOR VALUES FROM (100) to (200);
2807 ALTER TABLE range_parted2 DETACH PARTITION part_rp100 CONCURRENTLY;
2808 -- redundant constraint should not be created
2810 DROP TABLE range_parted2;
2812 -- Check ALTER TABLE commands for partitioned tables and partitions
2814 -- cannot add/drop column to/from *only* the parent
2815 ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
2816 ALTER TABLE ONLY list_parted2 DROP COLUMN b;
2818 -- cannot add a column to partition or drop an inherited one
2819 ALTER TABLE part_2 ADD COLUMN c text;
2820 ALTER TABLE part_2 DROP COLUMN b;
2822 -- Nor rename, alter type
2823 ALTER TABLE part_2 RENAME COLUMN b to c;
2824 ALTER TABLE part_2 ALTER COLUMN b TYPE text;
2826 -- cannot add NOT NULL or check constraints to *only* the parent, when
2828 ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
2829 ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2831 -- dropping them is ok though
2832 ALTER TABLE list_parted2 ALTER b SET NOT NULL;
2833 ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
2834 ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2835 ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
2836 -- ... and the partitions should still have both
2839 -- It's alright though, if no partitions are yet created
2840 CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
2841 ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
2842 ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
2843 DROP TABLE parted_no_parts;
2845 -- cannot drop inherited NOT NULL or check constraints from partition
2846 ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
2847 ALTER TABLE part_2 ALTER b DROP NOT NULL;
2848 ALTER TABLE part_2 DROP CONSTRAINT check_a2;
2850 -- can't drop NOT NULL from under an invalid PK
2851 CREATE TABLE list_parted3 (a int NOT NULL) PARTITION BY LIST (a);
2852 CREATE TABLE list_parted3_1 PARTITION OF list_parted3 FOR VALUES IN (1);
2853 ALTER TABLE ONLY list_parted3 ADD PRIMARY KEY (a);
2854 ALTER TABLE ONLY list_parted3 DROP CONSTRAINT list_parted3_a_not_null;
2856 -- Doesn't make sense to add NO INHERIT constraints on partitioned tables
2857 ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
2859 -- check that a partition cannot participate in regular inheritance
2860 CREATE TABLE inh_test () INHERITS (part_2);
2861 CREATE TABLE inh_test (LIKE part_2);
2862 ALTER TABLE inh_test INHERIT part_2;
2863 ALTER TABLE part_2 INHERIT inh_test;
2865 -- cannot drop or alter type of partition key columns of lower level
2866 -- partitioned tables; for example, part_5, which is list_parted2's
2867 -- partition, is partitioned on b;
2868 ALTER TABLE list_parted2 DROP COLUMN b;
2869 ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
2871 -- dropping non-partition key columns should be allowed on the parent table.
2872 ALTER TABLE list_parted DROP COLUMN b;
2873 SELECT * FROM list_parted;
2876 DROP TABLE list_parted, list_parted2, range_parted, list_parted3;
2877 DROP TABLE fail_def_part;
2878 DROP TABLE hash_parted;
2880 -- more tests for certain multi-level partitioning scenarios
2881 create table p (a int, b int) partition by range (a, b);
2882 create table p1 (b int, a int not null) partition by range (b);
2883 create table p11 (like p1);
2884 alter table p11 drop a;
2885 alter table p11 add a int;
2886 alter table p11 drop a;
2887 alter table p11 add a int not null;
2888 -- attnum for key attribute 'a' is different in p, p1, and p11
2889 select attrelid::regclass, attname, attnum
2892 and (attrelid = 'p'::regclass
2893 or attrelid = 'p1'::regclass
2894 or attrelid = 'p11'::regclass)
2895 order by attrelid::regclass::text;
2897 alter table p1 attach partition p11 for values from (2) to (5);
2899 insert into p1 (a, b) values (2, 3);
2900 -- check that partition validation scan correctly detects violating rows
2901 alter table p attach partition p1 for values from (1, 2) to (1, 10);
2907 -- validate constraint on partitioned tables should only scan leaf partitions
2908 create table parted_validate_test (a int) partition by list (a);
2909 create table parted_validate_test_1 partition of parted_validate_test for values in (0, 1);
2910 alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
2911 alter table parted_validate_test validate constraint parted_validate_test_chka;
2912 drop table parted_validate_test;
2913 -- test alter column options
2914 CREATE TABLE attmp(i integer);
2915 INSERT INTO attmp VALUES (1);
2916 ALTER TABLE attmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
2917 ALTER TABLE attmp ALTER COLUMN i RESET (n_distinct_inherited);
2921 DROP USER regress_alter_table_user1;
2923 -- check that violating rows are correctly reported when attaching as the
2924 -- default partition
2925 create table defpart_attach_test (a int) partition by list (a);
2926 create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
2927 create table defpart_attach_test_d (b int, a int);
2928 alter table defpart_attach_test_d drop b;
2929 insert into defpart_attach_test_d values (1), (2);
2931 -- error because its constraint as the default partition would be violated
2932 -- by the row containing 1
2933 alter table defpart_attach_test attach partition defpart_attach_test_d default;
2934 delete from defpart_attach_test_d where a = 1;
2935 alter table defpart_attach_test_d add check (a > 1);
2937 -- should be attached successfully and without needing to be scanned
2938 alter table defpart_attach_test attach partition defpart_attach_test_d default;
2940 -- check that attaching a partition correctly reports any rows in the default
2941 -- partition that should not be there for the new partition to be attached
2943 create table defpart_attach_test_2 (like defpart_attach_test_d);
2944 alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
2946 drop table defpart_attach_test;
2948 -- check combinations of temporary and permanent relations when attaching
2950 create table perm_part_parent (a int) partition by list (a);
2951 create temp table temp_part_parent (a int) partition by list (a);
2952 create table perm_part_child (a int);
2953 create temp table temp_part_child (a int);
2954 alter table temp_part_parent attach partition perm_part_child default; -- error
2955 alter table perm_part_parent attach partition temp_part_child default; -- error
2956 alter table temp_part_parent attach partition temp_part_child default; -- ok
2957 drop table perm_part_parent cascade;
2958 drop table temp_part_parent cascade;
2960 -- check that attaching partitions to a table while it is being used is
2962 create table tab_part_attach (a int) partition by list (a);
2963 create or replace function func_part_attach() returns trigger
2964 language plpgsql as $$
2966 execute 'create table tab_part_attach_1 (a int)';
2967 execute 'alter table tab_part_attach attach partition tab_part_attach_1 for values in (1)';
2970 create trigger trig_part_attach before insert on tab_part_attach
2971 for each statement execute procedure func_part_attach();
2972 insert into tab_part_attach values (1);
2973 drop table tab_part_attach;
2974 drop function func_part_attach();
2976 -- test case where the partitioning operator is a SQL function whose
2977 -- evaluation results in the table's relcache being rebuilt partway through
2978 -- the execution of an ATTACH PARTITION command
2979 create function at_test_sql_partop (int4, int4) returns int language sql
2980 as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$;
2981 create operator class at_test_sql_partop for type int4 using btree as
2982 operator 1 < (int4, int4), operator 2 <= (int4, int4),
2983 operator 3 = (int4, int4), operator 4 >= (int4, int4),
2984 operator 5 > (int4, int4), function 1 at_test_sql_partop(int4, int4);
2985 create table at_test_sql_partop (a int) partition by range (a at_test_sql_partop);
2986 create table at_test_sql_partop_1 (a int);
2987 alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values from (0) to (10);
2988 drop table at_test_sql_partop;
2989 drop operator class at_test_sql_partop using btree;
2990 drop function at_test_sql_partop;
2993 /* Test case for bug #16242 */
2995 -- We create a parent and child where the child has missing
2996 -- non-null attribute values, and arrange to pass them through
2997 -- tuple conversion from the child to the parent tupdesc
2998 create table bar1 (a integer, b integer not null default 1)
2999 partition by range (a);
3000 create table bar2 (a integer);
3001 insert into bar2 values (1);
3002 alter table bar2 add column b integer not null default 1;
3003 -- (at this point bar2 contains tuple with natts=1)
3004 alter table bar1 attach partition bar2 default;
3009 -- this exercises tuple conversion:
3010 create function xtrig()
3011 returns trigger language plpgsql
3016 for r in select * from old loop
3017 raise info 'a=%, b=%', r.a, r.b;
3022 create trigger xtrig
3023 after update on bar1
3024 referencing old table as old
3025 for each statement execute procedure xtrig();
3027 update bar1 set a = a + 1;
3029 /* End test case for bug #16242 */
3031 /* Test case for bug #17409 */
3033 create table attbl (p1 int constraint pk_attbl primary key);
3034 create table atref (c1 int references attbl(p1));
3035 cluster attbl using pk_attbl;
3036 alter table attbl alter column p1 set data type bigint;
3037 alter table atref alter column c1 set data type bigint;
3038 drop table attbl, atref;
3040 create table attbl (p1 int constraint pk_attbl primary key);
3041 alter table attbl replica identity using index pk_attbl;
3042 create table atref (c1 int references attbl(p1));
3043 alter table attbl alter column p1 set data type bigint;
3044 alter table atref alter column c1 set data type bigint;
3045 drop table attbl, atref;
3047 /* End test case for bug #17409 */
3049 -- Test that ALTER TABLE rewrite preserves a clustered index
3050 -- for normal indexes and indexes on constraints.
3051 create table alttype_cluster (a int);
3052 alter table alttype_cluster add primary key (a);
3053 create index alttype_cluster_ind on alttype_cluster (a);
3054 alter table alttype_cluster cluster on alttype_cluster_ind;
3055 -- Normal index remains clustered.
3056 select indexrelid::regclass, indisclustered from pg_index
3057 where indrelid = 'alttype_cluster'::regclass
3058 order by indexrelid::regclass::text;
3059 alter table alttype_cluster alter a type bigint;
3060 select indexrelid::regclass, indisclustered from pg_index
3061 where indrelid = 'alttype_cluster'::regclass
3062 order by indexrelid::regclass::text;
3063 -- Constraint index remains clustered.
3064 alter table alttype_cluster cluster on alttype_cluster_pkey;
3065 select indexrelid::regclass, indisclustered from pg_index
3066 where indrelid = 'alttype_cluster'::regclass
3067 order by indexrelid::regclass::text;
3068 alter table alttype_cluster alter a type int;
3069 select indexrelid::regclass, indisclustered from pg_index
3070 where indrelid = 'alttype_cluster'::regclass
3071 order by indexrelid::regclass::text;
3072 drop table alttype_cluster;
3075 -- Check that attaching or detaching a partitioned partition correctly leads
3076 -- to its partitions' constraint being updated to reflect the parent's
3077 -- newly added/removed constraint
3078 create table target_parted (a int, b int) partition by list (a);
3079 create table attach_parted (a int, b int) partition by list (b);
3080 create table attach_parted_part1 partition of attach_parted for values in (1);
3081 -- insert a row directly into the leaf partition so that its partition
3082 -- constraint is built and stored in the relcache
3083 insert into attach_parted_part1 values (1, 1);
3084 -- the following better invalidate the partition constraint of the leaf
3086 alter table target_parted attach partition attach_parted for values in (1);
3087 -- ...such that the following insert fails
3088 insert into attach_parted_part1 values (2, 1);
3089 -- ...and doesn't when the partition is detached along with its own partition
3090 alter table target_parted detach partition attach_parted;
3091 insert into attach_parted_part1 values (2, 1);
3093 -- Test altering table having publication
3094 create schema alter1;
3095 create schema alter2;
3096 create table alter1.t1 (a int);
3097 set client_min_messages = 'ERROR';
3098 create publication pub1 for table alter1.t1, tables in schema alter2;
3099 reset client_min_messages;
3100 alter table alter1.t1 set schema alter2;
3102 drop publication pub1;
3103 drop schema alter1 cascade;
3104 drop schema alter2 cascade;