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>
 <CAKAnmm+UgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA@mail.gmail.com>
In-Reply-To: <CAKAnmm+UgOUpGeurzeFg6r-q7RwhwuY-rQLd2nhSMk2sFfTJQA@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 13 Aug 2024 09:55:18 -0400
Message-ID: <CAKAnmmK2iECtmatmiOq_JE03K5dFPmugy_CqbGAs-SR-3U1bbw@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="000000000000ea8d98061f90f760"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmK2iECtmatmiOq_JE03K5dFPmugy_CqbGAs-SR-3U1bbw%40mail.gmail.com>
Precedence: bulk

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

I just remembered that one of the complaints was not wanting to worry about
looking up the data types. In my previous example, you can also leave out
the types and Postgres will do the right thing. I prefer the explicit data
type version for clarity, but though I would provide this one for
completeness:

prepare foo 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);

Cheers,
Greg

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

<div dir=3D"ltr"><div>I just remembered that one of the complaints was not =
wanting to worry about looking up the data types. In my previous example, y=
ou can also leave out the types and Postgres will do the right thing. I pre=
fer the explicit data type version for clarity, but though I would provide =
this one for completeness:</div><div class=3D"gmail_quote"><div><br></div><=
div>prepare foo as with x as (update tab1 set mid=3D$2 where id=3D$1 return=
ing 1)</div><div>=C2=A0 insert into tab1 select $1,$2,$3 where not exists (=
select 1 from x);</div><div><br></div><div>Cheers,</div><div>Greg</div><div=
><br></div></div></div>

--000000000000ea8d98061f90f760--