[lug] Postgres Alter table

[rm at mamma.varadinet.de]

On Thu, Jul 12, 2001 at 01:16:08AM -0600, John Starkey wrote:
> Does anyone know how to alter a table type in postgres? I've been sitting
> on this one for a while, checked the manual several times. I see that you
> can add constraints but I don't see anything about types.
> 
> I've got an int value I need to make real.

Hi John,

AFAIK you can't alter the type of a column after the table
is created. One workarround for your problem would be (assuming
oldtable is the original tablei and n is the column you need to
alter):

  SELECT a, b, c, ..., n::float as n, m INTO TABLE tmp FROM oldtable;

Once this is done do:

  DROP oldtable;
  SELECT * INTO TABLE oldtable FROM tmp;
  
But carefull: a lot of the inner workings in Postgres depends on
a tables internal ID -- since you drop and then recreate a table the
ID of oldtable will change. If you have indices or view (or constraints etc.)
you need to drop and recreate them as well.

Hope this helps

 Ralf

> TIA,
> 
> John
> 
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug