ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
-- As should this
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
-DROP TABLE pktable;
+DROP TABLE pktable cascade;
DROP TABLE fktable;
CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
-- This should fail, because we just chose really odd types
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
--- Again, so should this...
DROP TABLE FKTABLE;
+-- Again, so should this...
CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
references pktable(ptest1, ptest2);
--- This fails because we mixed up the column ordering
DROP TABLE FKTABLE;
+-- This fails because we mixed up the column ordering
CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
references pktable(ptest2, ptest1);
insert into atacc1 (test) values (4);
-- inserting NULL should fail
insert into atacc1 (test) values(NULL);
--- try adding a primary key oid constraint
+-- try adding a second primary key (should fail)
+alter table atacc1 add constraint atacc_oid1 primary key(oid);
+-- drop first primary key constraint
+alter table atacc1 drop constraint atacc_test1 restrict;
+-- try adding a primary key on oid (should succeed)
alter table atacc1 add constraint atacc_oid1 primary key(oid);
drop table atacc1;
alter table pg_class alter relname set not null;
-- try altering non-existent table, should fail
-alter table foo alter column bar set not null;
-alter table foo alter column bar drop not null;
+alter table non_existent alter column bar set not null;
+alter table non_existent alter column bar drop not null;
-- test setting columns to null and not null and vice versa
-- test checking for null values and primary key
create table atacc1 (test int not null);
alter table atacc1 add constraint "atacc1_pkey" primary key (test);
alter table atacc1 alter column test drop not null;
-drop index atacc1_pkey;
+alter table atacc1 drop constraint "atacc1_pkey";
alter table atacc1 alter column test drop not null;
insert into atacc1 values (null);
alter table atacc1 alter test set not null;
drop rule def_view_test_ins on def_view_test;
drop view def_view_test;
drop table def_test;
+
+-- alter table / drop column tests
+-- try altering system catalogs, should fail
+alter table pg_class drop column relname;
+
+-- try altering non-existent table, should fail
+alter table foo drop column bar;
+
+-- test dropping columns
+create table atacc1 (a int4 not null, b int4, c int4 not null, d int4);
+insert into atacc1 values (1, 2, 3, 4);
+alter table atacc1 drop a;
+alter table atacc1 drop a;
+
+-- SELECTs
+select * from atacc1;
+select * from atacc1 order by a;
+select * from atacc1 order by "........pg.dropped.1........";
+select * from atacc1 group by a;
+select * from atacc1 group by "........pg.dropped.1........";
+select atacc1.* from atacc1;
+select a from atacc1;
+select atacc1.a from atacc1;
+select b,c,d from atacc1;
+select a,b,c,d from atacc1;
+select * from atacc1 where a = 1;
+select "........pg.dropped.1........" from atacc1;
+select atacc1."........pg.dropped.1........" from atacc1;
+select "........pg.dropped.1........",b,c,d from atacc1;
+select * from atacc1 where "........pg.dropped.1........" = 1;
+
+-- UPDATEs
+update atacc1 set a = 3;
+update atacc1 set b = 2 where a = 3;
+update atacc1 set "........pg.dropped.1........" = 3;
+update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
+
+-- INSERTs
+insert into atacc1 values (10, 11, 12, 13);
+insert into atacc1 values (default, 11, 12, 13);
+insert into atacc1 values (11, 12, 13);
+insert into atacc1 (a) values (10);
+insert into atacc1 (a) values (default);
+insert into atacc1 (a,b,c,d) values (10,11,12,13);
+insert into atacc1 (a,b,c,d) values (default,11,12,13);
+insert into atacc1 (b,c,d) values (11,12,13);
+insert into atacc1 ("........pg.dropped.1........") values (10);
+insert into atacc1 ("........pg.dropped.1........") values (default);
+insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
+insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
+
+-- DELETEs
+delete from atacc1 where a = 3;
+delete from atacc1 where "........pg.dropped.1........" = 3;
+delete from atacc1;
+
+-- try dropping a non-existent column, should fail
+alter table atacc1 drop bar;
+
+-- try dropping the oid column, should fail
+alter table atacc1 drop oid;
+
+-- try creating a view and altering that, should fail
+create view myview as select * from atacc1;
+select * from myview;
+alter table myview drop d;
+drop view myview;
+
+-- test some commands to make sure they fail on the dropped column
+analyze atacc1(a);
+analyze atacc1("........pg.dropped.1........");
+vacuum analyze atacc1(a);
+vacuum analyze atacc1("........pg.dropped.1........");
+comment on column atacc1.a is 'testing';
+comment on column atacc1."........pg.dropped.1........" is 'testing';
+alter table atacc1 alter a set storage plain;
+alter table atacc1 alter "........pg.dropped.1........" set storage plain;
+alter table atacc1 alter a set statistics 0;
+alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
+alter table atacc1 alter a set default 3;
+alter table atacc1 alter "........pg.dropped.1........" set default 3;
+alter table atacc1 alter a drop default;
+alter table atacc1 alter "........pg.dropped.1........" drop default;
+alter table atacc1 alter a set not null;
+alter table atacc1 alter "........pg.dropped.1........" set not null;
+alter table atacc1 alter a drop not null;
+alter table atacc1 alter "........pg.dropped.1........" drop not null;
+alter table atacc1 rename a to x;
+alter table atacc1 rename "........pg.dropped.1........" to x;
+alter table atacc1 add primary key(a);
+alter table atacc1 add primary key("........pg.dropped.1........");
+alter table atacc1 add unique(a);
+alter table atacc1 add unique("........pg.dropped.1........");
+alter table atacc1 add check (a > 3);
+alter table atacc1 add check ("........pg.dropped.1........" > 3);
+create table atacc2 (id int4 unique);
+alter table atacc1 add foreign key (a) references atacc2(id);
+alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
+alter table atacc2 add foreign key (id) references atacc1(a);
+alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
+drop table atacc2;
+create index "testing_idx" on atacc1(a);
+create index "testing_idx" on atacc1("........pg.dropped.1........");
+
+-- test create as and select into
+insert into atacc1 values (21, 22, 23);
+create table test1 as select * from atacc1;
+select * from test1;
+drop table test1;
+select * into test2 from atacc1;
+select * from test2;
+drop table test2;
+
+-- try dropping all columns
+alter table atacc1 drop c;
+alter table atacc1 drop d;
+alter table atacc1 drop b;
+select * from atacc1;
+
+drop table atacc1;
+
+-- test inheritance
+create table parent (a int, b int, c int);
+insert into parent values (1, 2, 3);
+alter table parent drop a;
+create table child (d varchar(255)) inherits (parent);
+insert into child values (12, 13, 'testing');
+
+select * from parent;
+select * from child;
+alter table parent drop c;
+select * from parent;
+select * from child;
+
+drop table child;
+drop table parent;
+
+-- test copy in/out
+create table test (a int4, b int4, c int4);
+insert into test values (1,2,3);
+alter table test drop a;
+copy test to stdout;
+copy test(a) to stdout;
+copy test("........pg.dropped.1........") to stdout;
+copy test from stdin;
+10 11 12
+\.
+SET autocommit TO 'on';
+select * from test;
+copy test from stdin;
+21 22
+\.
+select * from test;
+copy test(a) from stdin;
+copy test("........pg.dropped.1........") from stdin;
+copy test(b,c) from stdin;
+31 32
+\.
+select * from test;
+drop table test;
+
+-- test inheritance
+
+create table dropColumn (a int, b int, e int);
+create table dropColumnChild (c int) inherits (dropColumn);
+create table dropColumnAnother (d int) inherits (dropColumnChild);
+
+-- these two should fail
+alter table dropColumnchild drop column a;
+alter table only dropColumnChild drop column b;
+
+-- these three should work
+alter table only dropColumn drop column e;
+alter table dropColumnChild drop column c;
+alter table dropColumn drop column a;
+
+create table renameColumn (a int);
+create table renameColumnChild (b int) inherits (renameColumn);
+create table renameColumnAnother (c int) inherits (renameColumnChild);
+
+-- these three should fail
+alter table renameColumnChild rename column a to d;
+alter table only renameColumnChild rename column a to d;
+alter table only renameColumn rename column a to d;
+
+-- these should work
+alter table renameColumn rename column a to d;
+alter table renameColumnChild rename column b to a;
+
+-- this should work
+alter table renameColumn add column w int;
+
+-- this should fail
+alter table only renameColumn add column x int;
+
+
+-- Test corner cases in dropping of inherited columns
+
+create table p1 (f1 int, f2 int);
+create table c1 (f1 int not null) inherits(p1);
+
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+-- should work
+alter table p1 drop column f1;
+-- c1.f1 is still there, but no longer inherited
+select f1 from c1;
+alter table c1 drop column f1;
+select f1 from c1;
+
+drop table p1 cascade;
+
+create table p1 (f1 int, f2 int);
+create table c1 () inherits(p1);
+
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+alter table p1 drop column f1;
+-- c1.f1 is dropped now, since there is no local definition for it
+select f1 from c1;
+
+drop table p1 cascade;
+
+create table p1 (f1 int, f2 int);
+create table c1 () inherits(p1);
+
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+alter table only p1 drop column f1;
+-- c1.f1 is NOT dropped, but must now be considered non-inherited
+alter table c1 drop column f1;
+
+drop table p1 cascade;
+
+create table p1 (f1 int, f2 int);
+create table c1 (f1 int not null) inherits(p1);
+
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+alter table only p1 drop column f1;
+-- c1.f1 is still there, but no longer inherited
+alter table c1 drop column f1;
+
+drop table p1 cascade;
+
+create table p1(id int, name text);
+create table p2(id2 int, name text, height int);
+create table c1(age int) inherits(p1,p2);
+create table gc1() inherits (c1);
+
+select relname, attname, attinhcount, attislocal
+from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
+where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
+order by relname, attnum;
+
+-- should work
+alter table only p1 drop column name;
+-- should work. Now c1.name is local and inhcount is 0.
+alter table p2 drop column name;
+-- should be rejected since its inherited
+alter table gc1 drop column name;
+-- should work, and drop gc1.name along
+alter table c1 drop column name;
+-- should fail: column does not exist
+alter table gc1 drop column name;
+-- should work and drop the attribute in all tables
+alter table p2 drop column height;
+
+select relname, attname, attinhcount, attislocal
+from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
+where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
+order by relname, attnum;
+
+drop table p1, p2 cascade;
+
+-- test renumbering of child-table columns in inherited operations
+
+create table p1 (f1 int);
+create table c1 (f2 text, f3 int) inherits (p1);
+
+alter table p1 add column a1 int check (a1 > 0);
+alter table p1 add column f2 text;
+
+insert into p1 values (1,2,'abc');
+insert into c1 values(11,'xyz',33,0); -- should fail
+insert into c1 values(11,'xyz',33,22);
+
+select * from p1;
+update p1 set a1 = a1 + 1, f2 = upper(f2);
+select * from p1;
+
+drop table p1 cascade;
projects / postgresql / blobdiff