如何用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 文件到临时表，并从那里更新实际表。
看起来像这样：

I would COPY the file to a temporary table and update the actual table from there. Could look like this:

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.

如果导入表较大，可能需要增加 temp_buffers 暂时为会话（会话中的第一件事）：

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 ，因为临时表不被autovacuum / auto-analyze覆盖。

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

ANALYZE tmp_x;

相关答案：

• 最佳删除方式数百万行（按ID）


• 如何将通用数据插入不同模式的临时表？


• 如何删除重复的条目？

• Best way to delete millions of rows by ID
• How can I insert common data into a temp table from disparate schemas?
• How to delete duplicate entries?

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