ALTER TABLE rewrite. New cool stuff:

* ALTER ... ADD COLUMN with defaults and NOT NULL constraints works per SQL
spec.  A default is implemented by rewriting the table with the new value
stored in each row.

* ALTER COLUMN TYPE.  You can change a column's datatype to anything you
want, so long as you can specify how to convert the old value.  Rewrites
the table.  (Possible future improvement: optimize no-op conversions such
as varchar(N) to varchar(N+1).)

* Multiple ALTER actions in a single ALTER TABLE command.  You can perform
any number of column additions, type changes, and constraint additions with
only one pass over the table contents.

Basic documentation provided in ALTER TABLE ref page, but some more docs
work is needed.

Original patch from Rod Taylor, additional work from Tom Lane.

6 files changed, 196 insertions, 3 deletions

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 089651c7278..feb08520326 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -7,7 +7,7 @@ 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;
+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;
@@ -419,7 +419,6 @@ create table atacc1 ( test int );
 insert into atacc1 (test) values (NULL);
 -- 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:  column "test" contains null values
 insert into atacc1 (test) values (3);
 drop table atacc1;
@@ -1143,3 +1142,96 @@ select f3,max(f1) from foo group by f3;
  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;
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index bd4bccfc9d4..5aae3720d30 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -145,6 +145,28 @@ SELECT * FROM FKTABLE;
         |        |      8
 (5 rows)
 
+-- Try altering the column type where foreign keys are involved
+ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
+ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
+SELECT * FROM PKTABLE;
+ ptest1 | ptest2 | ptest3  
+--------+--------+---------
+      1 |      3 | Test1-2
+      3 |      6 | Test3
+      4 |      8 | Test4
+      1 |      4 | Test2
+(4 rows)
+
+SELECT * FROM FKTABLE;
+ ftest1 | ftest2 | ftest3 
+--------+--------+--------
+      1 |      3 |      5
+      3 |      6 |     12
+        |        |      0
+        |        |      4
+        |        |      8
+(5 rows)
+
 DROP TABLE PKTABLE CASCADE;
 NOTICE:  drop cascades to constraint constrname on table fktable
 DROP TABLE FKTABLE;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f1890595ea0..e1205a9b001 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -613,3 +613,12 @@ SELECT * FROM inhf; /* Single entry with value 'text' */
  text
 (1 row)
 
+-- Test changing the type of inherited columns
+insert into d values('test','one','two','three');
+alter table a alter column aa type integer using bit_length(aa);
+select * from d;
+ aa | bb  | cc  |  dd   
+----+-----+-----+-------
+ 32 | one | two | three
+(1 row)
+
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index dcd968a0b65..6077788583c 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -9,7 +9,7 @@ COMMENT ON TABLE tmp_wrong IS 'table comment';
 COMMENT ON TABLE tmp IS 'table comment';
 COMMENT ON TABLE tmp IS NULL;
 
-ALTER TABLE tmp ADD COLUMN a int4;
+ALTER TABLE tmp ADD COLUMN a int4 default 3;
 
 ALTER TABLE tmp ADD COLUMN b name;
 
@@ -918,3 +918,60 @@ select * from foo;
 update foo set f3 = 'zz';
 select * from foo;
 select f3,max(f1) from foo group by f3;
+
+-- Simple tests for alter table column type
+alter table foo alter f1 TYPE integer; -- fails
+alter table foo alter f1 TYPE varchar(10);
+
+create table anothertab (atcol1 serial8, atcol2 boolean,
+	constraint anothertab_chk check (atcol1 <= 3));
+
+insert into anothertab (atcol1, atcol2) values (default, true);
+insert into anothertab (atcol1, atcol2) values (default, false);
+select * from anothertab;
+
+alter table anothertab alter column atcol1 type boolean; -- fails
+alter table anothertab alter column atcol1 type integer;
+
+select * from anothertab;
+
+insert into anothertab (atcol1, atcol2) values (45, null); -- fails
+insert into anothertab (atcol1, atcol2) values (default, null);
+
+select * from anothertab;
+
+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;
+alter table anothertab alter column atcol1 type boolean
+        using case when atcol1 % 2 = 0 then true else false end; -- fails
+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
+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;
+
+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;
+
+alter table another
+  alter f1 type text using f2 || ' more',
+  alter f2 type bigint using f1 * 10;
+
+select * from another;
+
+drop table another;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 34fb787680d..ad1274c7f84 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -97,6 +97,12 @@ UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2;
 -- Check FKTABLE for update of matched row
 SELECT * FROM FKTABLE;
 
+-- Try altering the column type where foreign keys are involved
+ALTER TABLE PKTABLE ALTER COLUMN ptest1 TYPE bigint;
+ALTER TABLE FKTABLE ALTER COLUMN ftest1 TYPE bigint;
+SELECT * FROM PKTABLE;
+SELECT * FROM FKTABLE;
+
 DROP TABLE PKTABLE CASCADE;
 DROP TABLE FKTABLE;
 
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 57f18673bfa..7bfe6cb7f2e 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -142,3 +142,10 @@ CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
 CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS);
 INSERT INTO inhf DEFAULT VALUES;
 SELECT * FROM inhf; /* Single entry with value 'text' */
+
+-- Test changing the type of inherited columns
+insert into d values('test','one','two','three');
+
+alter table a alter column aa type integer using bit_length(aa);
+
+select * from d;