I think this feature is a very common requirement.
For example. I created a table, which username and email columns are unique separately
CREATE TABLE test (
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
status VARCHAR(127)
);
I want to do an "update" when any of these two columns triggers conflict.
But postgres doesn't support "conflict(col1 or col2)", it only supports "conflict(col1)", like this:
insert into test (username, email, status) values('u1','e1','s1') on conflict(username) do update set status=CURRENT_TIMESTAMP;
Many guys said you can create a unique index on a combination of two columns like "unique(username, email)", this is an absolutely incorrect answer !
Assume I insert two records:
1. u1, e1, s1
2. u2, e2 ,s2
Now when I insert (u1, e3, s3), the combination (u1, e3) is unique, but if you use "on conflict(username, email) do update xxx", you will still get an exception ! it can not process conflict on any one of the columns !
So I think we should have a simple syntax to support it? (I don't want to write a Stored Procedure or use any Complex/hacker solution, thanks.).
Can the postgres team implement this feature?
--