GkBagiJN

1contacts >-< contacts_companies >-< companies >-< companies_stores >-< stores
2
3unique on: contacts:email, companies:name, stores:formatted_address 
4     
5{owner: "Alice A.", email: "A@Alice_stores.net", store_address:"1 main st"} 
6     
7--- test tables with m2m 
8create table test1(test1_id bigserial not null primary key, uq_value1 text ,constraint unique_uq_val1 unique (uq_value1));
9
10create table test2(test2_id bigserial not null primary key, uq_value2 text ,constraint unique_uq_val2 unique (uq_value2));
11
12
13create table test1_test2 (test1_id bigint not null, test2_id bigint not null, 
14primary key (test1_id, test2_id),
15constraint fk_test1  foreign key (test1_id) references test1(test1_id ),
16constraint fk_test2  foreign key (test2_id) references test2(test2_id )
17);
18------------------------------------
19-- insert value into the first table , or do a dummy update if it's already there
20with 
21ins1 as 
22(insert  into test1(uq_value1) values('foo') on conflict on constraint unique_uq_val1 do update 
23    set  uq_value1=test1.uq_value1
24returning test1_id 
25)
26,
27-- insert value into the second table , or do a dummy update if it's already there
28ins2 as 
29(insert  into test2(uq_value2) values('bar') on conflict on constraint unique_uq_val2  do update 
30    set  uq_value2=test2.uq_value2 
31returning test2_id 
32)
33,
34-- select PK of inserted records (since there is exactly one record in each insert,
35-- cross join is used
36sel_1 as 
37(
38select test1_id,test2_id
39from ins1  
40cross join ins2 
41)
42-- finally insert into link tables if such a record doesn't exist : 
43insert into test1_test2(test1_id, test2_id)
44select * from sel_1  a 
45where not exists( select null from test1_test2 b where (b.test1_id, b.test2_id) = (a.test1_id, a.test2_id))