Indexes Kinds UNIQUE INDEX · NULL · partial

UNIQUE Index

An index that doubles as a uniqueness rule. Speed up lookups and reject duplicates in one declaration. Plus the NULL-is-distinct gotcha and how partial unique indexes solve problems constraints can't.

What a UNIQUE index does

A UNIQUE index does two jobs at once: it speeds up lookups on the indexed column (like any index) and enforces that no two rows share the same value. Try to insert or update a duplicate and PostgreSQL rejects the change with a uniqueness violation.

It's the underlying mechanism behind UNIQUE and PRIMARY KEY constraints — they create unique B-tree indexes for you under the hood.

Syntax

CREATE UNIQUE INDEX index_name
ON table_name (column1 [, column2, ...]);

📌 Only B-tree supports unique. Other index types (Hash, GIN, GiST, BRIN) cannot enforce uniqueness — only B-tree can. Since B-tree is the default, you almost never have to think about this.

Example 1 — single-column unique index

SQL — Create & insert

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    full_name    VARCHAR(50),
    email        VARCHAR(100)
);

-- Create a unique index on email
CREATE UNIQUE INDEX idx_customers_email
    ON customers (email);

-- First insert succeeds
INSERT INTO customers (full_name, email)
VALUES ('Alice', 'alice@example.com');

-- Duplicate email rejected
INSERT INTO customers (full_name, email)
VALUES ('Alice 2', 'alice@example.com');
-- ERROR: duplicate key value violates unique constraint "idx_customers_email"

Example 2 — composite unique index

Combine multiple columns into a single unique index when the uniqueness rule spans more than one column.

SQL

-- One subscription per (user, plan) — but a user can have many plans
CREATE UNIQUE INDEX idx_subs_user_plan
    ON subscriptions (user_id, plan_id);

Each column on its own may repeat — only the combination has to be unique.

NULLs and uniqueness

By default, PostgreSQL treats every NULL as distinct from every other NULL — so you can have multiple rows with NULL in a uniquely-indexed column without any duplication error.

SQL

-- All three succeed even though email is NULL each time
INSERT INTO customers (full_name, email) VALUES ('Bob',   NULL);
INSERT INTO customers (full_name, email) VALUES ('Carol', NULL);
INSERT INTO customers (full_name, email) VALUES ('Dan',   NULL);

💡 PostgreSQL 15+ — NULLS NOT DISTINCT. If you want at most one NULL row, declare the index with NULLS NOT DISTINCT:
CREATE UNIQUE INDEX idx_x ON t (col) NULLS NOT DISTINCT;

UNIQUE constraint vs UNIQUE index

You can enforce uniqueness either way and the result is mechanically identical — both produce a unique B-tree index. The differences are stylistic:

UNIQUE constraint	UNIQUE index
Declared inside `CREATE TABLE` or with `ALTER TABLE … ADD CONSTRAINT`	Created with `CREATE UNIQUE INDEX`
Shows up in `information_schema.table_constraints`	Shows up only as an index in `pg_indexes`
Foreign keys can reference it directly	Foreign keys cannot reference an index alone
Idiomatic for "this is part of the data model"	Idiomatic for partial uniqueness or expression-based uniqueness

If you need a foreign key to point at the column, choose the constraint form. Otherwise either works — pick what reads better.

Example 3 — partial unique index

A partial unique index lets you enforce uniqueness only on a subset of rows — useful for soft-deleted data, draft records, or any "active rows only" rule. (Constraint form can't do this; only the index form can.)

SQL

-- Only one ACTIVE subscription per user; any number of cancelled ones
CREATE UNIQUE INDEX idx_one_active_sub
    ON subscriptions (user_id)
    WHERE status = 'active';

Recap

A unique index speeds up lookups and rejects duplicates.
Only B-tree indexes can be unique.
NULLs are distinct by default — use NULLS NOT DISTINCT to change that.
Functionally equivalent to a UNIQUE constraint, but the index form supports partial / expression uniqueness.
Use the constraint form when a FK needs to reference it.