Query goal: | Find columns of base tables that name and type suggest that the column should contain the row registration time or last modify time but the column does not have a default value. |
Notes about the query: | The query considers both column names in English and Estonian. |
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: | Add a default value to the column that invokes a function that returns current 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, A.is_nullable 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 D USING (domain_schema, domain_name) WHERE domain_default IS NULL AND column_default IS NULL AND A.data_type~*'(date|timestamp)' AND (A.column_name~*'(registr|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, ordinal_position; |
Collection name | Collection description |
---|---|
Find problems about base tables | A selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview |
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 |
---|---|
Default value | Queries of this catergory provide information about the use of default values. |
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. |