Thread: change column data type from smallint to integer

Hello,

we run 'out of space' in one of our columns which is smallint and we
need to make it integer.

I did some research and found out that the only way is to create a new
column with integer data type, then SET new = old, then drop old and
rename new like old [1].

Could somebody confirm if this is really the best way and if it is
stable in respect to indexes, RI, triggers, stored procedures, etc.

Thank you,
Iv

[1]
http://www.foranewliberty.com/blog/archives/2005/01/15/changing-data-types-in-postgresql/

On Thu, Sep 08, 2005 at 04:22:07AM +0200, pobox@verysmall.org wrote:

Hi,

> we run 'out of space' in one of our columns which is smallint and we
> need to make it integer.
>
> I did some research and found out that the only way is to create a new
> column with integer data type, then SET new = old, then drop old and
> rename new like old [1].
>
> Could somebody confirm if this is really the best way and if it is
> stable in respect to indexes, RI, triggers, stored procedures, etc.

In 8.0, you can alter the type directly in the table.  In releases
before 8.0, you have found the right workaround.

--
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
"Crear es tan difícil como ser libre" (Elsa Triolet)

Alvaro Herrera wrote:
> On Thu, Sep 08, 2005 at 04:22:07AM +0200, pobox@verysmall.org wrote:
>
> Hi,
>
>> we run 'out of space' in one of our columns which is smallint and we
>> need to make it integer.
>>
>> I did some research and found out that the only way is to create a new
>> column with integer data type, then SET new = old, then drop old and
>> rename new like old [1].
>>
>> Could somebody confirm if this is really the best way and if it is
>> stable in respect to indexes, RI, triggers, stored procedures, etc.
>
> In 8.0, you can alter the type directly in the table.  In releases
> before 8.0, you have found the right workaround.

Thank you, Alvaro, for the confirmation!