PostgreSQL中如何用字符串数组匹配id？TypeScript生成查询报错

问题背景 #

执行以下查询获取符合条件的建筑数据时，WHERE "id" = ANY(...)部分报错：

SELECT *
FROM "ContextBuilding"
WHERE "id" = ANY('UM3cxUnOtOmMsD79zcRWb','ulDME73WOGaChCbLH63iY','UoX9NoJIzANpXBvrjXQVx'::text[]) AND
coords &&
ST_MakeEnvelope(13.445188084976532, 52.52807719874824, 13.44677407320652, 52.529087036036735, 4326)

同时，TypeScript中动态生成的查询代码也执行失败：

const query = `
  SELECT *
  FROM ContextBuilding
  WHERE "id" = ANY(${tenantBuildingIDs}::text[]) AND
  coords &&
  ST_MakeEnvelope(${bounds[0]}, ${bounds[1]}, ${bounds[2]}, ${bounds[3]}, 4326)
  `

SELECT volume, id, uid, coords FROM "ContextBuilding"的查询输出示例：

错误原因 #

PostgreSQL的ANY()函数要求接收单个数组参数，原SQL错误地传入多个字符串参数，不符合语法规范；TypeScript中直接拼接数组会导致生成的SQL语法混乱，还存在SQL注入风险。

正确的SQL写法 #

有两种标准方式实现ID与字符串数组的匹配：

方式1：使用ARRAY构造函数 #

SELECT *
FROM "ContextBuilding"
WHERE "id" = ANY(ARRAY['UM3cxUnOtOmMsD79zcRWb','ulDME73WOGaChCbLH63iY','UoX9NoJIzANpXBvrjXQVx']) AND
coords && ST_MakeEnvelope(13.445188084976532, 52.52807719874824, 13.44677407320652, 52.529087036036735, 4326)

方式2：使用数组字面量转换类型 #

SELECT *
FROM "ContextBuilding"
WHERE "id" = ANY('{UM3cxUnOtOmMsD79zcRWb,ulDME73WOGaChCbLH63iY,UoX9NoJIzANpXBvrjXQVx}'::text[]) AND
coords && ST_MakeEnvelope(13.445188084976532, 52.52807719874824, 13.44677407320652, 52.529087036036735, 4326)

TypeScript中安全生成查询的方案 #

强烈推荐使用参数化查询，既避免语法错误，又能防止SQL注入：

import { Client } from 'pg';

// 假设tenantBuildingIDs为字符串数组，bounds为坐标数组
const tenantBuildingIDs = ['UM3cxUnOtOmMsD79zcRWb','ulDME73WOGaChCbLH63iY','UoX9NoJIzANpXBvrjXQVx'];
const bounds = [13.445188084976532, 52.52807719874824, 13.44677407320652, 52.529087036036735];

// 初始化数据库客户端
const client = new Client({ /* 数据库连接配置 */ });
await client.connect();

// 参数化查询，$1-$5为占位符
const query = `
  SELECT *
  FROM "ContextBuilding"
  WHERE "id" = ANY($1::text[]) AND
  coords && ST_MakeEnvelope($2, $3, $4, $5, 4326)
`;

// 执行查询，传入参数数组
const result = await client.query(query, [tenantBuildingIDs, ...bounds]);
console.log('查询结果:', result.rows);

await client.end();

如果必须拼接字符串（仅在无SQL注入风险场景下使用），需将数组转换为PostgreSQL兼容格式并处理单引号转义：

const formattedIDs = `{${tenantBuildingIDs.map(id => `'${id.replace(/'/g, "''")}'`).join(',')}}`;
const query = `
  SELECT *
  FROM "ContextBuilding"
  WHERE "id" = ANY(${formattedIDs}::text[]) AND
  coords && ST_MakeEnvelope(${bounds.join(', ')}, 4326)
`;

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