Query goal: | Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the depending domain and recreate it so that it references to a base type not to a domain. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT n.nspname AS domain_schema, t.typname AS domain_name, n_base.nspname AS base_domain_schema, t_base.typname AS base_domain_name FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid INNER JOIN pg_catalog.pg_type t_base ON t.typbasetype=t_base.oid INNER JOIN pg_catalog.pg_namespace n_base ON t_base.typnamespace=n_base.oid WHERE t.typtype='d' AND t_base.typtype='d' AND n.nspname 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 domain_schema, domain_name; |
SQL query | Description |
---|---|
SELECT format('DROP DOMAIN %1$I.%2$I;', n.nspname, t.typname) AS statements FROM pg_catalog.pg_type t INNER JOIN pg_catalog.pg_namespace n ON t.typnamespace=n.oid INNER JOIN pg_catalog.pg_type t_base ON t.typbasetype=t_base.oid WHERE t.typtype='d' AND t_base.typtype='d' AND n.nspname 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 n.nspname, t.typname; | Drop the depending domain. |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Domains | Queries of this category provide information about reusable specifications of column properties. |