List all foreign keys in PostgreSQL database, analog of MySQL SHOW CREATE TABLE mytable;

postgresql-list-foreign-keys.sql

--- Source: http://solaimurugan.blogspot.ru/2010/10/list-out-all-forien-key-constraints.html

SELECT 
  tc.constraint_name,
  tc.constraint_type,
  tc.table_name,
  kcu.column_name,
  tc.is_deferrable,
  tc.initially_deferred,
  rc.match_option AS match_type,
  
  rc.update_rule AS on_update,
  rc.delete_rule AS on_delete,
  ccu.table_name AS references_table,
  ccu.column_name AS references_field

FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
  ON tc.constraint_catalog = kcu.constraint_catalog
  AND tc.constraint_schema = kcu.constraint_schema
  AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
  ON tc.constraint_catalog = rc.constraint_catalog
  AND tc.constraint_schema = rc.constraint_schema
  AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
  ON rc.unique_constraint_catalog = ccu.constraint_catalog
  AND rc.unique_constraint_schema = ccu.constraint_schema
  AND rc.unique_constraint_name = ccu.constraint_name

--- any conditions for table etc. filtering
WHERE lower(tc.constraint_type) in ('foreign key')

SELECT 
        tb.table_schema, tb.table_name, tb.column_name, tb.data_type, tb.is_nullable,
        fx.constraint_name, fx.references_schema, fx.references_table, fx.references_field
FROM information_schema.columns tb
LEFT JOIN (
        SELECT 
          tc.constraint_schema,
          tc.table_name,
          kcu.column_name,
          tc.constraint_name,
          tc.constraint_type,  
          
          rc.update_rule AS on_update,
          rc.delete_rule AS on_delete,
          
          ccu.constraint_schema AS references_schema,
          ccu.table_name AS references_table,
          ccu.column_name AS references_field
        
        FROM information_schema.table_constraints tc
        
        LEFT JOIN information_schema.key_column_usage kcu
          ON tc.constraint_catalog = kcu.constraint_catalog
          AND tc.constraint_schema = kcu.constraint_schema
          AND tc.constraint_name = kcu.constraint_name
        
        LEFT JOIN information_schema.referential_constraints rc
          ON tc.constraint_catalog = rc.constraint_catalog
          AND tc.constraint_schema = rc.constraint_schema
          AND tc.constraint_name = rc.constraint_name
        
        LEFT JOIN information_schema.constraint_column_usage ccu
          ON rc.unique_constraint_catalog = ccu.constraint_catalog
          AND rc.unique_constraint_schema = ccu.constraint_schema
          AND rc.unique_constraint_name = ccu.constraint_name
        
        WHERE tc.constraint_schema NOT ILIKE 'pg_%' AND tc.constraint_schema NOT ILIKE 'inform%' AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')) fx
        ON fx.constraint_schema = tb.table_schema AND fx.table_name = tb.table_name AND fx.column_name = tb.column_name
WHERE tb.table_schema = 'Setup' AND tb.table_name = 'Branches'
ORDER BY tb.ordinal_position