I have a table that has two columns that are unique, and would like to upsert if the first column (or both columns) has a conflict, but do nothing if only the second column has a conflict. Is this possible?
CREATE TABLE test (
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
status VARCHAR(127)
);
The following works to check for conflicts on email:
INSERT INTO test(username, email)
VALUES ('test', '[email protected]')
ON CONFLICT(email) DO UPDATE SET status='upserted';
But I'd like to do something like this (invalid syntax below):
(INSERT INTO test(username, email)
VALUES ('test', '[email protected]')
ON CONFLICT(email) DO UPDATE SET status='upserted')
ON CONFLICT DO NOTHING;
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', '[email protected]')
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;
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加