MIME-Version: 1.0
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
 <37e09717-f121-4192-b152-18df17713414@aklaver.com> <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
 <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com> <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
 <A0C7725A-C82D-4B62-A485-D1C4CE7C5A8E@gmail.com> <accc0da1-6be2-4a02-a2c3-fbe45f8da8d8@aklaver.com>
In-Reply-To: <accc0da1-6be2-4a02-a2c3-fbe45f8da8d8@aklaver.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sun, 11 Aug 2024 21:18:00 -0400
Message-ID: <CAKAnmm+UgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA@mail.gmail.com>
Subject: Re: Insert works but fails for merge
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Alban Hertroys <haramrae@gmail.com>, yudhi s <learnerdatabase99@gmail.com>, 
	David G Johnston <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000af12b3061f724512"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BUgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA%40mail.gmail.com>
Precedence: bulk

--000000000000af12b3061f724512
Content-Type: text/plain; charset="UTF-8"

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

--000000000000af12b3061f724512
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">So it looks like the OP does not mind updating more than o=
ne 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 a=
nd cast once, and have your app send the raw uncasted strings many, many ti=
mes:<div><br></div><div>prepare foo(text,int,timestamptz) as with x as (upd=
ate tab1 set mid=3D$2 where id=3D$1 returning 1)</div><div>=C2=A0 insert in=
to tab1 select $1,$2,$3 where not exists (select 1 from x);<br></div><div><=
br></div><div><div>execute foo(&#39;<span style=3D"color:rgb(80,0,80)">5efd=
4c91-ef93-4477-840c-a723</span><span style=3D"color:rgb(80,0,80)">ae212d99&=
#39;, 123,=C2=A0</span><span style=3D"color:rgb(80,0,80)">&#39;2024-08-09T1=
1:33:49.402585600</span><span style=3D"color:rgb(80,0,80)">Z&#39;);</span><=
/div><div>execute foo(&#39;some_other_id<span style=3D"color:rgb(80,0,80)">=
&#39;, 456,=C2=A0</span><span style=3D"color:rgb(80,0,80)">&#39;2024-08-11T=
21:44:55.8675309</span><span style=3D"color:rgb(80,0,80)">Z&#39;);</span></=
div><div><span style=3D"color:rgb(80,0,80)">etc.</span></div><div><span sty=
le=3D"color:rgb(80,0,80)"><br></span></div><div><span style=3D"color:rgb(80=
,0,80)">Your app/driver may or may not already do protocol-level statement =
prepare/execute automagically, so test that way first.</span></div><div><br=
></div></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">It&#39;s ver=
sion 15.4 postgres.</blockquote><div><br></div><div>Keep on the latest revi=
sion. Right now, that&#39;s 15.8. Upgrading revisions is quick and painless=
.=C2=A0</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div=
></div>

--000000000000af12b3061f724512--