ALTER TABLE rewrite. New cool stuff:

[postgresql] / src / test / regress / expected / alter_table.out

--
-- ALTER_TABLE
-- add attribute
--
CREATE TABLE tmp (initial int4);
COMMENT ON TABLE tmp_wrong IS 'table comment';
ERROR:  relation "tmp_wrong" does not exist
COMMENT ON TABLE tmp IS 'table comment';
COMMENT ON TABLE tmp IS NULL;
ALTER TABLE tmp ADD COLUMN a int4 default 3;
ALTER TABLE tmp ADD COLUMN b name;
ALTER TABLE tmp ADD COLUMN c text;
ALTER TABLE tmp ADD COLUMN d float8;
ALTER TABLE tmp ADD COLUMN e float4;
ALTER TABLE tmp ADD COLUMN f int2;
ALTER TABLE tmp ADD COLUMN g polygon;
ALTER TABLE tmp ADD COLUMN h abstime;
ALTER TABLE tmp ADD COLUMN i char;
ALTER TABLE tmp ADD COLUMN j abstime[];
ALTER TABLE tmp ADD COLUMN k int4;
ALTER TABLE tmp ADD COLUMN l tid;
ALTER TABLE tmp ADD COLUMN m xid;
ALTER TABLE tmp ADD COLUMN n oidvector;
--ALTER TABLE tmp ADD COLUMN o lock;
ALTER TABLE tmp ADD COLUMN p smgr;
ALTER TABLE tmp ADD COLUMN q point;
ALTER TABLE tmp ADD COLUMN r lseg;
ALTER TABLE tmp ADD COLUMN s path;
ALTER TABLE tmp ADD COLUMN t box;
ALTER TABLE tmp ADD COLUMN u tinterval;
ALTER TABLE tmp ADD COLUMN v timestamp;
ALTER TABLE tmp ADD COLUMN w interval;
ALTER TABLE tmp ADD COLUMN x float8[];
ALTER TABLE tmp ADD COLUMN y float4[];
ALTER TABLE tmp ADD COLUMN z int2[];
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
        v, w, x, y, z)
   VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 
        'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', 
        314159, '(1,1)', '512',
        '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
        '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
        'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
SELECT * FROM tmp;
 initial | a |  b   |  c   |  d  |  e  | f |           g           |              h               | i |                                               j                                                |   k    |   l   |  m  |        n        |       p       |     q     |           r           |              s              |          t          |                      u                      |            v             |        w         |     x     |     y     |     z     
---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
         | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
(1 row)

DROP TABLE tmp;
-- the wolf bug - schema mods caused inconsistent row descriptors 
CREATE TABLE tmp (
        initial         int4
);
ALTER TABLE tmp ADD COLUMN a int4;
ALTER TABLE tmp ADD COLUMN b name;
ALTER TABLE tmp ADD COLUMN c text;
ALTER TABLE tmp ADD COLUMN d float8;
ALTER TABLE tmp ADD COLUMN e float4;
ALTER TABLE tmp ADD COLUMN f int2;
ALTER TABLE tmp ADD COLUMN g polygon;
ALTER TABLE tmp ADD COLUMN h abstime;
ALTER TABLE tmp ADD COLUMN i char;
ALTER TABLE tmp ADD COLUMN j abstime[];
ALTER TABLE tmp ADD COLUMN k int4;
ALTER TABLE tmp ADD COLUMN l tid;
ALTER TABLE tmp ADD COLUMN m xid;
ALTER TABLE tmp ADD COLUMN n oidvector;
--ALTER TABLE tmp ADD COLUMN o lock;
ALTER TABLE tmp ADD COLUMN p smgr;
ALTER TABLE tmp ADD COLUMN q point;
ALTER TABLE tmp ADD COLUMN r lseg;
ALTER TABLE tmp ADD COLUMN s path;
ALTER TABLE tmp ADD COLUMN t box;
ALTER TABLE tmp ADD COLUMN u tinterval;
ALTER TABLE tmp ADD COLUMN v timestamp;
ALTER TABLE tmp ADD COLUMN w interval;
ALTER TABLE tmp ADD COLUMN x float8[];
ALTER TABLE tmp ADD COLUMN y float4[];
ALTER TABLE tmp ADD COLUMN z int2[];
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
        v, w, x, y, z)
   VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 
        'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', 
        314159, '(1,1)', '512',
        '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
        '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
        'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
