+-- 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 might 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 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 default for table alter2.t1 column f1
+NOTICE: drop cascades to table alter2.t1
+NOTICE: drop cascades to constraint t1_f2_check on table alter2.t1