Thread: ALTER TABLE
Greetings, Is there a way to drop a field from a table? ALTER TABLE seems to only allow the adding and modification of fields. Thanks. Jonathan Karlen ********************************************************** Jonathan R. Karlen Karlen Internet Solutions jkarlen@karlen.com http://www.karlen.com
Hi, To the best of my knowledge, still no way of droping a field directly by using ALTER. Usual trick is to do something like: SELECT <fields>,<you>,<want>,<to>,<keep> INTO temp_table FROM <table_you_want_to_alter>; DROP TABLE <table_you_want_to_alter>; ALTER TABLE temp_table RENAME TO <table_you_want_to_alter>; But bear in my that: - this will not preserve oid's... - I think things like RULES and TRIGGERS on that table will be affected (i.e. not work) since these use the table OID and will have to be regenerated - constraints, indices, primary keys etc. will be lost and have to be regenerated HTH, Stuart >Greetings, > >Is there a way to drop a field from a table? ALTER TABLE seems to only >allow the adding and modification of fields. > >Thanks. > >Jonathan Karlen >********************************************************** >Jonathan R. Karlen >Karlen Internet Solutions >jkarlen@karlen.com >http://www.karlen.com +--------------------------+--------------------------------------+ | Stuart C. G. Rison | Ludwig Institute for Cancer Research | +--------------------------+ 91 Riding House Street | | N.B. new phone code!! | London, W1P 8BT | | Tel. +44 (0)207 878 4041 | UNITED KINGDOM | | Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk | +--------------------------+--------------------------------------+
On Mon, 9 Aug 1999, Jonathan R. Karlen wrote: JRK> Is there a way to drop a field from a table? ALTER TABLE seems to only JRK> allow the adding and modification of fields. Try to select all content to temporary table, then drop old table, create new with unneeded column(s) removed, and them reselect all data back. Also, don't forget to re-create indexes, rules, views and other table-related things as they are reference table by its (internal to Postgres) ID. Also don't forget to create needed permissions. For large tables, dumping, editing schema & data with simple script and then recreating database may be simpler solution... Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] ------------------------------------------------------------------------ *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru *** ------------------------------------------------------------------------