6 CREATE TABLE tmp (initial int4);
8 COMMENT ON TABLE tmp_wrong IS 'table comment';
9 COMMENT ON TABLE tmp IS 'table comment';
10 COMMENT ON TABLE tmp IS NULL;
12 ALTER TABLE tmp ADD COLUMN xmin integer; -- fails
14 ALTER TABLE tmp ADD COLUMN a int4 default 3;
16 ALTER TABLE tmp ADD COLUMN b name;
18 ALTER TABLE tmp ADD COLUMN c text;
20 ALTER TABLE tmp ADD COLUMN d float8;
22 ALTER TABLE tmp ADD COLUMN e float4;
24 ALTER TABLE tmp ADD COLUMN f int2;
26 ALTER TABLE tmp ADD COLUMN g polygon;
28 ALTER TABLE tmp ADD COLUMN h abstime;
30 ALTER TABLE tmp ADD COLUMN i char;
32 ALTER TABLE tmp ADD COLUMN j abstime[];
34 ALTER TABLE tmp ADD COLUMN k int4;
36 ALTER TABLE tmp ADD COLUMN l tid;
38 ALTER TABLE tmp ADD COLUMN m xid;
40 ALTER TABLE tmp ADD COLUMN n oidvector;
42 --ALTER TABLE tmp ADD COLUMN o lock;
43 ALTER TABLE tmp ADD COLUMN p smgr;
45 ALTER TABLE tmp ADD COLUMN q point;
47 ALTER TABLE tmp ADD COLUMN r lseg;
49 ALTER TABLE tmp ADD COLUMN s path;
51 ALTER TABLE tmp ADD COLUMN t box;
53 ALTER TABLE tmp ADD COLUMN u tinterval;
55 ALTER TABLE tmp ADD COLUMN v timestamp;
57 ALTER TABLE tmp ADD COLUMN w interval;
59 ALTER TABLE tmp ADD COLUMN x float8[];
61 ALTER TABLE tmp ADD COLUMN y float4[];
63 ALTER TABLE tmp ADD COLUMN z int2[];
65 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
67 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
68 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
69 314159, '(1,1)', '512',
70 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
71 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
72 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
78 -- the wolf bug - schema mods caused inconsistent row descriptors
83 ALTER TABLE tmp ADD COLUMN a int4;
85 ALTER TABLE tmp ADD COLUMN b name;
87 ALTER TABLE tmp ADD COLUMN c text;
89 ALTER TABLE tmp ADD COLUMN d float8;
91 ALTER TABLE tmp ADD COLUMN e float4;
93 ALTER TABLE tmp ADD COLUMN f int2;
95 ALTER TABLE tmp ADD COLUMN g polygon;
97 ALTER TABLE tmp ADD COLUMN h abstime;
99 ALTER TABLE tmp ADD COLUMN i char;
101 ALTER TABLE tmp ADD COLUMN j abstime[];
103 ALTER TABLE tmp ADD COLUMN k int4;
105 ALTER TABLE tmp ADD COLUMN l tid;
107 ALTER TABLE tmp ADD COLUMN m xid;
109 ALTER TABLE tmp ADD COLUMN n oidvector;
111 --ALTER TABLE tmp ADD COLUMN o lock;
112 ALTER TABLE tmp ADD COLUMN p smgr;
114 ALTER TABLE tmp ADD COLUMN q point;
116 ALTER TABLE tmp ADD COLUMN r lseg;
118 ALTER TABLE tmp ADD COLUMN s path;
120 ALTER TABLE tmp ADD COLUMN t box;
122 ALTER TABLE tmp ADD COLUMN u tinterval;
124 ALTER TABLE tmp ADD COLUMN v timestamp;
126 ALTER TABLE tmp ADD COLUMN w interval;
128 ALTER TABLE tmp ADD COLUMN x float8[];
130 ALTER TABLE tmp ADD COLUMN y float4[];
132 ALTER TABLE tmp ADD COLUMN z int2[];
134 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
136 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
137 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
138 314159, '(1,1)', '512',
139 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
140 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
141 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
149 -- rename - check on both non-temp and temp tables
151 CREATE TABLE tmp (regtable int);
152 CREATE TEMP TABLE tmp (tmptable int);
154 ALTER TABLE tmp RENAME TO tmp_new;
157 SELECT * FROM tmp_new;
159 ALTER TABLE tmp RENAME TO tmp_new2;
161 SELECT * FROM tmp; -- should fail
162 SELECT * FROM tmp_new;
163 SELECT * FROM tmp_new2;
169 -- ALTER TABLE ... RENAME on non-table relations
170 -- renaming indexes (FIXME: this should probably test the index's functionality)
171 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
172 ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
174 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
175 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
177 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
178 ALTER TABLE tmp_view RENAME TO tmp_view_new;
180 -- hack to ensure we get an indexscan here
182 set enable_seqscan to off;
183 set enable_bitmapscan to off;
185 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
186 reset enable_seqscan;
187 reset enable_bitmapscan;
189 DROP VIEW tmp_view_new;
190 -- toast-like relation name
191 alter table stud_emp rename to pg_toast_stud_emp;
192 alter table pg_toast_stud_emp rename to stud_emp;
194 -- renaming index should rename constraint as well
195 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
196 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
197 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
199 -- renaming constraint
200 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
201 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
202 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
204 -- renaming constraint should rename index as well
205 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
206 DROP INDEX onek_unique1_constraint; -- to see whether it's there
207 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
208 DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
209 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
211 -- renaming constraints vs. inheritance
212 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
213 \d constraint_rename_test
214 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
215 \d constraint_rename_test2
216 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
217 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
218 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
219 \d constraint_rename_test
220 \d constraint_rename_test2
221 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
222 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
223 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
224 \d constraint_rename_test
225 \d constraint_rename_test2
226 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
227 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
228 \d constraint_rename_test
229 \d constraint_rename_test2
230 DROP TABLE constraint_rename_test2;
231 DROP TABLE constraint_rename_test;
232 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
234 -- FOREIGN KEY CONSTRAINT adding TEST
236 CREATE TABLE tmp2 (a int primary key);
238 CREATE TABLE tmp3 (a int, b int);
240 CREATE TABLE tmp4 (a int, b int, unique(a,b));
242 CREATE TABLE tmp5 (a int, b int);
244 -- Insert rows into tmp2 (pktable)
245 INSERT INTO tmp2 values (1);
246 INSERT INTO tmp2 values (2);
247 INSERT INTO tmp2 values (3);
248 INSERT INTO tmp2 values (4);
250 -- Insert rows into tmp3
251 INSERT INTO tmp3 values (1,10);
252 INSERT INTO tmp3 values (1,20);
253 INSERT INTO tmp3 values (5,50);
255 -- Try (and fail) to add constraint due to invalid source columns
256 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
258 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
259 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
261 -- Try (and fail) to add constraint due to invalid data
262 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
264 -- Delete failing row
265 DELETE FROM tmp3 where a=5;
268 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
269 ALTER TABLE tmp3 drop constraint tmpconstr;
271 INSERT INTO tmp3 values (5,50);
273 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
274 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
275 ALTER TABLE tmp3 validate constraint tmpconstr;
277 -- Delete failing row
278 DELETE FROM tmp3 where a=5;
280 -- Try (and succeed) and repeat to show it works on already valid constraint
281 ALTER TABLE tmp3 validate constraint tmpconstr;
282 ALTER TABLE tmp3 validate constraint tmpconstr;
284 -- Try a non-verified CHECK constraint
285 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
286 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
287 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
288 DELETE FROM tmp3 WHERE NOT b > 10;
289 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
290 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
292 -- Test inherited NOT VALID CHECK constraints
294 CREATE TABLE tmp6 () INHERITS (tmp3);
295 CREATE TABLE tmp7 () INHERITS (tmp3);
297 INSERT INTO tmp6 VALUES (6, 30), (7, 16);
298 ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
299 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
300 DELETE FROM tmp6 WHERE b > 20;
301 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
303 -- An already validated constraint must not be revalidated
304 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
305 INSERT INTO tmp7 VALUES (8, 18);
306 ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
307 ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
308 ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
310 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
313 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
327 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
328 -- exclusion until validated
329 set constraint_exclusion TO 'partition';
330 create table nv_parent (d date);
331 create table nv_child_2010 () inherits (nv_parent);
332 create table nv_child_2011 () inherits (nv_parent);
333 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
334 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
335 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
336 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
337 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
338 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
339 -- after validation, the constraint should be used
340 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
341 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
344 -- Foreign key adding test with mixed types
346 -- Note: these tables are TEMP to avoid name conflicts when this test
347 -- is run in parallel with foreign_key.sql.
349 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
350 INSERT INTO PKTABLE VALUES(42);
351 CREATE TEMP TABLE FKTABLE (ftest1 inet);
352 -- This next should fail, because int=inet does not exist
353 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
354 -- This should also fail for the same reason, but here we
355 -- give the column name
356 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
358 -- This should succeed, even though they are different types,
359 -- because int=int8 exists and is a member of the integer opfamily
360 CREATE TEMP TABLE FKTABLE (ftest1 int8);
361 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
362 -- Check it actually works
363 INSERT INTO FKTABLE VALUES(42); -- should succeed
364 INSERT INTO FKTABLE VALUES(43); -- should fail
366 -- This should fail, because we'd have to cast numeric to int which is
367 -- not an implicit coercion (or use numeric=numeric, but that's not part
368 -- of the integer opfamily)
369 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
370 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
373 -- On the other hand, this should work because int implicitly promotes to
374 -- numeric, and we allow promotion on the FK side
375 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
376 INSERT INTO PKTABLE VALUES(42);
377 CREATE TEMP TABLE FKTABLE (ftest1 int);
378 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
379 -- Check it actually works
380 INSERT INTO FKTABLE VALUES(42); -- should succeed
381 INSERT INTO FKTABLE VALUES(43); -- should fail
385 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
386 PRIMARY KEY(ptest1, ptest2));
387 -- This should fail, because we just chose really odd types
388 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
389 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
391 -- Again, so should this...
392 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
393 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
394 references pktable(ptest1, ptest2);
396 -- This fails because we mixed up the column ordering
397 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
398 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
399 references pktable(ptest2, ptest1);
401 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
402 references pktable(ptest1, ptest2);
404 -- temp tables should go away by themselves, need not drop them.
406 -- test check constraint adding
408 create table atacc1 ( test int );
409 -- add a check constraint
410 alter table atacc1 add constraint atacc_test1 check (test>3);
412 insert into atacc1 (test) values (2);
414 insert into atacc1 (test) values (4);
417 -- let's do one where the check fails when added
418 create table atacc1 ( test int );
419 -- insert a soon to be failing row
420 insert into atacc1 (test) values (2);
421 -- add a check constraint (fails)
422 alter table atacc1 add constraint atacc_test1 check (test>3);
423 insert into atacc1 (test) values (4);
426 -- let's do one where the check fails because the column doesn't exist
427 create table atacc1 ( test int );
428 -- add a check constraint (fails)
429 alter table atacc1 add constraint atacc_test1 check (test1>3);
432 -- something a little more complicated
433 create table atacc1 ( test int, test2 int, test3 int);
434 -- add a check constraint (fails)
435 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
437 insert into atacc1 (test,test2,test3) values (4,4,2);
439 insert into atacc1 (test,test2,test3) values (4,4,5);
442 -- lets do some naming tests
443 create table atacc1 (test int check (test>3), test2 int);
444 alter table atacc1 add check (test2>test);
445 -- should fail for $2
446 insert into atacc1 (test2, test) values (3, 4);
449 -- inheritance related tests
450 create table atacc1 (test int);
451 create table atacc2 (test2 int);
452 create table atacc3 (test3 int) inherits (atacc1, atacc2);
453 alter table atacc2 add constraint foo check (test2>0);
454 -- fail and then succeed on atacc2
455 insert into atacc2 (test2) values (-3);
456 insert into atacc2 (test2) values (3);
457 -- fail and then succeed on atacc3
458 insert into atacc3 (test2) values (-3);
459 insert into atacc3 (test2) values (3);
464 -- same things with one created with INHERIT
465 create table atacc1 (test int);
466 create table atacc2 (test2 int);
467 create table atacc3 (test3 int) inherits (atacc1, atacc2);
468 alter table atacc3 no inherit atacc2;
470 alter table atacc3 no inherit atacc2;
471 -- make sure it really isn't a child
472 insert into atacc3 (test2) values (3);
473 select test2 from atacc2;
474 -- fail due to missing constraint
475 alter table atacc2 add constraint foo check (test2>0);
476 alter table atacc3 inherit atacc2;
477 -- fail due to missing column
478 alter table atacc3 rename test2 to testx;
479 alter table atacc3 inherit atacc2;
480 -- fail due to mismatched data type
481 alter table atacc3 add test2 bool;
482 alter table atacc3 inherit atacc2;
483 alter table atacc3 drop test2;
485 alter table atacc3 add test2 int;
486 update atacc3 set test2 = 4 where test2 is null;
487 alter table atacc3 add constraint foo check (test2>0);
488 alter table atacc3 inherit atacc2;
489 -- fail due to duplicates and circular inheritance
490 alter table atacc3 inherit atacc2;
491 alter table atacc2 inherit atacc3;
492 alter table atacc2 inherit atacc2;
493 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
494 select test2 from atacc2;
495 drop table atacc2 cascade;
498 -- adding only to a parent is allowed as of 9.2
500 create table atacc1 (test int);
501 create table atacc2 (test2 int) inherits (atacc1);
503 alter table atacc1 add constraint foo check (test>0) no inherit;
504 -- check constraint is not there on child
505 insert into atacc2 (test) values (-3);
506 -- check constraint is there on parent
507 insert into atacc1 (test) values (-3);
508 insert into atacc1 (test) values (3);
509 -- fail, violating row:
510 alter table atacc2 add constraint foo check (test>0) no inherit;
514 -- test unique constraint adding
516 create table atacc1 ( test int ) with oids;
517 -- add a unique constraint
518 alter table atacc1 add constraint atacc_test1 unique (test);
519 -- insert first value
520 insert into atacc1 (test) values (2);
522 insert into atacc1 (test) values (2);
524 insert into atacc1 (test) values (4);
525 -- try adding a unique oid constraint
526 alter table atacc1 add constraint atacc_oid1 unique(oid);
527 -- try to create duplicates via alter table using - should fail
528 alter table atacc1 alter column test type integer using 0;
531 -- let's do one where the unique constraint fails when added
532 create table atacc1 ( test int );
533 -- insert soon to be failing rows
534 insert into atacc1 (test) values (2);
535 insert into atacc1 (test) values (2);
536 -- add a unique constraint (fails)
537 alter table atacc1 add constraint atacc_test1 unique (test);
538 insert into atacc1 (test) values (3);
541 -- let's do one where the unique constraint fails
542 -- because the column doesn't exist
543 create table atacc1 ( test int );
544 -- add a unique constraint (fails)
545 alter table atacc1 add constraint atacc_test1 unique (test1);
548 -- something a little more complicated
549 create table atacc1 ( test int, test2 int);
550 -- add a unique constraint
551 alter table atacc1 add constraint atacc_test1 unique (test, test2);
552 -- insert initial value
553 insert into atacc1 (test,test2) values (4,4);
555 insert into atacc1 (test,test2) values (4,4);
556 -- should all succeed
557 insert into atacc1 (test,test2) values (4,5);
558 insert into atacc1 (test,test2) values (5,4);
559 insert into atacc1 (test,test2) values (5,5);
562 -- lets do some naming tests
563 create table atacc1 (test int, test2 int, unique(test));
564 alter table atacc1 add unique (test2);
565 -- should fail for @@ second one @@
566 insert into atacc1 (test2, test) values (3, 3);
567 insert into atacc1 (test2, test) values (2, 3);
570 -- test primary key constraint adding
572 create table atacc1 ( test int ) with oids;
573 -- add a primary key constraint
574 alter table atacc1 add constraint atacc_test1 primary key (test);
575 -- insert first value
576 insert into atacc1 (test) values (2);
578 insert into atacc1 (test) values (2);
580 insert into atacc1 (test) values (4);
581 -- inserting NULL should fail
582 insert into atacc1 (test) values(NULL);
583 -- try adding a second primary key (should fail)
584 alter table atacc1 add constraint atacc_oid1 primary key(oid);
585 -- drop first primary key constraint
586 alter table atacc1 drop constraint atacc_test1 restrict;
587 -- try adding a primary key on oid (should succeed)
588 alter table atacc1 add constraint atacc_oid1 primary key(oid);
591 -- let's do one where the primary key constraint fails when added
592 create table atacc1 ( test int );
593 -- insert soon to be failing rows
594 insert into atacc1 (test) values (2);
595 insert into atacc1 (test) values (2);
596 -- add a primary key (fails)
597 alter table atacc1 add constraint atacc_test1 primary key (test);
598 insert into atacc1 (test) values (3);
601 -- let's do another one where the primary key constraint fails when added
602 create table atacc1 ( test int );
603 -- insert soon to be failing row
604 insert into atacc1 (test) values (NULL);
605 -- add a primary key (fails)
606 alter table atacc1 add constraint atacc_test1 primary key (test);
607 insert into atacc1 (test) values (3);
610 -- let's do one where the primary key constraint fails
611 -- because the column doesn't exist
612 create table atacc1 ( test int );
613 -- add a primary key constraint (fails)
614 alter table atacc1 add constraint atacc_test1 primary key (test1);
617 -- adding a new column as primary key to a non-empty table.
618 -- should fail unless the column has a non-null default value.
619 create table atacc1 ( test int );
620 insert into atacc1 (test) values (0);
621 -- add a primary key column without a default (fails).
622 alter table atacc1 add column test2 int primary key;
623 -- now add a primary key column with a default (succeeds).
624 alter table atacc1 add column test2 int default 0 primary key;
627 -- something a little more complicated
628 create table atacc1 ( test int, test2 int);
629 -- add a primary key constraint
630 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
631 -- try adding a second primary key - should fail
632 alter table atacc1 add constraint atacc_test2 primary key (test);
633 -- insert initial value
634 insert into atacc1 (test,test2) values (4,4);
636 insert into atacc1 (test,test2) values (4,4);
637 insert into atacc1 (test,test2) values (NULL,3);
638 insert into atacc1 (test,test2) values (3, NULL);
639 insert into atacc1 (test,test2) values (NULL,NULL);
640 -- should all succeed
641 insert into atacc1 (test,test2) values (4,5);
642 insert into atacc1 (test,test2) values (5,4);
643 insert into atacc1 (test,test2) values (5,5);
646 -- lets do some naming tests
647 create table atacc1 (test int, test2 int, primary key(test));
648 -- only first should succeed
649 insert into atacc1 (test2, test) values (3, 3);
650 insert into atacc1 (test2, test) values (2, 3);
651 insert into atacc1 (test2, test) values (1, NULL);
654 -- alter table / alter column [set/drop] not null tests
655 -- try altering system catalogs, should fail
656 alter table pg_class alter column relname drop not null;
657 alter table pg_class alter relname set not null;
659 -- try altering non-existent table, should fail
660 alter table non_existent alter column bar set not null;
661 alter table non_existent alter column bar drop not null;
663 -- test setting columns to null and not null and vice versa
664 -- test checking for null values and primary key
665 create table atacc1 (test int not null) with oids;
666 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
667 alter table atacc1 alter column test drop not null;
668 alter table atacc1 drop constraint "atacc1_pkey";
669 alter table atacc1 alter column test drop not null;
670 insert into atacc1 values (null);
671 alter table atacc1 alter test set not null;
673 alter table atacc1 alter test set not null;
675 -- try altering a non-existent column, should fail
676 alter table atacc1 alter bar set not null;
677 alter table atacc1 alter bar drop not null;
679 -- try altering the oid column, should fail
680 alter table atacc1 alter oid set not null;
681 alter table atacc1 alter oid drop not null;
683 -- try creating a view and altering that, should fail
684 create view myview as select * from atacc1;
685 alter table myview alter column test drop not null;
686 alter table myview alter column test set not null;
692 create table parent (a int);
693 create table child (b varchar(255)) inherits (parent);
695 alter table parent alter a set not null;
696 insert into parent values (NULL);
697 insert into child (a, b) values (NULL, 'foo');
698 alter table parent alter a drop not null;
699 insert into parent values (NULL);
700 insert into child (a, b) values (NULL, 'foo');
701 alter table only parent alter a set not null;
702 alter table child alter a set not null;
704 alter table only parent alter a set not null;
705 insert into parent values (NULL);
706 alter table child alter a set not null;
707 insert into child (a, b) values (NULL, 'foo');
709 alter table child alter a set not null;
710 insert into child (a, b) values (NULL, 'foo');
714 -- test setting and removing default values
715 create table def_test (
717 c2 text default 'initial_default'
719 insert into def_test default values;
720 alter table def_test alter column c1 drop default;
721 insert into def_test default values;
722 alter table def_test alter column c2 drop default;
723 insert into def_test default values;
724 alter table def_test alter column c1 set default 10;
725 alter table def_test alter column c2 set default 'new_default';
726 insert into def_test default values;
727 select * from def_test;
729 -- set defaults to an incorrect type: this should fail
730 alter table def_test alter column c1 set default 'wrong_datatype';
731 alter table def_test alter column c2 set default 20;
733 -- set defaults on a non-existent column: this should fail
734 alter table def_test alter column c3 set default 30;
736 -- set defaults on views: we need to create a view, add a rule
737 -- to allow insertions into it, and then alter the view to add
739 create view def_view_test as select * from def_test;
740 create rule def_view_test_ins as
741 on insert to def_view_test
742 do instead insert into def_test select new.*;
743 insert into def_view_test default values;
744 alter table def_view_test alter column c1 set default 45;
745 insert into def_view_test default values;
746 alter table def_view_test alter column c2 set default 'view_default';
747 insert into def_view_test default values;
748 select * from def_view_test;
750 drop rule def_view_test_ins on def_view_test;
751 drop view def_view_test;
754 -- alter table / drop column tests
755 -- try altering system catalogs, should fail
756 alter table pg_class drop column relname;
758 -- try altering non-existent table, should fail
759 alter table nosuchtable drop column bar;
761 -- test dropping columns
762 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
763 insert into atacc1 values (1, 2, 3, 4);
764 alter table atacc1 drop a;
765 alter table atacc1 drop a;
768 select * from atacc1;
769 select * from atacc1 order by a;
770 select * from atacc1 order by "........pg.dropped.1........";
771 select * from atacc1 group by a;
772 select * from atacc1 group by "........pg.dropped.1........";
773 select atacc1.* from atacc1;
774 select a from atacc1;
775 select atacc1.a from atacc1;
776 select b,c,d from atacc1;
777 select a,b,c,d from atacc1;
778 select * from atacc1 where a = 1;
779 select "........pg.dropped.1........" from atacc1;
780 select atacc1."........pg.dropped.1........" from atacc1;
781 select "........pg.dropped.1........",b,c,d from atacc1;
782 select * from atacc1 where "........pg.dropped.1........" = 1;
785 update atacc1 set a = 3;
786 update atacc1 set b = 2 where a = 3;
787 update atacc1 set "........pg.dropped.1........" = 3;
788 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
791 insert into atacc1 values (10, 11, 12, 13);
792 insert into atacc1 values (default, 11, 12, 13);
793 insert into atacc1 values (11, 12, 13);
794 insert into atacc1 (a) values (10);
795 insert into atacc1 (a) values (default);
796 insert into atacc1 (a,b,c,d) values (10,11,12,13);
797 insert into atacc1 (a,b,c,d) values (default,11,12,13);
798 insert into atacc1 (b,c,d) values (11,12,13);
799 insert into atacc1 ("........pg.dropped.1........") values (10);
800 insert into atacc1 ("........pg.dropped.1........") values (default);
801 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
802 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
805 delete from atacc1 where a = 3;
806 delete from atacc1 where "........pg.dropped.1........" = 3;
809 -- try dropping a non-existent column, should fail
810 alter table atacc1 drop bar;
812 -- try dropping the oid column, should succeed
813 alter table atacc1 drop oid;
815 -- try dropping the xmin column, should fail
816 alter table atacc1 drop xmin;
818 -- try creating a view and altering that, should fail
819 create view myview as select * from atacc1;
820 select * from myview;
821 alter table myview drop d;
824 -- test some commands to make sure they fail on the dropped column
826 analyze atacc1("........pg.dropped.1........");
827 vacuum analyze atacc1(a);
828 vacuum analyze atacc1("........pg.dropped.1........");
829 comment on column atacc1.a is 'testing';
830 comment on column atacc1."........pg.dropped.1........" is 'testing';
831 alter table atacc1 alter a set storage plain;
832 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
833 alter table atacc1 alter a set statistics 0;
834 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
835 alter table atacc1 alter a set default 3;
836 alter table atacc1 alter "........pg.dropped.1........" set default 3;
837 alter table atacc1 alter a drop default;
838 alter table atacc1 alter "........pg.dropped.1........" drop default;
839 alter table atacc1 alter a set not null;
840 alter table atacc1 alter "........pg.dropped.1........" set not null;
841 alter table atacc1 alter a drop not null;
842 alter table atacc1 alter "........pg.dropped.1........" drop not null;
843 alter table atacc1 rename a to x;
844 alter table atacc1 rename "........pg.dropped.1........" to x;
845 alter table atacc1 add primary key(a);
846 alter table atacc1 add primary key("........pg.dropped.1........");
847 alter table atacc1 add unique(a);
848 alter table atacc1 add unique("........pg.dropped.1........");
849 alter table atacc1 add check (a > 3);
850 alter table atacc1 add check ("........pg.dropped.1........" > 3);
851 create table atacc2 (id int4 unique);
852 alter table atacc1 add foreign key (a) references atacc2(id);
853 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
854 alter table atacc2 add foreign key (id) references atacc1(a);
855 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
857 create index "testing_idx" on atacc1(a);
858 create index "testing_idx" on atacc1("........pg.dropped.1........");
860 -- test create as and select into
861 insert into atacc1 values (21, 22, 23);
862 create table test1 as select * from atacc1;
865 select * into test2 from atacc1;
869 -- try dropping all columns
870 alter table atacc1 drop c;
871 alter table atacc1 drop d;
872 alter table atacc1 drop b;
873 select * from atacc1;
877 -- test constraint error reporting in presence of dropped columns
878 create table atacc1 (id serial primary key, value int check (value < 10));
879 insert into atacc1(value) values (100);
880 alter table atacc1 drop column value;
881 alter table atacc1 add column value int check (value < 10);
882 insert into atacc1(value) values (100);
883 insert into atacc1(id, value) values (null, 0);
887 create table parent (a int, b int, c int);
888 insert into parent values (1, 2, 3);
889 alter table parent drop a;
890 create table child (d varchar(255)) inherits (parent);
891 insert into child values (12, 13, 'testing');
893 select * from parent;
895 alter table parent drop c;
896 select * from parent;
903 create table test (a int4, b int4, c int4);
904 insert into test values (1,2,3);
905 alter table test drop a;
907 copy test(a) to stdout;
908 copy test("........pg.dropped.1........") to stdout;
909 copy test from stdin;
913 copy test from stdin;
917 copy test(a) from stdin;
918 copy test("........pg.dropped.1........") from stdin;
919 copy test(b,c) from stdin;
927 create table dropColumn (a int, b int, e int);
928 create table dropColumnChild (c int) inherits (dropColumn);
929 create table dropColumnAnother (d int) inherits (dropColumnChild);
931 -- these two should fail
932 alter table dropColumnchild drop column a;
933 alter table only dropColumnChild drop column b;
937 -- these three should work
938 alter table only dropColumn drop column e;
939 alter table dropColumnChild drop column c;
940 alter table dropColumn drop column a;
942 create table renameColumn (a int);
943 create table renameColumnChild (b int) inherits (renameColumn);
944 create table renameColumnAnother (c int) inherits (renameColumnChild);
946 -- these three should fail
947 alter table renameColumnChild rename column a to d;
948 alter table only renameColumnChild rename column a to d;
949 alter table only renameColumn rename column a to d;
952 alter table renameColumn rename column a to d;
953 alter table renameColumnChild rename column b to a;
956 alter table if exists doesnt_exist_tab rename column a to d;
957 alter table if exists doesnt_exist_tab rename column b to a;
960 alter table renameColumn add column w int;
963 alter table only renameColumn add column x int;
966 -- Test corner cases in dropping of inherited columns
968 create table p1 (f1 int, f2 int);
969 create table c1 (f1 int not null) inherits(p1);
971 -- should be rejected since c1.f1 is inherited
972 alter table c1 drop column f1;
974 alter table p1 drop column f1;
975 -- c1.f1 is still there, but no longer inherited
977 alter table c1 drop column f1;
980 drop table p1 cascade;
982 create table p1 (f1 int, f2 int);
983 create table c1 () inherits(p1);
985 -- should be rejected since c1.f1 is inherited
986 alter table c1 drop column f1;
987 alter table p1 drop column f1;
988 -- c1.f1 is dropped now, since there is no local definition for it
991 drop table p1 cascade;
993 create table p1 (f1 int, f2 int);
994 create table c1 () inherits(p1);
996 -- should be rejected since c1.f1 is inherited
997 alter table c1 drop column f1;
998 alter table only p1 drop column f1;
999 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1000 alter table c1 drop column f1;
1002 drop table p1 cascade;
1004 create table p1 (f1 int, f2 int);
1005 create table c1 (f1 int not null) inherits(p1);
1007 -- should be rejected since c1.f1 is inherited
1008 alter table c1 drop column f1;
1009 alter table only p1 drop column f1;
1010 -- c1.f1 is still there, but no longer inherited
1011 alter table c1 drop column f1;
1013 drop table p1 cascade;
1015 create table p1(id int, name text);
1016 create table p2(id2 int, name text, height int);
1017 create table c1(age int) inherits(p1,p2);
1018 create table gc1() inherits (c1);
1020 select relname, attname, attinhcount, attislocal
1021 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1022 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1023 order by relname, attnum;
1026 alter table only p1 drop column name;
1027 -- should work. Now c1.name is local and inhcount is 0.
1028 alter table p2 drop column name;
1029 -- should be rejected since its inherited
1030 alter table gc1 drop column name;
1031 -- should work, and drop gc1.name along
1032 alter table c1 drop column name;
1033 -- should fail: column does not exist
1034 alter table gc1 drop column name;
1035 -- should work and drop the attribute in all tables
1036 alter table p2 drop column height;
1039 create table dropColumnExists ();
1040 alter table dropColumnExists drop column non_existing; --fail
1041 alter table dropColumnExists drop column if exists non_existing; --succeed
1043 select relname, attname, attinhcount, attislocal
1044 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1045 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1046 order by relname, attnum;
1048 drop table p1, p2 cascade;
1050 -- test attinhcount tracking with merged columns
1052 create table depth0();
1053 create table depth1(c text) inherits (depth0);
1054 create table depth2() inherits (depth1);
1055 alter table depth0 add c text;
1057 select attrelid::regclass, attname, attinhcount, attislocal
1059 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1060 order by attrelid::regclass::text, attnum;
1063 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1065 create table altstartwith (col integer) with oids;
1067 insert into altstartwith values (1);
1069 select oid > 0, * from altstartwith;
1071 alter table altstartwith set without oids;
1073 select oid > 0, * from altstartwith; -- fails
1074 select * from altstartwith;
1076 alter table altstartwith set with oids;
1078 select oid > 0, * from altstartwith;
1080 drop table altstartwith;
1082 -- Check inheritance cases
1083 create table altwithoid (col integer) with oids;
1085 -- Inherits parents oid column anyway
1086 create table altinhoid () inherits (altwithoid) without oids;
1088 insert into altinhoid values (1);
1090 select oid > 0, * from altwithoid;
1091 select oid > 0, * from altinhoid;
1093 alter table altwithoid set without oids;
1095 select oid > 0, * from altwithoid; -- fails
1096 select oid > 0, * from altinhoid; -- fails
1097 select * from altwithoid;
1098 select * from altinhoid;
1100 alter table altwithoid set with oids;
1102 select oid > 0, * from altwithoid;
1103 select oid > 0, * from altinhoid;
1105 drop table altwithoid cascade;
1107 create table altwithoid (col integer) without oids;
1109 -- child can have local oid column
1110 create table altinhoid () inherits (altwithoid) with oids;
1112 insert into altinhoid values (1);
1114 select oid > 0, * from altwithoid; -- fails
1115 select oid > 0, * from altinhoid;
1117 alter table altwithoid set with oids;
1119 select oid > 0, * from altwithoid;
1120 select oid > 0, * from altinhoid;
1122 -- the child's local definition should remain
1123 alter table altwithoid set without oids;
1125 select oid > 0, * from altwithoid; -- fails
1126 select oid > 0, * from altinhoid;
1128 drop table altwithoid cascade;
1130 -- test renumbering of child-table columns in inherited operations
1132 create table p1 (f1 int);
1133 create table c1 (f2 text, f3 int) inherits (p1);
1135 alter table p1 add column a1 int check (a1 > 0);
1136 alter table p1 add column f2 text;
1138 insert into p1 values (1,2,'abc');
1139 insert into c1 values(11,'xyz',33,0); -- should fail
1140 insert into c1 values(11,'xyz',33,22);
1143 update p1 set a1 = a1 + 1, f2 = upper(f2);
1146 drop table p1 cascade;
1148 -- test that operations with a dropped column do not try to reference
1151 create domain mytype as text;
1152 create temp table foo (f1 text, f2 mytype, f3 text);
1154 insert into foo values('bb','cc','dd');
1157 drop domain mytype cascade;
1160 insert into foo values('qq','rr');
1162 update foo set f3 = 'zz';
1164 select f3,max(f1) from foo group by f3;
1166 -- Simple tests for alter table column type
1167 alter table foo alter f1 TYPE integer; -- fails
1168 alter table foo alter f1 TYPE varchar(10);
1170 create table anothertab (atcol1 serial8, atcol2 boolean,
1171 constraint anothertab_chk check (atcol1 <= 3));
1173 insert into anothertab (atcol1, atcol2) values (default, true);
1174 insert into anothertab (atcol1, atcol2) values (default, false);
1175 select * from anothertab;
1177 alter table anothertab alter column atcol1 type boolean; -- fails
1178 alter table anothertab alter column atcol1 type integer;
1180 select * from anothertab;
1182 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1183 insert into anothertab (atcol1, atcol2) values (default, null);
1185 select * from anothertab;
1187 alter table anothertab alter column atcol2 type text
1188 using case when atcol2 is true then 'IT WAS TRUE'
1189 when atcol2 is false then 'IT WAS FALSE'
1190 else 'IT WAS NULL!' end;
1192 select * from anothertab;
1193 alter table anothertab alter column atcol1 type boolean
1194 using case when atcol1 % 2 = 0 then true else false end; -- fails
1195 alter table anothertab alter column atcol1 drop default;
1196 alter table anothertab alter column atcol1 type boolean
1197 using case when atcol1 % 2 = 0 then true else false end; -- fails
1198 alter table anothertab drop constraint anothertab_chk;
1199 alter table anothertab drop constraint anothertab_chk; -- fails
1200 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1202 alter table anothertab alter column atcol1 type boolean
1203 using case when atcol1 % 2 = 0 then true else false end;
1205 select * from anothertab;
1207 drop table anothertab;
1209 create table another (f1 int, f2 text);
1211 insert into another values(1, 'one');
1212 insert into another values(2, 'two');
1213 insert into another values(3, 'three');
1215 select * from another;
1218 alter f1 type text using f2 || ' more',
1219 alter f2 type bigint using f1 * 10;
1221 select * from another;
1226 create table tab1 (a int, b text);
1227 create table tab2 (x int, y tab1);
1228 alter table tab1 alter column b type varchar; -- fails
1230 -- disallow recursive containment of row types
1231 create temp table recur1 (f1 int);
1232 alter table recur1 add column f2 recur1; -- fails
1233 alter table recur1 add column f2 recur1[]; -- fails
1234 create domain array_of_recur1 as recur1[];
1235 alter table recur1 add column f2 array_of_recur1; -- fails
1236 create temp table recur2 (f1 int, f2 recur1);
1237 alter table recur1 add column f2 recur2; -- fails
1238 alter table recur1 add column f2 int;
1239 alter table recur1 alter column f2 type recur2; -- fails
1241 -- SET STORAGE may need to add a TOAST table
1242 create table test_storage (a text);
1243 alter table test_storage alter a set storage plain;
1244 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1245 alter table test_storage alter a set storage extended; -- re-add TOAST table
1247 select reltoastrelid <> 0 as has_toast_table
1249 where oid = 'test_storage'::regclass;
1251 -- ALTER TYPE with a check constraint and a child table (bug before Nov 2012)
1252 CREATE TABLE test_inh_check (a float check (a > 10.2));
1253 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
1254 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
1256 \d test_inh_check_child
1261 drop type lockmodes;
1262 create type lockmodes as enum (
1266 ,'ShareUpdateExclusiveLock'
1268 ,'ShareRowExclusiveLock'
1270 ,'AccessExclusiveLock'
1274 create or replace view my_locks as
1275 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1276 from pg_locks l join pg_class c on l.relation = c.oid
1277 where virtualtransaction = (
1278 select virtualtransaction
1280 where transactionid = txid_current()::integer)
1281 and locktype = 'relation'
1282 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1283 and c.relname != 'my_locks'
1286 create table alterlock (f1 int primary key, f2 text);
1288 begin; alter table alterlock alter column f2 set statistics 150;
1289 select * from my_locks order by 1;
1292 begin; alter table alterlock cluster on alterlock_pkey;
1293 select * from my_locks order by 1;
1296 begin; alter table alterlock set without cluster;
1297 select * from my_locks order by 1;
1300 begin; alter table alterlock set (fillfactor = 100);
1301 select * from my_locks order by 1;
1304 begin; alter table alterlock reset (fillfactor);
1305 select * from my_locks order by 1;
1308 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1309 select * from my_locks order by 1;
1312 begin; alter table alterlock set (autovacuum_enabled = off);
1313 select * from my_locks order by 1;
1316 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1317 select * from my_locks order by 1;
1320 begin; alter table alterlock alter column f2 set storage extended;
1321 select * from my_locks order by 1;
1324 begin; alter table alterlock alter column f2 set default 'x';
1325 select * from my_locks order by 1;
1329 drop table alterlock;
1331 drop type lockmodes;
1336 create function test_strict(text) returns text as
1337 'select coalesce($1, ''got passed a null'');'
1338 language sql returns null on null input;
1339 select test_strict(NULL);
1340 alter function test_strict(text) called on null input;
1341 select test_strict(NULL);
1343 create function non_strict(text) returns text as
1344 'select coalesce($1, ''got passed a null'');'
1345 language sql called on null input;
1346 select non_strict(NULL);
1347 alter function non_strict(text) returns null on null input;
1348 select non_strict(NULL);
1351 -- alter object set schema
1354 create schema alter1;
1355 create schema alter2;
1357 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1359 create view alter1.v1 as select * from alter1.t1;
1361 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1363 create domain alter1.posint integer check (value > 0);
1365 create type alter1.ctype as (f1 int, f2 text);
1367 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1368 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1370 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1372 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1373 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1375 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1377 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1378 create text search configuration alter1.cfg(parser = alter1.prs);
1379 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1380 create text search dictionary alter1.dict(template = alter1.tmpl);
1382 insert into alter1.t1(f2) values(11);
1383 insert into alter1.t1(f2) values(12);
1385 alter table alter1.t1 set schema alter2;
1386 alter table alter1.v1 set schema alter2;
1387 alter function alter1.plus1(int) set schema alter2;
1388 alter domain alter1.posint set schema alter2;
1389 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1390 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1391 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1392 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1393 alter type alter1.ctype set schema alter2;
1394 alter conversion alter1.ascii_to_utf8 set schema alter2;
1395 alter text search parser alter1.prs set schema alter2;
1396 alter text search configuration alter1.cfg set schema alter2;
1397 alter text search template alter1.tmpl set schema alter2;
1398 alter text search dictionary alter1.dict set schema alter2;
1400 -- this should succeed because nothing is left in alter1
1403 insert into alter2.t1(f2) values(13);
1404 insert into alter2.t1(f2) values(14);
1406 select * from alter2.t1;
1408 select * from alter2.v1;
1410 select alter2.plus1(41);
1413 drop schema alter2 cascade;
1419 CREATE TYPE test_type AS (a int);
1422 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1424 ALTER TYPE test_type ADD ATTRIBUTE b text;
1427 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1429 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1432 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1435 ALTER TYPE test_type DROP ATTRIBUTE b;
1438 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1440 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1442 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1445 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1446 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1449 DROP TYPE test_type;
1451 CREATE TYPE test_type1 AS (a int, b text);
1452 CREATE TABLE test_tbl1 (x int, y test_type1);
1453 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1455 CREATE TYPE test_type2 AS (a int, b text);
1456 CREATE TABLE test_tbl2 OF test_type2;
1457 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
1461 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1462 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1466 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1467 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1471 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1472 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1476 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1477 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1480 \d test_tbl2_subclass
1482 DROP TABLE test_tbl2_subclass;
1484 -- This test isn't that interesting on its own, but the purpose is to leave
1485 -- behind a table to test pg_upgrade with. The table has a composite type
1486 -- column in it, and the composite type has a dropped attribute.
1487 CREATE TYPE test_type3 AS (a int);
1488 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
1489 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
1491 CREATE TYPE test_type_empty AS ();
1492 DROP TYPE test_type_empty;
1495 -- typed tables: OF / NOT OF
1498 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
1499 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
1500 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
1501 CREATE TABLE tt1 (x int, y bigint); -- wrong base type
1502 CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
1503 CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column order
1504 CREATE TABLE tt4 (x int); -- too few columns
1505 CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
1506 CREATE TABLE tt6 () INHERITS (tt0); -- can't have a parent
1507 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
1508 ALTER TABLE tt7 DROP q; -- OK
1510 ALTER TABLE tt0 OF tt_t0;
1511 ALTER TABLE tt1 OF tt_t0;
1512 ALTER TABLE tt2 OF tt_t0;
1513 ALTER TABLE tt3 OF tt_t0;
1514 ALTER TABLE tt4 OF tt_t0;
1515 ALTER TABLE tt5 OF tt_t0;
1516 ALTER TABLE tt6 OF tt_t0;
1517 ALTER TABLE tt7 OF tt_t0;
1519 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
1520 ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
1521 ALTER TABLE tt7 NOT OF;
1524 -- make sure we can drop a constraint on the parent but it remains on the child
1525 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
1526 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
1527 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
1529 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
1530 DROP TABLE test_drop_constr_parent CASCADE;
1535 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1536 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1537 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1538 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1539 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1540 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1542 CREATE TABLE tt8(a int);
1543 CREATE SCHEMA alter2;
1545 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
1546 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
1547 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
1548 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
1549 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
1550 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
1554 DROP TABLE alter2.tt8;
1557 -- Check that we map relation oids to filenodes and back correctly.
1558 -- Don't display all the mappings so the test output doesn't change
1559 -- all the time, but make sure we actually do test some values.
1561 SUM((mapped_oid != oid OR mapped_oid IS NULL)::int) incorrectly_mapped,
1562 count(*) > 200 have_mappings
1565 oid, reltablespace, relfilenode, relname,
1566 pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) mapped_oid
1568 WHERE relkind IN ('r', 'i', 'S', 't', 'm')
1571 -- Checks on creating and manipulation of user defined relations in
1574 -- XXX: It would be useful to add checks around trying to manipulate
1575 -- catalog tables, but that might have ugly consequences when run
1576 -- against an existing server with allow_system_table_mods = on.
1578 SHOW allow_system_table_mods;
1579 -- disallowed because of search_path issues with pg_dump
1580 CREATE TABLE pg_catalog.new_system_table();
1581 -- instead create in public first, move to catalog
1582 CREATE TABLE new_system_table(id serial primary key, othercol text);
1583 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
1585 -- XXX: it's currently impossible to move relations out of pg_catalog
1586 ALTER TABLE new_system_table SET SCHEMA public;
1587 -- move back, will currently error out, already there
1588 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
1589 ALTER TABLE new_system_table RENAME TO old_system_table;
1590 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
1591 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
1592 UPDATE old_system_table SET id = -id;
1593 DELETE FROM old_system_table WHERE othercol = 'somedata';
1594 TRUNCATE old_system_table;
1595 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
1596 ALTER TABLE old_system_table DROP COLUMN othercol;
1597 DROP TABLE old_system_table;