| Query goal: | Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. 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. |
| Query type: | Sofware measure (Numeric values (software measures) about the database) |
| Query license: | MIT License |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT Count(*) AS nr_of_Boolean_cols, Count(*) FILTER (WHERE is_nullable='NO') AS nr_of_mandatory_Boolean_cols, Count(*) FILTER (WHERE is_nullable='YES') AS nr_of_optional_Boolean_cols, CASE WHEN Count(*)=0 THEN NULL ELSE Round((Count(*) FILTER (WHERE is_nullable='YES')*100)/ Count(*),1) END AS percentage_of_optional_Boolean_cols 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 T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres'); |
| 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 by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems 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. |