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?

Accepted Answer

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.

Convert existing SMALLINT data to INTEGER?

Solution

Problem