PostgreSQL | Query Foreign Key Table and Column Lists

Tags:

• PostgreSQL
• Database

Query the list of tables and columns that the table you want to inspect references through foreign keys.

SELECT CCU.table_name      AS search_table_name
     , CCU.column_name     AS search_column_name
     , KCU.table_name      AS foreign_table_name
     , KCU.column_name     AS foreign_column_name
     , KCU.constraint_name AS foreign_constraint_name
 FROM information_schema.table_constraints TC
      JOIN information_schema.key_column_usage KCU ON TC.constraint_name = KCU.constraint_name
      JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name
WHERE TC.constraint_type = 'FOREIGN KEY'
  AND CCU.table_name = #{table_name_to_query};

Query the list of tables and columns that reference the table you want to inspect through foreign keys.

SELECT TC.table_name   AS search_table_name
     , KCU.column_name AS search_column_name
     , CCU.table_name  AS foreign_table_name
     , CCU.column_name AS foreign_column_name
     , CCU.constraint_name AS foreign_constraint_name
  FROM information_schema.table_constraints TC
       JOIN information_schema.key_column_usage KCU ON KCU.constraint_name = TC.constraint_name
       JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name
 WHERE TC.constraint_type = 'FOREIGN KEY'
   AND TC.table_name = #{table_name_to_query};