Re: "reverse" (?) UPSERT -- how to ?

On Sat, Feb 17, 2024 at 10:24 AM Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:

> There will be a view giving rows for
> each detail row enriched with master table data
> UNION ALL
> rows for each master row that does not have any detail row with
> detail table columns NULLed
>

A better way to do that is to just use a LEFT JOIN.

Given a pk_detail (and pk_master) having been obtained from the view
> (therefore pk_detail being NULL or an integer value)
> UPDATE that detail row (namely when pk_detail is distinct from NULL) or
> insert a new detail row (when pk_detail IS
> NULL) linking that row to the master row identified by pk_master.

What you want is the MERGE command:

https://www.postgresql.org/docs/current/sql-merge.html

To simplify your example a little, let's create two tables, linked to each
other by foreign keys, in which only 2 of the five rows have matching
details:

drop table if exists t2;
drop table if exists t1;

create table t1(id serial primary key, v text);

insert into t1(v) select 'foo' from generate_series(1,5);

create table t2(id serial primary key, fk int references t1(id), w text);

insert into t2(fk,w) values (2, 'bar1'), (2, 'bar2'), (3,'bar3');

select * from t2 order by 1;

id | fk | w
----+----+------
1 | 2 | bar1
2 | 2 | bar2
3 | 3 | bar3
(3 rows)

Now we can use that left join, plus a merge based on the results, to
conditionally update or insert:

WITH x AS (SELECT t1.id as t1id, t2.id AS t2id, * FROM t1 LEFT JOIN t2 ON (
t1.id=t2.fk) )
MERGE INTO t2
USING x ON (x.t2id = t2.id)
WHEN MATCHED THEN UPDATE SET w='new info for existing row'
WHEN NOT MATCHED THEN INSERT (fk,w) VALUES (x.t1id, 'new info for a new
row');

select * from t2 order by 1;

id | fk | w
----+----+---------------------------
1 | 2 | new info for existing row
2 | 2 | new info for existing row
3 | 3 | new info for existing row
4 | 5 | new info for a new row
5 | 4 | new info for a new row
6 | 1 | new info for a new row
(6 rows)

Cheers,
Greg