如何为PostgreSQL表列实现通用‘未设置’哨兵值以区分设为NULL？

方案一：新增字段修改标记列（通用且保留外键约束） #

给edits表的每个可修改字段新增对应布尔标记列，通过标记明确区分「不修改」和「修改（包括设为NULL）」：

• 当标记列为true时，无论对应字段值是NULL还是非NULL，都将students表的对应字段更新为该值
• 当标记列为false或NULL时，忽略该字段的修改

修改后的edits表结构：

CREATE TABLE edits (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id) NOT NULL,
  name TEXT,
  name_changed BOOLEAN DEFAULT FALSE,
  city TEXT,
  city_changed BOOLEAN DEFAULT FALSE,
  mentor_of INTEGER REFERENCES students(id),
  mentor_of_changed BOOLEAN DEFAULT FALSE
);

执行更新的SQL示例：

UPDATE students
SET
  name = CASE WHEN e.name_changed THEN e.name ELSE students.name END,
  city = CASE WHEN e.city_changed THEN e.city ELSE students.city END,
  mentor_of = CASE WHEN e.mentor_of_changed THEN e.mentor_of ELSE students.mentor_of END
FROM edits e
WHERE students.id = e.student_id;

该方案完全通用，支持所有数据类型，同时保留mentor_of列的外键约束，逻辑清晰易维护。

方案二：窄表设计（每行记录单个字段的修改） #

将edits表改为每行仅记录一个学生的单个字段修改，通过「是否存在该行」区分「不修改」，通过「行内new_value是否为NULL」区分「修改为NULL」：

修改后的表结构：

CREATE TABLE edits (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id) NOT NULL,
  column_name TEXT NOT NULL CHECK (column_name IN ('name', 'city', 'mentor_of')),
  new_text_value TEXT,
  new_int_value INTEGER REFERENCES students(id),
  CHECK (
    (column_name = 'name' AND new_text_value IS NOT NULL AND new_int_value IS NULL)
    OR (column_name = 'city' AND new_text_value IS NOT NULL AND new_int_value IS NULL)
    OR (column_name = 'mentor_of' AND new_int_value IS NOT NULL AND new_text_value IS NULL)
    OR (column_name IN ('name', 'city', 'mentor_of') AND new_text_value IS NULL AND new_int_value IS NULL)
  )
);

执行更新的SQL示例：

-- 更新name字段
UPDATE students
SET name = e.new_text_value
FROM edits e
WHERE students.id = e.student_id AND e.column_name = 'name';

-- 更新city字段
UPDATE students
SET city = e.new_text_value
FROM edits e
WHERE students.id = e.student_id AND e.column_name = 'city';

-- 更新mentor_of字段（含设为NULL的场景）
UPDATE students
SET mentor_of = e.new_int_value
FROM edits e
WHERE students.id = e.student_id AND e.column_name = 'mentor_of';

该方案的优点是账本记录更精细，适合需要审计单个字段变更的场景；缺点是表结构稍复杂，更新时需分字段处理。

方案三：使用特殊哨兵值（仅适合特定类型，非通用） #

对于数值类型（如mentor_of的INTEGER），可选择业务上不可能出现的值作为哨兵（例如-1），通过检查约束限制该值仅用于标记「设置为NULL」，再在更新逻辑中转换：

修改后的edits表结构：

CREATE TABLE edits (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id) NOT NULL,
  name TEXT,
  city TEXT,
  mentor_of INTEGER CHECK (mentor_of IS NULL OR mentor_of = -1 OR EXISTS (SELECT 1 FROM students WHERE id = mentor_of))
);

执行更新的SQL示例：

UPDATE students
SET
  name = COALESCE(e.name, students.name),
  city = COALESCE(e.city, students.city),
  mentor_of = CASE WHEN e.mentor_of = -1 THEN NULL ELSE COALESCE(e.mentor_of, students.mentor_of) END
FROM edits e
WHERE students.id = e.student_id;

该方案不通用，不同数据类型需选择不同哨兵值（例如TEXT类型可用'__NULL__'），且需额外检查约束验证，灵活性不如前两种方案。

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