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.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT (opens in new tab)
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 
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

This query belongs to the following collections:

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 .

NameDescription
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

This query is classified under the following categories:

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.

NameDescription
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.