我目前有一个类似下表的数据库模式:
CREATE TABLE Measures(
expId SERIAL,
iteration INT NOT NULL,
value float4 NOT NULL,
PRIMARY KEY(expId, iteration)
);
因此，一个包含各种测量值的表，重复n次. 但是，因为我们拥有比最初预期更多的数据，所以我想使用新的表布局，而不是使用数组列，这总体上提供了更好的性能(已经进行了测试和基准测试):
CREATE TABLE TmpMeasures(
expId SERIAL PRIMARY KEY,
values float4[] NOT NULL
);
我现在的问题是如何将旧数据转换为新格式.
数据可能如下所示.并不是说迭代不会
始终生成所有数据，因此最终数组中可能有NULL个值:
INSERT INTO Measures (expId, iteration, value)
VALUES
(1, 1, 1.1), (1, 2, 1.2), (1, 3, 1.3),
(2, 1, 2.1), (2, 4, 2.4),
(3, 1, 3.1), (3, 4, 3.4);
转换可以通过两个步骤完成，大致如下，首先为实验创建数组，然后填充迭代值:
INSERT INTO TmpMeasures(expId, values)
SELECT expId, '{}'::float4[]
FROM Measures
ON CONFLICT DO NOTHING;
UPDATE TmpMeasures tm
SET values[iteration] = m.value
FROM Measures m WHERE tm.expId = m.expId;
然而，我现在的问题是，这UPDATE人实际上似乎只进行了第一次迭代，即iteration = 1次.
我不太明白为什么会这样.
我怀疑，对于values[iteration]，其他方法将try 按expid分组，按迭代排序，并将其聚合到一个数组中.
不幸的是，数据并不完美，但迭代应该是一致的.
所以，下面的方法看起来很管用，但它非常慢，我不太明白为什么首先需要它.
DO
$do$
BEGIN
FOR i IN 1..(SELECT max(iteration) FROM Measures m) LOOP
UPDATE TmpMeasures tm
SET values[i] = m.value
FROM Measures m
WHERE
tm.expId = m.expId AND
m.iteration=i;
END LOOP;
END
$do$;
为什么"普通"的UPDATE语句还不够呢？ 答:多亏了@Zegarek下面的指针，这确实是UPDATE的预期行为，它只接受FROM子句中的一行.
因此，在这一点上，对我来说更相关的问题是，是否有更好的数据转换方法，例如为expId=3生成[3.1, NULL, NULL, 3.4].