如何更新 PostgreSQL 中的大量行? [英] How to update large amount of rows in PostgreSQL?

问题描述

我需要更新表中的数千行.例如，我有 1000 行 ids - 1, 2.. 1000:

I need to update thousands of rows in a table. For example, I have 1000 rows with ids - 1, 2.. 1000:

mytable:
| id   | value1 | value2 |
|  1   |  Null  |  Null  |
|  2   |  Null  |  Null  |
...
| 1000 |  Null  |  Null  |

现在我需要更改前 10 行.我可以这样做:

Now I need to change first 10 rows. I can do it like this:

UPDATE mytable SET value1=42, value2=111 WHERE id=1
...
UPDATE mytable SET value1=42, value2=111 WHERE id=10

这需要很多请求，而且速度不是很快，所以我决定做这个优化:

This requires to many requests and not very fast, so I decide to do this optimization:

UPDATE mytable SET value1=42  WHERE id in (1, 2, 3.. 10)
UPDATE mytable SET value2=111 WHERE id in (1, 2, 3.. 10)

注意:在这种情况下，我实际上可以编写 SET value1=42, value2=111 但在现实世界的应用程序中，这组 id 并不相同，对于一个行我需要设置 value1，对于其他 - value2，对于我需要设置的某些行子集.因此，我需要两个查询.

Note: In this case I can actually write SET value1=42, value2=111 but in real world applications this sets of ids is not the same, for one rows I need to set value1, for other - value2, for some subset of rows I need to set both. Because of that I need two queries.

问题是我有大量的 id.此查询大约为 1Mb！

The problem is that I have very large amount of id's. This queries is something about 1Mb!

问题 1:这是优化此更新的正确方法吗?

Q1: Is this a right way to optimize this updates?

Q2:发送如此大的查询是否正确?我可以将此查询分成几个更小的部分来获得更快的更新吗?

Q2: Is it right to send queries that is so large? Can I get faster update by dividing this query into several smaller parts?

我不能使用 where 语句，我的程序中有很多行 ID.

I can't use where statement, I've just have lots of row id's in my program.

推荐答案

创建一个 临时表 并用您的目标 ID 和新值填充它.然后使用 UPDATE with FROM 子句加入该目标并执行它在一个命令中.

Create a TEMPORARY TABLE and populate it with your target ids and new values. Then use UPDATE with FROM clause to join to that target and do it in a single command.

一般来说，当你有大量的 id/values 时，如果你先把它们移到数据库中，生活会变得更容易.

In general, whenever you have large numbers of id/values like this life gets easier if you move them into the database first.

这篇关于如何更新 PostgreSQL 中的大量行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！