Thread: ALTER COLUMN

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?

Thanks,
Fran

Fran Fabrizio writes:

> 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?

Yes.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> Fran Fabrizio writes:
>> 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?

> Yes.

Dump and reload can be avoided by inserting the data into a temp table
instead.  Should be at least somewhat faster.

            regards, tom lane

> >> 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.