sql - 如何在 PostgreSQL 中批量插入新行

Question

我有没有 ID 的产品列表(300 万件)——只有标题。但我不知道数据库中已经存在哪些标题。必须将新产品(约 290 万项)添加到数据库中。之后，我必须知道每个产品(新的和现有的)的 ID。

在 PostgreSQL 中有最快的方法吗？我可以根据需要更改数据库(添加默认值、添加列等)。

Answer 1

导入数据

COPY将所有内容都添加到临时暂存表，并仅将新标题插入目标表。

CREATE TEMP TABLE tmp(title text);

COPY tmp FROM 'path/to/file.csv';
ANALYZE tmp;

INSERT INTO tbl
SELECT DISTINCT tmp.title
FROM   tmp 
LEFT   JOIN tbl USING (title)
WHERE  tbl.title IS NULL;

ID 应使用 serial 自动生成tbl 中的 tbl_id 列。

LEFT JOIN/IS NULL 构造取消了现有标题的资格。 NOT EXISTS 是另一种可能性。

DISTINCT 防止临时表 tmp 中的传入数据重复。

ANALYZE有助于确保查询规划器选择一个合理的计划，并且 autovacuum 不会分析临时表。

由于您有 300 万个项目，提高 temp_buffer 的设置可能是值得的(仅针对此 session ):

SET temp_buffers = 1000MB;

或者无论您负担得起多少，都足以将临时表保存在 RAM 中，这样速度要快得多。注意:必须在 session 中首先完成 - 在创建任何临时对象之前。

检索 ID

查看导入数据的所有 ID:

SELECT tbl.tbl_id, tbl.title
FROM   tbl
JOIN   tmp USING (title)

在同一个 session 中! session 结束时会自动删除一个临时表。