On Tue, Nov 12, 2024 at 1:35 AM Peter Geoghegan <p...@bowt.ie> wrote:
> On Mon, Nov 11, 2024 at 12:41 AM yudhi s <learnerdatabas...@gmail.com> > wrote: > > So it means it will ensure no duplication happens for ID values, but > still we are seeing "duplicate key" error. So what is the possible reason > here or are we encountering any buggy behaviour here? > > MERGE doesn't actually make any promises about not getting unique > violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING) > make such a promise. That's the main reason why Postgres supports > both. > > Okay. But here in this Merge statement it should first compare the ON clause which is the value of ID column and if its exists in the target table then its a MATCH which means it will do the UPDATE and if its not available in the target table then its a NOT MATCH and it will do the INSERT, so i am wondering at what exact situation it will throw duplicate key error. Also the WITH clause will only pick one record at a time and run the MERGE, so it will only merge one record at a time and then commit. Can you share your thoughts on how exactly this merge query can possibly cause the duplicate key error?
