在DBeaver中使用PostgreSQL 15时ROUND函数失效问题排查

场景与问题 #

在Windows 10系统上使用DBeaver操作PostgreSQL 15，完成Codecademy题目：计算2010年球队的最小“赢球成本”（球队总薪资除以胜场数）。

原SQL代码 #

WITH total_salary AS
(
        SELECT
        SUM(salaries.salary) AS "salary_sum",
        teams.name AS "team_name",
        salaries.yearid AS "year_id",
        teams.w AS "wins"
    FROM salaries
    JOIN teams
        ON salaries.team_id = teams.id
    WHERE salaries.yearid = 2010
    GROUP BY
        teams.name, teams.w, salaries.yearid
    ORDER BY
        teams.w DESC
),
cost_per_win_raw AS 
(
    SELECT 
        (salary_sum / wins) AS "cost_per_win_num"
    FROM total_salary
)
SELECT
    ROUND(cost_per_win_num, 2) AS "cost_per_win"
FROM cost_per_win_raw
GROUP BY cost_per_win
ORDER BY cost_per_win DESC;

报错信息 #

SQL Error [42883]: ERROR: function round(double precision, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 448

已知字段类型：teams.w为smallint类型，salaries.salary为double precision类型。

报错原因 #

PostgreSQL的ROUND函数有明确的重载规则：

• 当第一个参数为numeric类型时，支持传入第二个整数参数指定保留的小数位数；
• 当第一个参数为double precision类型时，仅支持无第二个参数的版本（仅能四舍五入到整数）。

你的代码中，salary_sum是SUM(double precision)的结果，仍为double precision类型，除以smallint类型的wins后，cost_per_win_num还是double precision类型。此时调用ROUND(cost_per_win_num, 2)，找不到匹配的函数签名，因此触发报错。

解决方法 #

方法1：显式转换为numeric类型后调用ROUND #

修改最后一步的SELECT语句，将cost_per_win_num转换为numeric类型：

SELECT
    ROUND(CAST(cost_per_win_num AS NUMERIC), 2) AS "cost_per_win"
FROM cost_per_win_raw
ORDER BY cost_per_win ASC
LIMIT 1;

也可以使用PostgreSQL简写的类型转换语法：

ROUND(cost_per_win_num::NUMERIC, 2)

方法2：在计算阶段提前转换类型 #

在cost_per_win_rawCTE中，先将salary_sum转为numeric再做除法，后续直接使用ROUND即可：

cost_per_win_raw AS 
(
    SELECT 
        (CAST(salary_sum AS NUMERIC) / wins) AS "cost_per_win_num"
    FROM total_salary
)
SELECT
    ROUND(cost_per_win_num, 2) AS "cost_per_win"
FROM cost_per_win_raw
ORDER BY cost_per_win ASC
LIMIT 1;

额外优化：修正业务逻辑 #

原代码最后使用GROUP BY cost_per_win属于冗余操作，题目要求获取最小赢球成本，推荐通过ORDER BY cost_per_win ASC LIMIT 1直接取最小值，或者用MIN函数：

SELECT
    MIN(ROUND(cost_per_win_num::NUMERIC, 2)) AS "min_cost_per_win"
FROM cost_per_win_raw;

内容的提问来源于stack exchange，提问作者Sharon R.