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 a int4 default 3;
14 ALTER TABLE tmp ADD COLUMN b name;
16 ALTER TABLE tmp ADD COLUMN c text;
18 ALTER TABLE tmp ADD COLUMN d float8;
20 ALTER TABLE tmp ADD COLUMN e float4;
22 ALTER TABLE tmp ADD COLUMN f int2;
24 ALTER TABLE tmp ADD COLUMN g polygon;
26 ALTER TABLE tmp ADD COLUMN h abstime;
28 ALTER TABLE tmp ADD COLUMN i char;
30 ALTER TABLE tmp ADD COLUMN j abstime[];
32 ALTER TABLE tmp ADD COLUMN k int4;
34 ALTER TABLE tmp ADD COLUMN l tid;
36 ALTER TABLE tmp ADD COLUMN m xid;
38 ALTER TABLE tmp ADD COLUMN n oidvector;
40 --ALTER TABLE tmp ADD COLUMN o lock;
41 ALTER TABLE tmp ADD COLUMN p smgr;
43 ALTER TABLE tmp ADD COLUMN q point;
45 ALTER TABLE tmp ADD COLUMN r lseg;
47 ALTER TABLE tmp ADD COLUMN s path;
49 ALTER TABLE tmp ADD COLUMN t box;
51 ALTER TABLE tmp ADD COLUMN u tinterval;
53 ALTER TABLE tmp ADD COLUMN v timestamp;
55 ALTER TABLE tmp ADD COLUMN w interval;
57 ALTER TABLE tmp ADD COLUMN x float8[];
59 ALTER TABLE tmp ADD COLUMN y float4[];
61 ALTER TABLE tmp ADD COLUMN z int2[];
63 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
65 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
66 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
67 314159, '(1,1)', '512',
68 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
69 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
70 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
76 -- the wolf bug - schema mods caused inconsistent row descriptors
81 ALTER TABLE tmp ADD COLUMN a int4;
83 ALTER TABLE tmp ADD COLUMN b name;
85 ALTER TABLE tmp ADD COLUMN c text;
87 ALTER TABLE tmp ADD COLUMN d float8;
89 ALTER TABLE tmp ADD COLUMN e float4;
91 ALTER TABLE tmp ADD COLUMN f int2;
93 ALTER TABLE tmp ADD COLUMN g polygon;
95 ALTER TABLE tmp ADD COLUMN h abstime;
97 ALTER TABLE tmp ADD COLUMN i char;
99 ALTER TABLE tmp ADD COLUMN j abstime[];
101 ALTER TABLE tmp ADD COLUMN k int4;
103 ALTER TABLE tmp ADD COLUMN l tid;
105 ALTER TABLE tmp ADD COLUMN m xid;
107 ALTER TABLE tmp ADD COLUMN n oidvector;
109 --ALTER TABLE tmp ADD COLUMN o lock;
110 ALTER TABLE tmp ADD COLUMN p smgr;
112 ALTER TABLE tmp ADD COLUMN q point;
114 ALTER TABLE tmp ADD COLUMN r lseg;
116 ALTER TABLE tmp ADD COLUMN s path;
118 ALTER TABLE tmp ADD COLUMN t box;
120 ALTER TABLE tmp ADD COLUMN u tinterval;
122 ALTER TABLE tmp ADD COLUMN v timestamp;
124 ALTER TABLE tmp ADD COLUMN w interval;
126 ALTER TABLE tmp ADD COLUMN x float8[];
128 ALTER TABLE tmp ADD COLUMN y float4[];
130 ALTER TABLE tmp ADD COLUMN z int2[];
132 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
134 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
135 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
136 314159, '(1,1)', '512',
137 '1 2 3 4 5 6 7 8', 'magnetic disk', '(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)', '["epoch" "infinity"]',
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}');
147 -- rename - check on both non-temp and temp tables
149 CREATE TABLE tmp (regtable int);
150 CREATE TEMP TABLE tmp (tmptable int);
152 ALTER TABLE tmp RENAME TO tmp_new;
155 SELECT * FROM tmp_new;
157 ALTER TABLE tmp RENAME TO tmp_new2;
159 SELECT * FROM tmp; -- should fail
160 SELECT * FROM tmp_new;
161 SELECT * FROM tmp_new2;
167 -- ALTER TABLE ... RENAME on non-table relations
168 -- renaming indexes (FIXME: this should probably test the index's functionality)
169 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
170 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
172 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
173 ALTER TABLE tmp_view RENAME TO tmp_view_new;
175 -- hack to ensure we get an indexscan here
177 set enable_seqscan to off;
178 set enable_bitmapscan to off;
180 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
181 reset enable_seqscan;
182 reset enable_bitmapscan;
184 DROP VIEW tmp_view_new;
185 -- toast-like relation name
186 alter table stud_emp rename to pg_toast_stud_emp;
187 alter table pg_toast_stud_emp rename to stud_emp;
189 -- FOREIGN KEY CONSTRAINT adding TEST
191 CREATE TABLE tmp2 (a int primary key);
193 CREATE TABLE tmp3 (a int, b int);
195 CREATE TABLE tmp4 (a int, b int, unique(a,b));
197 CREATE TABLE tmp5 (a int, b int);
199 -- Insert rows into tmp2 (pktable)
200 INSERT INTO tmp2 values (1);
201 INSERT INTO tmp2 values (2);
202 INSERT INTO tmp2 values (3);
203 INSERT INTO tmp2 values (4);
205 -- Insert rows into tmp3
206 INSERT INTO tmp3 values (1,10);
207 INSERT INTO tmp3 values (1,20);
208 INSERT INTO tmp3 values (5,50);
210 -- Try (and fail) to add constraint due to invalid source columns
211 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
213 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
214 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
216 -- Try (and fail) to add constraint due to invalid data
217 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
219 -- Delete failing row
220 DELETE FROM tmp3 where a=5;
223 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
225 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
228 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
238 -- Foreign key adding test with mixed types
240 -- Note: these tables are TEMP to avoid name conflicts when this test
241 -- is run in parallel with foreign_key.sql.
243 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
244 INSERT INTO PKTABLE VALUES(42);
245 CREATE TEMP TABLE FKTABLE (ftest1 inet);
246 -- This next should fail, because int=inet does not exist
247 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
248 -- This should also fail for the same reason, but here we
249 -- give the column name
250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
252 -- This should succeed, even though they are different types,
253 -- because int=int8 exists and is a member of the integer opfamily
254 CREATE TEMP TABLE FKTABLE (ftest1 int8);
255 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
256 -- Check it actually works
257 INSERT INTO FKTABLE VALUES(42); -- should succeed
258 INSERT INTO FKTABLE VALUES(43); -- should fail
260 -- This should fail, because we'd have to cast numeric to int which is
261 -- not an implicit coercion (or use numeric=numeric, but that's not part
262 -- of the integer opfamily)
263 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
264 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
267 -- On the other hand, this should work because int implicitly promotes to
268 -- numeric, and we allow promotion on the FK side
269 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
270 INSERT INTO PKTABLE VALUES(42);
271 CREATE TEMP TABLE FKTABLE (ftest1 int);
272 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
273 -- Check it actually works
274 INSERT INTO FKTABLE VALUES(42); -- should succeed
275 INSERT INTO FKTABLE VALUES(43); -- should fail
279 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
280 PRIMARY KEY(ptest1, ptest2));
281 -- This should fail, because we just chose really odd types
282 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
283 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
285 -- Again, so should this...
286 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
287 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
288 references pktable(ptest1, ptest2);
290 -- This fails because we mixed up the column ordering
291 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
292 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
293 references pktable(ptest2, ptest1);
295 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
296 references pktable(ptest1, ptest2);
298 -- temp tables should go away by themselves, need not drop them.
300 -- test check constraint adding
302 create table atacc1 ( test int );
303 -- add a check constraint
304 alter table atacc1 add constraint atacc_test1 check (test>3);
306 insert into atacc1 (test) values (2);
308 insert into atacc1 (test) values (4);
311 -- let's do one where the check fails when added
312 create table atacc1 ( test int );
313 -- insert a soon to be failing row
314 insert into atacc1 (test) values (2);
315 -- add a check constraint (fails)
316 alter table atacc1 add constraint atacc_test1 check (test>3);
317 insert into atacc1 (test) values (4);
320 -- let's do one where the check fails because the column doesn't exist
321 create table atacc1 ( test int );
322 -- add a check constraint (fails)
323 alter table atacc1 add constraint atacc_test1 check (test1>3);
326 -- something a little more complicated
327 create table atacc1 ( test int, test2 int, test3 int);
328 -- add a check constraint (fails)
329 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
331 insert into atacc1 (test,test2,test3) values (4,4,2);
333 insert into atacc1 (test,test2,test3) values (4,4,5);
336 -- lets do some naming tests
337 create table atacc1 (test int check (test>3), test2 int);
338 alter table atacc1 add check (test2>test);
339 -- should fail for $2
340 insert into atacc1 (test2, test) values (3, 4);
343 -- inheritance related tests
344 create table atacc1 (test int);
345 create table atacc2 (test2 int);
346 create table atacc3 (test3 int) inherits (atacc1, atacc2);
347 alter table atacc2 add constraint foo check (test2>0);
348 -- fail and then succeed on atacc2
349 insert into atacc2 (test2) values (-3);
350 insert into atacc2 (test2) values (3);
351 -- fail and then succeed on atacc3
352 insert into atacc3 (test2) values (-3);
353 insert into atacc3 (test2) values (3);
358 -- same things with one created with INHERIT
359 create table atacc1 (test int);
360 create table atacc2 (test2 int);
361 create table atacc3 (test3 int) inherits (atacc1, atacc2);
362 alter table atacc3 no inherit atacc2;
364 alter table atacc3 no inherit atacc2;
365 -- make sure it really isn't a child
366 insert into atacc3 (test2) values (3);
367 select test2 from atacc2;
368 -- fail due to missing constraint
369 alter table atacc2 add constraint foo check (test2>0);
370 alter table atacc3 inherit atacc2;
371 -- fail due to missing column
372 alter table atacc3 rename test2 to testx;
373 alter table atacc3 inherit atacc2;
374 -- fail due to mismatched data type
375 alter table atacc3 add test2 bool;
376 alter table atacc3 inherit atacc2;
377 alter table atacc3 drop test2;
379 alter table atacc3 add test2 int;
380 update atacc3 set test2 = 4 where test2 is null;
381 alter table atacc3 add constraint foo check (test2>0);
382 alter table atacc3 inherit atacc2;
383 -- fail due to duplicates and circular inheritance
384 alter table atacc3 inherit atacc2;
385 alter table atacc2 inherit atacc3;
386 alter table atacc2 inherit atacc2;
387 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
388 select test2 from atacc2;
389 drop table atacc2 cascade;
392 -- adding only to a parent is disallowed as of 8.4
394 create table atacc1 (test int);
395 create table atacc2 (test2 int) inherits (atacc1);
397 alter table only atacc1 add constraint foo check (test>0);
399 alter table only atacc2 add constraint foo check (test>0);
400 -- check constraint not there on parent
401 insert into atacc1 (test) values (-3);
402 insert into atacc1 (test) values (3);
403 -- check constraint is there on child
404 insert into atacc2 (test) values (-3);
405 insert into atacc2 (test) values (3);
409 -- test unique constraint adding
411 create table atacc1 ( test int ) with oids;
412 -- add a unique constraint
413 alter table atacc1 add constraint atacc_test1 unique (test);
414 -- insert first value
415 insert into atacc1 (test) values (2);
417 insert into atacc1 (test) values (2);
419 insert into atacc1 (test) values (4);
420 -- try adding a unique oid constraint
421 alter table atacc1 add constraint atacc_oid1 unique(oid);
422 -- try to create duplicates via alter table using - should fail
423 alter table atacc1 alter column test type integer using 0;
426 -- let's do one where the unique constraint fails when added
427 create table atacc1 ( test int );
428 -- insert soon to be failing rows
429 insert into atacc1 (test) values (2);
430 insert into atacc1 (test) values (2);
431 -- add a unique constraint (fails)
432 alter table atacc1 add constraint atacc_test1 unique (test);
433 insert into atacc1 (test) values (3);
436 -- let's do one where the unique constraint fails
437 -- because the column doesn't exist
438 create table atacc1 ( test int );
439 -- add a unique constraint (fails)
440 alter table atacc1 add constraint atacc_test1 unique (test1);
443 -- something a little more complicated
444 create table atacc1 ( test int, test2 int);
445 -- add a unique constraint
446 alter table atacc1 add constraint atacc_test1 unique (test, test2);
447 -- insert initial value
448 insert into atacc1 (test,test2) values (4,4);
450 insert into atacc1 (test,test2) values (4,4);
451 -- should all succeed
452 insert into atacc1 (test,test2) values (4,5);
453 insert into atacc1 (test,test2) values (5,4);
454 insert into atacc1 (test,test2) values (5,5);
457 -- lets do some naming tests
458 create table atacc1 (test int, test2 int, unique(test));
459 alter table atacc1 add unique (test2);
460 -- should fail for @@ second one @@
461 insert into atacc1 (test2, test) values (3, 3);
462 insert into atacc1 (test2, test) values (2, 3);
465 -- test primary key constraint adding
467 create table atacc1 ( test int ) with oids;
468 -- add a primary key constraint
469 alter table atacc1 add constraint atacc_test1 primary key (test);
470 -- insert first value
471 insert into atacc1 (test) values (2);
473 insert into atacc1 (test) values (2);
475 insert into atacc1 (test) values (4);
476 -- inserting NULL should fail
477 insert into atacc1 (test) values(NULL);
478 -- try adding a second primary key (should fail)
479 alter table atacc1 add constraint atacc_oid1 primary key(oid);
480 -- drop first primary key constraint
481 alter table atacc1 drop constraint atacc_test1 restrict;
482 -- try adding a primary key on oid (should succeed)
483 alter table atacc1 add constraint atacc_oid1 primary key(oid);
486 -- let's do one where the primary key constraint fails when added
487 create table atacc1 ( test int );
488 -- insert soon to be failing rows
489 insert into atacc1 (test) values (2);
490 insert into atacc1 (test) values (2);
491 -- add a primary key (fails)
492 alter table atacc1 add constraint atacc_test1 primary key (test);
493 insert into atacc1 (test) values (3);
496 -- let's do another one where the primary key constraint fails when added
497 create table atacc1 ( test int );
498 -- insert soon to be failing row
499 insert into atacc1 (test) values (NULL);
500 -- add a primary key (fails)
501 alter table atacc1 add constraint atacc_test1 primary key (test);
502 insert into atacc1 (test) values (3);
505 -- let's do one where the primary key constraint fails
506 -- because the column doesn't exist
507 create table atacc1 ( test int );
508 -- add a primary key constraint (fails)
509 alter table atacc1 add constraint atacc_test1 primary key (test1);
512 -- adding a new column as primary key to a non-empty table.
513 -- should fail unless the column has a non-null default value.
514 create table atacc1 ( test int );
515 insert into atacc1 (test) values (0);
516 -- add a primary key column without a default (fails).
517 alter table atacc1 add column test2 int primary key;
518 -- now add a primary key column with a default (succeeds).
519 alter table atacc1 add column test2 int default 0 primary key;
522 -- something a little more complicated
523 create table atacc1 ( test int, test2 int);
524 -- add a primary key constraint
525 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
526 -- try adding a second primary key - should fail
527 alter table atacc1 add constraint atacc_test2 primary key (test);
528 -- insert initial value
529 insert into atacc1 (test,test2) values (4,4);
531 insert into atacc1 (test,test2) values (4,4);
532 insert into atacc1 (test,test2) values (NULL,3);
533 insert into atacc1 (test,test2) values (3, NULL);
534 insert into atacc1 (test,test2) values (NULL,NULL);
535 -- should all succeed
536 insert into atacc1 (test,test2) values (4,5);
537 insert into atacc1 (test,test2) values (5,4);
538 insert into atacc1 (test,test2) values (5,5);
541 -- lets do some naming tests
542 create table atacc1 (test int, test2 int, primary key(test));
543 -- only first should succeed
544 insert into atacc1 (test2, test) values (3, 3);
545 insert into atacc1 (test2, test) values (2, 3);
546 insert into atacc1 (test2, test) values (1, NULL);
549 -- alter table / alter column [set/drop] not null tests
550 -- try altering system catalogs, should fail
551 alter table pg_class alter column relname drop not null;
552 alter table pg_class alter relname set not null;
554 -- try altering non-existent table, should fail
555 alter table non_existent alter column bar set not null;
556 alter table non_existent alter column bar drop not null;
558 -- test setting columns to null and not null and vice versa
559 -- test checking for null values and primary key
560 create table atacc1 (test int not null) with oids;
561 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
562 alter table atacc1 alter column test drop not null;
563 alter table atacc1 drop constraint "atacc1_pkey";
564 alter table atacc1 alter column test drop not null;
565 insert into atacc1 values (null);
566 alter table atacc1 alter test set not null;
568 alter table atacc1 alter test set not null;
570 -- try altering a non-existent column, should fail
571 alter table atacc1 alter bar set not null;
572 alter table atacc1 alter bar drop not null;
574 -- try altering the oid column, should fail
575 alter table atacc1 alter oid set not null;
576 alter table atacc1 alter oid drop not null;
578 -- try creating a view and altering that, should fail
579 create view myview as select * from atacc1;
580 alter table myview alter column test drop not null;
581 alter table myview alter column test set not null;
587 create table parent (a int);
588 create table child (b varchar(255)) inherits (parent);
590 alter table parent alter a set not null;
591 insert into parent values (NULL);
592 insert into child (a, b) values (NULL, 'foo');
593 alter table parent alter a drop not null;
594 insert into parent values (NULL);
595 insert into child (a, b) values (NULL, 'foo');
596 alter table only parent alter a set not null;
597 alter table child alter a set not null;
599 alter table only parent alter a set not null;
600 insert into parent values (NULL);
601 alter table child alter a set not null;
602 insert into child (a, b) values (NULL, 'foo');
604 alter table child alter a set not null;
605 insert into child (a, b) values (NULL, 'foo');
609 -- test setting and removing default values
610 create table def_test (
612 c2 text default 'initial_default'
614 insert into def_test default values;
615 alter table def_test alter column c1 drop default;
616 insert into def_test default values;
617 alter table def_test alter column c2 drop default;
618 insert into def_test default values;
619 alter table def_test alter column c1 set default 10;
620 alter table def_test alter column c2 set default 'new_default';
621 insert into def_test default values;
622 select * from def_test;
624 -- set defaults to an incorrect type: this should fail
625 alter table def_test alter column c1 set default 'wrong_datatype';
626 alter table def_test alter column c2 set default 20;
628 -- set defaults on a non-existent column: this should fail
629 alter table def_test alter column c3 set default 30;
631 -- set defaults on views: we need to create a view, add a rule
632 -- to allow insertions into it, and then alter the view to add
634 create view def_view_test as select * from def_test;
635 create rule def_view_test_ins as
636 on insert to def_view_test
637 do instead insert into def_test select new.*;
638 insert into def_view_test default values;
639 alter table def_view_test alter column c1 set default 45;
640 insert into def_view_test default values;
641 alter table def_view_test alter column c2 set default 'view_default';
642 insert into def_view_test default values;
643 select * from def_view_test;
645 drop rule def_view_test_ins on def_view_test;
646 drop view def_view_test;
649 -- alter table / drop column tests
650 -- try altering system catalogs, should fail
651 alter table pg_class drop column relname;
653 -- try altering non-existent table, should fail
654 alter table nosuchtable drop column bar;
656 -- test dropping columns
657 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
658 insert into atacc1 values (1, 2, 3, 4);
659 alter table atacc1 drop a;
660 alter table atacc1 drop a;
663 select * from atacc1;
664 select * from atacc1 order by a;
665 select * from atacc1 order by "........pg.dropped.1........";
666 select * from atacc1 group by a;
667 select * from atacc1 group by "........pg.dropped.1........";
668 select atacc1.* from atacc1;
669 select a from atacc1;
670 select atacc1.a from atacc1;
671 select b,c,d from atacc1;
672 select a,b,c,d from atacc1;
673 select * from atacc1 where a = 1;
674 select "........pg.dropped.1........" from atacc1;
675 select atacc1."........pg.dropped.1........" from atacc1;
676 select "........pg.dropped.1........",b,c,d from atacc1;
677 select * from atacc1 where "........pg.dropped.1........" = 1;
680 update atacc1 set a = 3;
681 update atacc1 set b = 2 where a = 3;
682 update atacc1 set "........pg.dropped.1........" = 3;
683 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
686 insert into atacc1 values (10, 11, 12, 13);
687 insert into atacc1 values (default, 11, 12, 13);
688 insert into atacc1 values (11, 12, 13);
689 insert into atacc1 (a) values (10);
690 insert into atacc1 (a) values (default);
691 insert into atacc1 (a,b,c,d) values (10,11,12,13);
692 insert into atacc1 (a,b,c,d) values (default,11,12,13);
693 insert into atacc1 (b,c,d) values (11,12,13);
694 insert into atacc1 ("........pg.dropped.1........") values (10);
695 insert into atacc1 ("........pg.dropped.1........") values (default);
696 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
697 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
700 delete from atacc1 where a = 3;
701 delete from atacc1 where "........pg.dropped.1........" = 3;
704 -- try dropping a non-existent column, should fail
705 alter table atacc1 drop bar;
707 -- try dropping the oid column, should succeed
708 alter table atacc1 drop oid;
710 -- try dropping the xmin column, should fail
711 alter table atacc1 drop xmin;
713 -- try creating a view and altering that, should fail
714 create view myview as select * from atacc1;
715 select * from myview;
716 alter table myview drop d;
719 -- test some commands to make sure they fail on the dropped column
721 analyze atacc1("........pg.dropped.1........");
722 vacuum analyze atacc1(a);
723 vacuum analyze atacc1("........pg.dropped.1........");
724 comment on column atacc1.a is 'testing';
725 comment on column atacc1."........pg.dropped.1........" is 'testing';
726 alter table atacc1 alter a set storage plain;
727 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
728 alter table atacc1 alter a set statistics 0;
729 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
730 alter table atacc1 alter a set default 3;
731 alter table atacc1 alter "........pg.dropped.1........" set default 3;
732 alter table atacc1 alter a drop default;
733 alter table atacc1 alter "........pg.dropped.1........" drop default;
734 alter table atacc1 alter a set not null;
735 alter table atacc1 alter "........pg.dropped.1........" set not null;
736 alter table atacc1 alter a drop not null;
737 alter table atacc1 alter "........pg.dropped.1........" drop not null;
738 alter table atacc1 rename a to x;
739 alter table atacc1 rename "........pg.dropped.1........" to x;
740 alter table atacc1 add primary key(a);
741 alter table atacc1 add primary key("........pg.dropped.1........");
742 alter table atacc1 add unique(a);
743 alter table atacc1 add unique("........pg.dropped.1........");
744 alter table atacc1 add check (a > 3);
745 alter table atacc1 add check ("........pg.dropped.1........" > 3);
746 create table atacc2 (id int4 unique);
747 alter table atacc1 add foreign key (a) references atacc2(id);
748 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
749 alter table atacc2 add foreign key (id) references atacc1(a);
750 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
752 create index "testing_idx" on atacc1(a);
753 create index "testing_idx" on atacc1("........pg.dropped.1........");
755 -- test create as and select into
756 insert into atacc1 values (21, 22, 23);
757 create table test1 as select * from atacc1;
760 select * into test2 from atacc1;
764 -- try dropping all columns
765 alter table atacc1 drop c;
766 alter table atacc1 drop d;
767 alter table atacc1 drop b;
768 select * from atacc1;
773 create table parent (a int, b int, c int);
774 insert into parent values (1, 2, 3);
775 alter table parent drop a;
776 create table child (d varchar(255)) inherits (parent);
777 insert into child values (12, 13, 'testing');
779 select * from parent;
781 alter table parent drop c;
782 select * from parent;
789 create table test (a int4, b int4, c int4);
790 insert into test values (1,2,3);
791 alter table test drop a;
793 copy test(a) to stdout;
794 copy test("........pg.dropped.1........") to stdout;
795 copy test from stdin;
799 copy test from stdin;
803 copy test(a) from stdin;
804 copy test("........pg.dropped.1........") from stdin;
805 copy test(b,c) from stdin;
813 create table dropColumn (a int, b int, e int);
814 create table dropColumnChild (c int) inherits (dropColumn);
815 create table dropColumnAnother (d int) inherits (dropColumnChild);
817 -- these two should fail
818 alter table dropColumnchild drop column a;
819 alter table only dropColumnChild drop column b;
823 -- these three should work
824 alter table only dropColumn drop column e;
825 alter table dropColumnChild drop column c;
826 alter table dropColumn drop column a;
828 create table renameColumn (a int);
829 create table renameColumnChild (b int) inherits (renameColumn);
830 create table renameColumnAnother (c int) inherits (renameColumnChild);
832 -- these three should fail
833 alter table renameColumnChild rename column a to d;
834 alter table only renameColumnChild rename column a to d;
835 alter table only renameColumn rename column a to d;
838 alter table renameColumn rename column a to d;
839 alter table renameColumnChild rename column b to a;
842 alter table renameColumn add column w int;
845 alter table only renameColumn add column x int;
848 -- Test corner cases in dropping of inherited columns
850 create table p1 (f1 int, f2 int);
851 create table c1 (f1 int not null) inherits(p1);
853 -- should be rejected since c1.f1 is inherited
854 alter table c1 drop column f1;
856 alter table p1 drop column f1;
857 -- c1.f1 is still there, but no longer inherited
859 alter table c1 drop column f1;
862 drop table p1 cascade;
864 create table p1 (f1 int, f2 int);
865 create table c1 () inherits(p1);
867 -- should be rejected since c1.f1 is inherited
868 alter table c1 drop column f1;
869 alter table p1 drop column f1;
870 -- c1.f1 is dropped now, since there is no local definition for it
873 drop table p1 cascade;
875 create table p1 (f1 int, f2 int);
876 create table c1 () inherits(p1);
878 -- should be rejected since c1.f1 is inherited
879 alter table c1 drop column f1;
880 alter table only p1 drop column f1;
881 -- c1.f1 is NOT dropped, but must now be considered non-inherited
882 alter table c1 drop column f1;
884 drop table p1 cascade;
886 create table p1 (f1 int, f2 int);
887 create table c1 (f1 int not null) inherits(p1);
889 -- should be rejected since c1.f1 is inherited
890 alter table c1 drop column f1;
891 alter table only p1 drop column f1;
892 -- c1.f1 is still there, but no longer inherited
893 alter table c1 drop column f1;
895 drop table p1 cascade;
897 create table p1(id int, name text);
898 create table p2(id2 int, name text, height int);
899 create table c1(age int) inherits(p1,p2);
900 create table gc1() inherits (c1);
902 select relname, attname, attinhcount, attislocal
903 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
904 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
905 order by relname, attnum;
908 alter table only p1 drop column name;
909 -- should work. Now c1.name is local and inhcount is 0.
910 alter table p2 drop column name;
911 -- should be rejected since its inherited
912 alter table gc1 drop column name;
913 -- should work, and drop gc1.name along
914 alter table c1 drop column name;
915 -- should fail: column does not exist
916 alter table gc1 drop column name;
917 -- should work and drop the attribute in all tables
918 alter table p2 drop column height;
921 create table dropColumnExists ();
922 alter table dropColumnExists drop column non_existing; --fail
923 alter table dropColumnExists drop column if exists non_existing; --succeed
925 select relname, attname, attinhcount, attislocal
926 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
927 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
928 order by relname, attnum;
930 drop table p1, p2 cascade;
933 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
935 create table altstartwith (col integer) with oids;
937 insert into altstartwith values (1);
939 select oid > 0, * from altstartwith;
941 alter table altstartwith set without oids;
943 select oid > 0, * from altstartwith; -- fails
944 select * from altstartwith;
946 alter table altstartwith set with oids;
948 select oid > 0, * from altstartwith;
950 drop table altstartwith;
952 -- Check inheritance cases
953 create table altwithoid (col integer) with oids;
955 -- Inherits parents oid column anyway
956 create table altinhoid () inherits (altwithoid) without oids;
958 insert into altinhoid values (1);
960 select oid > 0, * from altwithoid;
961 select oid > 0, * from altinhoid;
963 alter table altwithoid set without oids;
965 select oid > 0, * from altwithoid; -- fails
966 select oid > 0, * from altinhoid; -- fails
967 select * from altwithoid;
968 select * from altinhoid;
970 alter table altwithoid set with oids;
972 select oid > 0, * from altwithoid;
973 select oid > 0, * from altinhoid;
975 drop table altwithoid cascade;
977 create table altwithoid (col integer) without oids;
979 -- child can have local oid column
980 create table altinhoid () inherits (altwithoid) with oids;
982 insert into altinhoid values (1);
984 select oid > 0, * from altwithoid; -- fails
985 select oid > 0, * from altinhoid;
987 alter table altwithoid set with oids;
989 select oid > 0, * from altwithoid;
990 select oid > 0, * from altinhoid;
992 -- the child's local definition should remain
993 alter table altwithoid set without oids;
995 select oid > 0, * from altwithoid; -- fails
996 select oid > 0, * from altinhoid;
998 drop table altwithoid cascade;
1000 -- test renumbering of child-table columns in inherited operations
1002 create table p1 (f1 int);
1003 create table c1 (f2 text, f3 int) inherits (p1);
1005 alter table p1 add column a1 int check (a1 > 0);
1006 alter table p1 add column f2 text;
1008 insert into p1 values (1,2,'abc');
1009 insert into c1 values(11,'xyz',33,0); -- should fail
1010 insert into c1 values(11,'xyz',33,22);
1013 update p1 set a1 = a1 + 1, f2 = upper(f2);
1016 drop table p1 cascade;
1018 -- test that operations with a dropped column do not try to reference
1021 create domain mytype as text;
1022 create temp table foo (f1 text, f2 mytype, f3 text);
1024 insert into foo values('bb','cc','dd');
1027 drop domain mytype cascade;
1030 insert into foo values('qq','rr');
1032 update foo set f3 = 'zz';
1034 select f3,max(f1) from foo group by f3;
1036 -- Simple tests for alter table column type
1037 alter table foo alter f1 TYPE integer; -- fails
1038 alter table foo alter f1 TYPE varchar(10);
1040 create table anothertab (atcol1 serial8, atcol2 boolean,
1041 constraint anothertab_chk check (atcol1 <= 3));
1043 insert into anothertab (atcol1, atcol2) values (default, true);
1044 insert into anothertab (atcol1, atcol2) values (default, false);
1045 select * from anothertab;
1047 alter table anothertab alter column atcol1 type boolean; -- fails
1048 alter table anothertab alter column atcol1 type integer;
1050 select * from anothertab;
1052 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1053 insert into anothertab (atcol1, atcol2) values (default, null);
1055 select * from anothertab;
1057 alter table anothertab alter column atcol2 type text
1058 using case when atcol2 is true then 'IT WAS TRUE'
1059 when atcol2 is false then 'IT WAS FALSE'
1060 else 'IT WAS NULL!' end;
1062 select * from anothertab;
1063 alter table anothertab alter column atcol1 type boolean
1064 using case when atcol1 % 2 = 0 then true else false end; -- fails
1065 alter table anothertab alter column atcol1 drop default;
1066 alter table anothertab alter column atcol1 type boolean
1067 using case when atcol1 % 2 = 0 then true else false end; -- fails
1068 alter table anothertab drop constraint anothertab_chk;
1069 alter table anothertab drop constraint anothertab_chk; -- fails
1070 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1072 alter table anothertab alter column atcol1 type boolean
1073 using case when atcol1 % 2 = 0 then true else false end;
1075 select * from anothertab;
1077 drop table anothertab;
1079 create table another (f1 int, f2 text);
1081 insert into another values(1, 'one');
1082 insert into another values(2, 'two');
1083 insert into another values(3, 'three');
1085 select * from another;
1088 alter f1 type text using f2 || ' more',
1089 alter f2 type bigint using f1 * 10;
1091 select * from another;
1096 create table tab1 (a int, b text);
1097 create table tab2 (x int, y tab1);
1098 alter table tab1 alter column b type varchar; -- fails
1103 drop type lockmodes;
1104 create type lockmodes as enum (
1108 ,'ShareUpdateExclusiveLock'
1110 ,'ShareRowExclusiveLock'
1112 ,'AccessExclusiveLock'
1116 create or replace view my_locks as
1117 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1118 from pg_locks l join pg_class c on l.relation = c.oid
1119 where virtualtransaction = (
1120 select virtualtransaction
1122 where transactionid = txid_current()::integer)
1123 and locktype = 'relation'
1124 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1125 and c.relname != 'my_locks'
1128 create table alterlock (f1 int primary key, f2 text);
1130 -- share update exclusive
1131 begin; alter table alterlock alter column f2 set statistics 150;
1132 select * from my_locks order by 1;
1135 begin; alter table alterlock cluster on alterlock_pkey;
1136 select * from my_locks order by 1;
1139 begin; alter table alterlock set without cluster;
1140 select * from my_locks order by 1;
1143 begin; alter table alterlock set (fillfactor = 100);
1144 select * from my_locks order by 1;
1147 begin; alter table alterlock reset (fillfactor);
1148 select * from my_locks order by 1;
1151 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1152 select * from my_locks order by 1;
1155 begin; alter table alterlock set (autovacuum_enabled = off);
1156 select * from my_locks order by 1;
1159 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1160 select * from my_locks order by 1;
1163 begin; alter table alterlock alter column f2 set storage extended;
1164 select * from my_locks order by 1;
1167 -- share row exclusive
1168 begin; alter table alterlock alter column f2 set default 'x';
1169 select * from my_locks order by 1;
1173 drop table alterlock;
1175 drop type lockmodes;
1180 create function test_strict(text) returns text as
1181 'select coalesce($1, ''got passed a null'');'
1182 language sql returns null on null input;
1183 select test_strict(NULL);
1184 alter function test_strict(text) called on null input;
1185 select test_strict(NULL);
1187 create function non_strict(text) returns text as
1188 'select coalesce($1, ''got passed a null'');'
1189 language sql called on null input;
1190 select non_strict(NULL);
1191 alter function non_strict(text) returns null on null input;
1192 select non_strict(NULL);
1195 -- alter object set schema
1198 create schema alter1;
1199 create schema alter2;
1201 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1203 create view alter1.v1 as select * from alter1.t1;
1205 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1207 create domain alter1.posint integer check (value > 0);
1209 create type alter1.ctype as (f1 int, f2 text);
1211 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1212 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1214 create operator alter1.=(procedure = alter1.same, leftarg = alter1.ctype, rightarg = alter1.ctype);
1216 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1217 operator 1 alter1.=(alter1.ctype, alter1.ctype);
1219 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1221 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1222 create text search configuration alter1.cfg(parser = alter1.prs);
1223 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1224 create text search dictionary alter1.dict(template = alter1.tmpl);
1226 insert into alter1.t1(f2) values(11);
1227 insert into alter1.t1(f2) values(12);
1229 alter table alter1.t1 set schema alter2;
1230 alter table alter1.v1 set schema alter2;
1231 alter function alter1.plus1(int) set schema alter2;
1232 alter domain alter1.posint set schema alter2;
1233 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1234 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1235 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1236 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1237 alter type alter1.ctype set schema alter2;
1238 alter conversion alter1.ascii_to_utf8 set schema alter2;
1239 alter text search parser alter1.prs set schema alter2;
1240 alter text search configuration alter1.cfg set schema alter2;
1241 alter text search template alter1.tmpl set schema alter2;
1242 alter text search dictionary alter1.dict set schema alter2;
1244 -- this should succeed because nothing is left in alter1
1247 insert into alter2.t1(f2) values(13);
1248 insert into alter2.t1(f2) values(14);
1250 select * from alter2.t1;
1252 select * from alter2.v1;
1254 select alter2.plus1(41);
1257 drop schema alter2 cascade;
1263 CREATE TYPE test_type AS (a int);
1266 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
1268 ALTER TYPE test_type ADD ATTRIBUTE b text;
1271 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
1273 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
1276 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
1279 ALTER TYPE test_type DROP ATTRIBUTE b;
1282 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
1284 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
1286 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
1289 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
1290 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
1293 DROP TYPE test_type;
1295 CREATE TYPE test_type1 AS (a int, b text);
1296 CREATE TABLE test_tbl1 (x int, y test_type1);
1297 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
1299 CREATE TYPE test_type2 AS (a int, b text);
1300 CREATE TABLE test_tbl2 OF test_type2;
1304 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
1305 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
1309 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
1310 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
1314 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
1315 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
1319 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
1320 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
1324 CREATE TYPE test_type_empty AS ();
1325 DROP TYPE test_type_empty;