Convert existing SMALLINT data to INTEGER?

Question

I have a table in Postgres with ~ 1M rows. One column in this table stores SMALLINT data. Now I need to store numbers in this column that are larger than I anticipated. How can I convert this existing column from SMALLINT to INTEGER?

Answer 1

You need to change column data type from smallint to integer:

alter table T alter C type integer

T and C are table and column name respectively.

SET DATA TYPE

This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column type. The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

Please see ALTER TABLE documentation.