request to support "conflict on(col1 or col2) do update xxx" feature

From: sai <pianoboysai(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: request to support "conflict on(col1 or col2) do update xxx" feature
Date: 2021-12-10 02:21:33
Message-ID: CAC=V=q1+1aqF1AyAgRCxoonsqriq=dgxuu0EPbsZ0ctw9MWVyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

--
Best Regards,
Jin

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2021-12-10 02:56:21 Re: Need to know more about pg_test_fsync utility
Previous Message PGSQL DBA 2021-12-10 02:19:45 Need to know more about pg_test_fsync utility