How to Check If a Table Has a Primary Key in PostgreSQL

A table without a primary key can look perfectly normal. Queries run, and data goes in and out. Then one day logical replication refuses to copy your UPDATEs, or you find the same customer stored three times.

Checking for a primary key takes a few seconds. This guide shows you how, from a quick look at one table to a query that finds every table in your database that doesn’t have one.

A Quick Refresher: What Is a Primary Key?

A primary key is the column, or set of columns, that uniquely identifies each row. Think of it as the row’s ID card.

It has two rules:

• Unique: no two rows can have the same value.
• Not null: every row must have a value.

A table can have only one primary key. That one key can span more than one column.

Set Up Some Example Tables

To make the examples easy to follow, create three small tables:

-- Has a single-column primary key
CREATE TABLE customers (
    id    integer PRIMARY KEY,
    name  text
);

-- Has a two-column (composite) primary key
CREATE TABLE order_items (
    order_id  integer,
    line_no   integer,
    product   text,
    PRIMARY KEY (order_id, line_no)
);

-- Has no primary key at all
CREATE TABLE app_logs (
    logged_at  timestamptz,
    message    text
);

We’ll check all three with each method below.

Method 1: Use \d in psql (Fastest)

If you’re already in psql, describe the table:

\d customers

              Table "public.customers"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)

Look at the Indexes section. The words PRIMARY KEY tell you the table has one, and (id) tells you which column.

Here is the composite one:

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

\d order_items

Indexes:
    "order_items_pkey" PRIMARY KEY, btree (order_id, line_no)

And the table with no primary key:

\d app_logs

              Table "public.app_logs"
  Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
 logged_at | timestamp with time zone |           |          |
 message   | text                     |           |          |

No Indexes section at all, so there’s no primary key.

This works well for a single table. If you have to check fifty, use a query instead.

Method 2: Query pg_constraint (Most Reliable)

PostgreSQL stores every primary key in the pg_constraint catalog with contype = 'p'. The p stands for primary key.

SELECT conname AS constraint_name,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.customers'::regclass
  AND contype = 'p';

 constraint_name |   definition
-----------------+-----------------
 customers_pkey  | PRIMARY KEY (id)

Run the same query for app_logs and you get zero rows, so no primary key.

'public.customers'::regclass converts the table name into its internal ID. If the table doesn’t exist, you get a clear error instead of a quiet empty result. That’s useful when you’ve made a typo.

A Simple Yes or No

Want a plain true or false, for example in a script?

SELECT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conrelid = 'public.app_logs'::regclass
      AND contype = 'p'
) AS has_primary_key;

 has_primary_key
-----------------
 f

You can call it straight from the shell:

psql -d shop -tAc "SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'public.app_logs'::regclass AND contype = 'p');"

f

The -tAc flags drop the headers and formatting, so you get just t or f. That’s easy to check in a bash if statement.

Method 3: Use information_schema (Portable SQL)

If you’d rather use standard SQL that also works in other databases, use information_schema:

SELECT tc.table_name,
       tc.constraint_name,
       kcu.column_name,
       kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema   = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema = 'public'
  AND tc.table_name = 'order_items'
ORDER BY kcu.ordinal_position;

 table_name  | constraint_name  | column_name | ordinal_position
-------------+------------------+-------------+------------------
 order_items | order_items_pkey | order_id    |                1
 order_items | order_items_pkey | line_no     |                2

ordinal_position shows the column order inside the key. That matters for composite keys.

One thing to know: information_schema only shows tables you have some privilege on. If you’re not the owner and haven’t been granted access, the table won’t appear, and it can look as if there’s no primary key. When you need the full picture, use pg_constraint (Method 2) or connect as a superuser.

Method 4: Find Every Table Without a Primary Key

This is the query most DBAs keep handy. It lists every regular table, in every user schema, that has no primary key:

SELECT n.nspname AS schema_name,
       c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND NOT EXISTS (
      SELECT 1
      FROM pg_constraint con
      WHERE con.conrelid = c.oid
        AND con.contype = 'p'
  )
ORDER BY 1, 2;

 schema_name | table_name
-------------+------------
 public      | app_logs

What the filters do:

• relkind IN ('r', 'p') limits the results to regular tables and partitioned tables. Views, indexes, and sequences are skipped.
• The nspname filters skip PostgreSQL’s own system tables.

It’s worth running this before you set up logical replication, and from time to time on databases that many teams share.

Primary Key vs. Unique Index: Don’t Mix Them Up

Sometimes a table has no primary key but does have a unique index:

CREATE TABLE users (
    email  text,
    name   text
);
CREATE UNIQUE INDEX users_email_idx ON users (email);

\d users shows:

Indexes:
    "users_email_idx" UNIQUE, btree (email)

It says UNIQUE, not PRIMARY KEY. The difference matters:

	Primary Key	Unique Index
Duplicates allowed?	No	No
NULL allowed?	No	Yes, and multiple NULLs by default
How many per table?	One	As many as you like
Used by logical replication by default?	Yes	Only if you set it as REPLICA IDENTITY

So a unique index is close to a primary key, but not the same thing. The queries in Methods 2 and 4 correctly treat this table as having no primary key.

Why Missing Primary Keys Cause Trouble

You might wonder if it really matters. Here’s what tends to go wrong:

• Logical replication breaks. Tools like built-in logical replication and pglogical need a way to find the row on the other side. Without a primary key or replica identity, UPDATE and DELETE on that table fail with an error.
• Duplicates creep in. Nothing stops the same row from being inserted twice.
• Tools struggle. Many ORMs and admin GUIs need a primary key to edit or delete a single row.

How to Add a Primary Key If One Is Missing

First, make sure the column you want to use is actually unique and not null:

-- Any duplicates?
SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1;

-- Any NULLs?
SELECT count(*) FROM users WHERE email IS NULL;

If both queries come back clean, add the key:

ALTER TABLE users ADD PRIMARY KEY (email);

If there’s no natural key, like in our app_logs table, add a new ID column:

ALTER TABLE app_logs
    ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

On a large, busy table, be careful. Adding a primary key locks the table and has to scan it, and adding a new column with a value for every row rewrites the whole table. Do it during a quiet period, and test on a copy first.

Summary

• One table, quick look: run \d tablename and look for PRIMARY KEY.
• Yes or no in SQL or a script: use pg_constraint with contype = 'p'.
• Portable SQL: use information_schema, but remember it hides tables you can’t access.
• Audit the whole database: use the NOT EXISTS query in Method 4.

A unique index is not a primary key. If replication or your tools need one, add a real PRIMARY KEY.