Re: ALTER TABLE & COLUMN

> -----Original Message-----
> From: Christoph Dalitz [mailto:christoph(dot)dalitz(at)hs-niederrhein(dot)de]
>
> 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.

I've started a new project a few weeks ago. I had to make a choice between these two dbs. Just because table and column administration is difficult on PG, I chose MySQL (although I'd found PG better on all other areas). Then at that time PG 7.3 comes out and solves at least some of the problems! What a coincedence! Now I will switch to PG.

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

That's a huge problem really, not a syntactic sugar, otherwise more of a syntactic salt :)
I want some of my users (not all are db admin) to be able to alter tables with pgAdmin. How can I expect them to achieve those N-step voodoo as it will be seen to them?

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

This one is also true when we deal with bigger projects, like I will start in a month. I think PG should consider that "small" administration problem. PG deserves this.

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