Find non-key and non-foreign key base table columns with the same name and type that in some cases permit NULLs and in some cases not. Be consistent. Make sure that this selection is consistent.
Notes
In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH keys as (select
o.conname,
(select nspname from pg_namespace where oid=m.relnamespace) as key_schema,
m.relname as key_table,
m.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype in ('u','p','f') and o.conrelid in (select oid from pg_class c where c.relkind = 'r')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select key_schema as table_schema, key_table as table_name, a_key.attname as column_name
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false),
nn AS (SELECT c.table_schema, c.table_name, c.column_name, c.data_type, c.is_nullable
FROM information_schema.columns AS c
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 NOT EXISTS (SELECT 1
FROM keys_with_names AS kwn
WHERE kwn.table_schema=c.table_schema AND kwn.table_name=c.table_name AND kwn.column_name=c.column_name))
SELECT column_name, data_type, Count(*) AS total_columns, string_agg(table_schema || '.' || table_name || ' ' || CASE WHEN is_nullable='YES' THEN 'Nullable' ELSE 'Not nullable' END,'; ' ORDER BY is_nullable, table_schema, table_name) AS columns_and_nullability
FROM nn
WHERE EXISTS (SELECT 1 FROM nn AS n WHERE n.column_name=nn.column_name AND n.data_type=nn.data_type AND is_nullable='YES')
AND EXISTS (SELECT 1 FROM nn AS n WHERE n.column_name=nn.column_name AND n.data_type=nn.data_type AND is_nullable='NO')
GROUP BY column_name, data_type
HAVING Count(DISTINCT is_nullable)>1
ORDER BY Count(*) DESC, column_name, data_type;
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Missing data
Queries of this category provide information about missing data (NULLs) 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).
Further reading and related materials:
Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)