WITH tables as (SELECT A.table_schema, A.table_name, A.table_type 
FROM INFORMATION_SCHEMA.tables A
INNER JOIN INFORMATION_SCHEMA.schemata B
ON A.table_schema=B.schema_name
WHERE (A.table_schema = 'public'
OR B.schema_owner<>'postgres')
UNION SELECT schemaname, matviewname, 'MATERIALIZED VIEW' AS table_type
FROM pg_catalog.pg_matviews)
SELECT table_name, table_type, string_agg(table_schema, ';<br>' ORDER BY table_schema) AS schemas, Count(*) AS number_of_tables
FROM tables
GROUP BY table_name, table_type
HAVING Count(*)>1
ORDER BY table_type, table_name, Count(*) DESC;