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.
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 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;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
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 coalesce(column_default, domain_default) IS NOT NULL
AND domain_name 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.
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 coalesce(column_default, domain_default) IS NOT NULL
AND domain_name 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.
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 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.
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 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:
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
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.