alter_table.out

PostgreSQL7.4.6 for Linux

insert into atacc1 (test) values (2);
ERROR:  duplicate key violates unique constraint "atacc_test1"
-- should succeed
insert into atacc1 (test) values (4);
-- inserting NULL should fail
insert into atacc1 (test) values(NULL);
ERROR:  null value in column "test" violates not-null constraint
-- try adding a second primary key (should fail)
alter table atacc1 add constraint atacc_oid1 primary key(oid);
ERROR:  multiple primary keys for table "atacc1" are not allowed
-- 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);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"
drop table atacc1;
-- let's do one where the primary key constraint fails when added
create table atacc1 ( test int );
-- insert soon to be failing rows
insert into atacc1 (test) values (2);
insert into atacc1 (test) values (2);
-- add a primary key (fails)
alter table atacc1 add constraint atacc_test1 primary key (test);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
insert into atacc1 (test) values (3);
drop table atacc1;
-- let's do another one where the primary key constraint fails when added
create table atacc1 ( test int );
-- insert soon to be failing row
insert into atacc1 (test) values (NULL);
-- add a primary key (fails)
alter table atacc1 add constraint atacc_test1 primary key (test);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
ERROR:  column "test" contains null values
insert into atacc1 (test) values (3);
drop table atacc1;
-- let's do one where the primary key constraint fails
-- because the column doesn't exist
create table atacc1 ( test int );
-- add a primary key constraint (fails)
alter table atacc1 add constraint atacc_test1 primary key (test1);
ERROR:  column "test1" named in key does not exist
drop table atacc1;
-- something a little more complicated
create table atacc1 ( test int, test2 int);
-- add a primary key constraint
alter table atacc1 add constraint atacc_test1 primary key (test, test2);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"
-- try adding a second primary key - should fail
alter table atacc1 add constraint atacc_test2 primary key (test);
ERROR:  multiple primary keys for table "atacc1" are not allowed
-- insert initial value
insert into atacc1 (test,test2) values (4,4);
-- should fail
insert into atacc1 (test,test2) values (4,4);
ERROR:  duplicate key violates unique constraint "atacc_test1"
insert into atacc1 (test,test2) values (NULL,3);
ERROR:  null value in column "test" violates not-null constraint
insert into atacc1 (test,test2) values (3, NULL);
ERROR:  null value in column "test2" violates not-null constraint
insert into atacc1 (test,test2) values (NULL,NULL);
ERROR:  null value in column "test" violates not-null constraint
-- should all succeed
insert into atacc1 (test,test2) values (4,5);
insert into atacc1 (test,test2) values (5,4);
insert into atacc1 (test,test2) values (5,5);
drop table atacc1;
-- lets do some naming tests
create table atacc1 (test int, test2 int, primary key(test));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
-- only first should succeed
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
ERROR:  duplicate key violates unique constraint "atacc1_pkey"
insert into atacc1 (test2, test) values (1, NULL);
ERROR:  null value in column "test" violates not-null constraint
drop table atacc1;
-- alter table / alter column [set/drop] not null tests
-- try altering system catalogs, should fail
alter table pg_class alter column relname drop not null;
ERROR:  permission denied: "pg_class" is a system catalog
alter table pg_class alter relname set not null;
ERROR:  permission denied: "pg_class" is a system catalog
-- try altering non-existent table, should fail
alter table non_existent alter column bar set not null;
ERROR:  relation "non_existent" does not exist
alter table non_existent alter column bar drop not null;
ERROR:  relation "non_existent" does not exist
-- 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);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"
alter table atacc1 alter column test drop not null;
ERROR:  column "test" is in a primary key
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;
ERROR:  column "test" contains null values
delete from atacc1;
alter table atacc1 alter test set not null;
-- try altering a non-existent column, should fail
alter table atacc1 alter bar set not null;
ERROR:  column "bar" of relation "atacc1" does not exist
alter table atacc1 alter bar drop not null;
ERROR:  column "bar" of relation "atacc1" does not exist
-- try altering the oid column, should fail
alter table atacc1 alter oid set not null;
ERROR:  cannot alter system column "oid"
alter table atacc1 alter oid drop not null;
ERROR:  cannot alter system column "oid"
-- try creating a view and altering that, should fail
create view myview as select * from atacc1;
alter table myview alter column test drop not null;
ERROR:  "myview" is not a table
alter table myview alter column test set not null;
ERROR:  "myview" is not a table
drop view myview;
drop table atacc1;
-- test inheritance
create table parent (a int);
create table child (b varchar(255)) inherits (parent);
alter table parent alter a set not null;
insert into parent values (NULL);
ERROR:  null value in column "a" violates not-null constraint
insert into child (a, b) values (NULL, 'foo');
ERROR:  null value in column "a" violates not-null constraint
alter table parent alter a drop not null;
insert into parent values (NULL);
insert into child (a, b) values (NULL, 'foo');
alter table only parent alter a set not null;
ERROR:  column "a" contains null values
alter table child alter a set not null;
ERROR:  column "a" contains null values
delete from parent;
alter table only parent alter a set not null;
insert into parent values (NULL);
ERROR:  null value in column "a" violates not-null constraint
alter table child alter a set not null;
insert into child (a, b) values (NULL, 'foo');
ERROR:  null value in column "a" violates not-null constraint
delete from child;
alter table child alter a set not null;
insert into child (a, b) values (NULL, 'foo');
ERROR:  null value in column "a" violates not-null constraint
drop table child;
drop table parent;
-- test setting and removing default values
create table def_test (
	c1	int4 default 5,
	c2	text default 'initial_default'
);
insert into def_test default values;
alter table def_test alter column c1 drop default;
insert into def_test default values;
alter table def_test alter column c2 drop default;
insert into def_test default values;
alter table def_test alter column c1 set default 10;
alter table def_test alter column c2 set default 'new_default';
insert into def_test default values;
select * from def_test;
 c1 |       c2        
