Query goal: | Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. |
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: | 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+system catalog |
SQL query: | Click on query to copy it
WITH fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false) SELECT c.table_schema, c.table_name, c.column_name, c.data_type, c.character_maximum_length, c.is_nullable FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name) WHERE column_name~*'^(is|has|on)_' AND (c.data_type<>'boolean') AND coalesce(c.column_default, d.domain_default) IS NULL AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 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 NOT EXISTS (SELECT * FROM fk_columns WHERE fk_columns.table_schema=c.table_schema AND fk_columns.table_name=c.table_name AND fk_columns.column_name=c.column_name) 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. |
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. |
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). |