alter_table.out

PostgreSQL7.4.6 for Linux

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;
ERROR:  column "name" of relation "gc1" does not exist
-- 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;
 relname | attname | attinhcount | attislocal 
---------+---------+-------------+------------
 c1      | id      |           1 | f
 c1      | id2     |           1 | f
 c1      | age     |           0 | t
 gc1     | id      |           1 | f
 gc1     | id2     |           1 | f
 gc1     | age     |           1 | f
 p1      | id      |           0 | t
 p2      | id2     |           0 | t
(8 rows)

drop table p1, p2 cascade;
NOTICE:  drop cascades to table c1
NOTICE:  drop cascades to table gc1
--
-- Test the ALTER TABLE WITHOUT OIDS command
--
create table altstartwith (col integer) with oids;
insert into altstartwith values (1);
select oid > 0, * from altstartwith;
 ?column? | col 
----------+-----
 t        |   1
(1 row)

alter table altstartwith set without oids;
select oid > 0, * from altstartwith; -- fails
ERROR:  column "oid" does not exist
select * from altstartwith;
 col 
-----
   1
(1 row)

-- Run inheritance tests
create table altwithoid (col integer) with oids;
-- Inherits parents oid column
create table altinhoid () inherits (altwithoid) without oids;
insert into altinhoid values (1);
select oid > 0, * from altwithoid;
 ?column? | col 
----------+-----
 t        |   1
(1 row)

select oid > 0, * from altinhoid;
 ?column? | col 
----------+-----
 t        |   1
(1 row)

alter table altwithoid set without oids;
alter table altinhoid set without oids;
select oid > 0, * from altwithoid; -- fails
ERROR:  column "oid" does not exist
select oid > 0, * from altinhoid; -- fails
ERROR:  column "oid" does not exist
select * from altwithoid;
 col 
-----
   1
(1 row)

select * from altinhoid;
 col 
-----
   1
(1 row)

-- 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;
NOTICE:  merging definition of column "f2" for child "c1"
insert into p1 values (1,2,'abc');
insert into c1 values(11,'xyz',33,0); -- should fail
ERROR:  new row for relation "c1" violates check constraint "p1_a1"
insert into c1 values(11,'xyz',33,22);
select * from p1;
 f1 | a1 | f2  
----+----+-----
  1 |  2 | abc
 11 | 22 | xyz
(2 rows)

update p1 set a1 = a1 + 1, f2 = upper(f2);
select * from p1;
 f1 | a1 | f2  
----+----+-----
  1 |  3 | ABC
 11 | 23 | XYZ
(2 rows)

drop table p1 cascade;
NOTICE:  drop cascades to table c1
NOTICE:  drop cascades to constraint p1_a1 on table c1
-- test that operations with a dropped column do not try to reference
-- its datatype
create domain mytype as text;
create temp table foo (f1 text, f2 mytype, f3 text);
insert into foo values('aa','bb','cc');
select * from foo;
 f1 | f2 | f3 
----+----+----
 aa | bb | cc
(1 row)

drop domain mytype cascade;
NOTICE:  drop cascades to table foo column f2
select * from foo;
 f1 | f3 
----+----
 aa | cc
(1 row)

insert into foo values('qq','rr');
select * from foo;
 f1 | f3 
----+----
 aa | cc
 qq | rr
(2 rows)

update foo set f3 = 'zz';
select * from foo;
 f1 | f3 
----+----
 aa | zz
 qq | zz
(2 rows)

select f3,max(f1) from foo group by f3;
 f3 | max 
----+-----
 zz | qq
(1 row)