MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 10 Aug 2024 02:43:28 +0530
Message-ID: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
Subject: Insert works but fails for merge
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000991e7061f469e15"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqc%3DQKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q%40mail.gmail.com>
Precedence: bulk

--0000000000000991e7061f469e15
Content-Type: text/plain; charset="UTF-8"

Hello,
It's version 15.4 postgres. Where we have an insert working fine, but then
a similar insert with the same 'timestamp' value, when trying to be
executed through merge , it fails stating "You will need to rewrite or cast
the expression.". Why so?

*Example:-*
https://dbfiddle.uk/j5S7br-q

 CREATE TABLE tab1 (
    id varchar(100) ,
    mid INT,
    txn_timestamp TIMESTAMPTZ NOT NULL,
    cre_ts TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (txn_timestamp);

CREATE TABLE tab1_2024_08_09 PARTITION OF tab1
    FOR VALUES FROM ('2024-08-09 00:00:00') TO ('2024-08-10 00:00:00');

-- Below insert works fine
INSERT INTO tab1
    (id, mid, txn_timestamp, cre_ts)
VALUES
    ('5efd4c91-ef93-4477-840c-a723ae212d84', 123,
'2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402585600Z');

-- Below merge , which trying to insert similar row but failing

WITH source_data (id, mid, txn_timestamp, cre_ts) AS (
    VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123,
'2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402585600Z')
)
INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts)
SELECT id, mid, txn_timestamp, cre_ts
FROM source_data
ON CONFLICT (id) DO UPDATE
SET    mid = EXCLUDED.mid,
    txn_timestamp = EXCLUDED.txn_timestamp,
    cre_ts = EXCLUDED.cre_ts;

ERROR: column "txn_timestamp" is of type timestamp with time zone but
expression is of type text LINE 24: SELECT id, mid, txn_timestamp, cre_ts ^
HINT: You will need to rewrite or cast the expression.

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

<div dir=3D"ltr">Hello,<div>It&#39;s version 15.4 postgres. Where we have a=
n insert working fine, but then a similar insert with the same=C2=A0&#39;ti=
mestamp&#39; value, when trying to be executed through merge , it fails sta=
ting=C2=A0&quot;<span style=3D"color:rgb(240,128,128);font-family:monospace=
;font-size:medium;white-space:pre">You will need to rewrite or cast the exp=
ression.&quot;</span>. Why so?</div><div><br></div><div><b>Example:-</b></d=
iv><div><a href=3D"https://dbfiddle.uk/j5S7br-q">https://dbfiddle.uk/j5S7br=
-q</a><b><br></b></div><div><br></div><div>=C2=A0<font face=3D"monospace">C=
REATE TABLE tab1 (<br>=C2=A0 =C2=A0 id varchar(100) ,<br>=C2=A0 =C2=A0 mid =
INT,<br>=C2=A0 =C2=A0 txn_timestamp TIMESTAMPTZ NOT NULL,<br>=C2=A0 =C2=A0 =
cre_ts TIMESTAMPTZ NOT NULL<br>) PARTITION BY RANGE (txn_timestamp);<br><br=
>CREATE TABLE tab1_2024_08_09 PARTITION OF tab1<br>=C2=A0 =C2=A0 FOR VALUES=
 FROM (&#39;2024-08-09 00:00:00&#39;) TO (&#39;2024-08-10 00:00:00&#39;);<b=
r><br>-- Below insert works fine=C2=A0<br>INSERT INTO tab1<br>=C2=A0 =C2=A0=
 (id, mid, txn_timestamp, cre_ts)<br>VALUES<br>=C2=A0 =C2=A0 (&#39;5efd4c91=
-ef93-4477-840c-a723ae212d84&#39;, 123, &#39;2024-08-09T11:33:49.402585600Z=
&#39;, &#39;2024-08-09T11:33:49.402585600Z&#39;);<br><br>-- Below merge , w=
hich trying to insert similar row but failing=C2=A0</font></div><div><font =
face=3D"monospace"><br>WITH source_data (id, mid, txn_timestamp, cre_ts) AS=
 (<br>=C2=A0 =C2=A0 VALUES (&#39;5efd4c91-ef93-4477-840c-a723ae212d66&#39;,=
 123, &#39;2024-08-09T11:33:49.402585600Z&#39;, &#39;2024-08-09T11:33:49.40=
2585600Z&#39;)<br>)<br>INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts)<br=
>SELECT id, mid, txn_timestamp, cre_ts<br>FROM source_data<br>ON CONFLICT (=
id) DO UPDATE<br>SET=C2=A0 =C2=A0 mid =3D EXCLUDED.mid,<br>=C2=A0 =C2=A0 tx=
n_timestamp =3D EXCLUDED.txn_timestamp,<br>=C2=A0 =C2=A0 cre_ts =3D EXCLUDE=
D.cre_ts;</font><br></div><div><font face=3D"monospace"><br></font></div><d=
iv><span style=3D"color:rgb(240,128,128);font-family:monospace;font-size:me=
dium;white-space:pre">ERROR:  column &quot;txn_timestamp&quot; is of type t=
imestamp with time zone but expression is of type text
LINE 24: SELECT id, mid, txn_timestamp, cre_ts
                         ^
HINT:  You will need to rewrite or cast the expression.</span><font face=3D=
"monospace"><br></font></div></div>

--0000000000000991e7061f469e15--