Removing duplicate rows and changing related foreign keys in PostgreSQL

Question

I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row.

How do I get a list of foreign keys related to my table?

I have seen a number of old posts with heavy queries and wonder if there is an easier way nowadays?

Update: I have added an answer with a working query and would be happy if there is something more simple.

Answer 1

From http://www.postgresql.org/message-id/[email protected]

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