Query goal: | Find base table columns that have Boolean type and do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. |
Notes about the query: | The query finds all base table columns that have the type Boolean and that are optional. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Make the column mandatory by declaring NOT NULL constraint to it. |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
SELECT A.table_schema, A.table_name , A.column_name 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 WHERE A.data_type='boolean' AND is_nullable='YES' AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY A.table_schema, A.table_name, A.column_name; |
SQL query | Description |
---|---|
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL', A.table_schema, A.table_name , A.column_name) AS statements FROM information_schema.columns A INNER JOIN information_schema.tables T ON A.table_schema = T.table_schema AND A.table_name = T.table_name INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name WHERE A.data_type='boolean' AND is_nullable='YES' AND T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') ORDER BY A.table_schema, A.table_name, A.column_name; | Apply the NOT NUL L constraint directly to the column. |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. 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 |
---|---|
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. |
Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
Reference |
---|
https://thoughtbot.com/blog/avoid-the-threestate-boolean-problem |