Goal This query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values.
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)
Data Source INFORMATION_SCHEMA only
SQL Query 
SELECT A.table_schema, A.table_name , A.column_name, A.data_type
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 AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position;

Fixing Suggestion

Add default value 'infinity' to the column and declare the column mandatory. If the column is specified by using a domain, then add the default value and NOT NULL constraint to the domain.

Fixing Actions

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET DEFAULT ''infinity'';', 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 AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position;

Declare default value to the column.

Fix Action #2
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 AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position;

Add NOT NULL constraint to the column.

Fix Action #3
SELECT format('ALTER DOMAIN %1$I.%2$I SET DEFAULT ''infinity'';', A.domain_schema, A.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
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name;

Declare default value to the domain.

Fix Action #4
SELECT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;',  A.domain_schema, A.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
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name;

Add NOT NULL constraint to the domain.

# SQL Query to Generate Fix Description
1 
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET DEFAULT ''infinity'';', 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 AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position;
 Declare default value to the column.
2 
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 AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position;
 Add NOT NULL constraint to the column.
3 
SELECT format('ALTER DOMAIN %1$I.%2$I SET DEFAULT ''infinity'';', A.domain_schema, A.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
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name;
 Declare default value to the domain.
4 
SELECT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;',  A.domain_schema, A.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
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND A.column_name~*'(lopp|(?<!ed)end|expire)'
AND EXISTS (SELECT * FROM information_schema.columns B
WHERE A.table_schema=B.table_schema
AND A.table_name=B.table_name
AND B.data_type IN ('timestamp without time zone','timestamp with time zone')
AND B.column_name~*'(alg|begin|start)')
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name;
 Add NOT NULL constraint to the domain.

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:

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.

Result quality depends on names

Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.

NameDescription
Default valueQueries of this catergory provide information about the use of default values.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.