sql - Postgresql BEFORE INSERT 触发器在检查约束之前未运行

Question

我有一个触发器，它在插入时修剪一些数据。其中一列仅包含 20 个字符。如果它有 21 个字符，我们会剪掉第一个字符。但是，当我尝试插入 21 个字符时，它会返回一个值太长的错误。所以它似乎在运行我的触发器之前检查约束？

触发器创建:

CREATE TRIGGER my_trigger
  BEFORE INSERT
  ON public.mytable
  FOR EACH ROW
  EXECUTE PROCEDURE public.my_trigger();

触发器中有一些代码如下:

IF (NEW.mydata IS NOT NULL AND LENGTH(NEW.mydata) > 20 THEN
    NEW.mydata := substring(NEW.mydata from 2);
END IF;

但是，当我尝试插入并向其中插入 21 个字符时，它失败了:

[Err] ERROR: value too long for type character varying(20)

Answer 1

Postgres 在运行触发器之前不会检查表 约束。但是，它保证 NEW 中字段的类型与表中字段的类型相匹配，因此它必须检查 type 约束才能让您有机会看到数据。

您可以通过从类型中删除大小限制并将长度检查实现为表约束来解决此问题:

ALTER TABLE mytable
  ALTER COLUMN mydata TYPE TEXT,
  ADD CONSTRAINT mydata_length CHECK (length(mydata) <= 20);

在内部，TEXT 和 VARCHAR(N) 在 Postgres 中是相同的，因此就数据库而言，类型更改不应该有任何影响。但是，某些客户端库对这些类型的处理方式截然不同，因此请注意应用程序中性能/行为的差异。