all_name_collisions.sql

SELECT
          rn AS name
        , array_to_string( schemas, ', ') AS schemas
FROM ( SELECT
                relname AS rn
                , relkind
                , array( SELECT nspname
                                || ' ('
                                ||(CASE WHEN relkind='r' THEN 'table'
                                        WHEN relkind='v' THEN 'view'
                                        WHEN relkind='i' THEN 'index'
                                        WHEN relkind='S' THEN 'sequence'
                                        ELSE relkind::text
                                   END
                                  )
                                || ')'
                         FROM pg_class AS c_inner
                         JOIN pg_namespace n ON n.oid = c_inner.relnamespace
                         WHERE c_inner.relname = c.relname
                        )
                  AS schemas
        FROM pg_class c
        GROUP BY relname, relkind
     ) as rel_list
WHERE array_dims(schemas) <> '[1:1]'
ORDER BY relkind desc, name
;