PostgreSQL 10.22：视图插入遇基表非空约束报错，触发器无效求解决

问题原因 #

你创建的all_items视图仅包含基表item的id字段，向视图插入数据时，PostgreSQL会自动尝试给基表未指定的name字段赋值为NULL，但name有非空约束，因此报错。之前的触发器无效，是因为视图未包含name字段，触发器中的NEW记录根本没有name属性，自然无法通过NEW.name赋值。

可行解决方法 #

方法1：修改INSTEAD OF触发器，直接操作基表

重新编写触发函数，跳过视图的默认插入逻辑，直接向基表item插入数据并补全name字段：

CREATE OR REPLACE FUNCTION fill_NULL_attributes()
RETURNS trigger AS $$
BEGIN
  -- 直接向基表插入，主动设置name的默认值
  INSERT INTO item(id, name)
  VALUES(NEW.id, 'X');
  RETURN NULL; -- INSTEAD OF触发器返回NULL表示已完成插入处理
END;
$$ LANGUAGE plpgsql;

-- 先删除旧触发器（如果存在），再创建新触发器
DROP TRIGGER IF EXISTS all_items_insert_fix ON all_items;
CREATE TRIGGER all_items_insert_fix
    INSTEAD OF INSERT ON all_items
    FOR EACH ROW
    EXECUTE PROCEDURE fill_NULL_attributes();

之后执行INSERT INTO all_items VALUES (999)，就会自动给name赋值为X，不会触发非空约束报错。

方法2：给基表name字段添加默认值

这是更简单的方案，直接修改基表，给name设置默认值，这样即使插入时未指定该字段，PostgreSQL会自动填充默认值：

ALTER TABLE item ALTER COLUMN name SET DEFAULT 'X';

修改后无需触发器，直接执行INSERT INTO all_items VALUES (999)即可成功，基表的name字段会自动被设为X。

方法3：修改视图包含name字段并指定默认值

如果需要保留视图逻辑同时支持插入，可以修改视图，将name字段包含进来并设置默认值：

CREATE OR REPLACE VIEW all_items AS
SELECT i.id, COALESCE(i.name, 'X') AS name
FROM item i
WITH CHECK OPTION;

之后插入时可以只传id（自动用默认值X），也可以显式指定name：

-- 只传id，name自动用默认值X
INSERT INTO all_items(id) VALUES(999);
-- 显式指定name
INSERT INTO all_items(id, name) VALUES(1000, 'CustomName');

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