Drop the unused schemas or add schema elements to these.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH schemas_with_objects AS (
SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind NOT IN ('t')
UNION SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')
UNION SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace
WHERE (nspname='public' OR rolname<>'postgres')),
all_user_schemas AS (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name='public'
OR schema_owner<>'postgres')
SELECT schema_name
FROM all_user_schemas
EXCEPT SELECT schema_name
FROM schemas_with_objects
ORDER BY schema_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH schemas_with_objects AS (
SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind NOT IN ('t')
UNION SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')
UNION SELECT
n.nspname AS schema_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace
WHERE (nspname='public' OR rolname<>'postgres')),
all_user_schemas AS (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name='public'
OR schema_owner<>'postgres')
SELECT format('DROP SCHEMA %1$I;', schema_name) AS statements
FROM all_user_schemas
WHERE NOT EXISTS (SELECT *
FROM schemas_with_objects
WHERE all_user_schemas.schema_name=schemas_with_objects.schema_name)
ORDER BY statements;
Drop the schemas.
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Schemas
Queries of this category provide information about databse schemas
Unused implementation elements
Queries of this catergory provide information about the database objects that are not used.