SELECT * FROM tmp;
 initial | a |  b   |  c   |  d  |  e  | f |           g           |              h               | i |                                               j                                                |   k    |   l   |  m  |        n        |       p       |     q     |           r           |              s              |          t          |                      u                      |            v             |        w         |     x     |     y     |     z     
---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
         | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
(1 row)

DROP TABLE tmp;
--
-- rename - check on both non-temp and temp tables
--
CREATE TABLE tmp (regtable int);
CREATE TEMP TABLE tmp (tmptable int);
ALTER TABLE tmp RENAME TO tmp_new;
SELECT * FROM tmp;
 regtable 
----------
(0 rows)

SELECT * FROM tmp_new;
 tmptable 
----------
(0 rows)

ALTER TABLE tmp RENAME TO tmp_new2;
SELECT * FROM tmp;              -- should fail
ERROR:  relation "tmp" does not exist
SELECT * FROM tmp_new;
 tmptable 
----------
(0 rows)

SELECT * FROM tmp_new2;
 regtable 
----------
(0 rows)

DROP TABLE tmp_new;
DROP TABLE tmp_new2;
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1;
ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
ALTER TABLE tmp_view RENAME TO tmp_view_new;
-- analyze to ensure we get an indexscan here
ANALYZE tenk1;
-- 5 values, sorted 
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 unique1 
---------
       0
       1
       2
       3
       4
(5 rows)

DROP VIEW tmp_view_new;
-- toast-like relation name
alter table stud_emp rename to pg_toast_stud_emp;
alter table pg_toast_stud_emp rename to stud_emp;
-- FOREIGN KEY CONSTRAINT adding TEST
CREATE TABLE tmp2 (a int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"
CREATE TABLE tmp3 (a int, b int);
CREATE TABLE tmp4 (a int, b int, unique(a,b));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4"
CREATE TABLE tmp5 (a int, b int);
-- Insert rows into tmp2 (pktable)
INSERT INTO tmp2 values (1);
INSERT INTO tmp2 values (2);
INSERT INTO tmp2 values (3);
INSERT INTO tmp2 values (4);
-- Insert rows into tmp3
INSERT INTO tmp3 values (1,10);
INSERT INTO tmp3 values (1,20);
INSERT INTO tmp3 values (5,50);
-- Try (and fail) to add constraint due to invalid source columns
ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
ERROR:  column "c" referenced in foreign key constraint does not exist
-- Try (and fail) to add constraint due to invalide destination columns explicitly given
ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
ERROR:  column "b" referenced in foreign key constraint does not exist
-- Try (and fail) to add constraint due to invalid data
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
ERROR:  insert or update on table "tmp3" violates foreign key constraint "tmpconstr"
DETAIL:  Key (a)=(5) is not present in table "tmp2".
-- Delete failing row
DELETE FROM tmp3 where a=5;
-- Try (and succeed)
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
-- tmp4 is a,b
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
ERROR:  there is no unique constraint matching given keys for referenced table "tmp4"
DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
-- Foreign key adding test with mixed types
-- Note: these tables are TEMP to avoid name conflicts when this test
-- is run in parallel with foreign_key.sql.
CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
CREATE TEMP TABLE FKTABLE (ftest1 inet);
-- This next should fail, because inet=int does not exist
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
-- This should also fail for the same reason, but here we
-- give the column name
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
-- This should succeed, even though they are different types
-- because varchar=int does exist
DROP TABLE FKTABLE;
CREATE TEMP TABLE FKTABLE (ftest1 varchar);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
WARNING:  foreign key constraint "$1" will require costly sequential scans
DETAIL:  Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
-- As should this
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
WARNING:  foreign key constraint "$2" will require costly sequential scans
DETAIL:  Key columns "ftest1" and "ptest1" are of different types: character varying and integer.
DROP TABLE pktable cascade;
NOTICE:  drop cascades to constraint $2 on table fktable
NOTICE:  drop cascades to constraint $1 on table fktable
DROP TABLE fktable;
CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
                           PRIMARY KEY(ptest1, ptest2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
-- 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;
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
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);
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
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);
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
-- As does this...
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
     references pktable(ptest1, ptest2);
