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.)
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:
Name
Description
Find problems by overview
Queries 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:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Data redundancy
Queries of this category provide information about possible data redunancy.
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.