The number of tables by schema, by type, and in total

Copy SQL

WITH tables_union AS (SELECT tables.table_schema, tables.table_type
FROM information_schema.tables
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION ALL SELECT schemaname AS table_schema,  'MATERIALIZED VIEW' AS table_type
FROM pg_catalog.pg_matviews)
SELECT table_schema, table_type, count(*) AS number_of_tables
FROM tables_union
GROUP BY CUBE (table_schema, table_type)
ORDER BY table_schema, table_type;