----+-----------------
  5 | initial_default
    | initial_default
    | 
 10 | new_default
(4 rows)

-- set defaults to an incorrect type: this should fail
alter table def_test alter column c1 set default 'wrong_datatype';
ERROR:  invalid input syntax for integer: "wrong_datatype"
alter table def_test alter column c2 set default 20;
-- set defaults on a non-existent column: this should fail
alter table def_test alter column c3 set default 30;
ERROR:  column "c3" of relation "def_test" does not exist
-- set defaults on views: we need to create a view, add a rule
-- to allow insertions into it, and then alter the view to add
-- a default
create view def_view_test as select * from def_test;
create rule def_view_test_ins as
	on insert to def_view_test
	do instead insert into def_test select new.*;
insert into def_view_test default values;
alter table def_view_test alter column c1 set default 45;
insert into def_view_test default values;
alter table def_view_test alter column c2 set default 'view_default';
insert into def_view_test default values;
select * from def_view_test;
 c1 |       c2        
----+-----------------
  5 | initial_default
    | initial_default
    | 
 10 | new_default
    | 
 45 | 
 45 | view_default
(7 rows)

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;
ERROR:  permission denied: "pg_class" is a system catalog
-- try altering non-existent table, should fail
alter table nosuchtable drop column bar;
ERROR:  relation "nosuchtable" does not exist
-- 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;
ERROR:  column "a" of relation "atacc1" does not exist
-- SELECTs
select * from atacc1;
 b | c | d 
---+---+---
 2 | 3 | 4
(1 row)

select * from atacc1 order by a;
ERROR:  column "a" does not exist
select * from atacc1 order by "........pg.dropped.1........";
ERROR:  column "........pg.dropped.1........" does not exist
select * from atacc1 group by a;
ERROR:  column "a" does not exist
select * from atacc1 group by "........pg.dropped.1........";
ERROR:  column "........pg.dropped.1........" does not exist
select atacc1.* from atacc1;
 b | c | d 
---+---+---
 2 | 3 | 4
(1 row)

select a from atacc1;
ERROR:  column "a" does not exist
select atacc1.a from atacc1;
ERROR:  column atacc1.a does not exist
select b,c,d from atacc1;
 b | c | d 
---+---+---
 2 | 3 | 4
(1 row)

