alter_table.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

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 succeed
alter table atacc1 drop oid;
-- try dropping the xmin column, should fail
alter table atacc1 drop xmin;
ERROR:  cannot drop system column "xmin"
-- 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
drop table atacc2;
create index "testing_idx" on atacc1(a);
ERROR:  column "a" does not exist
create index "testing_idx" on atacc1("........pg.dropped.1........");
ERROR:  column "........pg.dropped.1........" does not exist
-- test create as and select into
insert into atacc1 values (21, 22, 23);
create table test1 as select * from atacc1;
select * from test1;
 b  | c  | d  
----+----+----
 21 | 22 | 23
(1 row)

drop table test1;
select * into test2 from atacc1;
select * from test2;
 b  | c  | d  
----+----+----
 21 | 22 | 23
(1 row)

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;
  
--
(1 row)

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;
 b  | c  
----+----
  2 |  3
 12 | 13
(2 rows)

select * from child;
 b  | c  |    d    
----+----+---------
 12 | 13 | testing
(1 row)

alter table parent drop c;
select * from parent;
 b  
----
  2
 12
(2 rows)

select * from child;
 b  |    d    
----+---------
 12 | testing
(1 row)

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;
2	3
copy test(a) to stdout;
ERROR:  column "a" of relation "test" does not exist
copy test("........pg.dropped.1........") to stdout;
ERROR:  column "........pg.dropped.1........" of relation "test" does not exist
copy test from stdin;
ERROR:  extra data after last expected column
CONTEXT:  COPY test, line 1: "10	11	12"
select * from test;
 b | c 
---+---
 2 | 3
(1 row)

copy test from stdin;
select * from test;
 b  | c  
----+----
  2 |  3
 21 | 22
(2 rows)

copy test(a) from stdin;
ERROR:  column "a" of relation "test" does not exist
copy test("........pg.dropped.1........") from stdin;
ERROR:  column "........pg.dropped.1........" of relation "test" does not exist
copy test(b,c) from stdin;
select * from test;
 b  | c  
----+----
  2 |  3
 21 | 22
 31 | 32
(3 rows)

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;
ERROR:  cannot drop inherited column "a"
alter table only dropColumnChild drop column b;
ERROR:  cannot drop inherited 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;
ERROR:  cannot rename inherited column "a"
alter table only renameColumnChild rename column a to d;
ERROR:  inherited column "a" must be renamed in child tables too
alter table only renameColumn rename column a to d;
ERROR:  inherited column "a" must be renamed in child tables too
-- 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;
ERROR:  column must be added to child tables too
-- Test corner cases in dropping of inherited columns
create table p1 (f1 int, f2 int);
create table c1 (f1 int not null) inherits(p1);
NOTICE:  merging column "f1" with inherited definition
-- should be rejected since c1.f1 is inherited
alter table c1 drop column f1;
ERROR:  cannot drop inherited column "f1"
-- should work
alter table p1 drop column f1;
-- c1.f1 is still there, but no longer inherited
select f1 from c1;
 f1 
----
(0 rows)

alter table c1 drop column f1;
select f1 from c1;
ERROR:  column "f1" does not exist
drop table p1 cascade;
NOTICE:  drop cascades to table c1
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;
ERROR:  cannot drop inherited 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;
ERROR:  column "f1" does not exist
drop table p1 cascade;
NOTICE:  drop cascades to table c1
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;
ERROR:  cannot drop inherited 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;
NOTICE:  drop cascades to table c1
create table p1 (f1 int, f2 int);
create table c1 (f1 int not null) inherits(p1);
NOTICE:  merging column "f1" with inherited definition
-- should be rejected since c1.f1 is inherited
alter table c1 drop column f1;
ERROR:  cannot drop inherited 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;
NOTICE:  drop cascades to table c1
create table p1(id int, name text);
create table p2(id2 int, name text, height int);
create table c1(age int) inherits(p1,p2);
NOTICE:  merging multiple inherited definitions of column "name"
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;
 relname | attname | attinhcount | attislocal 
---------+---------+-------------+------------
 c1      | id      |           1 | f
 c1      | name    |           2 | f
 c1      | id2     |           1 | f
 c1      | height  |           1 | f
 c1      | age     |           0 | t
 gc1     | id      |           1 | f
 gc1     | name    |           1 | f
 gc1     | id2     |           1 | f
 gc1     | height  |           1 | f
 gc1     | age     |           1 | f
 p1      | id      |           0 | t
 p1      | name    |           0 | t
 p2      | id2     |           0 | t
 p2      | name    |           0 | t
 p2      | height  |           0 | t
(15 rows)

-- 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;
ERROR:  cannot drop inherited 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;