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