CODEWITHSUNDEEP
postgresql
Travis Bear
Travis Bear

Reputation: 13849

Convert existing SMALLINT data to INTEGER?

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?

Upvotes: 6

Views: 11087

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324265

As Ondrej says, you can use ALTER TABLE .. ALTER COLUMN ... TYPE INTEGER.

However, this will trigger a full table re-write, locking the table against all concurrent writing or reading for the duration. This can be time consuming for large tables.

If you need to avoid that, you can make the change in stages.

  • BEGIN, add a new integer column and COMMIT.
  • BEGIN a transaction and LOCK TABLE ... IN EXCLUSIVE MODE to block concurrent inserts.
  • UPDATE the table, copying the smallint data to the integer column.
  • Now drop the smallint column, rename the integer column to the smallint's name, and COMMIT.

You can even avoid the LOCK TABLE and allow concurrent writes if you're willing to write a BEFORE INSERT OR UPDATE ON ... FOR EACH ROW trigger that copies the value from the smallint column to the integer column on any INSERT or UPDATE. You need to add this trigger within the same transaction you create the new integer column in so you catch rows added concurrently with your big UPDATE. To further reduce the impact of the change on your application you can update the rows in small batches in individual transactions, so you avoid long row-level locks.

I wrote more about this in this prior dba.SE answer.

Upvotes: 9

mys
mys

Reputation: 2473

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.

Upvotes: 14

Related Questions