Re: Evolving databases (eg deleting columns)

If you can afford time-wise to do a full dump and restore, editing the dump file
seems safest. That way foreign keys and other dependencies are maintained when
you delete a column, since all those dependencies get re-created. I'm certainly
no expert, but that's the best solution I've found so far. I believe better
support for ALTER is planned for  a future release.

Wes Sheldahl




"Christian H. Stork" <cstork%ics.uci.edu@interlock.lexmark.com> on 07/25/2002
08:19:18 PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Evolving databases (eg deleting columns)


Hi everyone,

I'm new to databases and PostgreSQL in particular.  Currently, I'm
playing around with some test DBs, which already contain some valuable
data.

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

I tried using pg_dump and editing the dump file.  While reading the dump
file helped me to understand how I could do it, the editing of the data
was quite cumbersome.

I also know about ALTER, but there seems to be no way to delete columns.
Which means that I'd have to create a new table, populate it, but what
do I do with other tables that used the original table as FOREIGN KEYS?
These kinds of problems make me suspect that there are other ways of
evolving tables.

I was just wondering how other users of PostgreSQL solve this problem.
What are good recipes to evolve databases?

Any hints welcome!  Thanks,
--
Chris Stork (PhD student at UC Irvine)  http://www.ics.uci.edu/~cstork/
OpenPGP fingerprint: B08B 602C C806 C492 D069  021E 41F3 8C8D 50F9 CA2F

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster