Declare NOT NULL constraint to the column. Modification time column should be mandatory and just like registration time column should have a default value that returns a current date or timestamp.
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
AND A.is_nullable='YES'
AND A.data_type~*'(date|timestamp)'
AND (A.column_name~*'(reg|creat|insert|loomise|lisamise)'
OR (A.column_name~*'(muutmis|update|change|modify)'
AND A.column_name!~*'(jargmine|next)'))
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY table_schema, table_name, column_name;
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
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.
Temporal data
Queries of this category provide information about temporal (time-related) data that is kept in the database.