PostgreSQL v7.4 ALTER TABLE 更改列
我需要更改 PostgreSQL v7.4 数据库中表中 CHAR 列的长度。此版本不支持使用 ALTER TABLE 语句直接更改列类型或大小的功能。因此，例如，直接将列从 CHAR(10) 更改为 CHAR(20) 是不可能的（是的，我知道，“使用 varchars”，但这在我当前的情况下不是一个选项）。有人对如何最好地实现这一目标有任何建议/技巧吗？我最初的想法：
-- 将表的数据保存在新的“保存”表中。 创建表 save_data AS SELECT * FROM table_to_change;
-- 从第一列中删除要更改的列。 ALTER TABLE table_to_change DROP column_name1; -- 对于从需要修改的第一列开始的每一列 ALTER TABLE table_to_change DROP column_name2; ...
-- 使用 CHAR 列的新大小重新添加列 ALTER TABLE table_to_change ADD column_name1 CHAR(new_size); -- 对于上面删除的每一列 ALTER TABLE table_to_change ADD column_name2...
-- 从“保存”表中复制数据 更新要更改的表 SET column_name1=save_data.column_name1, -- 对于上面删除/读取的每一列 列名2=保存日期.列名2, ... 来自保存数据 WHERE table_to_change.primary_key=save_data.primay_key;
哎呀！希望有更好的方法吗？任何建议表示赞赏。谢谢！
I have a need to change the length of CHAR columns in tables in a PostgreSQL v7.4 database. This version did not support the ability to directly change the column type or size using the ALTER TABLE statement. So, directly altering a column from a CHAR(10) to CHAR(20) for instance isn't possible (yeah, I know, "use varchars", but that's not an option in my current circumstance). Anyone have any advice/tricks on how to best accomplish this? My initial thoughts:
-- Save the table's data in a new "save" table.
CREATE TABLE save_data AS SELECT * FROM table_to_change;
-- Drop the columns from the first column to be changed on down.
ALTER TABLE table_to_change DROP column_name1; -- for each column starting with the first one that needs to be modified
ALTER TABLE table_to_change DROP column_name2;
...
-- Add the columns back, using the new size for the CHAR column
ALTER TABLE table_to_change ADD column_name1 CHAR(new_size); -- for each column dropped above
ALTER TABLE table_to_change ADD column_name2...
-- Copy the data bace from the "save" table
UPDATE table_to_change
SET column_name1=save_data.column_name1, -- for each column dropped/readded above
column_name2=save_date.column_name2,
...
FROM save_data
WHERE table_to_change.primary_key=save_data.primay_key;
Yuck! Hopefully there's a better way? Any suggestions appreciated. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不是 PostgreSQL，而是在 Oracle 中，我通过以下方式更改了列的类型：
TMP_COL）和新数据类型（即：CHAR(20) 的新列code>))UPDATE TBL SET TMP_COL = OLD_COL;OLD_COLTMP_COL重命名为OLD_COLNot PostgreSQL, but in Oracle I have changed a column's type by:
TMP_COL) and the new data type (ie:CHAR(20))UPDATE TBL SET TMP_COL = OLD_COL;OLD_COLTMP_COLtoOLD_COL我将使用 COPY 将表内容转储到平面文件，删除表，使用正确的列设置重新创建它，然后重新加载（再次使用 COPY）。
http://www.postgresql.org/docs/7.4/static/ sql-copy.html
执行此操作时出现停机是否可以接受？显然，我刚才描述的需要使表在一段时间内不可用，多长时间取决于数据大小和您正在使用的硬件。
编辑：但是 COPY 比 INSERT 和 UPDATE 快很多。根据文档，您可以使用 BINARY 模式使其更快。 BINARY 使其与其他 PGSQL 安装的兼容性较差，但您不会关心这一点，因为您只想将数据加载到从中转储数据的同一实例。
I would dump the table contents to a flat file with COPY, drop the table, recreate it with the correct column setup, and then reload (with COPY again).
http://www.postgresql.org/docs/7.4/static/sql-copy.html
Is it acceptable to have downtime while performing this operation? Obviously what I've just described requires making the table unusable for a period of time, how long depends on the data size and hardware you're working with.
Edit: But COPY is quite a bit faster than INSERTs and UPDATEs. According to the docs you can make it even faster by using BINARY mode. BINARY makes it less compatible with other PGSQL installs but you won't care about that because you only want to load the data to the same instance that you dumped it from.
解决你的问题的最好方法是将 pg 升级到不太过时的东西:)
说真的。 7.4 很快就会从“支持的版本”中删除，所以我不会等待 7.4 投入生产时发生这种情况。
The best approach to your problem is to upgrade pg to something less archaic :)
Seriously. 7.4 is going to be removed from "supported versions" pretty soon, so I wouldn't wait for it to happen with 7.4 in production.