PostgreSQL中能否像INSERT那样用VALUES表达式批量更新列与值？

当然可以像INSERT那样动态指定列名和对应值来执行UPDATE，但必须加上WHERE子句——否则会更新表中所有行，这在生产环境中绝对要避免。

单条记录的动态UPDATE实现 #

参考你INSERT的写法，用psycopg2的AsIs或动态字符串拼接生成SET子句，同时绑定参数避免SQL注入：

# 假设你有要更新的列名列表、对应的值，以及更新条件（比如主键id）
columns = ["col1", "col2", "col3"]
values = [100, "new_val", True]
update_id = 123

# 生成SET子句：col1 = %s, col2 = %s, col3 = %s
set_clause = ", ".join([f"{col} = %s" for col in columns])
update_statement = f"UPDATE public.ctr SET {set_clause} WHERE id = %s;"

# 执行更新：把values和条件参数合并传入
cur.execute(update_statement, values + [update_id])

批量UPDATE（适配Kinesis批量处理场景） #

如果你的Kinesis流一次推送多条记录，推荐用PostgreSQL的UPDATE ... FROM语法高效批量更新，避免循环单条执行：

# 假设批量数据是包含主键和对应列值的列表
batch_data = [
    {"id": 1, "col1": 200, "col2": "batch_val1"},
    {"id": 2, "col1": 300, "col2": "batch_val2"}
]

# 提取要更新的列（排除主键id）
columns = [k for k in batch_data[0].keys() if k != "id"]
# 生成SET子句：col1 = data.col1, col2 = data.col2
set_clause = ", ".join([f"{col} = data.{col}" for col in columns])

# 生成VALUES部分的占位符，比如 (%s, %s, %s), (%s, %s, %s)
value_placeholders = ", ".join([f"({', '.join(['%s']*(len(columns)+1))})" for _ in batch_data])

# 构建批量UPDATE语句
update_statement = f"""
UPDATE public.ctr
SET {set_clause}
FROM (VALUES {value_placeholders}) AS data(id, {','.join(columns)})
WHERE public.ctr.id = data.id;
"""

# 把批量数据展开为扁平的参数列表
flat_values = []
for item in batch_data:
    flat_values.append(item["id"])
    for col in columns:
        flat_values.append(item[col])

# 执行批量更新
cur.execute(update_statement, flat_values)

关键注意事项 #

• 永远不要省略WHERE子句：无条件的UPDATE会修改表中所有行，几乎必然引发生产事故
• 坚持参数化查询：不要直接拼接用户输入生成SQL，用psycopg2的参数绑定机制防止SQL注入；列名拼接需确保来源可信
• 批量更新优先用UPDATE ... FROM：相比循环单条更新，这种方式能大幅减少数据库交互开销，更适配Kinesis的批量处理场景

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