Re: Feature Request: ALTER FUNCTION (or something like that)

At 3/28/2001 09:52 AM, Joel Burton wrote:
>On Wed, 28 Mar 2001, Bruce Momjian wrote:
>
> > > Actually,
> > >
> > > This reminds me of something I have been meaning to ask. All the new
> > > performance features are cool, but what I really need are all the ALTER
> > > TABLE ... functions implemented. In 7.0.x you could only add columns and
> > > foreign keys. You couldn't drop anything or add stuff like CHECK
> > > constraints. Has this situation changed for 7.1?
> > >
> >
> > Not done in 7.1. I hope for 7.2.
>
>If you're curious, Christopher, on the TODO list is a whole page on the
>DROP COLUMN bit. There's been some headscratching about how we want to
>DROP COLUMNs (whether to hide them but keep them there, which may be
>easier to undo, and probably easier to develop, and doesn't require any
>time to process), or whether to actually recreate w/o the offending
>column.

You could hide and then delete on a vacuum.

On a personal note, one "work around" for altering a table column is to
rename the column, then do an update where you set the new columns value to
the renamed columns value thus copying its data over to the new column with
any casting if necessary.

It doesn't always work, but it's one way to try.

But then you're left with the renamed column dangling...

Selecting the table(with your preferred columns) into a new table can clean it
Delete the old table and rename the new table to the old name...

You lose the constraints/triggers you had in place though...

And you must pray that no one is altering the DB while your doing this...