One thought did just occur to me.  It is at least theoretically possible to
simplisticly migrate on column type to another by reading in the data and
oid of the row into a struct, drop the column, create a new column with the
correct data type, and populate.  This is ugly, but it is better than saying
"no, you can't".

Geoff

-----Original Message-----
From: Gowey, Geoffrey 
Sent: Monday, September 24, 2001 10:53 AM
To: 'Alex Pilosov'; Gowey, Geoffrey
Cc: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Changing data types



>This is not for -hackers. 

How so?

>And the answer is "no, you can't". Recreate the table with correct types
>and insert the old values into it.

You're kidding me, right?  *prepares to gargle* MS Sql server can.  Surely
we can implement this feature or aren't we aiming to go head to head with
commercial rdbms'?

>>On Mon, 24 Sep 2001, Gowey, Geoffrey wrote:

>> I posted this in my last message, but have not heard anything yet so I'm
>> wondering if it was overlooked.  I need to know how to change a column
from
>> being say a varchar(9) to an integer.  Does anyone know how to change the
>> data type?

Mon, 24 Sep 2001 10:55:54 -0400
"Gowey, Geoffrey" <ggowey@rxhope.com>
Re: Changing data types
"Gowey, Geoffrey" wrote:
> 
> One thought did just occur to me.  It is at least theoretically possible to
> simplisticly migrate on column type to another by reading in the data and
> oid of the row into a struct, drop the column, create a new column with the
> correct data type, and populate.  This is ugly, but it is better than saying
> "no, you can't".

The DROP COLUMN part is the one that is what's really hard. 

It is not currently supported in postgreSQL

Supporting it comes up now and then, but as the solution (changing
system tables 
and then rewriting the whole table) is considered ugly in current
implementation
it has always windled down to not doing it.

The way to manually change column type is something like:

alter table mytable add column newcolumn int;
update table set newcolumn = oldcolumn;
alter table rename oldcolumn to __del__001;
alter table rename newcolumn to oldcolumn;


but you can't DROP COLUMN without recreating the TABLE

------------
Hannu

Mon, 24 Sep 2001 17:26:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: Changing data types