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

[Greg Sabino Mullane <htamfids@xxxxxxxxx>]

On Sat, Feb 17, 2024 at 10:24 AM Karsten Hilbert <Karsten.Hilbert@xxxxxxx> 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