teMJ8qx2

1-- Working example
2
3DROP TABLE abc;
4CREATE TEMPORARY TABLE IF NOT EXISTS abc (
5
6  id serial
7  , dateadded timestamp default now()
8  , urn varchar
9  , other_data varchar
10  , more_data varchar
11  );
12ALTER TABLE abc
13 ADD CONSTRAINT urn_pk UNIQUE (urn);
14
15INSERT INTO abc (urn, other_data, more_data) VALUES ('01234','stuff','more stuff');
16INSERT INTO abc (urn, other_data, more_data) VALUES ('AEIOU','stuff','more stuff');
17INSERT INTO abc (urn, other_data) VALUES ('xxxxx','stuff');
18
19select * from abc;
20
21INSERT INTO abc (urn, other_data, more_data) values('xxxxx','stuff','moar')
22ON CONFLICT (urn)  DO UPDATE SET dateadded = now(), more_data ='mOAR'  WHERE abc.more_data IS NULL;
23
24select * from abc;
25
26-- Is it possible to update multiple columns by different crieria?
27
28INSERT INTO abc (urn, other_data, more_data) values('xxxxx','stuff','moar')
29ON CONFLICT (urn) 
30   DO UPDATE SET more_data ='Data'  WHERE abc.more_data IS NULL;
31   DO UPDATE SET other_data ='Other'  WHERE length(abc.other) < 2;