Re: ALTER TABLE & COLUMN

Date: 29 Nov 2002 15:22:06 -0500
From: Neil Conway <neilc(at)samurai(dot)com>
>
> > I am surprised by the fact that altering tables and columns is so
> > limited in PostgreSQL, this powerful DBMS! Changing column types and
> > droping columns are missing important features I think.
>
Actually this seems to be one of the rare areas where MySQL is better.
PG 7.3 improves a lot though.

> As for changing the type of a column, how would this be anything more
> than syntactic sugar over renaming the existing column to a temp name,
> adding a new column with the appropriate type & name, moving the data
> from the old column to the new one, and dropping the old one?
>
Is this latter procedure safe with respect to triggers?
Or is it necessary to recompile all triggers on the table?
The latter would be particularly cumbersume with PostgreSQL as there
is no ALTER TRIGGER RECOMPILE statement like in Oracle.

> Since a lot of data type changes are not obvious (how do you convert the
> data from one type to another, in all cases?), I don't see a real
> problem leaving this in the hands of the admin.
>
This assumes that the database admin and the application develloper are the
same person. In general this assumption is wrong and they are often even employed
by different companies so that the admin cannot be bothered with stupid schema
changes and the application develloper never gets DBA privileges.

So please keep on improving the possibility to make schema changes with ALTER commands.

Christoph Dalitz