如何在PostgreSQL中批量插入仅为新行

Question

我有一个没有ID，只有标题的产品列表（300万件）。但我不知道哪些标题已经存在于数据库中。新产品（大约290万件）必须添加到数据库中。之后我必须知道每个产品（新旧）的ID。

在PostgreSQL中是否有最快的方法？我可以根据需要更改数据库（添加默认值，添加列等）。

- FireShock

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;

应该在表tbl中使用带有serial列tbl_id的自动生成的ID。

LEFT JOIN / IS NULL结构会使已经存在的标题无效。另一种可能性是NOT EXISTS。

DISTINCT在临时表tmp中防止重复数据。

ANALYZE对于确保查询计划选择合理的方案很有用，而且临时表不会被自动清理分析。

由于您有300万个项目，因此提高temp_buffer设置（仅限本次会话）可能会更加划算：

SET temp_buffers = 1000MB;

或者你能支付多少钱并且足以在RAM中容纳临时表，这样会更快。注意：必须在会话中首先完成 - 在创建任何临时对象之前。

检索ID

要查看导入数据的所有ID：

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

在同一会话中！临时表会在会话结束时自动删除。

- Erwin Brandstetter

---

有没有比这更高效的不同方法？对于我来说，当记录数达到约200k时，这真的开始变慢了。 - philiporlando