The list of all the queries

Unnecessary domains

Query goal: Domain is a reusable artifact. Effort of its creation should be paid off by the advantages that it offers. If a domain is used in case of at most one column of a base table or even if it is used in case of more than one column but it does not specify neither a default value nor a check constraint, then there is no point of creating the domain.
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: If the domain is used in case of only one column, then use it in case of more columns. If the domain does not have a default value or a check constraint, then consider adding these or consider removing the domain from the database.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH domain_not_null AS (
SELECT nspname AS domain_schema,
typname AS domain_name,
typnotnull AS is_not_null
FROM pg_catalog.pg_type AS t INNER JOIN pg_catalog.pg_namespace AS n ON t.typnamespace=n.oid
WHERE typtype='d'),
domains AS (
SELECT domain_schema, 
domain_name, 
CASE WHEN data_type IN ('character varying', 'character') THEN data_type || '(' || character_maximum_length || ')' 
WHEN data_type IN ('numeric', 'decimal') THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')' 
ELSE data_type END AS data_type,
domain_default,
is_not_null,
EXISTS (SELECT 1 FROM Information_schema.domain_constraints AS dc WHERE dc.domain_schema=d.domain_schema AND dc.domain_name=d.domain_name) AS has_check,
(SELECT Count(*) AS cnt FROM Information_schema.columns c WHERE c.domain_schema=d.domain_schema AND c.domain_name=d.domain_name AND (c.table_schema, c.table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE')) AS number_of_columns
FROM Information_schema.domains AS d INNER JOIN domain_not_null USING (domain_schema, domain_name)
WHERE domain_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT domain_schema, domain_name, data_type, domain_default, is_not_null, has_check, number_of_columns
FROM Domains
WHERE number_of_columns<=1 OR (number_of_columns>1 AND has_check=FALSE AND domain_default IS NULL)
ORDER BY has_check DESC, domain_default NULLS FIRST, number_of_columns, domain_schema, domain_name;

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, 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 where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
DomainsQueries of this category provide information about reusable specifications of column properties.

The list of all the queries