Detect all duplicate foreign keys from public schema and generates a script that drops all duplicates but first.

duplicate_foreign_keys.sql

--
-- Detect all duplicate foreign keys from public schema and generates a script that drops all duplicates but first.
--
with fkeys as (

    select
        tc.constraint_name, 
        tc.table_name, 
        kcu.column_name, 
        ccu.table_name as foreign_table_name,
        ccu.column_name as foreign_column_name 
    from
        information_schema.table_constraints as tc
        inner join information_schema.key_column_usage as kcu 
            on tc.constraint_name = kcu.constraint_name and tc.table_schema = kcu.table_schema
        inner join information_schema.constraint_column_usage as ccu
            on ccu.constraint_name = tc.constraint_name and ccu.table_schema = tc.table_schema
    where 
        tc.constraint_type = 'FOREIGN KEY' 
        and tc.table_schema = 'public'

), dups as (

    select 
        count(*), table_name, column_name, foreign_table_name, foreign_column_name 
    from fkeys
    group by
        table_name, column_name, foreign_table_name, foreign_column_name 
    having count(*) > 1

), final as (

    select 
        row_number() over(partition by a.table_name, a.column_name, a.foreign_table_name, a.foreign_column_name) as num,
        a.constraint_name,
        a.table_name
    from 
        fkeys a 
        inner join dups b 
            on a.table_name = b.table_name 
                and a.column_name = b.column_name 
                and a.foreign_table_name = b.foreign_table_name 
                and a.foreign_column_name = b.foreign_column_name

)
select 
    string_agg(
        'alter table ' 
        || 'public."' 
        || table_name 
        || '" drop constraint "' 
        || constraint_name 
        || '";',
        chr(10)
    )
from final
where num > 1;