在处理空列值时，在串联数组列时保持一致的PostgreSQL数组列索引

Question

给定以下启动数据：

CREATE TABLE t1 AS
  SELECT generate_series(1, 20) AS id,
    (SELECT array_agg(generate_series) FROM generate_series(1, 6)) as array_1;
CREATE TABLE t2 AS
  SELECT generate_series(5, 10) AS id,
    (SELECT array_agg(generate_series) FROM generate_series(7, 10)) as array_2;
CREATE TABLE t3 AS
  SELECT generate_series(8, 15) AS id,
    (SELECT array_agg(generate_series) FROM generate_series(11, 15)) as array_3;

我想在几个表之间进行外部连接，每个表中都有一个固定长度列，在给定表中均匀，但在表之间可能有所不同（如上所述），与阵列列相连在每个表中，进入一个大阵列列。我想知道是否有一种有效的或直接的方法可以在新的组合列中保持一致的索引，从而用NULL值的数组代替NULL列值（由外部连接引起），以便最终数组列的长度均匀。与上面的示例不同，在我的实际用例中，我不知道每个表的数组列的长度 a先验，只是在该表中它的长度均匀。换句话说，而不是此查询：

SELECT id, (array_1 || array_2 || array_3 ) AS combined_array FROM
t1 LEFT OUTER JOIN t2 USING(id) LEFT OUTER JOIN t3 USING (id);

产生：

id |            combined_array
----+---------------------------------------
 1 | {1,2,3,4,5,6}
 2 | {1,2,3,4,5,6}
 3 | {1,2,3,4,5,6}
 4 | {1,2,3,4,5,6}
 5 | {1,2,3,4,5,6,7,8,9,10}
 6 | {1,2,3,4,5,6,7,8,9,10}
 7 | {1,2,3,4,5,6,7,8,9,10}
 8 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
 9 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
10 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
11 | {1,2,3,4,5,6,11,12,13,14,15}
12 | {1,2,3,4,5,6,11,12,13,14,15}
13 | {1,2,3,4,5,6,11,12,13,14,15}
14 | {1,2,3,4,5,6,11,12,13,14,15}
15 | {1,2,3,4,5,6,11,12,13,14,15}
16 | {1,2,3,4,5,6}
17 | {1,2,3,4,5,6}
18 | {1,2,3,4,5,6}
19 | {1,2,3,4,5,6}
20 | {1,2,3,4,5,6}
(20 rows)

我希望结果看起来像：

id |            combined_array
----+---------------------------------------
 1 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
 2 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
 3 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
 4 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
 5 | {1,2,3,4,5,6,7,8,9,10,NULL,NULL,NULL,NULL,NULL}
 6 | {1,2,3,4,5,6,7,8,9,10,NULL,NULL,NULL,NULL,NULL}
 7 | {1,2,3,4,5,6,7,8,9,10,NULL,NULL,NULL,NULL,NULL}
 8 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
 9 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
10 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
11 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,11,12,13,14,15}
12 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,11,12,13,14,15}
13 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,11,12,13,14,15}
14 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,11,12,13,14,15}
15 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,11,12,13,14,15}
16 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
17 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
18 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
19 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
20 | {1,2,3,4,5,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}
(20 rows)

，每行包含一个长度15的数组。

Answer 1

回答我自己的问题，这是我想出的查询似乎可以完成工作。对我来说，它似乎并不是特别优雅或效率，因此肯定仍然可以对其他答案开放。

SELECT id, (
  coalesce(array_1, array_fill(NULL::INT,
    ARRAY[(SELECT max(array_length(array_1, 1)) FROM t1)])) ||
  coalesce(array_2, array_fill(NULL::INT,
    ARRAY[(SELECT max(array_length(array_2, 1)) FROM t2)])) ||
  coalesce(array_3, array_fill(NULL::INT,
    ARRAY[(SELECT max(array_length(array_3, 1)) FROM t3)]))
) AS combined_array FROM
t1 LEFT OUTER JOIN t2 USING(id) LEFT OUTER JOIN t3 USING (id);