postgresql - 如何判断 Postgres 中的记录是否已更改

Question

我有一些“upsert”类型的问题...但是，我想把它扔在那里，因为它与我在 stackoverflow 上读到的任何问题都有点不同。

基本问题。

我正在努力从 mysql 迁移到 PostgreSQL 9.1.5(托管在 Heroku 上)。作为其中的一部分，我需要每天导入多个 CSV 文件。有些数据是销售信息，几乎可以保证是新的，需要插入。但是，数据的其他部分几乎可以保证是相同的。例如，csv 文件(注意复数)将包含 POS(销售点)信息。这很少改变(并且很可能只是通过添加)。然后是产品信息。大约有 10,000 种产品(绝大多数将保持不变，但可能同时进行添加和更新)。

最后一项(但很重要)是我需要能够为任何给定项目提供审计跟踪/信息。例如，如果我添加了一条新的 POS 记录，我需要能够将其追溯到找到它的文件。如果我更改了 UPC 代码或产品描述，那么我需要能够追溯到它到更改来源的导入(和文件)。

我正在考虑的解决方案。

由于数据是通过 CSV 提供给我的，所以我正在考虑 COPY 将是最好/最快的方式的想法。文件中的数据结构与我在数据库中的结构不完全相同(即最终目的地)。因此，我将它们复制到与 CSV 匹配的暂存模式中的表中(注意:每个数据源一个模式)。暂存模式中的表将有一个插入前行触发器。这些触发器可以决定如何处理数据(插入、更新或忽略)。

对于最有可能包含新数据的表，它会先尝试插入。如果记录已经存在，那么它将返回 NULL(并停止插入临时表)。对于很少变化的表，那么它会查询表，看是否找到记录。如果是，那么我需要一种方法来查看是否更改了任何字段。 (因为请记住，我需要证明记录已通过从文件 y 导入 x 进行了修改)我显然可以简单地编写代码并测试每一列。但是，正在寻找比这更“ Eloquent ”和更易于维护的东西。

在某种程度上，我正在做的是将导入系统与审计跟踪系统相结合。因此，在研究审计线索时，我查看了以下内容 wiki.postgresql.org文章。 hstore 似乎是获取更改的好方法(并且能够轻松忽略表中一些不重要的列 - 例如“last_modified”)

我大约 90% 确定它会全部工作...我已经创建了一些测试表等并进行了试验。

我的问题？

是一种更好、更易于维护的方法来完成从 10K 条记录中找出可能需要更改数据库的 3 条记录的任务。我当然可以编写一个 python 脚本(或其他东西)来读取文件并尝试弄清楚如何处理每条记录，但这感觉非常低效并且会导致大量往返。

一些最后的事情:

1. 我无法控制输入文件。如果他们只向我发送增量，我会很高兴，但他们没有，这完全超出了我的控制或影响范围。
2. 系统在发展，可能会添加新的数据源，这将大大增加正在处理的数据量(因此，我正在努力保持高效)
3. 我知道这不是很好的简单 SO 问题(例如“如何在 python 中对列表进行排序”)，但我相信 SO 的一大优点是您可以提出尖锐的问题，人们会分享他们对如何排序的想法他们认为解决问题的最佳方法是。

Answer 1

我有很多类似的操作。我所做的是COPY到临时暂存表:

CREATE TEMP TABLE target_tmp AS
SELECT * FROM target_tbl LIMIT 0;  -- only copy structure, no data

COPY target_tmp FROM '/path/to/target.csv';

为了性能，运行ANALYZE - temp。 autovacuum 不分析表!

ANALYZE target_tmp; 

同样为了性能，甚至可以在临时表上创建一个或两个索引，或者在数据允许的情况下添加一个主键。

ALTER TABLE ADD CONSTRAINT target_tmp_pkey PRIMARY KEY(target_id);

对于小型导入，您不需要性能方面的东西。

然后使用 SQL 命令的全部范围来消化新数据。
例如，如果目标表的主键是 target_id ..

也许 DELETE 什么已经不存在了？

DELETE FROM target_tbl t
WHERE NOT EXISTS (
   SELECT 1 FROM target_tmp t1
   WHERE  t1.target_id = t.target_id
);

然后 UPDATE 已经存在的内容:

UPDATE target_tbl t
SET    col1 = t1.col1
FROM   target_tmp t1
WHERE  t.target_id = t1.target_id

为避免空更新，只需添加:

...
AND    col1 IS DISTINCT FROM t1.col1; -- repeat for relevant columns

或者，如果整行是相关的:

...
AND    t IS DISTINCT FROM t1;         -- check the whole row

然后INSERT有什么新东西:

INSERT INTO target_tbl(target_id, col1)
SELECT t1.target_id, t1.col1
FROM   target_tmp t1
LEFT   JOIN target_tbl t USING (target_id)
WHERE  t.target_id IS NULL;

如果您的 session 继续进行清理(临时表会在 session 结束时自动删除):

DROP TABLE target_tmp;

或使用 ON COMMIT DROP 或类似 CREATE TEMP TABLE .
_{代码未经测试，但除了错别字外应该可以在任何现代版本的 PostgreSQL 中运行。}