/doc/notes/postgres_meta_info.sql

 1http://www.alberton.info/postgresql_meta_info.html
 2
 3SELECT * from information_schema.tables where table_schema = 'public';
 4SELECT * from information_schema.columns where table_schema = 'public';
 5SELECT tc.constraint_name,
 6          tc.constraint_type,
 7          tc.table_name,
 8          kcu.column_name,
 9	  tc.is_deferrable,
10          tc.initially_deferred,
11          rc.match_option AS match_type,
12          rc.update_rule AS on_update,
13          rc.delete_rule AS on_delete,
14          ccu.table_name AS references_table,
15          ccu.column_name AS references_field
16     FROM information_schema.table_constraints tc
17LEFT JOIN information_schema.key_column_usage kcu
18       ON tc.constraint_catalog = kcu.constraint_catalog
19      AND tc.constraint_schema = kcu.constraint_schema
20      AND tc.constraint_name = kcu.constraint_name
21LEFT JOIN information_schema.referential_constraints rc
22       ON tc.constraint_catalog = rc.constraint_catalog
23      AND tc.constraint_schema = rc.constraint_schema
24      AND tc.constraint_name = rc.constraint_name
25LEFT JOIN information_schema.constraint_column_usage ccu
26       ON rc.unique_constraint_catalog = ccu.constraint_catalog
27      AND rc.unique_constraint_schema = ccu.constraint_schema
28      AND rc.unique_constraint_name = ccu.constraint_name
29where constraint_type in ('FOREIGN KEY', 'PRIMARY KEY');