[lug] Postgres Alter table

[Ryan Kirkpatrick]

On Thu, 12 Jul 2001 rm at mamma.varadinet.de wrote:

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

	This would be even easier if you could drop a column. Instead of
selecting out the entire table, select out just the column you want to
convert. Then alter the table to drop the column and then recreate. After
that load the data back in. One would have to be careful about order,
maybe pull out a key field along with it.
	Though PGSQL does not yet support dropping a column from a table,
but if I remember correctly, it is on the TODO list.

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

	This is not that much of a worry. Indices will be automatically
dropped along with the table when it is dropped. So you only need to
recreate them (probably from the original scheme you used to build the
table in the first place). Same story for constraints. Views probably will
not be dropped with the table and will have to be reconstructed. Only
other worries is if you have sequences assocaited with the table (in which
case handle them like views, though remember to restore their 'next'
value), and foriegn key relationships.
	Yea, changing a fields data type is not easy, but it isn't exactly
trival from a programming point of view either. Guess the moral of the
story is to carefully design your database schema and make sure its final
before you start storing data you want to keep.
	Anway, my two cents.

---------------------------------------------------------------------------
|   "For to me to live is Christ, and to die is gain."                    |
|                                            --- Philippians 1:21 (KJV)   |
---------------------------------------------------------------------------
|   Ryan Kirkpatrick  |  Boulder, Colorado  |  http://www.rkirkpat.net/   |
---------------------------------------------------------------------------