Query goal: | Find the names of different types (base table, foreign table, view, materialized view) of tables. Naming of tables must be consistent. For instance, do not mix names in plural and singular form within the same table type. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
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 WHERE schemaname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY table_type, table_name, table_schema; |
Category name | Category description |
---|---|
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
Naming | Queries of this category provide information about the style of naming. |