# Avoid naming a constraint directly when using ON CONFLICT DO UPDATE

DevFeed: [Avoid naming a constraint directly when using ON CONFLICT DO UPDATE](<https://devfeed.tech/articles/avoid-naming-a-constraint-directly-when-using-on-conflict-do-update-33652.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2015/10/avoid-naming-constraint-directly-when.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2015-10-02T18:36:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [implementation](<https://devfeed.tech/topics/implementation.md>), [syntax](<https://devfeed.tech/topics/syntax.md>), [ordering](<https://devfeed.tech/topics/ordering.md>)

Tags: [concurrently](<https://devfeed.tech/tags/concurrently.md>), [implementation](<https://devfeed.tech/tags/implementation.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [syntax](<https://devfeed.tech/tags/syntax.md>)

## AI overview

The article explains PostgreSQL 9.5's UPSERT syntax and recommends using unique index inference with ON CONFLICT DO UPDATE instead of naming a constraint directly. It describes how inference selects arbiter indexes and handles variations such as column ordering, partial-index predicates, and multiple equivalent unique indexes.

## Source excerpt

PostgreSQL 9.5 will have support for a feature that is popularly known as "UPSERT" - the ability to either insert or update a row according to whether an existing row with the same key exists. If such a row already exists, the implementation should update it. If not, a new row should be inserted. This is supported by way of a new high level syntax (a clause that extends the INSERT statement) that more or less relieves the application developer from having to give any thought to race conditions. This common operation for client applications is set to become far simpler and far less error-prone than legacy ad-hoc approaches to UPSERT involving subtransactions. When we worked on UPSERT, many edge-cases were carefully considered. A technique called "unique index inference" allows DML statement authors to be very explicit about what condition they want to take the alternative (UPDATE or NOTHING) path on. That alternative path can only be taken in the event of a would-be duplicate violation in an "arbiter" unique index (for the DO NOTHING variant, a would-be exclusion violation is also a possible reason to take the alternative NOTHING path). The ability to write UPSERT statements explicitly and safely while also having lots of flexibility is an important differentiator for PostgreSQL's UPSERT in my view. As the 9.5 INSERT documentation explains, the inference syntax contains one or more column_name_index (columns) and/or expression_index expressions (expressions), and perhaps an optional index_predicate (for partial unique indexes, which are technically not constraints at all). This is internally used to figure out which of any available unique indexes ought to be considered as an arbiter of taking the alternative path. If none can be found, the optimizer raises an error. The inference syntax is very flexible, and very tolerant of variations in column ordering, whether or not a partial unique index predicate is satisfied, and several other things. It can infer multiple un