postgresql - 将现有的 SMALLINT 数据转换为 INTEGER？

Question

我在 Postgres 中有一个大约 100 万行的表。此表中的一列存储 SMALLINT 数据。现在我需要在此列中存储比我预期更大的数字。如何将这个现有列从 SMALLINT 转换为 INTEGER？

Answer 1

您需要将列数据类型从 smallint 更改为 integer:

alter table T alter C type integer

T和C分别是表名和列名。

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.

请参阅ALTER TABLE文档。