| Query goal: | The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. |
| 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: | Replace default value that is a textual value (has aposthrophes) with a default value that has no apostrophes, i.e., is not a representation of a textual value. |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT A.table_schema, T.table_type, A.table_name , A.column_name, A.data_type, d.domain_default, a.column_default 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 (A.table_schema = 'public' OR S.schema_owner<>'postgres') AND A.data_type IN ('smallint','integer','bigint','double precision','real','numeric') AND ((D.domain_default LIKE '%::'|| A.data_type AND D.domain_default~'['']') OR (A.column_default LIKE '%::'|| A.data_type AND A.column_default~'['']')) ORDER BY table_type, 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 |
|---|---|
| Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
| Data types | Queries of this category provide information about the data types and their usage. |
| Default value | Queries of this catergory provide information about the use of default values. |
| Performance | Queries of this category provide information about indexes in a database. |
| Validity and completeness | Queries 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). |