我正在从两个表创建一个物化视图,使用 array_agg
函数将字符串从 table_b
连接到table_a
。本质上,column_c
on table_a
是一个可为空的数字数组,对应于 table_b
的id
列(只有两列,id
和 description
)。但是,实例化视图无法包含table_a
上任何行的行,该行在 column_c
上为空/空。
值为空时,是否可以使 Materialized (1) 输入空数组; 或 (2) 空值table_a
column_c
?
CREATE MATERIALIZED VIEW my_materialized_view
AS
SELECT
id,
column_a,
column_b,
array_agg(description) as column_c
FROM table_a
JOIN table_b on table_b.id = any(column_c)
GROUP BY table_a.id
ORDER BY table_a.id ASC
WITH DATA;
使用 LEFT JOIN
:
CREATE MATERIALIZED VIEW my_materialized_view
AS
SELECT
id,
column_a,
column_b,
array_agg(description) as column_c
-- or
-- coalesce(array_agg(description), '{}') as column_c
FROM table_a
LEFT JOIN table_b on table_b.id = any(column_c)
GROUP BY table_a.id
ORDER BY table_a.id ASC
WITH DATA;