CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

From: agharta <agharta82(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)
Date: 2017-04-18 08:13:38
Message-ID: feffc038-e8c2-f7af-22eb-3f99e0b1460a@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Moreno Andreo 2017-04-18 09:42:38 # of connections and architecture design
Previous Message Tim Kane 2017-04-18 07:05:17 UDP buffer drops / statistics collector