PostgreSQL数组的数据复合更新元素使用where条件

Question

我有一个复合类型：

CREATE TYPE mydata_t AS
(
user_id integer,
value character(4)
);

另外，我有一个表，使用这个复合类型作为mydata_t的数组。

CREATE TABLE tbl
(
id serial NOT NULL,
data_list mydata_t[],
PRIMARY KEY (id)
);

这里我想更新data_list中的mydata_t，其中mydata_t.user_id是100000但我不知道哪个数组元素的user_id等于10000，所以我必须先搜索，找到其user_id等于100000的元素。。。这是我的问题。。。我不知道如何查询。。。。事实上，我想更新数组元素的value，其中它的user_id等于100000(同样，其中tbl的id是例如1(。。。我的问题是什么？

类似的东西(我知道它错了！！(

UPDATE "tbl" SET "data_list"[i]."value"='YYYY'
WHERE "id"=1 AND EXISTS (SELECT ROW_NUMBER() OVER() AS i
FROM unnest("data_list") "d" WHERE "d"."user_id"=10000 LIMIT 1)

例如，这是我的tbl数据：

Row1 => id = 1, data = ARRAY[ROW(5,'YYYY'),ROW(6,'YYYY')]
Row2 => id = 2, data = ARRAY[ROW(10,'YYYY'),ROW(11,'YYYY')]

现在我想更新tbl，其中id是2，并将其中一个tbl.data元素的值设置为'XXXX'，其中元素的user_id等于11

事实上，Row2的最终结果是：

Row2 => id = 2, data = ARRAY[ROW(10,'YYYY'),ROW(11,'XXXX')]

Answer 1

如果您知道value的值，您可以使用array_replace()函数进行更改：

UPDATE tbl
SET data_list = array_replace(data_list, (11, 'YYYY')::mydata_t, (11, 'XXXX')::mydata_t)
WHERE id = 2

如果您不知道value值，则情况会变得更加复杂：

UPDATE tbl SET data_list = data_arr
FROM (
-- UPDATE doesn't allow aggregate functions so aggregate here
SELECT array_agg(new_data) AS data_arr
FROM (
-- For the id value, get the data_list values that are NOT modified
SELECT (user_id, value)::mydata_t AS new_data
FROM tbl, unnest(data_list)
WHERE id = 2 AND user_id != 11
UNION
-- Add the values to update
VALUES ((11, 'XXXX')::mydata_t) 
) x 
) y
WHERE id = 2

不过，你应该记住，在后台有大量工作无法优化。必须从头到尾检查mydata_t值的数组，并且不能对此使用索引。此外，更新实际上会在磁盘上的底层文件中插入一个新行，如果您的阵列有多个条目，这将涉及大量工作。当您的数组大于PostgreSQL服务器的页面大小(通常为8kB(时，问题会更加严重。一切都在幕后，所以它会起作用，但会受到表演惩罚。尽管array_replace听起来像是在原地进行了更改(而且它们确实在内存中(，但UPDATE命令将向磁盘写入一个全新的元组。因此，如果您有4000个数组元素，这意味着必须读取至少40kB的数据(对于典型系统上的mydata_t类型，8个字节x TOAST文件中的4000=32kB，加上表的主页面，8kB(，然后在更新后写入磁盘。一个真正的表演杀手。

正如@klin所指出的，这种设计可能比它的价值更麻烦。如果您将data_list作为表(正如我所做的那样(，那么更新查询将变为：

UPDATE data_list SET value = 'XXXX'
WHERE id = 2 AND user_id = 11

这将具有更好的性能，特别是如果添加了适当的索引。如果业务逻辑需要，您仍然可以创建一个视图，以使用自定义类型以聚合形式发布数据。