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)
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:
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
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.