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

> On Feb 17, 2024, at 8:24 AM, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
>
> Dear list members,
>
> maybe I am overlooking something.
>
> PostgreSQL offers UPSERT functionality by way of
>
> INSERT INTO ... ON CONFLICT ... DO UPDATE ...;
>
> Consider this pseudo-code schema
>
> table master
> pk_master serial primary key
> value text
> ;
>
> table detail
> pk_detail serial primary key
> fk_master int foreign key master(pk_master)
> detail text
> ;
>
> where there can be any number of rows in detail linking to a
> particular row in master, including none (master rows without
> details, that is).
>
> 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
>
> What I want to achieve:
>
> 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.
>
> I know I can do so from client code. I also know I can wrap
> this functionality inside a plpgsql function.
>
> I am wondering though whether it can be done as one SQL
> statement. It seems to me that would call for an
>
> UPDATE ... ON MISSING ... DO INSERT ...;
>
> or
>
> CASE
> WHEN pk_detail IS NULL THEN INSERT ...
> WHEN pk_detail IS DISTINCT FROM NULL THEN UPDATE ...
> END
>
> both of which don't exist/work, of course.
>
> So, is this doable within one SQL statement (short of
> creating and running the abovementioned function in
> one go ;-) ?
>
> (the real story is about medication and intake regimens /
> schedules thereof involving a whole lot more columns, of
> course, which should not be relevant to the problem though)
>
> Thanks for taking the time to read,
> Karsten

Perhaps your pk_detail can be defined as generated always identity?
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>