PG_数仓增量数据同步更新

发表于 本文字数： 1.7k 阅读时长 ≈ 2 分钟

前提：

​ t1 - 原始业务表

​ t2 - 数据仓库表

要求：

​ 数仓表固定周期更新，需要将原始业务表中： 1. 新增的数据插入 2. 修改的数据更新 到数仓表中，可采用ON CONFLICT，注意constraint_column_name 必须是unique index，或者constraint_column_name组合索引必须是unique index;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table if exists t1,t2;
create table t1(id int primary key,name varchar,insert_time timestamp, update_time timestamp);
create table t2(id int primary key,name varchar,insert_time timestamp, update_time timestamp);

insert into t1 values (1,'1','2023-01-01 01:00:00','2023-01-01 01:00:00');
insert into t1 values (2,'2','2023-01-01 02:00:00','2023-01-01 02:00:00');
select * from t1;

insert into t2(id,name,insert_time,update_time) select id,name,now(),now() from t1 where t1.update_time > coalesce((select max(update_time) from t2),'1900-01-01 00:00:00') on conflict(id) do update set name = EXCLUDED.name, update_time = now();
select * from t2;

update t1 set name = name||'-1',update_time = now()  where id = 1;
insert into t2(id,name,insert_time,update_time) select id,name,now(),now() from t1 where t1.update_time > coalesce((select max(update_time) from t2),'1900-01-01 00:00:00') on conflict(id) do update set name = EXCLUDED.name, update_time = now();
select * from t1;
select * from t2;

update t1 set name = name||'-11',update_time = now()  where id = 1;
insert into t1 values (3,'3',now(),now());
insert into t2(id,name,insert_time,update_time) select id,name,now(),now() from t1 where t1.update_time > coalesce((select max(update_time) from t2),'1900-01-01 00:00:00') on conflict(id) do update set name = EXCLUDED.name, update_time = now();
select * from t1;
select * from t2;