6 CREATE TABLE tmp (initial int4);
8 ALTER TABLE tmp ADD COLUMN a int4;
10 ALTER TABLE tmp ADD COLUMN b name;
12 ALTER TABLE tmp ADD COLUMN c text;
14 ALTER TABLE tmp ADD COLUMN d float8;
16 ALTER TABLE tmp ADD COLUMN e float4;
18 ALTER TABLE tmp ADD COLUMN f int2;
20 ALTER TABLE tmp ADD COLUMN g polygon;
22 ALTER TABLE tmp ADD COLUMN h abstime;
24 ALTER TABLE tmp ADD COLUMN i char;
26 ALTER TABLE tmp ADD COLUMN j abstime[];
28 ALTER TABLE tmp ADD COLUMN k int4;
30 ALTER TABLE tmp ADD COLUMN l tid;
32 ALTER TABLE tmp ADD COLUMN m xid;
34 ALTER TABLE tmp ADD COLUMN n oidvector;
36 --ALTER TABLE tmp ADD COLUMN o lock;
37 ALTER TABLE tmp ADD COLUMN p smgr;
39 ALTER TABLE tmp ADD COLUMN q point;
41 ALTER TABLE tmp ADD COLUMN r lseg;
43 ALTER TABLE tmp ADD COLUMN s path;
45 ALTER TABLE tmp ADD COLUMN t box;
47 ALTER TABLE tmp ADD COLUMN u tinterval;
49 ALTER TABLE tmp ADD COLUMN v timestamp;
51 ALTER TABLE tmp ADD COLUMN w interval;
53 ALTER TABLE tmp ADD COLUMN x float8[];
55 ALTER TABLE tmp ADD COLUMN y float4[];
57 ALTER TABLE tmp ADD COLUMN z int2[];
59 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
61 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
62 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
63 314159, '(1,1)', '512',
64 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
65 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
66 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
72 -- the wolf bug - schema mods caused inconsistent row descriptors
77 ALTER TABLE tmp ADD COLUMN a int4;
79 ALTER TABLE tmp ADD COLUMN b name;
81 ALTER TABLE tmp ADD COLUMN c text;
83 ALTER TABLE tmp ADD COLUMN d float8;
85 ALTER TABLE tmp ADD COLUMN e float4;
87 ALTER TABLE tmp ADD COLUMN f int2;
89 ALTER TABLE tmp ADD COLUMN g polygon;
91 ALTER TABLE tmp ADD COLUMN h abstime;
93 ALTER TABLE tmp ADD COLUMN i char;
95 ALTER TABLE tmp ADD COLUMN j abstime[];
97 ALTER TABLE tmp ADD COLUMN k int4;
99 ALTER TABLE tmp ADD COLUMN l tid;
101 ALTER TABLE tmp ADD COLUMN m xid;
103 ALTER TABLE tmp ADD COLUMN n oidvector;
105 --ALTER TABLE tmp ADD COLUMN o lock;
106 ALTER TABLE tmp ADD COLUMN p smgr;
108 ALTER TABLE tmp ADD COLUMN q point;
110 ALTER TABLE tmp ADD COLUMN r lseg;
112 ALTER TABLE tmp ADD COLUMN s path;
114 ALTER TABLE tmp ADD COLUMN t box;
116 ALTER TABLE tmp ADD COLUMN u tinterval;
118 ALTER TABLE tmp ADD COLUMN v timestamp;
120 ALTER TABLE tmp ADD COLUMN w interval;
122 ALTER TABLE tmp ADD COLUMN x float8[];
124 ALTER TABLE tmp ADD COLUMN y float4[];
126 ALTER TABLE tmp ADD COLUMN z int2[];
128 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
130 VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
131 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
132 314159, '(1,1)', '512',
133 '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
134 '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
135 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
144 -- should preserve indices, which we can check by seeing if a SELECT
145 -- chooses an indexscan; however, in the absence of vacuum statistics
146 -- it might not. Therefore, vacuum first.
148 VACUUM ANALYZE tenk1;
150 ALTER TABLE tenk1 RENAME TO ten_k;
153 SELECT unique1 FROM ten_k WHERE unique1 < 20;
156 SELECT unique2 FROM ten_k WHERE unique2 < 20;
158 -- 100 values, sorted
159 SELECT hundred FROM ten_k WHERE hundred = 50;
161 ALTER TABLE ten_k RENAME TO tenk1;
164 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
166 -- ALTER TABLE ... RENAME on non-table relations
167 -- renaming indexes (FIXME: this should probably test the index's functionality)
168 ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1;
169 ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1;
171 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
172 ALTER TABLE tmp_view RENAME TO tmp_view_new;
174 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
175 DROP VIEW tmp_view_new;
176 -- renaming sequences
177 CREATE SEQUENCE foo_seq;
178 ALTER TABLE foo_seq RENAME TO foo_seq_new;
179 SELECT * FROM foo_seq_new;
180 DROP SEQUENCE foo_seq_new;
181 -- toast-like relation name
182 alter table stud_emp rename to pg_toast_stud_emp;
183 alter table pg_toast_stud_emp rename to stud_emp;
185 -- FOREIGN KEY CONSTRAINT adding TEST
187 CREATE TABLE tmp2 (a int primary key);
189 CREATE TABLE tmp3 (a int, b int);
191 CREATE TABLE tmp4 (a int, b int, unique(a,b));
193 CREATE TABLE tmp5 (a int, b int);
195 -- Insert rows into tmp2 (pktable)
196 INSERT INTO tmp2 values (1);
197 INSERT INTO tmp2 values (2);
198 INSERT INTO tmp2 values (3);
199 INSERT INTO tmp2 values (4);
201 -- Insert rows into tmp3
202 INSERT INTO tmp3 values (1,10);
203 INSERT INTO tmp3 values (1,20);
204 INSERT INTO tmp3 values (5,50);
206 -- Try (and fail) to add constraint due to invalid source columns
207 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
209 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
210 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
212 -- Try (and fail) to add constraint due to invalid data
213 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
215 -- Delete failing row
216 DELETE FROM tmp3 where a=5;
219 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
221 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
224 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
234 -- Foreign key adding test with mixed types
236 -- Note: these tables are TEMP to avoid name conflicts when this test
237 -- is run in parallel with foreign_key.sql.
239 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
240 CREATE TEMP TABLE FKTABLE (ftest1 inet);
241 -- This next should fail, because inet=int does not exist
242 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
243 -- This should also fail for the same reason, but here we
244 -- give the column name
245 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
246 -- This should succeed, even though they are different types
247 -- because varchar=int does exist
249 CREATE TEMP TABLE FKTABLE (ftest1 varchar);
250 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
252 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
253 DROP TABLE pktable cascade;
256 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
257 PRIMARY KEY(ptest1, ptest2));
258 -- This should fail, because we just chose really odd types
259 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
260 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
262 -- Again, so should this...
263 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
264 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
265 references pktable(ptest1, ptest2);
267 -- This fails because we mixed up the column ordering
268 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
269 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
270 references pktable(ptest2, ptest1);
272 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
273 references pktable(ptest1, ptest2);
275 -- temp tables should go away by themselves, need not drop them.
277 -- test check constraint adding
279 create table atacc1 ( test int );
280 -- add a check constraint
281 alter table atacc1 add constraint atacc_test1 check (test>3);
283 insert into atacc1 (test) values (2);
285 insert into atacc1 (test) values (4);
288 -- let's do one where the check fails when added
289 create table atacc1 ( test int );
290 -- insert a soon to be failing row
291 insert into atacc1 (test) values (2);
292 -- add a check constraint (fails)
293 alter table atacc1 add constraint atacc_test1 check (test>3);
294 insert into atacc1 (test) values (4);
297 -- let's do one where the check fails because the column doesn't exist
298 create table atacc1 ( test int );
299 -- add a check constraint (fails)
300 alter table atacc1 add constraint atacc_test1 check (test1>3);
303 -- something a little more complicated
304 create table atacc1 ( test int, test2 int, test3 int);
305 -- add a check constraint (fails)
306 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
308 insert into atacc1 (test,test2,test3) values (4,4,2);
310 insert into atacc1 (test,test2,test3) values (4,4,5);
313 -- lets do some naming tests
314 create table atacc1 (test int check (test>3), test2 int);
315 alter table atacc1 add check (test2>test);
316 -- should fail for $2
317 insert into atacc1 (test2, test) values (3, 4);
320 -- inheritance related tests
321 create table atacc1 (test int);
322 create table atacc2 (test2 int);
323 create table atacc3 (test3 int) inherits (atacc1, atacc2);
324 alter table atacc2 add constraint foo check (test2>0);
325 -- fail and then succeed on atacc2
326 insert into atacc2 (test2) values (-3);
327 insert into atacc2 (test2) values (3);
328 -- fail and then succeed on atacc3
329 insert into atacc3 (test2) values (-3);
330 insert into atacc3 (test2) values (3);
335 -- let's try only to add only to the parent
337 create table atacc1 (test int);
338 create table atacc2 (test2 int);
339 create table atacc3 (test3 int) inherits (atacc1, atacc2);
340 alter table only atacc2 add constraint foo check (test2>0);
341 -- fail and then succeed on atacc2
342 insert into atacc2 (test2) values (-3);
343 insert into atacc2 (test2) values (3);
344 -- both succeed on atacc3
345 insert into atacc3 (test2) values (-3);
346 insert into atacc3 (test2) values (3);
351 -- test unique constraint adding
353 create table atacc1 ( test int );
354 -- add a unique constraint
355 alter table atacc1 add constraint atacc_test1 unique (test);
356 -- insert first value
357 insert into atacc1 (test) values (2);
359 insert into atacc1 (test) values (2);
361 insert into atacc1 (test) values (4);
362 -- try adding a unique oid constraint
363 alter table atacc1 add constraint atacc_oid1 unique(oid);
366 -- let's do one where the unique constraint fails when added
367 create table atacc1 ( test int );
368 -- insert soon to be failing rows
369 insert into atacc1 (test) values (2);
370 insert into atacc1 (test) values (2);
371 -- add a unique constraint (fails)
372 alter table atacc1 add constraint atacc_test1 unique (test);
373 insert into atacc1 (test) values (3);
376 -- let's do one where the unique constraint fails
377 -- because the column doesn't exist
378 create table atacc1 ( test int );
379 -- add a unique constraint (fails)
380 alter table atacc1 add constraint atacc_test1 unique (test1);
383 -- something a little more complicated
384 create table atacc1 ( test int, test2 int);
385 -- add a unique constraint
386 alter table atacc1 add constraint atacc_test1 unique (test, test2);
387 -- insert initial value
388 insert into atacc1 (test,test2) values (4,4);
390 insert into atacc1 (test,test2) values (4,4);
391 -- should all succeed
392 insert into atacc1 (test,test2) values (4,5);
393 insert into atacc1 (test,test2) values (5,4);
394 insert into atacc1 (test,test2) values (5,5);
397 -- lets do some naming tests
398 create table atacc1 (test int, test2 int, unique(test));
399 alter table atacc1 add unique (test2);
400 -- should fail for @@ second one @@
401 insert into atacc1 (test2, test) values (3, 3);
402 insert into atacc1 (test2, test) values (2, 3);
405 -- test primary key constraint adding
407 create table atacc1 ( test int );
408 -- add a primary key constraint
409 alter table atacc1 add constraint atacc_test1 primary key (test);
410 -- insert first value
411 insert into atacc1 (test) values (2);
413 insert into atacc1 (test) values (2);
415 insert into atacc1 (test) values (4);
416 -- inserting NULL should fail
417 insert into atacc1 (test) values(NULL);
418 -- try adding a second primary key (should fail)
419 alter table atacc1 add constraint atacc_oid1 primary key(oid);
420 -- drop first primary key constraint
421 alter table atacc1 drop constraint atacc_test1 restrict;
422 -- try adding a primary key on oid (should succeed)
423 alter table atacc1 add constraint atacc_oid1 primary key(oid);
426 -- let's do one where the primary key 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 primary key (fails)
432 alter table atacc1 add constraint atacc_test1 primary key (test);
433 insert into atacc1 (test) values (3);
436 -- let's do another one where the primary key constraint fails when added
437 create table atacc1 ( test int );
438 -- insert soon to be failing row
439 insert into atacc1 (test) values (NULL);
440 -- add a primary key (fails)
441 alter table atacc1 add constraint atacc_test1 primary key (test);
442 insert into atacc1 (test) values (3);
445 -- let's do one where the primary key constraint fails
446 -- because the column doesn't exist
447 create table atacc1 ( test int );
448 -- add a primary key constraint (fails)
449 alter table atacc1 add constraint atacc_test1 primary key (test1);
452 -- something a little more complicated
453 create table atacc1 ( test int, test2 int);
454 -- add a primary key constraint
455 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
456 -- try adding a second primary key - should fail
457 alter table atacc1 add constraint atacc_test2 primary key (test);
458 -- insert initial value
459 insert into atacc1 (test,test2) values (4,4);
461 insert into atacc1 (test,test2) values (4,4);
462 insert into atacc1 (test,test2) values (NULL,3);
463 insert into atacc1 (test,test2) values (3, NULL);
464 insert into atacc1 (test,test2) values (NULL,NULL);
465 -- should all succeed
466 insert into atacc1 (test,test2) values (4,5);
467 insert into atacc1 (test,test2) values (5,4);
468 insert into atacc1 (test,test2) values (5,5);
471 -- lets do some naming tests
472 create table atacc1 (test int, test2 int, primary key(test));
473 -- only first should succeed
474 insert into atacc1 (test2, test) values (3, 3);
475 insert into atacc1 (test2, test) values (2, 3);
476 insert into atacc1 (test2, test) values (1, NULL);
479 -- alter table / alter column [set/drop] not null tests
480 -- try altering system catalogs, should fail
481 alter table pg_class alter column relname drop not null;
482 alter table pg_class alter relname set not null;
484 -- try altering non-existent table, should fail
485 alter table non_existent alter column bar set not null;
486 alter table non_existent alter column bar drop not null;
488 -- test setting columns to null and not null and vice versa
489 -- test checking for null values and primary key
490 create table atacc1 (test int not null);
491 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
492 alter table atacc1 alter column test drop not null;
493 alter table atacc1 drop constraint "atacc1_pkey";
494 alter table atacc1 alter column test drop not null;
495 insert into atacc1 values (null);
496 alter table atacc1 alter test set not null;
498 alter table atacc1 alter test set not null;
500 -- try altering a non-existent column, should fail
501 alter table atacc1 alter bar set not null;
502 alter table atacc1 alter bar drop not null;
504 -- try altering the oid column, should fail
505 alter table atacc1 alter oid set not null;
506 alter table atacc1 alter oid drop not null;
508 -- try creating a view and altering that, should fail
509 create view myview as select * from atacc1;
510 alter table myview alter column test drop not null;
511 alter table myview alter column test set not null;
517 create table parent (a int);
518 create table child (b varchar(255)) inherits (parent);
520 alter table parent alter a set not null;
521 insert into parent values (NULL);
522 insert into child (a, b) values (NULL, 'foo');
523 alter table parent alter a drop not null;
524 insert into parent values (NULL);
525 insert into child (a, b) values (NULL, 'foo');
526 alter table only parent alter a set not null;
527 alter table child alter a set not null;
529 alter table only parent alter a set not null;
530 insert into parent values (NULL);
531 alter table child alter a set not null;
532 insert into child (a, b) values (NULL, 'foo');
534 alter table child alter a set not null;
535 insert into child (a, b) values (NULL, 'foo');
539 -- test setting and removing default values
540 create table def_test (
542 c2 text default 'initial_default'
544 insert into def_test default values;
545 alter table def_test alter column c1 drop default;
546 insert into def_test default values;
547 alter table def_test alter column c2 drop default;
548 insert into def_test default values;
549 alter table def_test alter column c1 set default 10;
550 alter table def_test alter column c2 set default 'new_default';
551 insert into def_test default values;
552 select * from def_test;
554 -- set defaults to an incorrect type: this should fail
555 alter table def_test alter column c1 set default 'wrong_datatype';
556 alter table def_test alter column c2 set default 20;
558 -- set defaults on a non-existent column: this should fail
559 alter table def_test alter column c3 set default 30;
561 -- set defaults on views: we need to create a view, add a rule
562 -- to allow insertions into it, and then alter the view to add
564 create view def_view_test as select * from def_test;
565 create rule def_view_test_ins as
566 on insert to def_view_test
567 do instead insert into def_test select new.*;
568 insert into def_view_test default values;
569 alter table def_view_test alter column c1 set default 45;
570 insert into def_view_test default values;
571 alter table def_view_test alter column c2 set default 'view_default';
572 insert into def_view_test default values;
573 select * from def_view_test;
575 drop rule def_view_test_ins on def_view_test;
576 drop view def_view_test;
579 -- alter table / drop column tests
580 -- try altering system catalogs, should fail
581 alter table pg_class drop column relname;
583 -- try altering non-existent table, should fail
584 alter table foo drop column bar;
586 -- test dropping columns
587 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
588 insert into atacc1 values (1, 2, 3, 4);
589 alter table atacc1 drop a;
590 alter table atacc1 drop a;
593 select * from atacc1;
594 select * from atacc1 order by a;
595 select * from atacc1 order by "........pg.dropped.1........";
596 select * from atacc1 group by a;
597 select * from atacc1 group by "........pg.dropped.1........";
598 select atacc1.* from atacc1;
599 select a from atacc1;
600 select atacc1.a from atacc1;
601 select b,c,d from atacc1;
602 select a,b,c,d from atacc1;
603 select * from atacc1 where a = 1;
604 select "........pg.dropped.1........" from atacc1;
605 select atacc1."........pg.dropped.1........" from atacc1;
606 select "........pg.dropped.1........",b,c,d from atacc1;
607 select * from atacc1 where "........pg.dropped.1........" = 1;
610 update atacc1 set a = 3;
611 update atacc1 set b = 2 where a = 3;
612 update atacc1 set "........pg.dropped.1........" = 3;
613 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
616 insert into atacc1 values (10, 11, 12, 13);
617 insert into atacc1 values (default, 11, 12, 13);
618 insert into atacc1 values (11, 12, 13);
619 insert into atacc1 (a) values (10);
620 insert into atacc1 (a) values (default);
621 insert into atacc1 (a,b,c,d) values (10,11,12,13);
622 insert into atacc1 (a,b,c,d) values (default,11,12,13);
623 insert into atacc1 (b,c,d) values (11,12,13);
624 insert into atacc1 ("........pg.dropped.1........") values (10);
625 insert into atacc1 ("........pg.dropped.1........") values (default);
626 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
627 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
630 delete from atacc1 where a = 3;
631 delete from atacc1 where "........pg.dropped.1........" = 3;
634 -- try dropping a non-existent column, should fail
635 alter table atacc1 drop bar;
637 -- try dropping the oid column, should fail
638 alter table atacc1 drop oid;
640 -- try creating a view and altering that, should fail
641 create view myview as select * from atacc1;
642 select * from myview;
643 alter table myview drop d;
646 -- test some commands to make sure they fail on the dropped column
648 analyze atacc1("........pg.dropped.1........");
649 vacuum analyze atacc1(a);
650 vacuum analyze atacc1("........pg.dropped.1........");
651 comment on column atacc1.a is 'testing';
652 comment on column atacc1."........pg.dropped.1........" is 'testing';
653 alter table atacc1 alter a set storage plain;
654 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
655 alter table atacc1 alter a set statistics 0;
656 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
657 alter table atacc1 alter a set default 3;
658 alter table atacc1 alter "........pg.dropped.1........" set default 3;
659 alter table atacc1 alter a drop default;
660 alter table atacc1 alter "........pg.dropped.1........" drop default;
661 alter table atacc1 alter a set not null;
662 alter table atacc1 alter "........pg.dropped.1........" set not null;
663 alter table atacc1 alter a drop not null;
664 alter table atacc1 alter "........pg.dropped.1........" drop not null;
665 alter table atacc1 rename a to x;
666 alter table atacc1 rename "........pg.dropped.1........" to x;
667 alter table atacc1 add primary key(a);
668 alter table atacc1 add primary key("........pg.dropped.1........");
669 alter table atacc1 add unique(a);
670 alter table atacc1 add unique("........pg.dropped.1........");
671 alter table atacc1 add check (a > 3);
672 alter table atacc1 add check ("........pg.dropped.1........" > 3);
673 create table atacc2 (id int4 unique);
674 alter table atacc1 add foreign key (a) references atacc2(id);
675 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
676 alter table atacc2 add foreign key (id) references atacc1(a);
677 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
679 create index "testing_idx" on atacc1(a);
680 create index "testing_idx" on atacc1("........pg.dropped.1........");
682 -- test create as and select into
683 insert into atacc1 values (21, 22, 23);
684 create table test1 as select * from atacc1;
687 select * into test2 from atacc1;
691 -- try dropping all columns
692 alter table atacc1 drop c;
693 alter table atacc1 drop d;
694 alter table atacc1 drop b;
695 select * from atacc1;
700 create table parent (a int, b int, c int);
701 insert into parent values (1, 2, 3);
702 alter table parent drop a;
703 create table child (d varchar(255)) inherits (parent);
704 insert into child values (12, 13, 'testing');
706 select * from parent;
708 alter table parent drop c;
709 select * from parent;
716 create table test (a int4, b int4, c int4);
717 insert into test values (1,2,3);
718 alter table test drop a;
720 copy test(a) to stdout;
721 copy test("........pg.dropped.1........") to stdout;
722 copy test from stdin;
725 SET autocommit TO 'on';
727 copy test from stdin;
731 copy test(a) from stdin;
732 copy test("........pg.dropped.1........") from stdin;
733 copy test(b,c) from stdin;
741 create table dropColumn (a int, b int, e int);
742 create table dropColumnChild (c int) inherits (dropColumn);
743 create table dropColumnAnother (d int) inherits (dropColumnChild);
745 -- these two should fail
746 alter table dropColumnchild drop column a;
747 alter table only dropColumnChild drop column b;
749 -- these three should work
750 alter table only dropColumn drop column e;
751 alter table dropColumnChild drop column c;
752 alter table dropColumn drop column a;
754 create table renameColumn (a int);
755 create table renameColumnChild (b int) inherits (renameColumn);
756 create table renameColumnAnother (c int) inherits (renameColumnChild);
758 -- these three should fail
759 alter table renameColumnChild rename column a to d;
760 alter table only renameColumnChild rename column a to d;
761 alter table only renameColumn rename column a to d;
764 alter table renameColumn rename column a to d;
765 alter table renameColumnChild rename column b to a;
768 alter table renameColumn add column w int;
771 alter table only renameColumn add column x int;
774 -- Test corner cases in dropping of inherited columns
776 create table p1 (f1 int, f2 int);
777 create table c1 (f1 int not null) inherits(p1);
779 -- should be rejected since c1.f1 is inherited
780 alter table c1 drop column f1;
782 alter table p1 drop column f1;
783 -- c1.f1 is still there, but no longer inherited
785 alter table c1 drop column f1;
788 drop table p1 cascade;
790 create table p1 (f1 int, f2 int);
791 create table c1 () inherits(p1);
793 -- should be rejected since c1.f1 is inherited
794 alter table c1 drop column f1;
795 alter table p1 drop column f1;
796 -- c1.f1 is dropped now, since there is no local definition for it
799 drop table p1 cascade;
801 create table p1 (f1 int, f2 int);
802 create table c1 () inherits(p1);
804 -- should be rejected since c1.f1 is inherited
805 alter table c1 drop column f1;
806 alter table only p1 drop column f1;
807 -- c1.f1 is NOT dropped, but must now be considered non-inherited
808 alter table c1 drop column f1;
810 drop table p1 cascade;
812 create table p1 (f1 int, f2 int);
813 create table c1 (f1 int not null) inherits(p1);
815 -- should be rejected since c1.f1 is inherited
816 alter table c1 drop column f1;
817 alter table only p1 drop column f1;
818 -- c1.f1 is still there, but no longer inherited
819 alter table c1 drop column f1;
821 drop table p1 cascade;
823 create table p1(id int, name text);
824 create table p2(id2 int, name text, height int);
825 create table c1(age int) inherits(p1,p2);
826 create table gc1() inherits (c1);
828 select relname, attname, attinhcount, attislocal
829 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
830 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
831 order by relname, attnum;
834 alter table only p1 drop column name;
835 -- should work. Now c1.name is local and inhcount is 0.
836 alter table p2 drop column name;
837 -- should be rejected since its inherited
838 alter table gc1 drop column name;
839 -- should work, and drop gc1.name along
840 alter table c1 drop column name;
841 -- should fail: column does not exist
842 alter table gc1 drop column name;
843 -- should work and drop the attribute in all tables
844 alter table p2 drop column height;
846 select relname, attname, attinhcount, attislocal
847 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
848 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
849 order by relname, attnum;
851 drop table p1, p2 cascade;
854 -- Test the ALTER TABLE WITHOUT OIDS command
856 create table altstartwith (col integer) with oids;
858 insert into altstartwith values (1);
860 select oid > 0, * from altstartwith;
862 alter table altstartwith set without oids;
864 select oid > 0, * from altstartwith; -- fails
865 select * from altstartwith;
867 -- Run inheritance tests
868 create table altwithoid (col integer) with oids;
870 -- Inherits parents oid column
871 create table altinhoid () inherits (altwithoid) without oids;
873 insert into altinhoid values (1);
875 select oid > 0, * from altwithoid;
876 select oid > 0, * from altinhoid;
878 alter table altwithoid set without oids;
879 alter table altinhoid set without oids; -- fails
881 select oid > 0, * from altwithoid; -- fails
882 select oid > 0, * from altinhoid; -- fails
883 select * from altwithoid;
884 select * from altinhoid;
886 -- test renumbering of child-table columns in inherited operations
888 create table p1 (f1 int);
889 create table c1 (f2 text, f3 int) inherits (p1);
891 alter table p1 add column a1 int check (a1 > 0);
892 alter table p1 add column f2 text;
894 insert into p1 values (1,2,'abc');
895 insert into c1 values(11,'xyz',33,0); -- should fail
896 insert into c1 values(11,'xyz',33,22);
899 update p1 set a1 = a1 + 1, f2 = upper(f2);
902 drop table p1 cascade;