ERROR:  foreign key constraint "$1" cannot be implemented
DETAIL:  Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
-- add a check constraint
alter table atacc1 add constraint atacc_test1 check (test>3);
-- should fail
insert into atacc1 (test) values (2);
ERROR:  new row for relation "atacc1" violates check constraint "atacc_test1"
-- should succeed
insert into atacc1 (test) values (4);
drop table atacc1;
-- let's do one where the check fails when added
create table atacc1 ( test int );
-- insert a soon to be failing row
insert into atacc1 (test) values (2);
-- add a check constraint (fails)
alter table atacc1 add constraint atacc_test1 check (test>3);
ERROR:  check constraint "atacc_test1" is violated by some row
insert into atacc1 (test) values (4);
drop table atacc1;
-- let's do one where the check fails because the column doesn't exist
create table atacc1 ( test int );
-- add a check constraint (fails)
alter table atacc1 add constraint atacc_test1 check (test1>3);
ERROR:  column "test1" does not exist
drop table atacc1;
-- something a little more complicated
create table atacc1 ( test int, test2 int, test3 int);
-- add a check constraint (fails)
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
-- should fail
insert into atacc1 (test,test2,test3) values (4,4,2);
ERROR:  new row for relation "atacc1" violates check constraint "atacc_test1"
-- should succeed
insert into atacc1 (test,test2,test3) values (4,4,5);
drop table atacc1;
-- lets do some naming tests
create table atacc1 (test int check (test>3), test2 int);
alter table atacc1 add check (test2>test);
-- should fail for $2
insert into atacc1 (test2, test) values (3, 4);
ERROR:  new row for relation "atacc1" violates check constraint "$1"
drop table atacc1;
-- inheritance related tests
create table atacc1 (test int);
create table atacc2 (test2 int);
create table atacc3 (test3 int) inherits (atacc1, atacc2);
alter table atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
ERROR:  new row for relation "atacc2" violates check constraint "foo"
insert into atacc2 (test2) values (3);
-- fail and then succeed on atacc3
insert into atacc3 (test2) values (-3);
ERROR:  new row for relation "atacc3" violates check constraint "foo"
insert into atacc3 (test2) values (3);
drop table atacc3;
drop table atacc2;
drop table atacc1;
-- let's try only to add only to the parent
create table atacc1 (test int);
create table atacc2 (test2 int);
create table atacc3 (test3 int) inherits (atacc1, atacc2);
alter table only atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
ERROR:  new row for relation "atacc2" violates check constraint "foo"
insert into atacc2 (test2) values (3);
-- both succeed on atacc3
insert into atacc3 (test2) values (-3);
insert into atacc3 (test2) values (3);
drop table atacc3;
drop table atacc2;
drop table atacc1;
-- test unique constraint adding
create table atacc1 ( test int );
-- add a unique constraint
alter table atacc1 add constraint atacc_test1 unique (test);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
-- insert first value
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
ERROR:  duplicate key violates unique constraint "atacc_test1"
-- should succeed
insert into atacc1 (test) values (4);
-- try adding a unique oid constraint
alter table atacc1 add constraint atacc_oid1 unique(oid);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"
drop table atacc1;
-- let's do one where the unique 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 unique constraint (fails)
alter table atacc1 add constraint atacc_test1 unique (test);
NOTICE:  ALTER TABLE / ADD UNIQUE 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 one where the unique constraint fails
-- because the column doesn't exist
create table atacc1 ( test int );
-- add a unique constraint (fails)
alter table atacc1 add constraint atacc_test1 unique (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 unique constraint
alter table atacc1 add constraint atacc_test1 unique (test, test2);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"
-- 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"
-- 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, unique(test));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"
alter table atacc1 add unique (test2);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"
-- should fail for @@ second one @@
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
ERROR:  duplicate key violates unique constraint "atacc1_test_key"
drop table atacc1;
-- test primary key constraint adding
create table atacc1 ( test int );
-- add a primary key constraint
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"
-- insert first value
insert into atacc1 (test) values (2);
-- should fail
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);
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 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;
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)

-- 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;