Goal Find tables with the same name in different schemas. Make sure that this is not a duplication.
Notes In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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, ';
' 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;
Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Data redundancyQueries of this category provide information about possible data redunancy.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.

Further reading and related materials:

Reference
https://refactoring.guru/smells/alternative-classes-with-different-interfaces
https://refactoring.guru/smells/duplicate-code
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code)