MIME-Version: 1.0
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
In-Reply-To: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 9 Aug 2024 14:24:46 -0700
Message-ID: <CAKFQuwY3itH6rZ8xxjyZOgm6wbNCB2Rt+z24vpb4dZYD_9YypQ@mail.gmail.com>
Subject: Re: Insert works but fails for merge
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000005892d061f46c89a"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwY3itH6rZ8xxjyZOgm6wbNCB2Rt%2Bz24vpb4dZYD_9YypQ%40mail.gmail.com>
Precedence: bulk

--00000000000005892d061f46c89a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Aug 9, 2024 at 2:14=E2=80=AFPM yudhi s <learnerdatabase99@gmail.com=
> wrote:

>
> Why so?
>

Because you stuck a CTE in between the column list of the insert - where
types are known - and the values command - where types are unknown since
you didn't specify them.  As the row comes out of the CTE every column must
have a known type, and so in the absence of context they get typed as text.

David J.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Fri, Aug 9, 2024 at 2:14=E2=80=AFPM yudhi s &lt;<a href=
=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99@gmail.com</a>&gt;=
 wrote:</span><br></div></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><br></div></blockquote><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div> Why =
so?</div></div></blockquote><div><br></div><div class=3D"gmail_default" sty=
le=3D"font-family:arial,helvetica,sans-serif">Because you stuck a CTE in be=
tween the column list of the insert - where types are known - and the value=
s command - where types are unknown since you didn&#39;t specify them.=C2=
=A0 As the=C2=A0row comes out of the CTE every column must have a known typ=
e, and so in the absence of context they get typed as text.</div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></d=
iv><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-s=
erif">David J.</div><div class=3D"gmail_default" style=3D"font-family:arial=
,helvetica,sans-serif"><br></div></div></div>

--00000000000005892d061f46c89a--