Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

[GENERAL] CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

From
agharta
Date:

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.

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). 

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).

So, how to use UPSERT in this case?
Best regards,

Agharta

Re: [GENERAL] CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

From
Alban Hertroys
Date:
> On 18 Apr 2017, at 10:13, agharta <agharta82@gmail.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
(uniquecomposite/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
uniquesomehow. 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
youstill 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
columnadded 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
amwrong 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.

Re: [GENERAL] CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

From
agharta
Date:
Hi,
Thank you for your suggestion, i'll try to implement it.


Many thanks,
Cheers,
Agharta


Il 18/04/2017 12:38, Alban Hertroys ha scritto:
>> On 18 Apr 2017, at 10:13, agharta <agharta82@gmail.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
(uniquecomposite/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
themunique 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,
unlessyou 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
columnadded 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
amwrong 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.
>
>