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