alter_table.out

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

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 "c1_a1_check"
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 c1_a1_check 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)

-- Simple tests for alter table column type
alter table foo alter f1 TYPE integer; -- fails
ERROR:  column "f1" cannot be cast to type "pg_catalog.int4"
alter table foo alter f1 TYPE varchar(10);
create table anothertab (atcol1 serial8, atcol2 boolean,
	constraint anothertab_chk check (atcol1 <= 3));
NOTICE:  CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"
insert into anothertab (atcol1, atcol2) values (default, true);
insert into anothertab (atcol1, atcol2) values (default, false);
select * from anothertab;
 atcol1 | atcol2 
--------+--------
      1 | t
      2 | f
(2 rows)

alter table anothertab alter column atcol1 type boolean; -- fails
ERROR:  column "atcol1" cannot be cast to type "pg_catalog.bool"
alter table anothertab alter column atcol1 type integer;
select * from anothertab;
 atcol1 | atcol2 
--------+--------
      1 | t
      2 | f
(2 rows)

insert into anothertab (atcol1, atcol2) values (45, null); -- fails
ERROR:  new row for relation "anothertab" violates check constraint "anothertab_chk"
insert into anothertab (atcol1, atcol2) values (default, null);
select * from anothertab;
 atcol1 | atcol2 
--------+--------
      1 | t
      2 | f
      3 | 
(3 rows)

alter table anothertab alter column atcol2 type text
      using case when atcol2 is true then 'IT WAS TRUE' 
                 when atcol2 is false then 'IT WAS FALSE'
                 else 'IT WAS NULL!' end;
select * from anothertab;
 atcol1 |    atcol2    
--------+--------------
      1 | IT WAS TRUE
      2 | IT WAS FALSE
      3 | IT WAS NULL!
(3 rows)

alter table anothertab alter column atcol1 type boolean
        using case when atcol1 % 2 = 0 then true else false end; -- fails
ERROR:  default for column "atcol1" cannot be cast to type "pg_catalog.bool"
alter table anothertab alter column atcol1 drop default;
alter table anothertab alter column atcol1 type boolean
        using case when atcol1 % 2 = 0 then true else false end; -- fails
ERROR:  operator does not exist: boolean <= integer
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
alter table anothertab drop constraint anothertab_chk;
alter table anothertab alter column atcol1 type boolean
        using case when atcol1 % 2 = 0 then true else false end;
select * from anothertab;
 atcol1 |    atcol2    
--------+--------------
 f      | IT WAS TRUE
 t      | IT WAS FALSE
 f      | IT WAS NULL!
(3 rows)

drop table anothertab;
create table another (f1 int, f2 text);
insert into another values(1, 'one');
insert into another values(2, 'two');
insert into another values(3, 'three');
select * from another;
 f1 |  f2   
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

alter table another
  alter f1 type text using f2 || ' more',
  alter f2 type bigint using f1 * 10;
select * from another;
     f1     | f2 
------------+----
 one more   | 10
 two more   | 20
 three more | 30
(3 rows)

drop table another;
--
-- alter function
--
create function test_strict(text) returns text as
    'select coalesce($1, ''got passed a null'');'
    language sql returns null on null input;
select test_strict(NULL);
 test_strict 
-------------
 
(1 row)

alter function test_strict(text) called on null input;
select test_strict(NULL);
    test_strict    
-------------------
 got passed a null
(1 row)

create function non_strict(text) returns text as
    'select coalesce($1, ''got passed a null'');'
    language sql called on null input;
select non_strict(NULL);
    non_strict     
-------------------
 got passed a null
(1 row)

alter function non_strict(text) returns null on null input;
select non_strict(NULL);
 non_strict 
------------
 
(1 row)

--
-- alter object set schema
--
create schema alter1;
create schema alter2;
create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
NOTICE:  CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
create view alter1.v1 as select * from alter1.t1;
create function alter1.plus1(int) returns int as 'select $1+1' language sql;
create domain alter1.posint integer check (value > 0);
create type alter1.ctype as (f1 int, f2 text);
insert into alter1.t1(f2) values(11);
insert into alter1.t1(f2) values(12);
alter table alter1.t1 set schema alter2;
alter table alter1.v1 set schema alter2;
alter function alter1.plus1(int) set schema alter2;
alter domain alter1.posint set schema alter2;
alter type alter1.ctype set schema alter2;
-- this should succeed because nothing is left in alter1
drop schema alter1;
insert into alter2.t1(f2) values(13);
insert into alter2.t1(f2) values(14);
select * from alter2.t1;
 f1 | f2 
----+----
  1 | 11
  2 | 12
  3 | 13
  4 | 14
(4 rows)

select * from alter2.v1;
 f1 | f2 
----+----
  1 | 11
  2 | 12
  3 | 13
  4 | 14
(4 rows)

select alter2.plus1(41);
 plus1 
-------
    42
(1 row)

-- clean up
drop schema alter2 cascade;
NOTICE:  drop cascades to composite type alter2.ctype
NOTICE:  drop cascades to type alter2.ctype
NOTICE:  drop cascades to type alter2.posint
NOTICE:  drop cascades to function alter2.plus1(integer)
NOTICE:  drop cascades to view alter2.v1
NOTICE:  drop cascades to rule _RETURN on view alter2.v1
NOTICE:  drop cascades to sequence alter2.t1_f1_seq
NOTICE:  drop cascades to table alter2.t1 column f1
NOTICE:  drop cascades to table alter2.t1
NOTICE:  drop cascades to constraint t1_f2_check on table alter2.t1