postgresql - 如何使 PostgreSQL 约束仅适用于新值

Question

我是 PostgreSQL 的新手，非常喜欢约束如何与行级安全性配合使用，但我很困惑如何让它们执行我想要的操作。

我有一列，我想添加一个约束来为文本列创建最小长度，此检查适用于此:

(length((column_name):: text) > 6)

但是，它还会阻止用户更新 column_name 已低于 6 个字符的任何行。

我想让他们无法将该值更改为该值，但仍然可以更新已经发生这种情况的行，以便他们可以根据我的新政策根据需要进行更改。

这可能吗？

Answer 1

BUT, it also then prevents users updating any rows where column_name is already under 6 characters.

嗯，不。当您尝试添加 CHECK 约束时，系统会检查所有现有行，如果发现任何违规，则会引发异常。
你必须做到 NOT VALID 。那么是的。

您确实需要一个INSERT 或UPDATE 上的触发器来检查新值。没有那么便宜，也没有子弹防弹那么好，但仍然相当坚固。喜欢:

CREATE OR REPLACE FUNCTION trg_col_min_len6()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF TG_OP = 'UPDATE'
   AND OLD.column_name IS NOT DISTINCT FROM NEW.column_name THEN
      -- do nothing
   ELSE
      RAISE EXCEPTION 'New value for column "note" must have at least 6 characters.';
   END IF;
  
   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER tbl1_column_name_min_len6
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW
WHEN (length(NEW.column_name) < 7)
EXECUTE FUNCTION trg_col_min_len6();

db<> fiddle here

直接检查触发器的 WHEN 条件应该是最有效的。然后，触发函数仅在短值时被调用，并且可以非常简单。
请参阅:

• Trigger with multiple WHEN conditions
• Fire trigger on update of columnA or ColumnB or ColumnC