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

SystemVerilog Streaming Operator: Knowing Right from Left

SystemVerilog has this cool feature that is very handy for converting one type of collection of bits into another type of collection of bits.  It's the streaming operator.  Or the streaming concatenation operator.  Or maybe it's the concatenation of streaming expressions (it's also called pack/unpack parenthetically).  Whatever you want to call it, it's nice.  If you have an array of bytes and you want to turn it into an int, or an array of ints that you want to turn into an array of bytes, or if you have a class instance that you want to turn into a stream of bits, then streaming is amazing.  What used to require a mess of nested for-loops can now be done with a concise single line of code. As nice as it is, getting the hang of the streaming operator is tough.  The SystemVerilog 1800-2012 LRM isn't totally clear (at least to me) on the details of how they work.  The statement from the LRM that really got me was this, "The stream_operator <...
Read more