sql - 列复制和更新与列创建和插入

Question

我在 PostgreSQL 9.2.10 中有一个包含 3200 万行和 31 列的表。我正在通过添加具有更新值的列来更改表格。

例如，如果初始表是:

id     initial_color
--     -------------
1      blue
2      red
3      yellow

我正在修改表格，结果是:

id     initial_color     modified_color
--     -------------     --------------
1      blue              blue_green
2      red               red_orange
3      yellow            yellow_brown

我有代码可以读取 initial_color 列并更新值。

鉴于我的表有 3200 万行并且我必须在 31 列中的五列上应用此过程，执行此操作的最有效方法是什么？我目前的选择是:

1. 复制列并更新新列中的行
2. 创建一个空列并插入新值

我可以一次选择一列，也可以一次选择所有五列。列类型是 character varying 或 character。

Answer 1

The columns types are either character varying or character.

不要使用character，那是一种误解。 varchar 没问题，但我建议只为任意字符数据使用 text。

• Any downsides of using data type "text" for storing strings?

Given that my table has 32 million rows and that I have to apply this procedure on five of the 31 columns, what is the most efficient way to do this?

如果您没有依赖现有表的对象( View 、外键、函数)，最有效的方法是创建一个新表。像这样的东西(细节取决于你安装的细节):

BEGIN;
LOCK TABLE tbl_org IN SHARE MODE;  -- to prevent concurrent writes

CREATE TABLE tbl_new (LIKE tbl_org INCLUDING STORAGE INCLUDING COMMENTS);

ALTER tbl_new ADD COLUMN modified_color text
            , ADD COLUMN modified_something text;
            -- , etc
INSERT INTO tbl_new (<all columns in order here>)
SELECT <all columns in order here>
    ,  myfunction(initial_color) AS modified_color  -- etc
FROM   tbl_org;
-- ORDER  BY tbl_id;  -- optionally order rows while being at it.

-- Add constraints and indexes like in the original table here

DROP tbl_org;
ALTER tbl_new RENAME TO tbl_org;
COMMIT;

如果你有依赖对象，你需要做更多。

要么是，请务必添加一次全部五个。如果您在单独的查询中更新每个，由于 Postgres 的 MVCC 模型，您每次都会编写另一个行版本。

具有更多详细信息、链接和解释的相关案例:

• Updating database rows without locking the table in PostgreSQL 9.2
• Best way to populate a new column in a large table?
• Optimizing bulk update performance in PostgreSQL

在创建新表时，您还可以以优化的方式对列进行排序:

• Calculating and saving space in PostgreSQL