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 :-)

Comments

Post a Comment

Popular posts from this blog

SystemVerilog Fork Disable "Gotchas"

This is a long post with a lot of SystemVerilog code. The purpose of this entry is to hopefully save you from beating your head against the wall trying to figure out some of the subtleties of SystemVerilog processes (basically, threads). Subtleties like these are commonly referred to in the industry as "Gotchas" which makes them sound so playful and fun, but they really aren't either. I encourage you to run these examples with your simulator (if you have access to one) so that a) you can see the results first hand and better internalize what's going on, and b) you can tell me in the comments if this code works fine for you and I'll know I should go complain to my simulator vendor. OK, I'll start with a warm-up that everyone who writes any Verilog or SystemVerilog at all should be aware of, tasks are static by default. If you do this: module top; task do_stuff ( int wait_time); #wait_time $display ( "waited %0d, then did stuff" , wa
Read more

'git revert' Is Not Equivalent To 'svn revert'

I just learned that if you have some changes in your working tree that you want to get rid of, you don't type 'git revert' like you might guess. No, that's what cvs, subversion, mercurial, and bazaar (to name a few) use revert to mean, but not git. With git, revert is used to undo actual commits. Thankfully, you can undo your revert with another 'git revert', I just learned. So let me repeat to be clear, if you have changes to your working files that you want to abandon, DO NOT do this: git revert HEAD That will undo your last commit. Do this instead: git reset --hard HEAD I'm glad I have that straightened out now. I'm wondering if /etc was really a good place for me to start out playing with git. UPDATE: Nearly two years later and I'm still getting comments on this. I'm glad I've been able to help people out this way. The discussion in the comments is good, and one thing I'd like to point out is that I now always use
Read more

Put /etc Under Revision Control (with git)

UPDATE : There is a much easier way now : sudo aptitude install etckeeper There, now etc is under revision control and changes are automatically committed with a helpful message after you install new packages. Thanks for the tip, Marcelo! Read on for the original blog entry. --- I put my /etc directory under revision control. I used git. It's awesome. Not that I think I'm going to wholesale copy my /etc from one machine to another, nor because I'm going to branch my /etc directory and spend weeks developing some cool new feature (whatever that would look like...) and then merge it back into the main branch. No, it's mostly because I like the idea of having the big "undo" option that having things under revision control provides. Having to commit changes and write check-in comments when I make changes helps me stay more organized too. It's never good when I go in willy-nilly to make a "quick change" to some configuration file. I r
Read more