Optional base table columns that have a default value that is not the empty string

Query goal:

Find optiona base table columns that have a default value that is not the empty string. Such columns should be mandatory.

Notes about the query:

The query takes into account a possibility that the default value can be associated with the column directly or throug a 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:

Declare NOT NULL constraint either directly to the column or to the domain of the column. Do not declare the NOT NULL constraint to both column and domain.

Data source:

INFORMATION_SCHEMA only

SQL query:

Click on query to copy it

SELECT A.table_schema, A.table_name , A.column_name, CASE WHEN A.data_type ILIKE 'character%' AND A.character_maximum_length IS NOT NULL THEN A.data_type || '(' || A.character_maximum_length::text || ')'
WHEN A.data_type ILIKE 'timestamp%' AND A.datetime_precision IS NOT NULL THEN A.data_type || '(' || A.datetime_precision || ')'
WHEN A.data_type ILIKE 'numeric%' AND A.numeric_precision IS NOT NULL THEN A.data_type || '(' || A.numeric_precision::text || ',' ||coalesce(A.numeric_scale,0)::text || ')'
WHEN A.data_type ILIKE 'interval%' AND A.interval_type IS NOT NULL THEN A.data_type || '(' || A.interval_type::text || ')'
WHEN A.data_type='USER-DEFINED' THEN A.udt_schema || '.' || A.udt_name 
ELSE A.data_type END AS data_type, 
D.domain_name, D.domain_schema, A.column_default, D.domain_default
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL 
AND coalesce(column_default, domain_default)!~*'^''[[:space:]]*''::'
AND is_nullable='YES'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY table_schema, table_name, ordinal_position;

SQL statements for generating SQL statements that help us to fix the problem

SQL query	Description
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', A.table_schema, A.table_name , A.column_name) AS statements FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name LEFT JOIN information_schema.domains D USING (domain_schema, domain_name) WHERE coalesce(column_default, domain_default) IS NOT NULL AND is_nullable='YES' AND column_default!~'^''[[:space:]]''::' AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY table_schema, table_name, column_name;	Declare NOT NULL constraint to the column.
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', D.domain_schema, D.domain_name) AS statements FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name INNER JOIN information_schema.domains D USING (domain_schema, domain_name) WHERE coalesce(column_default, domain_default) IS NOT NULL AND is_nullable='YES' AND domain_default!~'^''[[:space:]]''::' AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY statements;	Declare NOT NULL constraint to the domain.

SQL query

Description

SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', A.table_schema, A.table_name , A.column_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL 
AND is_nullable='YES'
AND column_default!~*'^''[[:space:]]*''::'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY table_schema, table_name, column_name;

Declare NOT NULL constraint to the column.

SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', D.domain_schema, D.domain_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
INNER JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL 
AND is_nullable='YES'
AND domain_default!~*'^''[[:space:]]*''::'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY statements;

Declare NOT NULL constraint to the domain.

Collections where the query belongs to

Collection name	Collection description
Find problems about integrity constraints	A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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 .

Collection name

Collection description

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

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 where the query belongs to

Category name	Category description
Default value	Queries of this catergory provide information about the use of default values.
Missing data	Queries of this category provide information about missing data (NULLs) in a database.
Validity and completeness	Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

Category name

Category description

Default value

Queries of this catergory provide information about the use of default values.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

Validity and completeness

Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).