On 8/10/24 05:07, yudhi s wrote:
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.
Why not use INSERT ... ON CONFLICT instead of MERGE?
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 <http://target.id> = source.id <http://source.id>
WHEN MATCHED THEN
UPDATE SET mid = source.mid
WHEN NOT MATCHED THEN
INSERT (id, mid, txn_timestamp, cre_ts)
VALUES (source.id <http://source.id>,source.mid,
source.txn_timestamp, source.cre_ts);
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
