How to find tables where more than one column references the same table in postgresql

Question

I have a postgresql database consisting more than 600 tables.

This is my situation roughly:

Table1{
       column1 data_type,
       column2 data_type,

       constraint const_name1 foreign key(column1)
       references Table2(table_id)

       constraint const_name2 foreign key(column2)
       references Table2(table_id)
       }


Table2{
       table_id data_type
       }

Now I need to find the Table1's, Where more than one column has referenced the same table Table2 on the same column. And with that hugh number of tables, it is not possible to find them manually.

Is there a sql available for this? Any ideas? Thanks in advance.

What about [this](http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys)? This is how you can get all foreign keys. Then it should be easy to do the rest. — Tomas Greif, Sep 12 '13 at 07:05

SpaceDog · Answer 1 · 2013-09-12T07:23:02.250

You can use the pg_constraint table in the pg_catalog to find constraints. (Documentation)

To get a list of foreign keys and the tables they reference you can do:

SELECT c.conname  AS constraint_name, 
       sc.relname AS source_table, 
       dc.relname AS dest_table
  FROM pg_catalog.pg_constraint c 
       LEFT JOIN pg_catalog.pg_class sc ON c.conrelid = sc.oid
       LEFT JOIN pg_catalog.pg_class dc ON c.conrelid = dc.oid
 WHERE contype = 'f'    
;

From there you can filter as you want to identify the cases you're interested in.

score 1 · Accepted Answer · answered Sep 12 '13 at 07:29

something like this:

select
    sc.relname, 
    dc.relname as ref_relname,
    c.confkey
from pg_catalog.pg_constraint as c 
    inner join pg_catalog.pg_class as sc on sc.oid = c.conrelid
    inner join pg_catalog.pg_class as dc on dc.oid = c.confrelid
where c.contype = 'f'
group by sc.relname, dc.relname, c.confkey
having count(*) > 1

sql fiddle demo

How to find tables where more than one column references the same table in postgresql

2 Answers2