Removing duplicate rows and changing related foreign keys in PostgreSQL
From http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

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';


It produces

   confrelid   |         fcol         |   conrelid    |     col
---------------+----------------------+---------------+----------------
   my_table    | my_referenced_column | another_table | some_column


Update: RhodiumToad on freenode #postgres channel gave a simpler query which however needs some parsing and filtering if only specific column is needed.

SELECT
  conrelid :: REGCLASS,
  conname,
  pg_get_constraintdef(oid)
FROM pg_constraint
WHERE confrelid = 'my_table' :: REGCLASS;


producing

  conrelid  |       conname        |         pg_get_constraintdef
------------+----------------------+----------------------------------------------------------------------------------------------------
 some_table | some_constraint_name | FOREIGN KEY (some_column) REFERENCES my_table(my_referenced_column) DEFERRABLE INITIALLY DEFERRED