pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed"reverse" (?) UPSERT -- how to ?
8+ messages / 4 participants
[nested] [flat]
* "reverse" (?) UPSERT -- how to ?
@ 2024-02-17 15:24 Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:33 ` Re: "reverse" (?) UPSERT -- how to ? Rob Sargent <robjsargent@gmail.com>
2024-02-17 16:54 ` Re: "reverse" (?) UPSERT -- how to ? Greg Sabino Mullane <htamfids@gmail.com>
2024-02-17 16:55 ` Re: "reverse" (?) UPSERT -- how to ? Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 3 replies; 8+ messages in thread
From: Karsten Hilbert @ 2024-02-17 15:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2024-02-17 16:33 ` Rob Sargent <robjsargent@gmail.com>
2024-02-17 18:22 ` Re: "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Rob Sargent @ 2024-02-17 16:33 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
> 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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:33 ` Re: "reverse" (?) UPSERT -- how to ? Rob Sargent <robjsargent@gmail.com>
@ 2024-02-17 18:22 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Karsten Hilbert @ 2024-02-17 18:22 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Am Sat, Feb 17, 2024 at 09:33:11AM -0700 schrieb Rob Sargent:
> > So, is this doable within one SQL statement (short of
> > creating and running the abovementioned function in
> > one go ;-) ?
> >
>
> Perhaps your pk_detail can be defined as generated always identity?
Rob, I'm sure there's something to be learned from that
suggestion the significance of which I don't grasp yet. Would
you mind expanding a bit ?
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2024-02-17 16:54 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-02-17 18:25 ` Re: "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Greg Sabino Mullane @ 2024-02-17 16:54 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
On Sat, Feb 17, 2024 at 10:24 AM Karsten Hilbert <Karsten.Hilbert@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:54 ` Re: "reverse" (?) UPSERT -- how to ? Greg Sabino Mullane <htamfids@gmail.com>
@ 2024-02-17 18:25 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 18:29 ` Re: "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Karsten Hilbert @ 2024-02-17 18:25 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Am Sat, Feb 17, 2024 at 11:54:05AM -0500 schrieb Greg Sabino Mullane:
> > 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.
+1 except that I've got use for both parts of the UNION on
their own (they are both views themselves involving a bunch
of joins with yet other tables, 4 or 5 each or so ;-)
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:54 ` Re: "reverse" (?) UPSERT -- how to ? Greg Sabino Mullane <htamfids@gmail.com>
2024-02-17 18:25 ` Re: "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2024-02-17 18:29 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Karsten Hilbert @ 2024-02-17 18:29 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: Greg Sabino Mullane <htamfids@gmail.com>
Am Sat, Feb 17, 2024 at 07:25:11PM +0100 schrieb Karsten Hilbert:
> +1 except that I've got use for both parts of the UNION on
> their own (they are both views themselves involving a bunch
> of joins with yet other tables, 4 or 5 each or so ;-)
Just for kicks, attached find the SQL change script ...
Using a LEFT JOIN would get unwieldy, fast, I guess.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Attachments:
v23-clin-v_intakes_with_regimens.sql (application/sql, 6.7K) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2024-02-17 16:55 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-17 18:20 ` Re: "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-02-17 16:55 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; pgsql-general@lists.postgresql.org
On 2/17/24 07:24, Karsten Hilbert wrote:
> Dear list members,
>
> So, is this doable within one SQL statement (short of
> creating and running the abovementioned function in
> one go ;-) ?
Don't know what version of Postgres you are on, assuming 15+ then maybe?:
MERGE
https://www.postgresql.org/docs/current/sql-merge.html
>
> (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
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "reverse" (?) UPSERT -- how to ?
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:55 ` Re: "reverse" (?) UPSERT -- how to ? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-17 18:20 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Karsten Hilbert @ 2024-02-17 18:20 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; Greg Sabino Mullane <htamfids@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Am Sat, Feb 17, 2024 at 08:55:57AM -0800 schrieb Adrian Klaver:
> >Dear list members,
> >
>
> >So, is this doable within one SQL statement (short of
> >creating and running the abovementioned function in
> >one go ;-) ?
>
> Don't know what version of Postgres you are on, assuming 15+ then maybe?:
Ah, sorry, I omitted that detail :(
Release target for new installs is current Debian Stable, IOW
Bookworm, which has PG 15.
postgresql:
Installiert: 15+248
Installationskandidat: 15+248
Versionstabelle:
16+257 500
500 https://deb.debian.org/debian testing/main i386 Packages
*** 15+248 990
990 https://deb.debian.org/debian bookworm/main i386 Packages
100 /var/lib/dpkg/status
However, legacy deployments may run, say PG 13,
root@hermes:~/bin# rmadison postgresql
postgresql | 11+200+deb10u5 | oldoldstable | all
postgresql | 13+225+deb11u1 | oldstable | all
postgresql | 15+248 | stable | all
postgresql | 16+257 | testing | all
postgresql | 16+257 | unstable | all
so ideally it would work on that, too. However, it does not
seem too much to ask to *upgrade* GNUmed database servers to
Debian *Stable* if on plans on running the next,
as-yet-unreleased version ;-)
> MERGE
>
> https://www.postgresql.org/docs/current/sql-merge.html
:-) I knew I missed something. As Greg also pointed out
that is what I need.
Thanks !
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-02-17 18:29 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-17 15:24 "reverse" (?) UPSERT -- how to ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:33 ` Rob Sargent <robjsargent@gmail.com>
2024-02-17 18:22 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:54 ` Greg Sabino Mullane <htamfids@gmail.com>
2024-02-17 18:25 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 18:29 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-17 16:55 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-17 18:20 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
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