Re: Evolving databases (eg deleting columns) - Mailing list pgsql-general

On Thu, Jul 25, 2002 at 05:19:18PM -0700, Christian H. Stork wrote:

> My question: How can I evolve databases (ie deleting columns,
> adding/changing/removing constraints, etc)?

You can use ALTER TABLE for adding & removing constraints.  But you
can't delete columns.

You can leave the columns in, however.  You can add a new column,
renaming the former column to something new (like colname_dead), and
name the new column to the name of the original column.  Then do

    UPDATE table SET colname=colname_dead;
    UPDATE table SET colname_dead=NULL;

Then, make that column null always.  There's very little overhead
attached to a column with all nulls (4 bytes?  I think that's it.
There's a map that needs to be maintained, but that's it).

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110