sql - 冲突时删除 postgres

Question

我试图在单个查询中切换一个关系，如果关系已经存在，它会删除它，否则会创建它。我可以添加带有 bool 的第三列来切换它，但我宁愿删除它。

架构

CREATE TABLE IF NOT EXISTS thread_subscription (
    profile_id     INTEGER REFERENCES profile (id),
    thread_id      INTEGER REFERENCES thread (id),
    UNIQUE(profile_id,thread_id)
)

查询

INSERT INTO thread_subscription (profile_id,thread_id) 
VALUES ($1,$2) ON CONFLICT (profile_id,thead_id) 
DO DELETE FROM thread_subscription 
WHERE profile_id = $1 AND thread_id = $2;

Answer 1

所以您的意图是在表上运行 INSERT 命令，并且您希望在重复键上它实际上会 DELETE 相关记录。虽然在技术上可行，但我不推荐这种设置，因为这是远距离操作，很难调试。

不过，可以使用 Postgres 函数实现相同类型的功能。这使得切换订阅的意图明确，并且不会干扰标准数据库语句(INSERT)。

该函数的代码如下:它有两个参数作为输入，验证订阅表中是否已有记录，然后执行相关操作；它在 DELETE 时返回 0，在 INSERT 时返回 1。可以看到this db fiddle有关其工作原理的完整演示。

CREATE OR REPLACE FUNCTION toggle_subscription(
    pid NUMERIC, 
    tid NUMERIC
) 
RETURNS NUMERIC AS $$
DECLARE
    row_exists NUMERIC;
BEGIN

    SELECT 1 
    INTO row_exists 
    FROM thread_subscription 
    WHERE profile_id = pid and thread_id = tid;

    IF (row_exists > 0) THEN
        DELETE FROM thread_subscription WHERE profile_id = pid and thread_id = tid;
        RETURN 0;
    ELSE
        INSERT INTO thread_subscription(profile_id, thread_id) VALUES(pid, tid);
        RETURN 1;
    END IF;

END; 
$$
LANGUAGE plpgsql;