Postgres参数化查询报错column $3不存在及参数类型识别问题

错误根因 #

1. 首次报错column "$3" does not exist：PostgreSQL中双引号用于识别数据库对象（列、表、自定义类型等），用双引号包裹$1、$2参数占位符后，占位符会被识别为列名，导致参数计数错位，最终报错不存在$3列。
2. 二次报错could not determine data type of parameter $1：PostgreSQL查询解析器无法自动推断函数入参位置的占位符类型，尤其是to_tsquery这类支持多输入类型的函数，会出现类型推断失败的问题。

可行解决方案 #

方案1：使用显式CAST声明参数类型（通用方案，兼容所有PostgreSQL客户端） #

直接修改查询语句，用更明确的CAST语法声明参数类型，即可解决类型推断问题：

SELECT cards.* FROM cards WHERE
(name_tokens @@ to_tsquery(CAST($1 AS text))) AND
(cards.rarity = CAST($2 AS card_rarity))
ORDER BY cards.id;

参数依旧传入[ 'absorb & in', 'L' ]即可正常运行。

方案2：显式预备语句（性能更优，适合高频调用的场景） #

提前声明预备语句绑定参数类型，后续直接调用执行即可：

-- 声明预备语句，提前绑定参数类型
PREPARE card_search(text, card_rarity) AS
SELECT cards.* FROM cards WHERE
(name_tokens @@ to_tsquery($1)) AND
(cards.rarity = $2)
ORDER BY cards.id;

-- 执行查询
EXECUTE card_search('absorb & in', 'L');

方案3：Node.js pg库专属适配 #

如果你使用的是Node.js的pg驱动，可以直接使用如下查询配置，新版驱动已支持枚举类型自动适配：

const query = {
  text: `SELECT cards.* FROM cards WHERE
  (name_tokens @@ to_tsquery($1::text)) AND
  (cards.rarity = $2::card_rarity)
  ORDER BY cards.id;`,
  values: ['absorb & in', 'L']
}
await client.query(query);

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