sql - 是否可以在 Postgres 中仅在 2 列之一发生冲突时插入？

Question

我有一个表，其中有两列是唯一的，如果第一列(或两列)有冲突，我想更新插入，但如果只有第二列有冲突，则不执行任何操作。这可能吗？

CREATE TABLE test (
    username    VARCHAR(255) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    status      VARCHAR(127)
);

以下用于检查电子邮件冲突:

INSERT INTO test(username, email)
    VALUES ('test', 'test@test.test')
    ON CONFLICT(email) DO UPDATE SET status='upserted';

但我想做这样的事情(下面的语法无效):

(INSERT INTO test(username, email)
    VALUES ('test', 'test@test.test')
    ON CONFLICT(email) DO UPDATE SET status='upserted')
    ON CONFLICT DO NOTHING;

Answer 1

是的，您可以这样做，但它需要一些条件技巧。

首先，您可以只有一个 ON CONFLICT 子句，但是这个子句可以指定多个列，并在它们上面定义约束。在您的情况下，这将是 ON CONFLICT (username, email)。当其中一个或两个列触发冲突时，conflict_action 开始。

其次，conflict_action 子句应将要插入的候选行(由 EXCLUDED 引用)的值与当前值进行比较，并采取适当的措施。 DO NOTHING 实际上是不可能的，但您可以将旧值分配给新行，这样效果是一样的(但会发生更新)。不漂亮，但它看起来有点像这样:

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;