Query goal: | Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. |
Notes about the query: | M denotes a mandatory column and O an optional column. Columns are sorted based on the ordinal position. |
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: | 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: | Click on query to copy it
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; |
Category name | Category 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. |
Reference |
---|
https://wiki.postgresql.org/wiki/Alter_column_position |
https://stackoverflow.com/questions/285733/how-do-i-alter-the-position-of-a-column-in-a-postgresql-database-table |