ON CONFLICT DO for UPDATE statements

We run two separate databases which are synchronized through our own
replication system (two master databases, so we use our own trigger based
methods). Let's call the two databases "main" and "remote".

From time to time we get constraint violations on sorting fields classified
UNIQUE when users add records belonging the same data entity:

Create table orderpos (

int id not null primary key,

int order not null, -- the entity

int sort not null, -- sorting criteria for reporting purposes

text data,

UNIQUE (order, sort) );

I know that with PostgreSQL 9.5 I can use UPSERT to modify the field "sort"
when adding it to the remote database. I simply change the content of the
field sort to MAX(sort)+1 for this order. This will resolve the issue on the
remote side.

However, the modified value will be replicated back to the main database
where the record was generated. My concern is that in the meantime someone
might have added another order position to the master database and the value
coming back from the remote database is no longer UNIQUE.

Is there a similar concept like UPSERT for UPDATE statements? I.e.

UPDATE orderpos SET sort = 2

WHERE if = 4

ON CONFLICT sort DO UPDATE SET sort = GetSafeSortValue(2)

If not, what is the best way of avoiding a constraint violation in UPDATE
statements?

- A BEFORE UPDATE trigger checking the UNIQUE condition and calling
GetSafeSort() in case of a conflict?

- A rule?

- Can triggers be used to catch constraint violations?

My least preferred solution would by the replication software handling an
exception.

Thanks

Klaus

---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
https://www.avast.com/antivirus