Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments.
Notes
Columns that belong to the primary keys are not considered because NOT NULL constraint is added automatically to these columns. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring NOT NULL at the level of the domain and not at the level of base table columns.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name
FROM pg_catalog.pg_type, pg_catalog.pg_namespace
WHERE pg_namespace.oid = pg_type.typnamespace AND pg_type.typnotnull = FALSE
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
pk_columns AS (SELECT table_schema, table_name, c.column_name, c.is_nullable
FROM
(information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name))
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)
WHERE
tc.constraint_type IN ('PRIMARY KEY')),
col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable
FROM domains_null AS d INNER JOIN
Information_schema.column_domain_usage AS du USING (domain_schema, domain_name)
INNER JOIN Information_schema.columns AS c USING (table_schema, table_name, column_name)
WHERE c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND
(table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name
FROM pk_columns)),
col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null
FROM col_nulls
GROUP BY domain_schema, domain_name)
SELECT domain_schema, domain_name, string_agg(table_schema || '.' || table_name || '.' || column_name,'; ' ORDER BY table_schema, table_name, column_name) AS columns
FROM col_nulls
WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0)
GROUP BY domain_schema, domain_name
ORDER BY domain_schema, domain_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name
FROM pg_catalog.pg_type, pg_catalog.pg_namespace
WHERE pg_namespace.oid = pg_type.typnamespace
AND pg_type.typnotnull = FALSE
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres')),
pk_columns AS (SELECT table_schema, table_name, c.column_name
FROM information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name)
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)
WHERE tc.constraint_type IN ('PRIMARY KEY')),
col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable
FROM domains_null AS d INNER JOIN Information_schema.columns AS c USING (domain_schema, domain_name)
WHERE c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres')
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE')
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name
FROM pk_columns)),
col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null
FROM col_nulls
GROUP BY domain_schema, domain_name)
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I DROP NOT NULL;', table_schema, table_name, column_name) AS statements
FROM col_nulls
WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0)
ORDER BY table_schema, table_name, column_name;
Drop the NOT NULL constraint to the column.
WITH domains_null AS (SELECT pg_namespace.nspname AS domain_schema, pg_type.typname AS domain_name
FROM pg_catalog.pg_type, pg_catalog.pg_namespace
WHERE pg_namespace.oid = pg_type.typnamespace
AND pg_type.typnotnull = FALSE
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres')),
pk_columns AS (SELECT table_schema, table_name, c.column_name
FROM information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name)
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)
WHERE tc.constraint_type IN ('PRIMARY KEY')),
col_nulls AS (SELECT d.domain_schema, d.domain_name, c.table_schema, c.table_name, c.column_name, c.is_nullable
FROM domains_null AS d INNER JOIN Information_schema.columns AS c USING (domain_schema, domain_name)
WHERE c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres')
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE')
AND (table_schema, table_name, column_name) NOT IN (SELECT table_schema, table_name, column_name
FROM pk_columns)),
col_nulls_agg AS (SELECT domain_schema, domain_name, Count(*) FILTER (WHERE is_nullable='YES') AS cnt_is_null, Count(*) FILTER (WHERE is_nullable='NO') AS cnt_is_not_null
FROM col_nulls
GROUP BY domain_schema, domain_name)
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', domain_schema, domain_name) AS statements
FROM col_nulls
WHERE (domain_schema, domain_name) IN (SELECT domain_schema, domain_name FROM col_nulls_agg WHERE cnt_is_null=0 AND cnt_is_not_null>0)
ORDER BY statements;
Add the NOT NULL constraint to the domain.
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.
Missing data
Queries of this category provide information about missing data (NULLs) in a database.