主页 / dba / 问题 / 237750
Accepted
ZAR
Asked: 2019-05-10 07:15:06 +0800 CST

使用现有的多对多连接更新数据

  • 772

这是一个策略问题——但我整个星期都在努力解决这个问题，并且真的可以使用一些智慧即服务。

语境

我正在尝试将一个干净、可操作的 PostgreSQL 数据库与来自大量杂乱、嘈杂的原始源表的清理记录拼凑在一起。

假设我们有联系人、公司、商店。许多联系人可以在公司工作。contacts_companies一家商店可能涉及多家公司（所有者、维护人员等）。和之间存在多对多 (M:N) 连接companies_stores。

表关系：

contacts >-< contacts_companies >-< companies >-< companies_stores >-< stores

unique on: contacts:email, companies:name, stores:formatted_address

多个数据源在上述表格之间存在各种重叠。有些只是存储信息，有些有一些联系人和存储信息等。当我浏览每个数据源时，数据将被清理然后更新到适当的表中。

问题

upsert 操作是问题所在。使用 ON CONFLICT 插入对于特定表来说很好，但是之间的连接点呢？

为了说明这个问题，假设我们有一个具有以下记录的数据源：

{owner: "Alice A.", email: "A@Alice_stores.net", store_address:"1 main st"}

这个记录有

  • 一个联系人{name: Alice A, email: A@Alice_stores.net}，
  • 一家公司{name: Alice Stores}
  • 一家商店{address: 1 main st}

数据库可能已经有 的商店1 main st，但没有 的联系人/公司Alice。

如果我们单独更新联系人、公司和商店，我们将不知道在联结表中放置哪些正确的 ID：

  • 如果 INSERT，在联结处为 FK 使用新的 id。
  • 如果是 Update，则在联结处使用现有的 id 作为 fk。

问题

当全面存在 M:N 关系时，从各种来源更新多个表的最佳策略是什么？

我想到的一种策略是查询所有表/联结，将它们保存在内存中并在内存中执行所有条件/更新，然后进行更有力的 upsert。但这很快就会失控......

postgresql upsert

1 个回答

  1. Best Answer

    一种方法是使用returning 带有 cte 的子句在一个语句中进行所有更新。出于说明目的，我使用 1 多对多关系。

    --- test tables with m2m 
create table test1(test1_id bigserial not null primary key, uq_value1 text ,constraint unique_uq_val1 unique (uq_value1));

create table test2(test2_id bigserial not null primary key, uq_value2 text ,constraint unique_uq_val2 unique (uq_value2));


create table test1_test2 (test1_id bigint not null, test2_id bigint not null, 
primary key (test1_id, test2_id),
constraint fk_test1  foreign key (test1_id) references test1(test1_id ),
constraint fk_test2  foreign key (test2_id) references test2(test2_id )
);
------------------------------------
-- insert value into the first table , or do a dummy update if it's already there
with 
ins1 as 
(insert  into test1(uq_value1) values('foo') on conflict on constraint unique_uq_val1 do update 
    set  uq_value1=test1.uq_value1
returning test1_id 
)
,
-- insert value into the second table , or do a dummy update if it's already there
ins2 as 
(insert  into test2(uq_value2) values('bar') on conflict on constraint unique_uq_val2  do update 
    set  uq_value2=test2.uq_value2 
returning test2_id 
)
,
-- select PK of inserted records (since there is exactly one record in each insert,
-- cross join is used
sel_1 as 
(
select test1_id,test2_id
from ins1  
cross join ins2 
)
-- finally insert into link tables if such a record doesn't exist : 
insert into test1_test2(test1_id, test2_id)
select * from sel_1  a 
where not exists( select null from test1_test2 b where (b.test1_id, b.test2_id) = (a.test1_id, a.test2_id))
    • 1

相关问题