如何使用 Postgres 中的 CSV 文件中的值更新选定的行? [英] How to update selected rows with values from a CSV file in Postgres?

问题描述

我正在使用 Postgres，并且想要进行一个大型更新查询，该查询将从 CSV 文件中提取，假设我有一个包含 (id,banana,apple) 的表.

I'm using Postgres and would like to make a big update query that would pick up from a CSV file, lets say I got a table that's got (id, banana, apple).

我想运行一个更新来更改香蕉而不是苹果，每个新香蕉及其 ID 都将保存在一个 CSV 文件中.

I'd like to run an update that changes the Bananas and not the Apples, each new Banana and their ID would be in a CSV file.

我尝试查看 Postgres 站点，但这些示例让我很沮丧.

I tried looking at the Postgres site but the examples are killing me.

推荐答案

COPY 将文件复制到临时临时表并从那里更新实际表.喜欢:

COPY the file to a temporary staging table and update the actual table from there. Like:

CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- but see below

COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);

UPDATE tbl
SET    banana = tmp_x.banana
FROM   tmp_x
WHERE  tbl.id = tmp_x.id;

DROP TABLE tmp_x; -- else it is dropped at end of session automatically

如果导入的表与要更新的表完全匹配，这可能很方便:

If the imported table matches the table to be updated exactly, this may be convenient:

CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;

创建一个与现有表结构匹配的空临时表，没有约束.

Creates an empty temporary table matching the structure of the existing table, without constraints.

在 Postgres 10 之前，SQL COPY 为此需要超级用户权限.
在 Postgres 11 或更高版本中，还有一些预定义角色(以前默认角色")以允许它.手册:

Up to Postgres 10, SQL COPY requires superuser privileges for this.
In Postgres 11 or later, there are also some predefined roles (formerly "default roles") to allow it. The manual:

COPY 命名文件或命令只允许数据库超级用户或被授予角色 pg_read_server_files 之一的用户，pg_write_server_files 或 pg_execute_server_program [...]

COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program [...]

psql 元命令 copy 适用于任何数据库角色.手册:

执行前端(客户端)复制.这是一个运行SQL COPY 命令，但不是服务器读取或写入指定文件，psql读取或写入文件并路由数据服务器和本地文件系统之间.这意味着文件可访问性和特权是本地用户的，而不是本地用户的服务器，并且不需要 SQL 超级用户权限.

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

临时表的作用域仅限于单个角色的单个session，所以上面必须在同一个psql session中执行:

The scope of temporary tables is limited to a single session of a single role, so the above has to be executed in the same psql session:

CREATE TEMP TABLE ...;
copy tmp_x FROM '/absolute/path/to/file' (FORMAT csv);
UPDATE ...;

如果您在 bash 命令中编写此脚本，请确保将其全部包装在 单个 psql 调用中.喜欢:

If you are scripting this in a bash command, be sure to wrap it all in a single psql call. Like:

echo 'CREATE TEMP TABLE tmp_x ...; copy tmp_x FROM ...; UPDATE ...;' | psql

通常，您需要元命令 \ 在 psql 中的 psql 元命令和 SQL 命令之间切换，但是 copy 是此规则的一个例外.再次看手册:

Normally, you need the meta-command \ to switch between psql meta commands and SQL commands in psql, but copy is an exception to this rule. The manual again:

特殊解析规则适用于 copy 元命令.与大多数其他元命令不同，该行的其余部分始终被视为 copy 的参数，并且在参数中既不执行变量插值，也不执行反引号扩展.

special parsing rules apply to the copy meta-command. Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of copy, and neither variable interpolation nor backquote expansion are performed in the arguments.

大桌子

如果导入表很大，可能需要为会话临时增加temp_buffers(会话中的第一件事):

Big tables

If the import-table is big it may pay to increase temp_buffers temporarily for the session (first thing in the session):

SET temp_buffers = '500MB';  -- example value

给临时表添加索引:

CREATE INDEX tmp_x_id_idx ON tmp_x(id);

并手动运行ANALYZE，因为临时表不包含在自动清理/自动分析中.

And run ANALYZE manually, since temporary tables are not covered by autovacuum / auto-analyze.

ANALYZE tmp_x;

相关答案:

• 删除百万的最佳方法按 ID 的行数
• 如何将通用数据从不同的架构插入到临时表中?
• 如何删除重复条目?

这篇关于如何使用 Postgres 中的 CSV 文件中的值更新选定的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！