From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | agharta <agharta82(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT) |
Date: | 2017-04-18 10:38:24 |
Message-ID: | AA27CFDA-3AD7-4DE1-8866-F52666AF4C8C@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 18 Apr 2017, at 10:13, agharta <agharta82(at)gmail(dot)com> wrote:
>
> Hi all,
>
> I have a problem with INSERT ... ON CONFLICT sql command.
>
> Reading 9.6 documentation i see that ON CONFLICT command will accpets only index_column_name or index_expression (unique composite/primary indexes are valid too).
>
> So, my problem is that i can't create any type of upsert-valid index . Let me explain.
>
> I have a table T1 containing F1, F2, F3, F4 fields.
>
> I can insert same records in T1, MAX TWICE.
How is UPSERT supposed to know which of a pair of duplicate records it is supposed to update? You'll have to make them unique somehow. The safest approach is usually to add a surrogate key based on a sequence.
> I can have records like (A,B,C,D),(B,A,D,C), etc.. and (A,B,C,D) AGAIN. Any other next insert of (A,B,C,D) is not allowed (actually it is avoided by a complex-and-slow-performance select count in before insert/update trigger).
You're probably better off with an EXISTS query there. Something like:
select F1, F2, F3, F4,
case
when exists (select 1 from T1 t where t.F1 = T1.F1 and t.F2 = T1.F2 and t.F3 = T1.F3 and t.F4 = T1.F4 and t.pk <> T1.pk) then 1
else 0
end as have_duplicate
from T1
where F1 = NEW.F1 and F2 = NEW.F2 and F3 = NEW.F3 and F4 = NEW.F4
limit 1;
The pk field in there is the surrogate key from the previous paragraph.
Alternatively, wrap your COUNT around a sub-query that's limited to 2 results. No extra pk needed in that case, unless you still need to use UPSERT with that.
In either case it will make a big difference to have an index on at least (F1, F2, F3, F4), perhaps with the new pk column added at the end.
> In this case i can't create any type of primary/unique index, like a composite F1,F2, F3, F4 index. (correct me if i am wrong please).
Correct, you'll most likely have to add a new one (unless someone comes up with better suggestions).
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | Luciano Mittmann | 2017-04-18 12:20:14 | Re: streaming replication and archive_status |
Previous Message | Moreno Andreo | 2017-04-18 09:42:38 | # of connections and architecture design |