Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table.
Notes
M denotes a mandatory column and O an optional column. Columns are sorted based on the ordinal position.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Changing the order of columns in an existing table that is already in use requires dropping and re-creating the table and loading data to the new table. Probably this is not worth the trouble. Change the order only in case of newly create tables.
Data Source
INFORMATION_SCHEMA only
SQL Query
WITH nullability_pattern AS (SELECT table_schema, table_name,
string_agg(CASE WHEN is_nullable='NO' THEN 'M' ELSE 'O' END,'' ORDER BY ordinal_position) AS pattern
FROM INFORMATION_SCHEMA.columns
WHERE (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)
GROUP BY table_schema, table_name)
SELECT table_schema, table_name, pattern
FROM nullability_pattern
WHERE pattern!~'^M+O*$'
ORDER BY table_schema, table_name;
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Missing data
Queries of this category provide information about missing data (NULLs) in a database.