In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined.
Notes
For each found table the query shows the names of primary key columns with the ordinal number of the column in the table. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Although SQL pays attention to the order of columns in SQL tables it does not provide a comfortable way to change the order of columns in the table without dropping and recreating the table.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH pk_columns AS (select target_table_oid, target_schema as table_schema, target_table as table_name, a.attname as column_name, a.attnum as ordinal_position
from (select
nc.nspname 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
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype='p') t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false),
non_pk_columns AS (SELECT table_schema, table_name, column_name, ordinal_position
FROM INFORMATION_SCHEMA.columns
EXCEPT SELECT table_schema, table_name, column_name, ordinal_position
FROM pk_columns)
SELECT table_schema, table_name, EXISTS (SELECT *
FROM pg_inherits pi WHERE pi.inhrelid=p.target_table_oid) AS has_parent, string_agg(column_name || '(' || ordinal_position::text || ')',',' ORDER BY ordinal_position) AS primary_key_columns
FROM pk_columns AS p
WHERE EXISTS (SELECT 1 FROM non_pk_columns AS n WHERE p.table_schema=n.table_schema AND p.table_name=n.table_name AND p.ordinal_position>n.ordinal_position)
GROUP BY table_schema, table_name, has_parent
ORDER BY has_parent, table_schema, table_name;
Collections
This query belongs to the following collections:
Name
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
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
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.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.