如何解决PostgreSQL子查询返回多行的报错并获取预期聚合结果？

问题场景 #

先创建临时表并插入测试数据：

CREATE TEMPORARY TABLE IF NOT EXISTS "tmp" 
(
    "fuel_type" TEXT,
    "litres" NUMERIC(8,2),
    "is_rent" BOOLEAN
);

insert into tmp values ('petrol', 10, False);
insert into tmp values ('petrol', 20, False);
insert into tmp values ('diesel', 20, False);

执行以下查询时触发错误：

SELECT
    (SELECT row(fuel_type, SUM(litres)) 
     FROM tmp 
     GROUP BY fuel_type);

错误提示：

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

期望得到的输出：

ARRAY_AGG
fuel_type|litres
petrol,40
diesel,20

解决方法 #

错误原因是将返回多行的子查询作为单个表达式使用，PostgreSQL不允许这种操作。要实现需求，直接用ARRAY_AGG函数包裹聚合后的行即可：

SELECT ARRAY_AGG(row(fuel_type, SUM(litres))) AS array_agg
FROM tmp
GROUP BY fuel_type;

如果需要更直观的结构化输出，也可以直接返回分组统计结果：

SELECT fuel_type, SUM(litres) AS total_litres
FROM tmp
GROUP BY fuel_type;

该查询返回结果：

fuel_type | total_litres
----------+--------------
petrol    | 40.00
diesel    | 20.00

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