How to get the table a FK refers to in Postgres sql

Question

For a given table, I am trying to get all the columns, to include their name, type, whether the column is a primary key, or a foreign key, and if it is a FK, what table it points to. I wrote the query below, but it seems to be give me the what the columns are referenced by, instead of the other way around:

select c.column_name, c.udt_name, constraint_type, kcu.table_name as references from information_schema.columns c
 left outer join information_schema.constraint_column_usage u on c.column_name=u.column_name
 left outer join information_schema.table_constraints t on u.constraint_name=t.constraint_name
 left outer join information_schema.key_column_usage AS kcu on t.constraint_name = kcu.constraint_name
where c.table_name=@name

I am not too worried about edge cases, I am just trying to inverse the references column. Thanks for your time.

Paul A Jungwirth · Accepted Answer · 2016-04-19 15:35:25Z

Here is a query to get all the foreign keys from a given table along with what they point to:

SELECT  c.conname,
        t1.relname AS from_table,
        a1.attname AS from_column,
        t2.relname AS to_table,
        a2.attname AS to_column
FROM    pg_catalog.pg_constraint c,
        pg_catalog.pg_class t1,
        pg_catalog.pg_class t2,
        pg_catalog.pg_attribute a1,
        pg_catalog.pg_attribute a2,
        pg_catalog.pg_namespace n1,
        pg_catalog.pg_namespace n2
WHERE   c.conrelid = t1.oid
AND     c.confrelid = t2.oid
AND     c.contype = 'f'
AND     a1.attrelid = t1.oid
AND     a1.attnum = ANY(c.conkey)
AND     a2.attrelid = t2.oid
AND     a2.attnum = ANY(c.confkey)
AND     t1.relkind = 'r'
AND     t2.relkind = 'r'
AND     n1.oid = t1.relnamespace
AND     n2.oid = t2.relnamespace
AND     n1.nspname NOT IN ('pg_catalog', 'pg_toast')
AND     n2.nspname NOT IN ('pg_catalog', 'pg_toast')
AND     pg_catalog.pg_table_is_visible(t1.oid)
AND     pg_catalog.pg_table_is_visible(t2.oid)
AND     t1.relname = @name;

I see you are using information_schema instead of pg_catalog, so the easiest way might be to turn my query into a CTE or subquery, then join to it based on your table and column name.

EDIT: It sounds like you just need some help joining my query to what you already have. Actually your query has a lot of errors in how it does the joins, so I've rewritten it to include the correct foreign key results:

SELECT  c.column_name,
        c.udt_name,
        t.constraint_type,
        t.constraint_name,
        x.to_table
FROM    information_schema.columns c
LEFT OUTER JOIN (
        SELECT  t.constraint_type,
                t.constraint_catalog,
                t.constraint_schema,
                t.constraint_name,
                t.table_catalog,
                t.table_schema,
                t.table_name,
                u.column_name
        FROM    information_schema.constraint_column_usage u
        LEFT OUTER JOIN information_schema.table_constraints t
        ON      t.table_catalog = u.table_catalog
        AND     t.table_schema = u.table_schema
        AND     t.table_name = u.table_name
        AND     t.constraint_catalog = u.constraint_catalog
        AND     t.constraint_schema = u.constraint_schema
        AND     t.constraint_name = u.constraint_name
        WHERE   t.constraint_type IS DISTINCT FROM 'FOREIGN KEY'
) t
ON      c.table_catalog = t.table_catalog
AND     c.table_schema = t.table_schema
AND     c.table_name = t.table_name
AND     c.column_name = t.column_name
LEFT OUTER JOIN (
  SELECT  c.conname,
          t1.relname AS from_table,
          a1.attname AS from_column,
          t2.relname AS to_table,
          a2.attname AS to_column
  FROM    pg_catalog.pg_constraint c,
          pg_catalog.pg_class t1,
          pg_catalog.pg_class t2,
          pg_catalog.pg_attribute a1,
          pg_catalog.pg_attribute a2,
          pg_catalog.pg_namespace n1,
          pg_catalog.pg_namespace n2
  WHERE   c.conrelid = t1.oid
  AND     c.confrelid = t2.oid
  AND     c.contype = 'f'
  AND     a1.attrelid = t1.oid
  AND     a1.attnum = ANY(c.conkey)
  AND     a2.attrelid = t2.oid
  AND     a2.attnum = ANY(c.confkey)
  AND     t1.relkind = 'r'
  AND     t2.relkind = 'r'
  AND     n1.oid = t1.relnamespace
  AND     n2.oid = t2.relnamespace
  AND     n1.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND     n2.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND     pg_catalog.pg_table_is_visible(t1.oid)
  AND     pg_catalog.pg_table_is_visible(t2.oid)
) x
ON      x.from_table = c.table_name
AND     x.from_column = c.column_name
WHERE   c.table_name = @cards
;

This version also prevents lots of duplicate rows if you have NOT NULL or CHECK constraints.

This a very informative answer, but does not really do what I need it to

buqing · Accepted Answer · 2016-04-19 14:27:54Z

1

I am not sure if you know how to programming with other languages(such as java, C#) besides writing Sql queries

If you know any of them, you can use the function which are int the JDBC/ODBC drivers to get the things you want

Here is an example of using jdbc

you open a connection to the database

from the connection you get, and you can get the meta data, here is the code

    conn=dataSource.getConnection();
    DatabaseMetaData dbmeta = conn.getMetaData();

After you get the meta data, you can get all the columns, primary keys, foreign keys and even indexed. See the sample codes below

ResultSet rsKey = dbmeta.getPrimaryKeys(null, schemaName, tableName); ResultSet rs = dbmeta.getColumns(null, schemaName, tableName, "%"); ResultSet rsForeign=meta.getExportedKeys(null, schemaName, tableName);

answered Apr 19, 2016 at 14:27

buqing

1,05510 silver badges29 bronze badges

2 Comments

TheCatWhisperer Over a year ago

Great answer! I knew I could get column names from the jbdc driver, but no idea it was that easy to get FKs and PKs

TheCatWhisperer Over a year ago

Marked the other as the answer simply because he responded to the question as asked

Collectives™ on Stack Overflow

How to get the table a FK refers to in Postgres sql

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related