pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: "reverse" (?) UPSERT -- how to ?
Date: Sat, 17 Feb 2024 09:33:11 -0700
Message-ID: <5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com> (raw)
In-Reply-To: <ZdDPpWxB6IH_Dx1b@hermes.hilbert.loc>
References: <ZdDPpWxB6IH_Dx1b@hermes.hilbert.loc>



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





view thread (8+ messages)  latest in thread

Message-ID: <5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com>
Permalink:  ../5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com/
Also on:    postgresql.org/message-id/5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com, Karsten.Hilbert@gmx.net
  Subject: Re: "reverse" (?) UPSERT -- how to ?
  In-Reply-To: <5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com>
  References: <ZdDPpWxB6IH_Dx1b@hermes.hilbert.loc> <5F927293-F62E-41C4-A62B-AB184E91B0F1@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox