On Tue, Nov 12, 2024 at 1:35 AM Peter Geoghegan <pg@bowt.ie> wrote:
On Mon, Nov 11, 2024 at 12:41 AM yudhi s <learnerdatabase99@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?
