sql - SQL - Postgres 字符串 agg 给出重复项

问题描述

我试图从系统表中收集外键映射。我在下面的查询中使用了这个。

查询1：

select
    kcu.table_schema,
    kcu.table_name as foreign_table,
    string_agg(kcu.column_name, ', ') as fk_columns,
    rel_tco.table_name as primary_table,
    kcu.constraint_name
from
    information_schema.table_constraints tco
join information_schema.key_column_usage kcu on
    tco.constraint_schema = kcu.constraint_schema
    and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco on
    tco.constraint_schema = rco.constraint_schema
    and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco on
    rco.unique_constraint_schema = rel_tco.constraint_schema
    and rco.unique_constraint_name = rel_tco.constraint_name
where
    tco.constraint_type = 'FOREIGN KEY'
group by
    kcu.table_schema,
    kcu.table_name,
    rel_tco.table_name,
    rel_tco.table_schema,
    kcu.constraint_name
order by
    kcu.table_schema,
    kcu.table_name;

但这不会提供指向 Fk 的主表列。所以我在Stackoverflow上找到了这个查询。

查询 2

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'

现在这个查询给出了单独的列。假设有一个像 (logid, item) 这样的 FK，那么我会得到两行。我想在我的第一个查询中使用 string_agg，我得到了结果，但它最终重复了。喜欢（logid，项目，logid，项目）

重复查询：

select
    kcu.table_schema,
    kcu.table_name as foreign_table,
    string_agg(kcu.column_name, ', ') as fk_columns,
    rel_tco.table_name as primary_table,
    kcu.constraint_name,
    string_agg(ccu.column_name, ', ') as pk_columns
    from 
    information_schema.table_constraints tco
join information_schema.key_column_usage kcu on
    tco.constraint_schema = kcu.constraint_schema
    and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco on
    tco.constraint_schema = rco.constraint_schema
    and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco on
    rco.unique_constraint_schema = rel_tco.constraint_schema
    and rco.unique_constraint_name = rel_tco.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tco.constraint_name 
where
    tco.constraint_type = 'FOREIGN KEY'
group by
    kcu.table_schema,
    kcu.table_name,
    rel_tco.table_name,
    rel_tco.table_schema,
    kcu.constraint_name
order by
    kcu.table_schema,
    kcu.table_name;

有人可以帮我解决这个查询吗？

示例 FK：

ALTER TABLE myschema.user ADD CONSTRAINT view_option_fk01 FOREIGN KEY (account_id, user_id) REFERENCES myschema.account(account_id, user_id);

预期的查询输出：

标签： sqlpostgresqljoinstring-agg