PostgreSQL DVDrental中RefreshReportData函数主键重复报错求助

报错核心原因是payment表中存在同一rental_id对应多条付款记录（比如用户逾期产生的滞纳金、分次付款等），当执行rental JOIN payment时，会返回重复的rental_id行；而detailed_report表将rental_id设为主键，插入重复值直接触发唯一约束冲突。

另外你的触发器函数update_summary_table_function还存在隐藏问题：detailed_report表中没有film_id字段，触发器里引用NEW.film_id会导致执行失败，需要一并修复。

1. 修复重复主键问题（二选一） #

方案A：合并同一租赁的付款金额（推荐，符合报表统计需求） #

如果报表需要统计每笔租赁的总付款金额，修改插入逻辑，对同一rental_id的付款金额求和，确保每个rental_id只插入一条记录：

CREATE OR REPLACE FUNCTION RefreshReportData() RETURNS VOID AS $$
BEGIN
    -- 清空报表表数据
    DELETE FROM detailed_report;
    DELETE FROM summary_report;
    
    -- 插入合并后的报表数据
    INSERT INTO detailed_report
    SELECT
        r.rental_id,
        r.rental_date,
        r.return_date,
        r.customer_id,
        f.title AS film_title,
        f.rental_duration,
        f.rental_rate,
        f.replacement_cost,
        SUM(p.amount) AS payment_amount -- 合并同一租赁的所有付款
    FROM
        rental r
    JOIN
        inventory i ON r.inventory_id = i.inventory_id
    JOIN
        film f ON i.film_id = f.film_id
    JOIN
        payment p ON r.rental_id = p.rental_id
    GROUP BY 
        r.rental_id, r.rental_date, r.return_date, r.customer_id,
        f.title, f.rental_duration, f.rental_rate, f.replacement_cost; -- 按非聚合字段分组
    
END;
$$ LANGUAGE plpgsql;

方案B：修改detailed_report主键结构 #

如果需要保留每笔付款的明细记录，不能用rental_id作为单一主键，可改为联合主键或新增自增主键：

-- 先删除原表（注意备份数据），重新创建表结构
DROP TABLE detailed_report;
CREATE TABLE detailed_report (
    report_id SERIAL PRIMARY KEY, -- 新增自增主键
    rental_id INT,
    payment_id INT, -- 加入payment_id区分不同付款记录
    rental_date TIMESTAMP,
    return_date TIMESTAMP,
    customer_id INT,
    film_title VARCHAR(255),
    rental_duration INT,
    rental_rate DECIMAL(5, 2),
    replacement_cost DECIMAL(8, 2),
    payment_amount DECIMAL(8, 2)
);
-- 给rental_id加索引，提升查询效率
CREATE INDEX idx_detailed_rental_id ON detailed_report(rental_id);

2. 修复触发器的隐藏问题 #

原触发器引用了不存在的NEW.film_id，提供两种修复方式：

方式1：给detailed_report新增film_id字段 #

-- 新增film_id字段
ALTER TABLE detailed_report ADD COLUMN film_id INT;
-- 修改插入逻辑，同步插入film_id
CREATE OR REPLACE FUNCTION RefreshReportData() RETURNS VOID AS $$
BEGIN
    DELETE FROM detailed_report;
    DELETE FROM summary_report;
    
    INSERT INTO detailed_report
    SELECT
        r.rental_id,
        r.rental_date,
        r.return_date,
        r.customer_id,
        f.title AS film_title,
        f.rental_duration,
        f.rental_rate,
        f.replacement_cost,
        SUM(p.amount) AS payment_amount,
        f.film_id -- 新增插入film_id
    FROM
        rental r
    JOIN
        inventory i ON r.inventory_id = i.inventory_id
    JOIN
        film f ON i.film_id = f.film_id
    JOIN
        payment p ON r.rental_id = p.rental_id
    GROUP BY 
        r.rental_id, r.rental_date, r.return_date, r.customer_id,
        f.title, f.rental_duration, f.rental_rate, f.replacement_cost, f.film_id;
    
END;
$$ LANGUAGE plpgsql;

方式2：修改触发器函数，通过rental_id关联查询film_id #

CREATE OR REPLACE FUNCTION update_summary_table_function() RETURNS TRIGGER AS $$
DECLARE
    v_film_id INT;
    v_category_id INT;
BEGIN
    -- 通过rental_id关联查询对应film_id
    SELECT f.film_id INTO v_film_id
    FROM rental r
    JOIN inventory i ON r.inventory_id = i.inventory_id
    JOIN film f ON i.film_id = f.film_id
    WHERE r.rental_id = NEW.rental_id;
    
    -- 查询影片对应的分类ID
    SELECT category_id INTO v_category_id FROM film_category WHERE film_id = v_film_id;
    
    -- 插入/更新汇总表，避免分类不存在时更新失败
    INSERT INTO summary_report (film_category, total_rentals, total_revenue)
    VALUES (v_category_id::VARCHAR, 1, NEW.payment_amount)
    ON CONFLICT (film_category) DO UPDATE
    SET 
        total_rentals = summary_report.total_rentals + 1,
        total_revenue = summary_report.total_revenue + NEW.payment_amount;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1. 执行修改后的表结构/函数代码
2. 调用SELECT RefreshReportData();
3. 检查detailed_report和summary_report的数据是否正常生成

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