select a,b,c,d from atacc1;
ERROR:  column "a" does not exist
select * from atacc1 where a = 1;
ERROR:  column "a" does not exist
select "........pg.dropped.1........" from atacc1;
ERROR:  column "........pg.dropped.1........" does not exist
select atacc1."........pg.dropped.1........" from atacc1;
ERROR:  column atacc1.........pg.dropped.1........ does not exist
select "........pg.dropped.1........",b,c,d from atacc1;
ERROR:  column "........pg.dropped.1........" does not exist
select * from atacc1 where "........pg.dropped.1........" = 1;
ERROR:  column "........pg.dropped.1........" does not exist
-- UPDATEs
update atacc1 set a = 3;
ERROR:  column "a" of relation "atacc1" does not exist
update atacc1 set b = 2 where a = 3;
ERROR:  column "a" does not exist
update atacc1 set "........pg.dropped.1........" = 3;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
ERROR:  column "........pg.dropped.1........" does not exist
-- INSERTs
insert into atacc1 values (10, 11, 12, 13);
ERROR:  INSERT has more expressions than target columns
insert into atacc1 values (default, 11, 12, 13);
ERROR:  INSERT has more expressions than target columns
insert into atacc1 values (11, 12, 13);
insert into atacc1 (a) values (10);
ERROR:  column "a" of relation "atacc1" does not exist
insert into atacc1 (a) values (default);
ERROR:  column "a" of relation "atacc1" does not exist
insert into atacc1 (a,b,c,d) values (10,11,12,13);
ERROR:  column "a" of relation "atacc1" does not exist
insert into atacc1 (a,b,c,d) values (default,11,12,13);
ERROR:  column "a" of relation "atacc1" does not exist
insert into atacc1 (b,c,d) values (11,12,13);
insert into atacc1 ("........pg.dropped.1........") values (10);
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
insert into atacc1 ("........pg.dropped.1........") values (default);
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
-- DELETEs
delete from atacc1 where a = 3;
ERROR:  column "a" does not exist
delete from atacc1 where "........pg.dropped.1........" = 3;
ERROR:  column "........pg.dropped.1........" does not exist
delete from atacc1;
-- try dropping a non-existent column, should fail
alter table atacc1 drop bar;
ERROR:  column "bar" of relation "atacc1" does not exist
-- try dropping the oid column, should fail
alter table atacc1 drop oid;
ERROR:  cannot drop system column "oid"
-- try creating a view and altering that, should fail
create view myview as select * from atacc1;
select * from myview;
 b | c | d 
---+---+---
(0 rows)

alter table myview drop d;
ERROR:  "myview" is not a table
drop view myview;
-- test some commands to make sure they fail on the dropped column
analyze atacc1(a);
ERROR:  column "a" of relation "atacc1" does not exist
analyze atacc1("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
vacuum analyze atacc1(a);
ERROR:  column "a" of relation "atacc1" does not exist
vacuum analyze atacc1("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
comment on column atacc1.a is 'testing';
ERROR:  column "a" of relation "atacc1" does not exist
comment on column atacc1."........pg.dropped.1........" is 'testing';
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set storage plain;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set storage plain;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set statistics 0;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set default 3;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set default 3;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a drop default;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" drop default;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a set not null;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" set not null;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 alter a drop not null;
ERROR:  column "a" of relation "atacc1" does not exist
alter table atacc1 alter "........pg.dropped.1........" drop not null;
ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
alter table atacc1 rename a to x;
ERROR:  column "a" does not exist
alter table atacc1 rename "........pg.dropped.1........" to x;
ERROR:  column "........pg.dropped.1........" does not exist
alter table atacc1 add primary key(a);
ERROR:  column "a" named in key does not exist
alter table atacc1 add primary key("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" named in key does not exist
alter table atacc1 add unique(a);
ERROR:  column "a" named in key does not exist
alter table atacc1 add unique("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" named in key does not exist
alter table atacc1 add check (a > 3);
ERROR:  column "a" does not exist
alter table atacc1 add check ("........pg.dropped.1........" > 3);
ERROR:  column "........pg.dropped.1........" does not exist
create table atacc2 (id int4 unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"
alter table atacc1 add foreign key (a) references atacc2(id);
ERROR:  column "a" referenced in foreign key constraint does not exist
alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
ERROR:  column "........pg.dropped.1........" referenced in foreign key constraint does not exist
alter table atacc2 add foreign key (id) references atacc1(a);
ERROR:  column "a" referenced in foreign key constraint does not exist
alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" referenced in foreign key constraint does not exist