public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: yudhi s <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Insert works but fails for merge
Date: Sat, 10 Aug 2024 07:52:31 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
<[email protected]>
<CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
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
[email protected]
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Insert works but fails for merge
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox