Use multiple conflict_target in ON CONFLICT clause
A sample table and data
CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
CONSTRAINT col2_unique UNIQUE (col2)
);
INSERT INTO dupes values(1,1,'a'),(2,2,'b');
Reproducing the problem
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2
Let's call this Q1. The result is
ERROR: duplicate key value violates unique constraint "col2_unique"
DETAIL: Key (col2)=(2) already exists.
What the documentation says
conflict_target can perform unique index inference. When performing
inference, it consists of one or more index_column_name columns and/or
index_expression expressions, and an optional index_predicate. All
table_name unique indexes that, without regard to order, contain
exactly the conflict_target-specified columns/expressions are inferred
(chosen) as arbiter indexes. If an index_predicate is specified, it
must, as a further requirement for inference, satisfy arbiter indexes.
This gives the impression that the following query should work, but it does not because it would actually require a together unique index on col1 and col2. However such an index would not guarantee that col1 and col2 would be unique individually which is one of the OP's requirements.
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2
Let's call this query Q2 (this fails with a syntax error)
Why?
Postgresql behaves this way is because what should happen when a conflict occurs on the second column is not well defined. There are number of possibilities. For example in the above Q1 query, should postgresql update col1
when there is a conflict on col2
? But what if that leads to another conflict on col1
? how is postgresql expected to handle that?
A solution
A solution is to combine ON CONFLICT with old fashioned UPSERT.
CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently, or key2
-- already exists in col2,
-- we could get a unique-key failure
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the UPDATE again.
END;
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
You would need to modify the logic of this stored function so that it updates the columns exactly the way you want it to. Invoke it like
SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');
Upsert if on conflict occurs on multiple columns in Postgres db
Actually, found it here but not in the post marked as answer but the most rated post. Use multiple conflict_target in ON CONFLICT clause
So our query will be as follows:
INSERT into table (col1, col2, col3)
VALUES ('1', 'A', 'colval1A')
ON CONFLICT (col1, col2) DO UPDATE
SET col3 = 'good_value'
Possible to upsert in Postgres on conflict on exactly one of 2 columns?
Yes, you can do this, but it requires some conditional trickery.
First of all, you can have only one ON CONFLICT
clause, but this clause can specify multiple columns with constraints defined on them. In your case that would be ON CONFLICT (username, email)
. When either or both of the columns trigger a conflict, the conflict_action
kicks in.
Secondly, the conflict_action
clause should compare values from the candidate row for insertion (referenced by EXCLUDED
) against current values and take appropriate action. DO NOTHING
will in practice not be possible, but you can assign the old value to the new row so the effect is the same (but the update will happen). Not pretty, but it will look somewhat like this:
INSERT INTO test(username, email)
VALUES ('test', 'test@test.test')
ON CONFLICT(username, email) DO UPDATE
SET status = CASE WHEN username != EXCLUDED.username -- only email offending
THEN status -- so "do nothing"
ELSE 'upserted' -- some other action
END;
Related Topics
How to Check If a SQL Server String Is Null or Empty
Split and Get Second Row as Value
How to Get Textual Contents from Blob in Oracle SQL
Oracle Sql: Extracting Text Between Two Characters
Database Corruption With Mariadb:Table Doesn't Exist in Engine
How to Combine First Name, Middle Name and Last Name in SQL Server
How to Modify Query to Remove Double-Quotes from All Columns
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Sql Take Just the Numeric Values from a Varchar
How to Get Max Date Value of Date Column in Multiple Tables
Sql. How to Check If Record Exists in Table
Avoid Duplicates in Insert into Select Query in SQL Server
How to Kill/Stop a Long SQL Query Immediately
Total Sum of Multiple Columns in Oracle SQL Statement by Unique Id
How to Compare Datetime With Only Date in SQL Server
Postgresql Error: Relation Already Exists