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
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 |