Re: Insert works but fails for merge

I just remembered that one of the complaints was not wanting to worry about looking up the data types. In my previous example, you can also leave out the types and Postgres will do the right thing. I prefer the explicit data type version for clarity, but though I would provide this one for complete

Re: Insert works but fails for merge

So it looks like the OP does not mind updating more than one row. If you want to keep it simple and not do a lot of casting, consider using a CTE to do a reverse-upsert and use a prepared statement. Prepare and cast once, and have your app send the raw uncasted strings many, many times: prepare fo

Re: Insert works but fails for merge

On 8/11/24 03:09, Alban Hertroys wrote: On 10 Aug 2024, at 22:23, yudhi s wrote: On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver wrote: 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 s

Re: Insert works but fails for merge

> On 10 Aug 2024, at 22:23, yudhi s wrote: > On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver > wrote: > > 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_

Re: Insert works but fails for merge

Apology for the confusion. The other column is the txn_timestamp in the composite unique key, which is also the partition key. But yes we cant use both in the ON clause because of certain business requirements. We realized it late. And that's why "on conflict " We are unable to use. On Sun, 11 A

Re: Insert works but fails for merge

On 8/10/24 13:23, yudhi s wrote: On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver > wrote: Why not use INSERT ... ON CONFLICT instead of MERGE? > > MERGE INTO tab1 AS target > USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123,

Re: Insert works but fails for merge

On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver wrote: > > > 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

Re: Insert works but fails for merge

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 ou

Re: Insert works but fails for merge

On Saturday, August 10, 2024, yudhi s wrote: > > 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. > I’d do most anything before resorting to dynamic SQL. Usually o

Re: Insert works but fails for merge

On Sat, Aug 10, 2024 at 2:56 AM Adrian Klaver wrote: > 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 "Y

Re: Insert works but fails for merge

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

Re: Insert works but fails for merge

On Fri, Aug 9, 2024 at 2:14 PM yudhi s 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 t