Wyszukiwanie kluczy obcych powiązanych z kolumną w tabeli w bazie danych PostgreSQL

Czasami potrzebne jest wyszukiwanie wszystkich tabel i ich kolumn powiązanych kluczami obcymi z kolumną w tabeli źródłowej. W przypadku bazy danych PostgreSQL można to zrobić wykorzystując information_schema.

Taka możliwość przydaje się jeśli musimy np. przepiąć wszystkie dane powiązane do wiersza na inny wiersz tej samej tabeli, a są do niego powiązane dane poprzez klucz obcy. Przykładowo załóżmy, że mamy CRM i w nim tabelę klient o automatycznie numerowanym kluczu głównym założonym na kolumnie o nazwie id. Do klucza głównego tej tabeli istnieją klucze obce w dużej części systemu, a okazało się, że przez nie uwagę pracownicy stworzyli dwa razy tego samego klienta. Po dwóch miesiącach błąd został wykryty i należy przeciąć na klienta A (434) wszystkie dane dodatkowe z klienta B (222) np. korespondencja, dane finansowe itp. Wykorzystując poniższe zapytanie można wyszukać wszystkie tabele i kolumny na których należy wykonać update zmieniając identyfikatory kluczy obcych np.

select
  f_kcu.table_name, f_kcu.column_name
from
  information_schema.key_column_usage as p_kcu
  join information_schema.constraint_column_usage f_ccu 
     on p_kcu.table_name = f_ccu.table_name 
        and p_kcu.column_name = f_ccu.column_name
  join information_schema.key_column_usage f_kcu 
     on f_ccu.constraint_name = f_kcu.constraint_name
where
  p_kcu.table_name = 'klient'
  and p_kcu.column_name = 'id'
  and p_kcu.constraint_name <> f_kcu.constraint_name
order by f_kcu.table_name;

Przykładowe zapytanie do aktualizacji wierszy z tabelach powiązanych.

UPDATE wiadomosci SET client_id = 222 WHERE client_id = 434;