PostgreSQL中单独可用的COUNT语句在INSERT时返回NULL问题排查

我是一名学生，正在使用PostgreSQL 15.2与pgAdmin4操作Sakila DVD租赁数据库完成项目。在执行一段向summary表插入数据的代码时，用于统计total_rentals和total_titles的子查询COUNT语句单独运行结果正确，但整段INSERT执行后，这两列全为NULL。相关代码如下：

INSERT INTO summary (store_id, cat_group, total_rentals, total_titles, avg_rental_duration) 
    SELECT DISTINCT detailed.store_id, cat_group_fx(detailed.category_name), 
        (SELECT COUNT (detailed.rental_id)
             FROM detailed, summary AS selfsum
            WHERE selfsum.store_id = detailed.store_id
             AND cat_group_fx(detailed.category_name) = selfsum.cat_group
            GROUP BY detailed.store_id, selfsum.cat_group),  
        (SELECT COUNT (inventory.inventory_id)
             FROM inventory, detailed, summary AS selfsum
            WHERE inventory.film_id = detailed.film_id
             AND inventory.store_id = detailed.store_id
            AND detailed.store_id = selfsum.store_id
             AND cat_group_fx(detailed.category_name) = selfsum.cat_group
            GROUP BY detailed.store_id, selfsum.cat_group), 
        AVG(rental.return_date - rental.rental_date) 
    FROM detailed, rental 
    GROUP BY detailed.store_id, detailed.category_name;

补充信息：cat_group_fx是自定义函数，用于按流派分类租赁记录；detailed表用于整合Sakila各表的粒度数据。

核心问题是INSERT执行时目标表summary为空：

• 子查询里关联了summary AS selfsum，但此时summary还未插入任何数据，WHERE条件无法匹配到任何行，导致COUNT子查询返回NULL。
• 单独运行子查询时，你可能是在假设summary已有数据的场景下测试的，但实际INSERT过程中目标表是空表，逻辑不成立。

不需要关联空的目标表summary，直接基于detailed、rental和inventory表做聚合统计，同时修正主查询的表关联逻辑（原隐式关联会产生笛卡尔积，导致AVG计算错误）。修改后的代码如下：

INSERT INTO summary (store_id, cat_group, total_rentals, total_titles, avg_rental_duration) 
SELECT
    d.store_id,
    cat_group_fx(d.category_name) AS cat_group,
    COUNT(DISTINCT d.rental_id) AS total_rentals,
    COUNT(DISTINCT i.inventory_id) AS total_titles,
    AVG(r.return_date - r.rental_date) AS avg_rental_duration
FROM detailed d
JOIN rental r ON d.rental_id = r.rental_id
LEFT JOIN inventory i ON d.film_id = i.film_id AND d.store_id = i.store_id
GROUP BY d.store_id, cat_group_fx(d.category_name);

关键修改说明： #

1. 移除子查询中对summary的关联，直接在主查询用聚合函数统计，避免依赖空目标表。
2. 使用显式JOIN替代隐式笛卡尔积，通过rental_id关联detailed和rental表，保证数据关联逻辑正确。
3. 用COUNT(DISTINCT)确保统计的是唯一的租赁记录和库存条目，避免重复计数。
4. 去掉多余的DISTINCT，因为GROUP BY已经会返回唯一的store_id+cat_group组合。

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