Subject: Insert works but fails for merge

From: yudhi s <[email-masked]>
Date: Sat, 10 Aug 2024 02:43:28 +0530

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.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 9 Aug 2024 14:26:50 -0700

On 8/9/24 14:13, yudhi s wrote:
> 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 <https://dbfiddle.uk/j5S7br-q>*
> *
> 
> CREATE TABLE tab1 (
>      id varchar(100) ,
>      mid INT,
> expression is of type text LINE 24: SELECT id, mid, txn_timestamp, 
> cre_ts ^ HINT: You will need to rewrite or cast the expression.

VALUES:

https://www.postgresql.org/docs/current/sql-values.html

"When VALUES is used in INSERT, the values are all automatically coerced 
to the data type of the corresponding destination column. When it's used 
in other contexts, it might be necessary to specify the correct data 
type. If the entries are all quoted literal constants, coercing the 
first is sufficient to determine the assumed type for all:

SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), 
('192.168.1.43'));
"

The VALUES is not directly attached to the INSERT, you will need to do 
explicit casts:

VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123, 
'2024-08-09T11:33:49.402585600Z'::timestamptz, 
'2024-08-09T11:33:49.402585600Z'::timestamptz)

-- 
Adrian Klaver
[email-masked]

---

From: yudhi s <[email-masked]>
Date: Sat, 10 Aug 2024 17:37:31 +0530

> WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'),
Thank You Adrian and David.

Even converting the merge avoiding the WITH clause/CTE as below , is still
making it fail with the same error. So it seems , only direct "insert into
values" query can be auto converted/casted but not the other queries.

In our case , we were using this merge query in application code(in Java)
as a framework to dynamically take these values as bind values and do the
merge of input data/message. But it seems we have to now cast each and
every field which we get from the incoming message to make this merge work
in a correct way. I am wondering if the only way now is to get the data
types from information_schema.columns and then use the cast function to
write the values of the merge query dynamically casted/converted for each
of the fields in the application code. Please correct me if my
understanding is wrong.

MERGE INTO tab1 AS target
USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123,
'2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) AS
source(id, mid,txn_timestamp, cre_ts)
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET mid  = source.mid
WHEN NOT MATCHED THEN
INSERT (id, mid, txn_timestamp, cre_ts)
    VALUES (source.id,source.mid,  source.txn_timestamp, source.cre_ts);