Skip site navigation (1) Skip section navigation (2)

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

From: Thomas Swan <tswan-lst(at)ics(dot)olemiss(dot)edu>
To: Joel Burton <jburton(at)scw(dot)org>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Feature Request: ALTER FUNCTION (or something like that)
Date: 2001-03-28 16:55:35
Message-ID: 5.1.0.12.0.20010328102516.028cacc0@tangent.ics.olemiss.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
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...


In response to

pgsql-hackers by date

Next:From: Mikheev, VadimDate: 2001-03-28 17:58:43
Subject: RE: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
Previous:From: Tom LaneDate: 2001-03-28 16:09:28
Subject: Re: Unsigned ints (Help)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group