postgresql – 如何判断Postgres中的记录是否已更改
我有一点“upsert”类型的问题……但是,我想把它扔出去,因为它与我在stackoverflow上读到的任何东西都有点不同.
基本问题. 我正在努力从mysql迁移到PostgreSQL 9.1.5(在Heroku上托管).作为其中的一部分,我需要每天导入多个CSV文件.一些数据是销售信息,几乎保证是新的,需要插入.但是,数据的其他部分几乎保证是相同的.例如,csv文件(注释复数)将在其中包含POS(销售点)信息.这很少改变(最有可能仅通过添加).然后是产品信息.大约有10,000种产品(绝大多数产品将保持不变,但可以同时添加和更新). 最后一项(但很重要)是,我要求能够为任何给定项目提供审计跟踪/信息.例如,如果我添加一个新的POS记录,我需要能够追溯到它找到的文件.如果我更改了UPC代码或产品描述,那么我需要能够追溯它到变更来自的导入(和文件). 我正在考虑的解决方案. 由于数据是通过CSV提供给我的,所以我正在努力解决COPY将是最好/最快的方法.文件中数据的结构与数据库中的数据结构不同(即最终目的地).因此,我将它们复制到与CSV匹配的登台模式中的表中(注意:每个数据源一个模式).登台模式中的表将具有前插入行触发器.这些触发器可以决定如何处理数据(插入,更新或忽略). 对于最有可能包含新数据的表,它将首先尝试插入.如果记录已经存在,那么它将返回NULL(并将插入停止到登台表中).对于很少更改的表,它将查询表并查看是否找到了记录.如果是,那么我需要一种方法来查看是否有任何字段被更改. (因为记住,我需要证明记录是通过从文件y导入x来修改的)我显然可以通过锅炉清除代码并测试每一列.但是,正在寻找一些比这更“雄辩”和更易于维护的东西. 在某种程度上,我正在做的是将导入系统与审计跟踪系统相结合.因此,在研究审计跟踪时,我回顾了以下wiki.postgresql.org文章.似乎hstore可能是获取更改的好方法(并且能够轻松忽略表中不重要的某些列 – 例如“last_modified”) 我大约90%肯定它会全部工作……我已经创建了一些测试表等,并玩弄它. 我的问题? 是一种更好,更易于维护的方法来完成从10K中找到需要更改数据库的3条记录的任务.我当然可以编写一个读取文件的python脚本(或其他东西),并试图弄清楚如何处理每条记录,但这种效率非常低效,并且会导致大量的往返行程. 最后几件事: >我无法控制输入文件.如果他们只向我发送了三角洲,我会喜欢它,但他们没有,而且完全不受我的控制或影响.
我有很多类似的操作.我所做的是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命令来消化新数据. 也许删除不再有什么? DELETE FROM target_tbl t WHERE NOT EXISTS ( SELECT 1 FROM target_tmp t1 WHERE t1.target_id = t.target_id ); 然后更新已有的内容: UPDATE target_tbl t SET col1 = t1.col1 FROM target_tmp t1 WHERE t.target_id = t1.target_id 要避免空UPDATE,只需添加: ... AND col1 IS DISTINCT FROM t1.col1; -- repeat for relevant columns 或者,如果整行是相关的: ... AND t IS DISTINCT FROM t1; -- check the whole row 然后插入新的内容: 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; 如果会话继续进行清理(临时表在会话结束时自动删除): DROP TABLE target_tmp; 或者使用ON COMMIT DROP或类似于 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |