| Query goal: | Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | Low (Many false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Determine as to whether there is markedly higher probability that a new row has one of the truth-values. If yes, then declare a default value. The default value can be directly associated with a column or it could be associated with a domain (if the column has been defined based on the domain). |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT c.table_schema, c.table_name, c.column_name FROM information_schema.columns AS c LEFT JOIN information_schema.column_domain_usage cdc ON c.table_schema=cdc.table_schema AND c.table_name=cdc.table_name AND c.column_name=cdc.column_name LEFT JOIN information_schema.domains d ON cdc.domain_schema=d.domain_schema AND cdc.domain_name=d.domain_name WHERE (c.table_schema, c.table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND c.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) AND c.data_type='boolean' AND coalesce(c.column_default, domain_default) IS NULL ORDER BY c.table_schema, c.table_name, c.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 |
| Category name | Category description |
|---|---|
| Boolean data | Queries of this category provide information about truth-values data that is kept in the database. |
| Comfortability of data management | Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient. |
| Default value | Queries of this catergory provide information about the use of default values. |