Thread: 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
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
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
On Thu, 2002-07-25 at 20:19, Christian H. Stork wrote: > My question: How can I evolve databases (ie deleting columns, > adding/changing/removing constraints, etc)? PostgreSQL doesn't support deleting columns (I've had this issue myself recently). However, there is a workaround. It is described at: http://postgresql.org/docs/faq-english.html#4.4 It states: 4.4) How do you remove a column from a table? We do not support ALTER TABLE DROP COLUMN, but do this: BEGIN; LOCK TABLE old_table; SELECT ... -- select all columns but the one you want to remove INTO TABLE new_table FROM old_table; DROP TABLE old_table; ALTER TABLE new_table RENAME TO old_table; COMMIT; I hope this helps. -- Kevin Breit <mrproper@ximian.com>
You'll also have to recreate any triggers/referential integrity constraints On Saturday 27 July 2002 10:20 pm, you wrote: > On Thu, 2002-07-25 at 20:19, Christian H. Stork wrote: > > My question: How can I evolve databases (ie deleting columns, > > adding/changing/removing constraints, etc)? > > PostgreSQL doesn't support deleting columns (I've had this issue myself > recently). However, there is a workaround. It is described at: > http://postgresql.org/docs/faq-english.html#4.4 > > It states: > > > 4.4) How do you remove a column from a table? > We do not support ALTER TABLE DROP COLUMN, but do this: > > BEGIN; > LOCK TABLE old_table; > SELECT ... -- select all columns but the one you want to remove > INTO TABLE new_table > FROM old_table; > DROP TABLE old_table; > ALTER TABLE new_table RENAME TO old_table; > COMMIT; > > I hope this helps. -- Regards, Oliver GT webMarque +44(0)1792 655968 / 07808 678244 / oliver@gtwebmarque.com NOTE No contracts may be concluded on behalf of GT webMarque by means of e-mail communications. The contents of this e-mail are confidential to the intended recipient at the e-mail address to which it has been addressed; it may not be disclosed to or used by anyone other than this addressee, nor may it be copied in any way. If received in error please return to sender via e-mail. DISCLAIMER Please note that neither GT webMarque Ltd nor the sender accept any responsibility for viruses transmitted via e-mail. It is your responsibility to scan attachments (if any).