Goal If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible.
Notes The query takes into account constraints that are associated directly with the column as well as constraints that are associated with the column through a domain. The query returns also temporal columns that do not have any CHECK constraints.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA only
SQL Query
WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, c.data_type
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.columns c USING (table_schema, table_name, column_name)
WHERE  c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') AND
cc.check_clause NOT LIKE '%IS NOT NULL' AND 
ccu.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)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, c.data_type
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN information_schema.columns c USING (table_schema, table_name, column_name)
WHERE c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') AND
t.table_type IN ('BASE TABLE','FOREIGN') AND cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.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)),
temp_columns AS (SELECT c.table_schema, c.table_name, t.table_type, c.column_name, c.data_type, '' AS check_clause, '' AS check_type
FROM information_schema.tables t INNER JOIN information_schema.schemata s ON t.table_schema=s.schema_name
INNER JOIN information_schema.columns c USING (table_schema, table_name)
WHERE c.data_type IN ('date', 'timestamp without time zone', 'timestamp with time zone', 'time without time zone', 'time with time zone') 
AND table_type IN ('BASE TABLE','FOREIGN')
AND (T.table_schema = 'public' OR S.schema_owner<>'postgres')),
result AS (SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM checks
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, check_type
FROM temp_columns AS tc
WHERE NOT EXISTS (SELECT 1
FROM checks AS ch
WHERE ch.table_schema=tc.table_schema AND ch.table_name=tc.table_name AND ch.column_name=tc.column_name))
SELECT table_schema, table_name, table_type, check_clause, check_type, string_agg(column_name || '.' || data_type, ',
' ORDER BY column_name) AS columns FROM result GROUP BY table_schema, table_name, table_type, check_clause, check_type ORDER BY table_schema, table_name, check_clause;
Collections

This query belongs to the following collections:

NameDescription
Find problems about integrity constraintsA 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 by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Temporal dataQueries of this category provide information about temporal (time-related) data that is kept in the database.
Validity and completenessQueries 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).