Friday, April 12, 2013

List Foreign Keys in Your Postgresql Database

I wanted to know which tables referenced a certain other table in my database, but being a very occasional user of SQL, I didn't know where to begin to find that. Fortunately, the internet had the answer, from Tom Lane himself actually. Just run this query on your Postgresql database:

select confrelid::regclass, af.attname as fcol,
       conrelid::regclass, a.attname as col
from pg_attribute af, pg_attribute a,
  (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey
   from (select conrelid,confrelid,conkey,confkey,
                generate_series(1,array_upper(conkey,1)) as i
         from pg_constraint where contype = 'f') ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and
      a.attnum = conkey and a.attrelid = conrelid;

That shows all foreign key relationships in your database. If you just want to see which tables reference a particular table, do this (replace my_table and my_referenced_column with the table column you want to see the references to):

select confrelid::regclass, af.attname as fcol,
       conrelid::regclass, a.attname as col
from pg_attribute af, pg_attribute a,
  (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey
   from (select conrelid,confrelid,conkey,confkey,
                generate_series(1,array_upper(conkey,1)) as i
         from pg_constraint where contype = 'f') ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and
      a.attnum = conkey and a.attrelid = conrelid 
  AND confrelid::regclass = 'my_table'::regclass AND af.attname = 'my_referenced_column';

I love that I can get personal attention from the main developers of software tools that I use. Even if it was really only personal for the original person who asked the question :-)

No comments: