Vercel Postgres动态构建批量更新SQL无生效问题排查

我在Vercel Postgres中创建了如下简单表：

export const sets = createTable("card_table", {
  id: varchar("id").primaryKey(),
  info: jsonb("info"),
});

该表已填充数据，我需要实现批量更新操作。目前已掌握单条更新的方式：

const new_data = {
  id: "idx",
  new_field: {
    x: 'test'
  }
}

const query = 
`
  UPDATE card_table
  SET
     info = jsonb_set(
              info, '{new_field}', '${JSON.stringify(new_data.new_field)}'
            )
  WHERE info ->> 'id' = '${new_data.id}'
`

现在我尝试修改查询语句以实现批量更新：

const data = [
       {
         "id": "id1",
         "new_field": {
           "x": "test"
         }
       },
       {
         "id": "id2",
         "new_field": {
           "x": "test"
         }
       }
    ]

const updateValues = JSON.stringify(
  data.map((d) => ({ id: d.id, new_field: d.new_field }))
);

const query = 
`
  UPDATE card_table
  SET
     info = jsonb_set(
              info, '{new_field}', (elem->'new_field')::jsonb
            )
  FROM jsonb_array_elements('${updateValues}')
  WHERE info ->> 'id' = (elem->'id')::text
`

但执行后没有报错，却未更新任何行。请问我哪里出错了？或者有没有更优的实现方式？

错误原因 #

你的批量更新查询有两个关键问题：

• 未给jsonb_array_elements的结果起别名elem：在FROM子句中，你调用了jsonb_array_elements('${updateValues}')，但没有将结果集命名为elem，导致后续引用elem->'new_field'和elem->'id'时找不到对应的数据列，所以匹配不到任何行，自然没有更新。
• SQL注入风险：直接将JSON字符串拼接进SQL语句，会导致严重的SQL注入漏洞，同时如果JSON中包含特殊字符（比如单引号），还会导致语法错误。

修正后的批量更新语句 #

方式一：修正原查询（仅适合测试场景） #

先给jsonb_array_elements的结果起别名，同时简化类型转换逻辑：

const data = [
  { id: "id1", new_field: { x: "test" } },
  { id: "id2", new_field: { x: "test" } }
];

const updateValues = JSON.stringify(data);

const query = `
  UPDATE card_table
  SET info = jsonb_set(info, '{new_field}', elem->'new_field')
  FROM jsonb_array_elements('${updateValues}') AS elem
  WHERE info ->> 'id' = elem->>'id'
`;

注意：这种方式仍存在SQL注入风险，生产环境禁止使用。

方式二：参数化查询（推荐，安全高效） #

使用Vercel Postgres的参数化查询，避免字符串拼接带来的注入问题，同时SDK会自动处理参数类型转换：

import { sql } from '@vercel/postgres';

const data = [
  { id: "id1", new_field: { x: "test" } },
  { id: "id2", new_field: { x: "test" } }
];

const query = sql`
  UPDATE card_table
  SET info = jsonb_set(info, '{new_field}', elem->'new_field')
  FROM jsonb_array_elements($1::jsonb) AS elem
  WHERE info ->> 'id' = elem->>'id'
`;

await query(data);

更优实现建议 #

• 利用表主键提升性能：你的表本身有id主键字段，但当前更新是通过info->>'id'匹配，无法利用主键索引。如果info中的id和表主键id一致，建议直接用主键匹配：

const query = sql`
  UPDATE card_table ct
  SET info = jsonb_set(ct.info, '{new_field}', elem->'new_field')
  FROM jsonb_array_elements($1::jsonb) AS elem
  WHERE ct.id = elem->>'id'
`;

这种方式会触发主键索引，大幅提升批量更新的效率。

• 原子性保障：Postgres的UPDATE语句本身是原子性的，要么全部更新成功，要么全部失败，无需额外手动开启事务（除非有关联操作需要统一提交/回滚）。

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