postgresql 改变列数据类型 Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19559851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter a Column Data Type Postgres
提问by user2597012
Hey I have just started working on PostgreSQL, and I am wondering how can we change a column's data type, I tried the following command:
嘿，我刚刚开始研究 PostgreSQL，我想知道如何更改列的数据类型，我尝试了以下命令：
alter table tableName alter column columnName type timestamp with time zone;
However I got the following message:
但是我收到以下消息：
column "columnName" cannot be cast to type timestamp with time zone
The current column's data type is int, and i would like to change it to timestamp
当前列的数据类型为 int，我想将其更改为时间戳
采纳答案by Tomasz Myrta
Postgres doesn't know how to translate int to timestamp. There are several cases and usually they have different starting date.
Postgres 不知道如何将 int 转换为时间戳。有几种情况，通常它们有不同的开始日期。
- Create temporary column with timestamp
- Update table and copy data from old column to temporary column using your own translation
- Drop old column
- Rename temporary column.
- 创建带有时间戳的临时列
- 使用您自己的翻译更新表并将数据从旧列复制到临时列
- 删除旧列
- 重命名临时列。
If you look into documentation, you will find one line syntax with example how to convert unix time integer type:
如果您查看文档，您会发现一行语法以及如何转换 unix 时间整数类型的示例：
ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]
回答by Frank Conry
There is a better way to do this, with the USINGclause. Like so:
有一个更好的方法来做到这一点，使用USING子句。像这样：
ALTER TABLE tableName
ALTER columnName type TIMESTAMP WITH TIME ZONE
USING to_timestamp(columnName) AT TIME ZONE 'America/New_York';
回答by Ritesh Jha
Postgres does't allow int type column to change directly into timezone. To achive this, you have to first change column type to varchar and then change it to timezone.
Postgres 不允许 int 类型列直接更改为时区。要实现这一点，您必须首先将列类型更改为 varchar，然后将其更改为时区。
alter table tableName alter column columnName type varchar(64);
alter table tableName alter column columnName type varchar(64);
alter table tableName alter column columnName type timestamp with time zone;
alter table tableName alter column columnName type timestamp with time zone;
回答by Bashir Saidi Wamula
I achieved it from timestamp to timestamp with time zone by:
我通过以下方式实现了从时间戳到带时区的时间戳：
ALTER TABLE tableName ALTER COLUMN columnName SET DATA TYPE timestamp with time zone;
but if it is from timestamp to int or bigint you may need to do this:
但如果它是从时间戳到 int 或 bigint 你可能需要这样做：
ALTER TABLE tableName ALTER COLUMN columnName SET DATA TYPE int8 USING columnName::bigint
