Re: alter table

Hi,

alter table syntax currently (as of 7.1.3 at least) does not support changing a
column type or dropping a column. I believe these are scheduled to be supported
in a future release. There are two possible workarounds for these operations.

One is to create a temporary table with the same schema as your existing table,
select your data into the temporary table, drop the original table, create the
table with the new column definitions (or without the columns you're wanting to
drop), then select the data you want from the temporary table into the new
table, and drop the temporary table.

That will work fine, UNLESS you have some foreign keys, triggers, or other sorts
of dependencies on other tables. If you do, then the above will still appear to
work, but will also silently break those links, as triggers will continue to
refer to the oid of the old table. In this case, the most conservative advice
I've seen is to back up the entire database, both schema and data, with pg_dump,
then edit the SQL in the resulting file to change any table definitions that
need changing. If you're dropping a column, you'll probably also need to delete
the data for that column from the table's COPY statement. Then drop the
database, and restore it from your modified backup. That way any linkages to the
altered table will be recreated with the correct oid and therefore remain in
place. Obviously, this approach will require your database to be offline for as
long as it takes to drop and restore it.

Obligatory disclaimer: I have used the first method successfully, but I haven't
tried the second method (yet), it's based on earlier postings to this list. If I
have misstated it in any way, I trust that I'll be corrected in short order.
And of course you can always check the list archives.

Thanks,

Wes Sheldahl




"Yuri A. Kabaenkov" <sec%artofit.com@interlock.lexmark.com> on 11/30/2001
10:22:56 AM

Please respond to sec <sec%artofit.com@interlock.lexmark.com>

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] alter table


Hello,

      I've read documentation on alter table syntax and doesn't find
      anything about change column type or drop column.

      Also when i add column by command
      alter table test add column a integer not null default '10'

      It adds column but doesn't set default value.

      How can i fix it?



------------
With respect,
Yuri A. Kabaenkov
hellman@artofit.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)