public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Alban Hertroys <[email protected]>
Cc: yudhi s <[email protected]>
Cc: David G Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Insert works but fails for merge
Date: Sun, 11 Aug 2024 21:18:00 -0400
Message-ID: <CAKAnmm+UgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
<[email protected]>
<CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
<[email protected]>
<CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
<[email protected]>
<[email protected]>
So it looks like the OP does not mind updating more than one row. If you
want to keep it simple and not do a lot of casting, consider using a CTE to
do a reverse-upsert and use a prepared statement. Prepare and cast once,
and have your app send the raw uncasted strings many, many times:
prepare foo(text,int,timestamptz) as with x as (update tab1 set mid=$2
where id=$1 returning 1)
insert into tab1 select $1,$2,$3 where not exists (select 1 from x);
execute foo('5efd4c91-ef93-4477-840c-a723ae212d99', 123,
'2024-08-09T11:33:49.402585600Z');
execute foo('some_other_id', 456, '2024-08-11T21:44:55.8675309Z');
etc.
Your app/driver may or may not already do protocol-level statement
prepare/execute automagically, so test that way first.
It's version 15.4 postgres.
Keep on the latest revision. Right now, that's 15.8. Upgrading revisions is
quick and painless.
Cheers,
Greg
view thread (8+ messages) latest in thread
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Insert works but fails for merge
In-Reply-To: <CAKAnmm+UgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox