PostgreSQL中字符数组空值导致同组Projects校验查询失效问题

原查询无法检测同一PartNo+PartName下，projects列同时存在非空数组与空数组/NULL的不一致情况。原因是PostgreSQL的COUNT(DISTINCT)会忽略NULL值，若分组内混合了非空数组和NULL，COUNT(DISTINCT)只会统计非空数组的数量，无法触发HAVING COUNT(...) > 1的条件；即便空数组'{}'本身是非NULL值，若原逻辑未统一处理NULL与空数组，也可能因数据类型细节导致判断失效。

用COALESCE函数将NULL值转换为空数组'{}'::character varying[]，统一NULL与空数组的判定逻辑，让COUNT(DISTINCT)能正确识别非空数组和空数组（含转换后的NULL）的差异。

SELECT COUNT(*) from (
    select c.partno, c.partname
    FROM unnest(items) as c
    GROUP BY c.partno, c.partname
    HAVING COUNT(distinct COALESCE(c.projects, '{}'::character varying[])) > 1
) as xxx
INTO errCount;

IF errCount > 0 THEN
    RETURN QUERY 
        SELECT 0 as status, format('Projects value should be the same for all Codes of the Part No %s and Name %s',c.partno,c.partname) as message  
        FROM unnest(items) as c
        GROUP BY c.partno, c.partname
        HAVING COUNT(distinct COALESCE(c.projects, '{}'::character varying[])) > 1 
        ;
    RETURN;
END IF;

• COALESCE(c.projects, '{}'::character varying[])：把projects列的NULL值替换为空数组，确保NULL和原生空数组被视为同一类值。
• 修改后的COUNT(DISTINCT ...)会准确统计分组内的数组值差异，只要存在非空数组与空数组（含转换后的NULL）的混合，就会触发COUNT(...) > 1的条件，从而检测出不一致。

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