How to Handle Multiple Conflict Targets in PostgreSQL's ON CONFLICT Clause?

Using multiple conflict targets in the ON CONFLICT clause

If the table contains multiple unique columns and the INSERT ... ON CONFLICT ... DO UPDATE syntax is required to update other columns in the event of a conflict, you must create a unique index that contains all conflict target columns.

Question:

When trying to use multiple columns as conflict targets in a ON CONFLICT clause, an error occurs because a unique index is required to perform conflict detection. Consider the following example:

<code class="language-sql">INSERT INTO table
...
ON CONFLICT (col1, col2)
DO UPDATE
SET
....</code>

Solution:

To resolve this issue, create a unique index on both columns:

<code class="language-sql">CREATE UNIQUE INDEX idx_t_col1_col2 ON table (col1, col2);</code>

After creating a unique index, the ON CONFLICT clause can specify two columns as conflict targets to achieve the desired update behavior in the event of a conflict:

<code class="language-sql">INSERT INTO table
...
ON CONFLICT (col1, col2)
DO UPDATE
SET
....</code>

Example:

Create a table with unique columns id and a and insert data while handling conflicts:

<code class="language-sql">CREATE TABLE t (id integer, a text, b text);
CREATE UNIQUE INDEX idx_t_id_a ON t (id, a);
INSERT INTO t VALUES (1, 'a', 'foo');
INSERT INTO t VALUES (1, 'a', 'bar') ON CONFLICT (id, a) DO UPDATE SET b = 'bar';</code>

Run query to verify updated data:

<code class="language-sql">SELECT * FROM t;</code>

Output:

<code> id | a |  b  
----+---+-----
  1 | a | bar</code>

The above is the detailed content of How to Handle Multiple Conflict Targets in PostgreSQL's ON CONFLICT Clause?. For more information, please follow other related articles on the PHP Chinese website!