Query goal: | Find columns that contain registration or modification time but are optional. |
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 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: | Click on query to copy it
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; |
Collection name | Collection 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 . |
Category name | Category 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. |