使用postgres npm包执行批量更新时触发PostgresError语法错误

问题重现 #

使用postgres npm包执行批量更新时触发PostgresError: syntax error at or near "FROM"，相关代码如下：

exports.updateMatches = async ({ payload: { matches } }) => {
  const formattedMatches = objArrToArr(matches);
  const cols = Object.keys(matches[0]);
  const colsWOId = cols
    .filter((item) => item !== "id")
    .map((item) => camelToSnake(item));
    
  return await sql`
        update matches
        set ${colsWOId.map((col) => `${sql(col)} = excluded.${sql(col)}`).join(", ")}
        from (values ${sql(formattedMatches)}) as excluded(${cols.join(", ")})
        where matches.id = (excluded.id):: int
            returning *;`;
};

注：该postgres npm包不支持sql.identifier()/sql.raw()，代码参照官方批量更新示例编写：

const users = [
  [1, 'John', 34],
  [2, 'Jane', 27],
]
await sql`
  update users set name = update_data.name, age = (update_data.age)::int
  from (values ${sql(users)}) as update_data (id, name, age)
  where users.id = (update_data.id)::int
  returning users.id, users.name, users.age
`     

错误详情：

PostgresError: syntax error at or near "FROM"
at ErrorResponse (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/connection.js:788:26)
at handle (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/connection.js:474:6)
at Socket.data (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/connection.js:315:9)
at Socket.emit (node:events:519:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at Readable.push (node:internal/streams/readable:390:5)
at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
at cachedError (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/query.js:170:23)
at new Query (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/query.js:36:24)
at sql (/home/raf/apps/tourna-pro/api/node_modules/postgres/cjs/src/index.js:112:11)
at exports.updateMatches (/home/raf/apps/tourna-pro/api/src/service/tournament-format.js:139:24)
severity_local: 'ERROR',
severity: 'ERROR',
code: '42601',
position: '148',
file: 'scan.l',
line: '1242',
routine: 'scanner_yyerror'   

错误原因 #

核心问题在于set子句的拼接方式：

• ${colsWOId.map((col) => ${sql(col)} = excluded.${sql(col)}).join(", ")} 是将sql(col)返回的SQL片段对象直接转换为字符串后拼接，这会导致生成的SQL语法无效（比如可能出现类似[object Object] = excluded.[object Object]的错误内容）。
• Postgres解析到混乱的set子句内容后，无法识别后续的FROM关键字，因此抛出语法错误。

解决方案 #

利用postgres模板字符串支持数组参数的特性，直接传入set子句的片段数组，由库自动完成正确的拼接和转义：

exports.updateMatches = async ({ payload: { matches } }) => {
  const formattedMatches = objArrToArr(matches);
  const cols = Object.keys(matches[0]);
  const colsWOId = cols
    .filter((item) => item !== "id")
    .map((item) => camelToSnake(item));
    
  // 生成每个set的SQL片段数组
  const setClauses = colsWOId.map(col => sql`${sql(col)} = excluded.${sql(col)}`);
  
  return await sql`
        update matches
        set ${setClauses}
        from (values ${sql(formattedMatches)}) as excluded(${cols.map(c => sql(c))})
        where matches.id = (excluded.id)::int
        returning *;`;
};

关键修改点 #

1. Set子句处理：将原来的字符串拼接改为生成sql片段数组setClauses，直接传入模板字符串，库会自动用逗号分隔每个片段。
2. Excluded列定义：cols.map(c => sql(c))同样用SQL片段数组代替字符串拼接，避免列名转义问题。

这样生成的SQL语法完全符合Postgres要求，不会出现语法解析错误。

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