PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

从COPY导数里竟然还能挖掘出这么多一个个小问题

    • 有事先吐槽,没事再整理文章
    • 场景描述
    • 先copy导数再合并表数据
      • merge into
      • CTE
      • UPDATE小细节
    • 创建触发器copy导数一步到位
    • 拓展

看腻了就来听听视频演示吧(持续更新中):
https://www.bilibili.com/video/BV1Z6vseLEux/
https://www.bilibili.com/video/BV1u1v8e7EBv/

有事先吐槽,没事再整理文章

吐下槽先 😁
不怕需求有多难,就怕提需求的人脑洞有多离谱。
真是问题处理多了,啥都见怪不怪了。

场景描述

数仓供数,业务从那边拿文本数据,类似csv和txt的文本数据再用copy导入到PostgreSQL和openGauss库里。早期数据文件几个GB采用truncate表后再全量导入,随着业务发展现在数据文件达到几十、上百GB的全量数据,前来咨询是否能做到:数仓供数那边提供增量数据文件,包含修改的数据,用 copy 导入时对已经存在的数据做update,新数据做insert。

针对业务场景这个需求也是挺正常滴 . . .

先copy导数再合并表数据

先将增量数据导入相同表结构的临时表里,然后再合并表数据。

merge into

PostgreSQL 15开始支持merge into语法:https://www.postgresql.org/docs/15/sql-merge.html

-- merge into,约束条件是关联条件on后面的数据唯一,不存重复数据,否则会报错:ERROR: unable to get a stable set of rows in the source tables drop table IF EXISTS test1; CREATE TABLE test1(id int,name text); insert into test1 values(1,'PostgreSQL'),(2,'Oracle'),(3,'MySQL'); create unique index on test1(id); drop table IF EXISTS test2; CREATE TABLE test2(id int,name text); insert into test2 values(5,'TiDB'),(1,'PG'),(2,'OG'),(4,'Oracle'),(6,'SQLServer'); create unique index on test1(id); select * from test1 order by 1; merge into test1 t1 using test2 t2 on (t1.id = t2.id) when matched then update set name = t2.name when not matched then insert values (t2.id, t2.name);