PostgreSQL触发器实现：确保表中仅一行col2值为true

前提假设 #

假设你的表名为target_table，基础结构如下：

CREATE TABLE target_table (
    id INT PRIMARY KEY,
    col2 BOOLEAN NOT NULL DEFAULT FALSE
);

1. 创建触发器函数 #

这个函数会在更新行的col2为true时，自动将表内其余所有行的col2设为false：

CREATE OR REPLACE FUNCTION enforce_single_true_col2()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当当前行的col2被设置为true时执行逻辑
    IF NEW.col2 = TRUE THEN
        -- 将其他所有行的col2置为false，排除当前更新的行
        UPDATE target_table
        SET col2 = FALSE
        WHERE id != NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

将触发器绑定到目标表，仅当col2字段发生变化时触发，避免无意义的执行：

CREATE TRIGGER trigger_single_true_col2
AFTER UPDATE OF col2 ON target_table
FOR EACH ROW
WHEN (NEW.col2 IS DISTINCT FROM OLD.col2)
EXECUTE FUNCTION enforce_single_true_col2();

测试示例 #

1. 插入初始数据：

INSERT INTO target_table (id, col2) VALUES (1, TRUE), (2, FALSE), (3, FALSE);

查询表数据：

SELECT * FROM target_table;

结果：

id | col2
----+------
  1 | t
  2 | f
  3 | f

2. 更新id=3的col2为true：

UPDATE target_table SET col2 = TRUE WHERE id = 3;

再次查询表数据：

SELECT * FROM target_table;

结果：

id | col2
----+------
  1 | f
  2 | f
  3 | t

注意事项 #

• 如果将当前唯一col2=true的行改为false，触发器不会阻止操作，此时表中所有行的col2都会是false。若需要强制表中始终存在一行col2=true，可在触发器函数中添加额外逻辑（比如阻止修改最后一个true行）。
• 确保表的id字段是主键或带有唯一约束，避免WHERE id != NEW.id出现匹配错误。

内容的提问来源于stack exchange，提问作者doozy