Re: Duplicate key error

On Tue, Nov 12, 2024 at 1:35 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Nov 11, 2024 at 12:41 AM yudhi s <learnerdatabase99(at)gmail(dot)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?