Advertisement

SQL – Postgres string agg is giving duplicates

join postgresql sql string-agg

Im trying to collect the foreign key mapping from system tables. And I used this below query.

query 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;

But this won’t provide the Primary tables columns that are pointing as Fk. So I found this query on Stackoverflow.

query 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'

Now this query gives individual columns. Lets say there is a FK like (logid, item) then I’ll get two rows. I thought to use string_agg with my first query, I got the results but it endup duplicates. Like(logid, item,logid, item)

query with duplicates:

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;

Can someone help me to fix this query?

Example FK:

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

Expected Query output:

Advertisement

Answer

Use SELECT DISTINCT... to remove duplicates

Advertisement