PostgreSQL使用触发器自动插入数据时触发非空约束错误的问题咨询

嘿，我来帮你排查这个问题！你遇到的错误其实是由几个容易忽略的小问题导致的，咱们一步步拆解：

错误根源分析 #

你看到的(null, null)失败行，主要是以下三个核心问题共同导致的：

• 触发器类型错误：你创建的触发器没有加上FOR EACH ROW，默认是语句级触发器。这种触发器不会针对每一行插入数据生成有效的NEW变量，所以NEW.customer_id和你写错的NEW.purchase都会被视为null，直接导致插入purchases时触发非空约束。
• 列名混淆错误：你的purchases表实际定义的是amount列，但触发器函数里错误地写成了purchase；同时customers表也没有purchase字段，你实际需要取的是NEW.amount。
• 表结构设计错误：purchases的customer_id被设为serial类型，但它应该是引用customers.customer_id的integer类型外键——serial是自增序列类型，会自动生成新值，完全不符合你复用customers表中customer_id的需求。

修复方案 #

1. 修正purchases表结构

如果已经创建了purchases表，先修改列类型并添加外键约束：

-- 修改customer_id列类型为integer
ALTER TABLE purchases ALTER COLUMN customer_id TYPE INTEGER;
-- 添加外键约束，确保与customers表关联正确
ALTER TABLE purchases ADD CONSTRAINT fk_purchases_customer 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

如果还没创建purchases表，直接用这个正确的创建语句：

CREATE TABLE purchases (
    customer_id INTEGER PRIMARY KEY REFERENCES customers(customer_id),
    amount DOUBLE PRECISION NOT NULL
);

2. 修正触发器函数

把错误的列名purchase改成amount，同时加上必要的返回值（PL/pgSQL触发器函数需要返回NEW或OLD）：

CREATE OR REPLACE FUNCTION auto_insert_purchases()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$body$
BEGIN
    -- 匹配表的实际列名，插入正确的字段值
    INSERT INTO purchases(customer_id, amount) 
    VALUES (NEW.customer_id, NEW.amount);
    RETURN NEW; -- AFTER触发器返回NEW不影响逻辑，但符合语法规范
END
$body$;

3. 修正触发器定义

一定要加上FOR EACH ROW，把触发器设置为行级触发器，这样才能获取到每一行插入的NEW变量值：

CREATE TRIGGER tr_auto_insert_purchases
AFTER INSERT ON customers
FOR EACH ROW -- 关键：指定为行级触发器
EXECUTE FUNCTION auto_insert_purchases();

测试验证 #

现在执行你的插入语句（注意serial类型的customer_id可以不用手动指定，让它自动生成，当然手动指定合法值也没问题）：

-- 推荐写法：让serial自动生成customer_id
INSERT INTO customers(c_name, amount) VALUES ('Stewie Griffin', 4.99);

-- 或者手动指定customer_id（确保值在customers表的序列范围内）
INSERT INTO customers(customer_id, c_name, amount) VALUES (2, 'Stewie Griffin', 4.99);

这时候触发器会自动把对应的customer_id和amount插入到purchases表中，不会再触发非空约束错误啦。

备注：内容来源于stack exchange，提问作者user20137453