plpgsql: pure parser and reentrant scanner
[pgsql.git] / src / test / regress / sql / alter_table.sql
blobc88f9eaab04e6dd1540ed2db6001372255aa2923
1 --
2 -- ALTER_TABLE
3 --
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;
13 -- add attribute
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,
70         v, w, x, y, z)
71    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
72         'c',
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}');
78 SELECT * FROM attmp;
80 DROP TABLE attmp;
82 -- the wolf bug - schema mods caused inconsistent row descriptors
83 CREATE TABLE attmp (
84         initial         int4
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,
133         v, w, x, y, z)
134    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
135         'c',
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}');
141 SELECT * FROM attmp;
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;
151 \d+ attmp_idx
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;
159 DROP TABLE attmp;
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;
170 SELECT * FROM attmp;
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;
194 RESET ROLE;
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
228 RESET ROLE;
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);
235 BEGIN;
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";
242 COMMIT;
243 BEGIN;
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";
250 COMMIT;
251 BEGIN;
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";
258 COMMIT;
259 DROP TABLE alter_idx_rename_test_2;
261 -- renaming views
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
267 RESET ROLE;
269 -- hack to ensure we get an indexscan here
270 set enable_seqscan to off;
271 set enable_bitmapscan to off;
272 -- 5 values, sorted
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),
326   PRIMARY KEY (a));
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;
370 -- Try (and succeed)
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';
425 -- cleanup
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
429 -- attmp4 is a,b
431 ALTER TABLE attmp5 add constraint attmpconstr foreign key(a) references attmp4(a) match full;
433 DROP TABLE attmp7;
435 DROP TABLE attmp6;
437 DROP TABLE attmp5;
439 DROP TABLE attmp4;
441 DROP TABLE attmp3;
443 DROP TABLE attmp2;
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
450 \d nv_parent
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;
466 \d nv_child_2009
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);
482 DROP TABLE FKTABLE;
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
490 DROP TABLE FKTABLE;
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;
496 DROP TABLE FKTABLE;
497 DROP TABLE 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
507 DROP TABLE FKTABLE;
508 DROP TABLE PKTABLE;
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;
515 DROP TABLE FKTABLE;
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);
520 DROP TABLE FKTABLE;
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);
525 -- As does this...
526 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
527      references pktable(ptest1, ptest2);
528 DROP TABLE FKTABLE;
529 DROP TABLE PKTABLE;
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
556 ORDER BY 1,2,3;
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
560 ORDER BY 1,2,3;
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);
569 -- should fail
570 insert into atacc1 (test) values (2);
571 -- should succeed
572 insert into atacc1 (test) values (4);
573 drop table atacc1;
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);
582 drop table atacc1;
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);
588 drop table atacc1;
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);
594 -- should fail
595 insert into atacc1 (test,test2,test3) values (4,4,2);
596 -- should succeed
597 insert into atacc1 (test,test2,test3) values (4,4,5);
598 drop table atacc1;
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);
605 drop table atacc1;
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);
618 drop table atacc3;
619 drop table atacc2;
620 drop table atacc1;
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;
627 -- fail
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;
642 -- succeed
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;
654 drop table atacc1;
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);
660 -- ok:
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;
669 drop table atacc2;
670 drop table atacc1;
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);
679 -- should fail
680 insert into atacc1 (test) values (2);
681 -- should succeed
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;
685 drop table atacc1;
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);
695 drop table atacc1;
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);
702 drop table atacc1;
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);
710 -- should fail
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);
716 drop table atacc1;
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);
724 drop table atacc1;
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);
733 -- should fail
734 insert into atacc1 (test) values (2);
735 -- should succeed
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);
745 drop table atacc1;
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);
755 drop table atacc1;
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);
764 drop table atacc1;
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);
771 drop table atacc1;
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;
781 drop table atacc1;
783 -- this combination used to have order-of-execution problems (bug #15580)
784 create table atacc1 (a int);
785 insert into atacc1 values(1);
786 alter table atacc1
787   add column b float8 not null default random(),
788   add primary key(a);
789 drop table atacc1;
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;
796 drop table atacc1;
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;
804 drop table atacc1;
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;
811 drop table atacc1;
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);
821 -- should fail
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);
830 drop table atacc1;
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);
838 drop table atacc1;
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);
853 \d atacc1
854 alter table atacc1 alter column test drop not null;
855 \d atacc1
856 alter table atacc1 drop constraint "atacc1_pkey";
857 alter table atacc1 alter column test drop not null;
858 \d atacc1
859 insert into atacc1 values (null);
860 alter table atacc1 alter test set not null;
861 delete from atacc1;
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;
872 drop view myview;
874 drop table atacc1;
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;
891 delete from atacc1;
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;
909 drop table atacc1;
911 -- test inheritance
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;
923 drop table child;
924 drop table parent;
926 -- test setting and removing default values
927 create table def_test (
928         c1      int4 default 5,
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
950 -- a default
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;
964 drop table def_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;
979 -- SELECTs
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;
996 -- UPDATEs
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;
1002 -- INSERTs
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);
1016 -- DELETEs
1017 delete from atacc1 where a = 3;
1018 delete from atacc1 where "........pg.dropped.1........" = 3;
1019 delete from atacc1;
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;
1037 drop view myview;
1039 -- test some commands to make sure they fail on the dropped column
1040 analyze atacc1(a);
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........");
1071 drop table atacc2;
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;
1079 drop table attest1;
1080 select * into attest2 from atacc1;
1081 select * from attest2;
1082 drop table 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;
1090 drop table 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);
1099 drop table atacc1;
1101 -- test inheritance
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;
1114 drop table child;
1115 drop table parent;
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);
1125 drop table child;
1126 drop table parent;
1128 -- test copy in/out
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;
1136 10      11      12
1138 select * from attest;
1139 copy attest from stdin;
1140 21      22
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;
1146 31      32
1148 select * from attest;
1149 drop table attest;
1151 -- test inheritance
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;
1185 -- this should work
1186 alter table renameColumn add column w int;
1188 -- this should fail
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;
1199 -- should work
1200 alter table p1 drop column f1;
1201 -- c1.f1 is still there, but no longer inherited
1202 select f1 from c1;
1203 alter table c1 drop column f1;
1204 select f1 from c1;
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
1215 select f1 from c1;
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;
1251 -- should work
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;
1264 -- IF EXISTS test
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
1284 from pg_attribute
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);
1300 select * from p1;
1301 update p1 set a1 = a1 + 1, f2 = upper(f2);
1302 select * from p1;
1304 drop table p1 cascade;
1306 -- test that operations with a dropped column do not try to reference
1307 -- its datatype
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');
1313 select * from foo;
1315 drop domain mytype cascade;
1317 select * from foo;
1318 insert into foo values('qq','rr');
1319 select * from foo;
1320 update foo set f3 = 'zz';
1321 select * from foo;
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
1378   add unique(f1,f4);
1379 create index on anothertab(f2,f3);
1380 create unique index on anothertab(f4);
1382 \d anothertab
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;
1389 \d anothertab
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;
1402 alter table another
1403   alter f1 type text using f2 || ' and ' || f3 || ' more',
1404   alter f2 type bigint using f1 * 10,
1405   drop column f3;
1407 select * from another;
1409 drop table another;
1411 -- Create an index that skips WAL, then perform a SET DATA TYPE that skips
1412 -- rewriting the index.
1413 begin;
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);
1416 commit;
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
1422 drop table at_tab2;
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);
1442 \d at_part_1
1443 \d at_part_2
1444 alter table at_partitioned attach partition at_part_2 for values from (1000) to (2000);
1445 \d at_part_2
1446 alter table at_partitioned alter column b type numeric using b::numeric;
1447 \d at_part_1
1448 \d at_part_2
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%';
1472 select relname,
1473   c.oid = oldoid as orig_oid,
1474   case relfilenode
1475     when 0 then 'none'
1476     when c.oid then 'own'
1477     when oldfilenode then 'orig'
1478     else 'OTHER'
1479     end as storage,
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%'
1483   order by relname;
1485 select conname, obj_description(oid, 'pg_constraint') as desc
1486   from pg_constraint where conname like 'at_partitioned%'
1487   order by conname;
1489 alter table at_partitioned alter column name type varchar(127);
1491 select relname,
1492   c.oid = oldoid as orig_oid,
1493   case relfilenode
1494     when 0 then 'none'
1495     when c.oid then 'own'
1496     when oldfilenode then 'orig'
1497     else 'OTHER'
1498     end as storage,
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%'
1502   order by relname;
1504 select conname, obj_description(oid, 'pg_constraint') as desc
1505   from pg_constraint where conname like 'at_partitioned%'
1506   order by conname;
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;
1541 \d+ test_storage
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);
1547 \d 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
1552   order by 1, 2;
1553 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
1554 \d test_inh_check
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
1559   order by 1, 2;
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);
1565 \d test_inh_check
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
1570   order by 1, 2;
1571 ALTER TABLE test_inh_check ALTER COLUMN b TYPE numeric;
1572 \d test_inh_check
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
1577   order by 1, 2;
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);
1604 BEGIN;
1605 ALTER TABLE check_fk_presence_2 DROP CONSTRAINT check_fk_presence_2_id_fkey;
1606 ANALYZE check_fk_presence_2;
1607 ROLLBACK;
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;
1616 \d+ at_view_1
1617 \d+ at_view_2
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;
1622 \d+ at_view_1
1623 \d+ at_view_2
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)
1632 begin;
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;
1645 select * from t1;
1646 select * from log;
1647 rollback;
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)
1655 RETURNS boolean
1656 LANGUAGE plpgsql AS $$
1657 DECLARE
1658     v_relfilenode oid;
1659 BEGIN
1660     v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename;
1662     EXECUTE p_ddl;
1664     RETURN v_relfilenode <> (SELECT relfilenode FROM pg_class WHERE oid = p_tablename);
1665 END;
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;
1677 $$);
1678 SELECT check_ddl_rewrite('rewrite_test', $$
1679     ALTER TABLE rewrite_test
1680         ADD COLUMN notempty2_rewrite serial,
1681         ADD COLUMN empty2 text;
1682 $$);
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;
1688 $$);
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;
1693 $$);
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;
1700 $$);
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;
1706 $$);
1708 -- cleanup
1709 DROP FUNCTION check_ddl_rewrite(regclass, text);
1710 DROP TABLE rewrite_test;
1713 -- lock levels
1715 drop type lockmodes;
1716 create type lockmodes as enum (
1717  'SIReadLock'
1718 ,'AccessShareLock'
1719 ,'RowShareLock'
1720 ,'RowExclusiveLock'
1721 ,'ShareUpdateExclusiveLock'
1722 ,'ShareLock'
1723 ,'ShareRowExclusiveLock'
1724 ,'ExclusiveLock'
1725 ,'AccessExclusiveLock'
1728 drop view my_locks;
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
1734         from pg_locks
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'
1739 group by c.relname;
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;
1748 rollback;
1750 begin; alter table alterlock cluster on alterlock_pkey;
1751 select * from my_locks order by 1;
1752 commit;
1754 begin; alter table alterlock set without cluster;
1755 select * from my_locks order by 1;
1756 commit;
1758 begin; alter table alterlock set (fillfactor = 100);
1759 select * from my_locks order by 1;
1760 commit;
1762 begin; alter table alterlock reset (fillfactor);
1763 select * from my_locks order by 1;
1764 commit;
1766 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1767 select * from my_locks order by 1;
1768 commit;
1770 begin; alter table alterlock set (autovacuum_enabled = off);
1771 select * from my_locks order by 1;
1772 commit;
1774 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1775 select * from my_locks order by 1;
1776 rollback;
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;
1781 commit;
1783 begin; alter table alterlock alter column f2 set storage extended;
1784 select * from my_locks order by 1;
1785 rollback;
1787 begin; alter table alterlock alter column f2 set default 'x';
1788 select * from my_locks order by 1;
1789 rollback;
1791 begin;
1792 create trigger ttdummy
1793         before delete or update on alterlock
1794         for each row
1795         execute procedure
1796         ttdummy (1, 1);
1797 select * from my_locks order by 1;
1798 rollback;
1800 begin;
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;
1804 rollback;
1806 begin;
1807 alter table alterlock2
1808 add constraint alterlock2nv foreign key (f1) references alterlock (f1) NOT VALID;
1809 select * from my_locks order by 1;
1810 commit;
1811 begin;
1812 alter table alterlock2 validate constraint alterlock2nv;
1813 select * from my_locks order by 1;
1814 rollback;
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
1821         from pg_locks
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'
1826 group by c.relname;
1828 -- raise exception
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);
1834 begin;
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);
1838 rollback;
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
1843 begin;
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);
1847 rollback;
1849 -- cleanup
1850 drop table alterlock2;
1851 drop table alterlock;
1852 drop view my_locks;
1853 drop type lockmodes;
1856 -- alter function
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
1925 drop schema 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);
1936 -- clean up
1937 drop schema alter2 cascade;
1940 -- composite types
1943 CREATE TYPE test_type AS (a int);
1944 \d test_type
1946 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1948 ALTER TYPE test_type ADD ATTRIBUTE b text;
1949 \d test_type
1951 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1953 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1954 \d test_type
1956 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1957 \d test_type
1959 ALTER TYPE test_type DROP ATTRIBUTE b;
1960 \d test_type
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;
1967 \d test_type
1969 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1970 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1971 \d test_type
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);
1990 \d test_type2
1991 \d test_tbl2
1993 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1994 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1995 \d test_type2
1996 \d test_tbl2
1998 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1999 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
2000 \d test_type2
2001 \d test_tbl2
2003 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
2004 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
2005 \d test_type2
2006 \d test_tbl2
2008 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
2009 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
2010 \d test_type2
2011 \d test_tbl2
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;
2021 \d test_tblx
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;
2063 \d tt7
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";
2069 -- should fail
2070 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
2071 DROP TABLE test_drop_constr_parent CASCADE;
2074 -- IF EXISTS test
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;
2093 \d alter2.tt8
2095 DROP TABLE alter2.tt8;
2096 DROP SCHEMA alter2;
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
2113 \d tt9
2114 DROP TABLE tt9;
2117 -- Check that comments on constraints and indexes are not lost at ALTER TABLE.
2118 CREATE TABLE comment_test (
2119   id int,
2120   constraint id_notnull_constraint not null id,
2121   positive_col int CHECK (positive_col > 0),
2122   indexed_col int,
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
2183 SELECT
2184     oid, mapped_oid, reltablespace, relfilenode, relname
2185 FROM pg_class,
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
2193 -- pg_catalog.
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;
2215 -- set logged
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'
2219 UNION ALL
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'
2221 UNION ALL
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'
2223 ORDER BY relname;
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'
2231 UNION ALL
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'
2233 UNION ALL
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'
2235 ORDER BY relname;
2236 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
2237 DROP TABLE unlogged3;
2238 DROP TABLE unlogged2;
2239 DROP TABLE unlogged1;
2241 -- set unlogged
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'
2245 UNION ALL
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'
2247 UNION ALL
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'
2249 ORDER BY relname;
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'
2258 UNION ALL
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'
2260 UNION ALL
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'
2262 ORDER BY relname;
2263 ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
2264 DROP TABLE logged3;
2265 DROP TABLE logged2;
2266 DROP TABLE logged1;
2268 -- test ADD COLUMN IF NOT EXISTS
2269 CREATE TABLE test_add_column(c1 integer);
2270 \d test_add_column
2271 ALTER TABLE test_add_column
2272         ADD COLUMN c2 integer;
2273 \d test_add_column
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
2278 \d test_add_column
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
2283 \d test_add_column
2284 ALTER TABLE test_add_column
2285         ADD COLUMN c2 integer, -- fail because c2 already exists
2286         ADD COLUMN c3 integer primary key;
2287 \d test_add_column
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;
2291 \d test_add_column
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
2295 \d test_add_column
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;
2300 \d test_add_column
2301 ALTER TABLE test_add_column
2302         ADD COLUMN IF NOT EXISTS c4 integer REFERENCES test_add_column;
2303 \d test_add_column
2304 ALTER TABLE test_add_column
2305         ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 8);
2306 \d test_add_column
2307 ALTER TABLE test_add_column
2308         ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10);
2309 \d test_add_column*
2310 DROP TABLE test_add_column;
2311 \d 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;
2320 \d ataddindex
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 =);
2328 \d ataddindex
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;
2335 \d ataddindex
2336 DROP TABLE ataddindex;
2338 CREATE TABLE ataddindex(id int, ref_id int);
2339 ALTER TABLE ataddindex
2340   ADD UNIQUE (id),
2341   ADD FOREIGN KEY (ref_id) REFERENCES ataddindex (id);
2342 \d ataddindex
2343 DROP TABLE ataddindex;
2345 CREATE TABLE atnotnull1 ();
2346 ALTER TABLE atnotnull1
2347   ADD COLUMN a INT,
2348   ALTER a SET NOT NULL;
2349 ALTER TABLE atnotnull1
2350   ADD COLUMN b INT,
2351   ADD NOT NULL b;
2352 ALTER TABLE atnotnull1
2353   ADD COLUMN c INT,
2354   ADD PRIMARY KEY (c);
2355 \d+ atnotnull1
2357 -- cannot drop column that is part of the partition key
2358 CREATE TABLE partitioned (
2359         a int,
2360         b int
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 (
2372         a int,
2373         b int
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;
2384 -- ATTACH PARTITION
2387 -- check that target table is partitioned
2388 CREATE TABLE unparted (
2389         a int
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 (
2397         a int NOT NULL,
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 (
2415         a int
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);
2428 DROP TABLE child;
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 (
2461         b char(3),
2462         a int NOT NULL
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",
2472         a int NOT NULL
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 (
2483         a int NOT NULL,
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
2508 -- exists
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 (
2514         a int,
2515         b char
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
2524 DELETE FROM part_2;
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 (
2539         LIKE list_parted2,
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 (
2559         a int,
2560         b int
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
2573 DELETE FROM part1;
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
2600 -- table
2601 CREATE TABLE part_5 (
2602         LIKE list_parted2
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 (
2625         c int,
2626         LIKE list_parted2,
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
2634 -- columns.
2635 CREATE TABLE part_7 (
2636         LIKE list_parted2,
2637         CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
2638 ) PARTITION BY LIST (b);
2639 CREATE TABLE part_7_a_null (
2640         c int,
2641         d int,
2642         e int,
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
2652 -- violating rows
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);
2695 DROP TABLE quuux;
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 (
2704         a int,
2705         b int
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
2725 -- table
2726 CREATE TABLE hpart_5 (
2727         LIKE hash_parted
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;
2747 -- DETACH PARTITION
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 (
2778     a int
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;
2784 DROP TABLE part_rp;
2786 -- concurrent detach
2787 CREATE TABLE range_parted2 (
2788         a int
2789 ) PARTITION BY RANGE(a);
2790 CREATE TABLE part_rp PARTITION OF range_parted2 FOR VALUES FROM (0) to (100);
2791 BEGIN;
2792 -- doesn't work in a partition block
2793 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
2794 COMMIT;
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;
2801 -- works fine
2802 ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
2803 \d+ range_parted2
2804 -- constraint should be created
2805 \d part_rp
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
2809 \d part_rp100
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
2827 -- partitions exist
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
2837 \d+ part_2
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;
2875 -- cleanup
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
2890 from pg_attribute
2891 where attname = 'a'
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);
2903 -- cleanup
2904 drop table p;
2905 drop table p1;
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);
2918 ANALYZE attmp;
2919 DROP TABLE attmp;
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
2942 -- successfully
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
2949 -- partitions.
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
2961 -- prevented
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 $$
2965   begin
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)';
2968     return null;
2969   end $$;
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;
3006 -- this works:
3007 select * from bar1;
3009 -- this exercises tuple conversion:
3010 create function xtrig()
3011   returns trigger language plpgsql
3012 as $$
3013   declare
3014     r record;
3015   begin
3016     for r in select * from old loop
3017       raise info 'a=%, b=%', r.a, r.b;
3018     end loop;
3019     return NULL;
3020   end;
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
3085 -- partition too...
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;
3101 \d+ alter2.t1
3102 drop publication pub1;
3103 drop schema alter1 cascade;
3104 drop schema alter2 cascade;