| From: | "Mark Cowlishaw" <markc(at)ot(dot)com(dot)au> |
|---|---|
| To: | "Peter Eisentraut" <peter_e(at)gmx(dot)net>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | "Fran Fabrizio" <ffabrizio(at)exchange(dot)webmd(dot)net>, <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: ALTER COLUMN |
| Date: | 2001-05-10 04:35:01 |
| Message-ID: | 041f01c0d90a$93d5b310$5250460a@meta2k |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
> >> What's the best way to alter a column definition after the fact (i.e.
> >> int8-->int4). Is the answer dump, drop table, make new table with new
> >> definition and same name, import data?
...
> Dump and reload can be avoided by inserting the data into a temp table
> instead. Should be at least somewhat faster.
>
> regards, tom lane
I just had to do something similar * to this when I changed a varchar(100)
to varchar(255). The only problem with the temp-table method is that you
seem to have to re-enable all the constraints for that table. Is this
observation correct? (In 7.0.3)
Surely there is a better way to do this?! Altering table in-place should be
possible, even if it means that table is inaccessable for the duration.
Cheers.
* Actually what I did was dump the data to a file, create a new table from
the dumped data, ensured it was all there like it was meant to be, renamed
the old table, renamed the new table to the original name and then updated
all my constraints/triggers.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Igor | 2001-05-10 04:55:36 | Vacuudb problem |
| Previous Message | Tatsuo Ishii | 2001-05-10 02:09:52 | Re: Charset and encoding |