PostgreSQL中数组拼接为字符串时如何去除重复值？

尝试将每个feature_id对应的语言列表拼接成字符串，但结果存在大量重复值。请问该问题产生的原因是什么，以及如何实现每种语言仅保留一个？

原SQL代码：

--indivdual features diff with list of all languages applied
SELECT 
  t1.feature_id,
  array_to_string(array_agg(t2.language), ', ') AS applied_languages_in_old_version,
  array_to_string(array_agg(t4.language), ', ') AS applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
INNER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id
INNER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t2.feature_id = t3.feature_id
INNER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t3.feature_id = t4.feature_id
WHERE t2.name_type = 'PRIMARY_FOR_LANGUAGE' 
AND t4.name_type = 'PRIMARY_FOR_LANGUAGE'
GROUP BY 
  t1.feature_id
ORDER BY t1.feature_id;

多表连接产生了笛卡尔积：同一个feature_id在t2表中有多条不同语言的记录，在t4表中也有多条不同语言的记录，当直接连接四张表时，t2里的每一条语言记录都会和t4里的每一条语言记录配对，导致聚合前的结果集中同一语言重复出现，array_agg自然会把这些重复值全部包含进去。

方法1：在array_agg中使用DISTINCT #

这是最直接的去重方式，在聚合函数内指定只保留唯一值：

SELECT 
  t1.feature_id,
  array_to_string(array_agg(DISTINCT t2.language), ', ') AS applied_languages_in_old_version,
  array_to_string(array_agg(DISTINCT t4.language), ', ') AS applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
INNER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id
INNER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t2.feature_id = t3.feature_id
INNER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t3.feature_id = t4.feature_id
WHERE t2.name_type = 'PRIMARY_FOR_LANGUAGE' 
AND t4.name_type = 'PRIMARY_FOR_LANGUAGE'
GROUP BY 
  t1.feature_id
ORDER BY t1.feature_id;

方法2：先分别聚合两个版本的语言列表，再关联 #

通过子查询先对新旧版本的语言列表单独去重聚合，再与主表关联，能避免笛卡尔积带来的冗余数据，性能更优：

WITH old_version_langs AS (
  SELECT 
    feature_id,
    array_to_string(array_agg(language), ', ') AS applied_languages_in_old_version
  FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name
  WHERE name_type = 'PRIMARY_FOR_LANGUAGE'
  GROUP BY feature_id
),
new_version_langs AS (
  SELECT 
    feature_id,
    array_to_string(array_agg(language), ', ') AS applied_languages_in_new_verison
  FROM kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name
  WHERE name_type = 'PRIMARY_FOR_LANGUAGE'
  GROUP BY feature_id
)
SELECT 
  t1.feature_id,
  o.applied_languages_in_old_version,
  n.applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
JOIN old_version_langs o ON t1.feature_id = o.feature_id
JOIN new_version_langs n ON t1.feature_id = n.feature_id
ORDER BY t1.feature_id;

内容的提问来源于stack exchange，提问作者danaburtono