Find PostgreSQL Tables Without a Primary Key

Apr 8, 2026 · 4 min read · postgresql administration schema database information_schema ·

Share on:

How to Find PostgreSQL Tables Without a Primary Key

A missing primary key is one of the most common and damaging database design oversights. Without a primary key, PostgreSQL has no reliable way to uniquely identify a row. This causes problems with logical replication, ORM frameworks, and application-level updates or deletes that may silently affect the wrong rows.

This SQL query scans information_schema to find every user table in your database that has no primary key or unique key constraint. Running it regularly is a simple way to catch schema problems before they reach production.

SQL Script

 1SELECT
 2    tbl.table_schema,
 3    tbl.table_name
 4FROM
 5    information_schema.tables tbl
 6WHERE
 7    table_type = 'BASE TABLE'
 8    AND table_schema NOT IN ('pg_catalog', 'information_schema')
 9    AND NOT EXISTS (
10        SELECT 1
11        FROM information_schema.key_column_usage kcu
12        WHERE kcu.table_name   = tbl.table_name
13          AND kcu.table_schema = tbl.table_schema
14    );

Notes: Works on any version of PostgreSQL. Returns one row per table that has no entry in key_column_usage, meaning no primary key and no unique constraint. Returns no rows if all tables have keys defined.

Code Breakdown

information_schema.tables

A standard SQL view that lists every table and view in the database. The table_type column distinguishes base tables from views, foreign tables, and other relation types.

table_type = 'BASE TABLE'

Restricts results to real user-created tables. This excludes views (VIEW) and foreign tables (FOREIGN).

table_schema NOT IN ('pg_catalog', 'information_schema')

Excludes PostgreSQL internal schemas. Without this filter, system tables would appear in the results.

NOT EXISTS (...)

The core logic. For each table, it checks whether any row exists in key_column_usage for that table. If none exists, the table has no defined key constraints at all.

information_schema.key_column_usage

Lists every column that participates in a named constraint — including primary keys, unique constraints, and foreign keys. A table with no rows here has no key constraints defined.

kcu.table_name = tbl.table_name AND kcu.table_schema = tbl.table_schema

Matches on both table name and schema to avoid cross-schema false matches when two schemas have tables with the same name.

Key Points

Any table returned by this query has no primary key and no unique constraint on any column.
Results are limited to your current database. Run the query on each database separately to get a full picture of your cluster.
An empty result set is the goal — it means every user table has at least one key constraint.
Temporary tables do not appear in information_schema.tables — only permanent tables are listed.

Insights and Explanations

A primary key enforces row uniqueness and creates a unique index automatically. Without one:

Logical replication fails or behaves unpredictably. PostgreSQL logical replication uses the primary key (or replica identity) to identify which row to update or delete on a replica. With no key, PostgreSQL defaults to comparing every column — which is slow and error-prone.
ORMs and frameworks misbehave. Most ORM tools (Django, Rails, Hibernate) assume every table has a primary key. Tables without one often require workarounds or simply fail.
Duplicate rows become invisible. Without a unique identifier, it is easy to insert duplicate data with no way to distinguish or remove individual rows cleanly.

For each table returned, determine whether it is a staging or temporary table (which may not need a key) or a real application table (which should always have one). For application tables, add a primary key:

1-- Add a surrogate primary key if no natural key exists
2ALTER TABLE your_schema.your_table ADD COLUMN id bigserial PRIMARY KEY;
3
4-- Or promote an existing unique column to primary key
5ALTER TABLE your_schema.your_table ADD PRIMARY KEY (existing_unique_column);

If the table is used with logical replication and you cannot add a primary key right away, set the replica identity to use a unique index as a temporary measure:

1ALTER TABLE your_schema.your_table REPLICA IDENTITY USING INDEX your_unique_index_name;

Additional Considerations

Permissions: Any user with access to information_schema can run this query. No superuser access is required.
Performance: information_schema views are fast on typical databases. On a database with thousands of tables, the NOT EXISTS subquery may take a moment but is not a concern for a one-time audit.
Foreign keys: A table with only outbound foreign keys (referencing another table) will still appear in results if it has no key constraints of its own.