PostgreSQL to_json函数在psql正常，node-pg执行时报类型不存在错误

问题场景 #

现有PostgreSQL数据库包含companies表和invoices表，需求是查询发票数据时，将关联的公司信息以JSON对象形式放到伪列company中。

在psql命令行执行以下查询语句可正常得到预期结果：

SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) "company" FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = 1;

返回结果：

id | amt | paid |  add_date  | paid_date |    company
----+-----+------+------------+-----------+--------------
1 | 100 | f    | 2024-01-04 |           | {"code":"apple","name":"Apple Computer","description":"Maker of OSX."}

但在Node.js应用中使用node-pg执行带参数的相同查询时，却报错提示“type "to_json" does not exist”：

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) 'company' FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1;", [id]);

错误详情：

{
    "error": {
        "length": 94,
        "name": "error",
        "severity": "ERROR",
        "code": "42704",
        "position": "54",
        "file": "parse_type.c",
        "line": "270",
        "routine": "typenameType"
    },
    "message": "type \"to_json\" does not exist"
}

问题原因 #

问题出在伪列的命名方式上：

• 在psql中使用的是双引号"company"，这是PostgreSQL中指定列别名的正确写法，数据库会将to_json(c)的结果命名为company列。
• 而node-pg的查询语句中用了单引号'company'，PostgreSQL会将这个语法解析为类型转换操作——即试图把to_json(c)的结果转换成名为to_json的数据类型，但PostgreSQL根本不存在这个类型，因此触发报错。

PostgreSQL中，表达式 '类型名'是CAST(表达式 AS 类型名)的简写语法，所以你的查询被数据库错误解析成：

SELECT ... CAST(to_json(c) AS to_json) ...

解决方案 #

只需要把伪列的单引号改成正确的别名写法即可，有三种可选方式：

1. 使用双引号（需转义） #

在JavaScript字符串中，双引号需要用反斜杠转义：

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) \"company\" FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1;", [id]);

2. 使用模板字符串（更简洁） #

用ES6模板字符串可以避免转义双引号：

const results = await db.query(`SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) "company" FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1;`, [id]);

3. 使用AS关键字（推荐） #

如果列名没有特殊字符，直接用AS指定别名，不需要引号：

const results = await db.query("SELECT i.id, i.amt, i.paid, i.add_date, i.paid_date, to_json(c) AS company FROM invoices i INNER JOIN companies c ON i.comp_code=c.code WHERE i.id = $1;", [id]);

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