Обсуждение: 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 ***
------------------------------------------------------------------------