node-postgres查询异常：执行COUNT(*)报错‘column count不存在’

错误原因 #

你通过pool.query获取的washingCount、washedCount、dirtyCount是node-postgres返回的查询结果对象，并非直接的统计数值。这个对象的结构包含rows数组，实际的统计值存储在rows[0].count中（PostgreSQL会给COUNT(*)默认分配列名count）。如果直接访问washingCount.count，相当于在结果对象上找不存在的属性，因此触发报错。

修复方案1：正确提取结果值 #

调整代码，从查询结果对象的rows数组中取出统计值：

const [washingRes, washedRes, dirtyRes] = await Promise.all([
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washing'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washed'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'dirty'")
    ])

const washingCount = washingRes.rows[0].count;
const washedCount = washedRes.rows[0].count;
const dirtyCount = dirtyRes.rows[0].count;

修复方案2：给统计列指定明确别名 #

为避免默认列名带来的歧义，可以给COUNT(*)指定自定义别名，再通过别名提取值：

const [washingRes, washedRes, dirtyRes] = await Promise.all([
        pool.query("SELECT COUNT(*) AS washing_count FROM clothes WHERE status = 'washing'"),
        pool.query("SELECT COUNT(*) AS washed_count FROM clothes WHERE status = 'washed'"),
        pool.query("SELECT COUNT(*) AS dirty_count FROM clothes WHERE status = 'dirty'")
    ])

const washingCount = washingRes.rows[0].washing_count;
const washedCount = washedRes.rows[0].washed_count;
const dirtyCount = dirtyRes.rows[0].dirty_count;

优化方案：合并为单次查询 #

多次请求数据库会增加开销，建议将三个统计合并为一次查询，效率更高：

const res = await pool.query(`
    SELECT 
        COUNT(*) FILTER (WHERE status = 'washing') AS washing_count,
        COUNT(*) FILTER (WHERE status = 'washed') AS washed_count,
        COUNT(*) FILTER (WHERE status = 'dirty') AS dirty_count
    FROM clothes
`);

const { washing_count, washed_count, dirty_count } = res.rows[0];

如果使用的PostgreSQL版本低于9.4，可兼容使用CASE WHEN写法：

const res = await pool.query(`
    SELECT 
        SUM(CASE WHEN status = 'washing' THEN 1 ELSE 0 END) AS washing_count,
        SUM(CASE WHEN status = 'washed' THEN 1 ELSE 0 END) AS washed_count,
        SUM(CASE WHEN status = 'dirty' THEN 1 ELSE 0 END) AS dirty_count
    FROM clothes
`);

const { washing_count, washed_count, dirty_count } = res.rows[0];

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