postgresql 将列类型从整数更改为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30361756/
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
Changing a column type from integer to string
提问by eye_mew
Using PostgreSQL, what's the command to migrate an integercolumn type to a stringcolumn type?
使用PostgreSQL，将integer列类型迁移到列类型的命令是什么string？
Obviously I'd like to preserve the data, by converting the old integer data to strings.
显然，我想通过将旧的整数数据转换为字符串来保留数据。
回答by Crozin
You can convert from INTEGERto CHARACTER VARYINGout-of-the-box, all you need is ALTER TABLEquery chaning column type:
您可以转换INTEGER为CHARACTER VARYING开箱即用，您需要的只是ALTER TABLE查询更改列类型：
PostgreSQL 9.3 Schema Setup:
PostgreSQL 9.3 架构设置：
CREATE TABLE tbl (col INT);
INSERT INTO tbl VALUES (1), (10), (100);
ALTER TABLE tbl ALTER COLUMN col TYPE CHARACTER VARYING(10);
Query 1:
查询 1：
SELECT col, pg_typeof(col) FROM tbl
结果：
| col | pg_typeof |
|-----|-------------------|
| 1 | character varying |
| 10 | character varying |
| 100 | character varying |
回答by Andreas
I suggest a four step process:
我建议一个四步过程：
- Create a new string column. name it temp for now. See http://www.postgresql.org/docs/9.3/static/ddl-alter.htmlfor details
- Set the string column. something like
update myTable set temp=cast(intColumn as text)see http://www.postgresql.org/docs/9.3/static/functions-formatting.htmlfor more interesting number->string conversions
- 创建一个新的字符串列。现在将其命名为 temp。有关详细信息，请参阅http://www.postgresql.org/docs/9.3/static/ddl-alter.html
- 设置字符串列。类似
update myTable set temp=cast(intColumn as text)看到http://www.postgresql.org/docs/9.3/static/functions-formatting.html以获得更有趣的数字->字符串转换
Make sure everything in temp looks the way you want it.
确保 temp 中的所有内容都符合您的要求。
- Remove your old integer column. Once again, see http://www.postgresql.org/docs/9.3/static/ddl-alter.htmlfor details
- Rename temp to the old column name. Again: http://www.postgresql.org/docs/9.3/static/ddl-alter.html
- 删除旧的整数列。再次，请参阅http://www.postgresql.org/docs/9.3/static/ddl-alter.html了解详情
- 将 temp 重命名为旧列名。再次：http: //www.postgresql.org/docs/9.3/static/ddl-alter.html
This assumes you can perform the operation while no clients are connected; offline. If you need to make this (drastic) change in an online table, take a look at setting up a new table with triggers for live updates, then swap to the new table in an atomic operation. see ALTER TABLE without locking the table?
这假设您可以在没有客户端连接的情况下执行操作；离线。如果您需要在在线表中进行此（剧烈）更改，请查看设置带有实时更新触发器的新表，然后在原子操作中切换到新表。在不锁定表的情况下查看ALTER TABLE？
