具有可变 WHERE 子句的批量更新表

2022-01-17 00:00:00 python postgresql sql-update sql

问题描述

我有一堆值对 [(foo1, bar1), (foo2, bar2), ...] 我想做一堆更新设置'foo' 列到 'foo1' ，其中 'bar' 列是 'bar1'".

I have a bunch of pairs of values [(foo1, bar1), (foo2, bar2), ...] and I want to do a bunch of updates of "set the 'foo' column to 'foo1' where the 'bar' column is 'bar1'".

我在 Python 中使用 psycopg2 执行此操作.我可以用查询 UPDATE table SET foo = %s WHERE bar = %s 来执行 executemany，但这是很多小的更新，而且会花很长时间.

I am doing this in Python with psycopg2. I could do executemany with the query UPDATE table SET foo = %s WHERE bar = %s, but that's a lot of little updates and would take mad long.

我怎样才能轻松快速地做到这一点?也许有临时表?

How can I do this easily and fast? Perhaps something with a temp table?

Postgres 9.3 版.

Postgres version 9.3.

解决方案

UPDATE tbl t 
SET    foo = v.foo
FROM  (
   VALUES ('foo1'::text, 'bar1'::text), ('foo2', 'bar2'), ...
   ) v(foo, bar)
WHERE t.bar = v.bar;

仅在值表达式的第一行中需要显式类型转换.示例中的 text - 可以是任何东西.后续行中的字符串文字被强制转换为相同的类型.

Explicit type casts are only required in the first row of the values expression. text in the example - could be anything. String literal in subsequent rows are coerced to the same types.

根据您拥有键值对的形式，其他方法可能更方便.像:创建一个临时表，COPY 到它，然后像任何其他表一样使用 UPDATE 中的临时表.详情:

Depending on the form you have the key-value pairs, other methods may be more convenient. Like: create a temporary table, COPY to it, then use the temp table in the UPDATE like any other table. Details:

  • 如何在 Postgres 中使用 CSV 文件中的值更新选定的行?

或者你可以并行传递两个简单的数组和unnest(Postgres 9.3的语法):

Or you can pass two simple arrays and unnest in parallel (syntax for Postgres 9.3):

UPDATE tbl t 
SET    foo = v.foo
FROM  (
   SELECT unnest('{foo1,foo2,...}'::text[]) AS foo
        , unnest('{bar1,bar2,...}'::text[]) AS bar
   ) v(foo, bar)
WHERE t.bar = v.bar;

Postgres 9.4 有更好的办法:

Postgres 9.4 has a better way:

  • PostgreSQL 中有没有类似 zip() 函数的东西，它结合了两个数组?

相关文章