sql - 将结果设置为新列中超过一百万行的数组-postgreSQL

Question

我正在尝试将所有照片 url 放入一个数组中，并将该数组设置为另一个表中的新列。这些表具有一对多的关系。我的 list_reviews 表中有大约 100 万行和大约 300 万张照片。

有没有办法分批做到这一点？当我试图一次性完成所有操作时，我得到的只是空数组。

https://www.postgresql.org/message-id/[e-mail]和Postgresql 选择行（结果）作为数组

如果我一次只做一个，这些工作。我一直在考虑尝试使用此处找到的 STREAMING，https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#into-database但不确定我是否完全理解发生了什么这里。

CREATE TABLE list_reviews (
  id SERIAL PRIMARY KEY,
  product_id INT,
  photos TEXT[]);

CREATE TABLE review_photos (
  id SERIAL,
  review_id INT REFERENCES list_reviews(id) ON DELETE CASCADE,
  url TEXT);

UPDATE list_reviews SET photos = array(
   SELECT url
   FROM review_photos
   WHERE review_photos.id = list_reviews.id 
   AND list_reviews.id = 5);

list_reviews 看起来像：

+----+------------+--------+--+
| id | product_id | photos |  |
+----+------------+--------+--+
|  5 |          1 | []     |  |
+----+------------+--------+--+

review_photos 看起来像：

+----+-----------+------------+--+
| id | review_id |   photos   |  |
+----+-----------+------------+--+
|  1 |         5 | something1 |  |
|  2 |         5 | something2 |  |
|  3 |         5 | something3 |  |
+----+-----------+------------+--+

并希望看到 list_reviews：

+----+------------+--------------------------------------+--+
| id | product_id |                photos                |  |
+----+------------+--------------------------------------+--+
|  5 |          1 | [something1, something2, something3] |  |
+----+------------+--------------------------------------+--+

标签： sqlnode.jspostgresqlpg-promise

Answer 1

你的代码基本上看起来没问题。我更喜欢使用array_agg()（因为操作更明确），但 Postgres 允许为array.

一个问题是过滤。我想你打算：

UPDATE list_reviews lr
    SET photos = array(SELECT rp.url
                       FROM review_photos rp
                       WHERE rp.id = lr.id 
                      )
    WHERE lr.id = 5;

list_reviews您的查询将使用 id = 5 的照片中的 url更新所有行。

您可以通过在处理过程中设置范围来分批执行此lr.id操作。例如：

UPDATE list_reviews lr
    SET photos = array(SELECT rp.url
                       FROM review_photos rp
                       WHERE rp.id = lr.id 
                      )
    WHERE lr.id > 0 and lr.id < 10000;

但是，替换现有表可能更简单：

create temporary table temp_list_reviews as
    select id, product_id,  -- all columns but photos
           array(SELECT rp.url
                           FROM review_photos rp
                           WHERE rp.id = lr.id 
                          ) as photos
    from list_reviews;

truncate table list_reviews;

insert into list_reviews (id, product_id, photos)
    select id, product_id, photo
    from temp_list_reviews;

由于日志记录的考虑，批量插入通常比更新快。