Query to generate full DDL (including comments) for all foreign keys in PostgreSQL. Can be used in flyway migrations

generate_ddl_for_foreign_keys.sql

with fks as (
    select conname as fk_name,
           source_rel.relnamespace::regnamespace::text as table_schema,
           conrelid::regclass::text as table_name,
           (
               select string_agg(quote_ident(attname), ', ' order by unnest.ordinality)
               from pg_attribute, unnest(conkey) with ordinality
               where unnest.unnest = attnum
                 and attrelid = conrelid
           ) as table_columns,
           target_rel.relnamespace::regnamespace::text as foreign_schema,
           confrelid::regclass::text as foreign_table,
           (
               select string_agg(quote_ident(attname), ', ' order by unnest.ordinality)
               from pg_attribute, unnest(confkey) with ordinality
               where unnest.unnest = attnum
                 and attrelid = confrelid
           )             as foreign_columns,
           confmatchtype as match_type,
           confupdtype   as on_update,
           confdeltype   as on_delete,
           condeferrable as is_deferrable,
           condeferred   as is_initially_deferred,
           description   as comment
    from pg_constraint
             join pg_class source_rel on source_rel.oid = conrelid
             join pg_class target_rel on target_rel.oid = confrelid
             left join pg_description on objoid = pg_constraint.oid
    where contype = 'f'
--         and conrelid = 'public.foo'::regclass -- filter by constrained table
--         and confrelid = 'public.bar'::regclass -- filter by referenced table
)
select string_agg(ddl, E'\n\n')
from (
    select format(
        E'alter table %2$s.%3$s drop constraint %1$s;\n' ||
        E'alter table %2$s.%3$s\n' ||
        E'\tadd constraint %1$s\n' ||
        E'\t\tforeign key (%4$s) references %5$s.%6$s (%7$s)' ||
        case match_type
            when 'f' then E'\n\t\t\tmatch full'
            when 'p' then E'\n\t\t\tmatch partial'
            else ''
        end ||
        case on_delete
            when 'r' then E'\n\t\t\ton delete restrict'
            when 'c' then E'\n\t\t\ton delete cascade'
            when 'n' then E'\n\t\t\ton delete set null'
            when 'd' then E'\n\t\t\ton delete set default'
            else ''
        end ||
        case on_update
            when 'r' then E'\n\t\t\ton update restrict'
            when 'c' then E'\n\t\t\ton update cascade'
            when 'n' then E'\n\t\t\ton update set null'
            when 'd' then E'\n\t\t\ton update set default'
            else ''
        end ||
        case is_deferrable
            when true then E'\n\t\t\tdeferrable'
            else ''
        end ||
        case is_initially_deferred
            when true then E' initially deferred'
            else ''
        end ||
        ';' ||
        case
            when comment notnull then format(
                    E'\ncomment on constraint %s on %s.%s' ||
                    E'\n\tis %s;',
                    quote_ident(fk_name),
                    quote_ident(table_schema),
                    quote_ident(table_name),
                    quote_literal(comment)
                )
            else ''
        end,
        quote_ident(fk_name),
        quote_ident(table_schema),
        quote_ident(table_name),
        table_columns,
        quote_ident(foreign_schema),
        quote_ident(foreign_table),
        foreign_columns
    ) as ddl
    from fks
) as ddl;

This query was written as basis for automatic change of foreign keys.
For example, one can replace

case on_delete 
...
end

with '\n\t\t\ton delete cascade' and add and confdeltype = 'a' to where-clause to generate migration, adding on delete cascade to all fks

More information can be found in Postgres documentation