Unique Index vs Unique Constraint in Postgres

In Postgres, a unique constraint and a unique index enforce uniqueness the same way, because a unique constraint is implemented by creating a unique B-tree index. The difference is in what each can express: a constraint is part of the table's declared schema and can be deferrable and named in ON CONFLICT ON CONSTRAINT, while an index can be partial (where deleted_at is null), built on expressions (lower(email)), and created concurrently without blocking writes. Use a constraint for plain column uniqueness, and an index when you need a condition, an expression or a non-blocking build.

#What Postgres actually creates

Run both and look at the catalog:

create table users (
  id bigint generated always as identity primary key,
  email text not null,
  username text not null,
  phone text,
  deleted_at timestamptz
);

alter table users add constraint users_email_key unique (email);
create unique index users_username_idx on users (username);

select indexname, indexdef from pg_indexes where tablename = 'users';

     indexname      |                                indexdef
--------------------+-------------------------------------------------------------------------
 users_pkey         | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
 users_email_key    | CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email)
 users_username_idx | CREATE UNIQUE INDEX users_username_idx ON public.users USING btree (username)

Both produce a unique B-tree index. Now the constraints:

select conname, contype from pg_constraint where conrelid = 'users'::regclass;

     conname     | contype
-----------------+---------
 users_pkey      | p
 users_email_key | u

Only the constraint appears in pg_constraint (and in information_schema.table_constraints). The plain index is invisible there. That is the root of most practical differences.

A duplicate insert fails the same way either way:

ERROR:  duplicate key value violates unique constraint "users_username_idx"

Postgres says "unique constraint" even for a plain index, and uses the index's name.

#Unique index vs unique constraint: the differences

#When only an index will do

#Unique among active rows (partial)

Soft deletes are the classic case. You want one active account per e-mail, but deleted rows may repeat:

create unique index users_email_active_idx
  on users (email)
  where deleted_at is null;

A constraint cannot have a where clause.

#Case-insensitive uniqueness (expression)

create unique index users_email_lower_idx on users (lower(email));

Now [email protected] and [email protected] collide. The alternative is a citext column with a normal unique constraint, which also makes comparisons case-insensitive everywhere.

#Building on a busy table (concurrently)

alter table ... add constraint ... unique builds its index while holding a lock that blocks reads and writes on the table. On a large table that can stall your app. create unique index concurrently builds without blocking writes:

create unique index concurrently users_email_uidx on users (email);

It cannot run inside a transaction block, takes longer, and if it fails (for example because duplicates exist) it leaves an invalid index you must drop and recreate.

#When only a constraint will do

#Deferrable uniqueness

Swapping two values in a column with a unique rule fails row by row unless the check waits until commit:

create table playlist_items (
  playlist_id bigint not null,
  position int not null,
  track_id bigint not null,
  constraint playlist_position_unique unique (playlist_id, position)
    deferrable initially immediate
);

begin;
set constraints playlist_position_unique deferred;
update playlist_items set position = 2 where playlist_id = 1 and track_id = 10;
update playlist_items set position = 1 where playlist_id = 1 and track_id = 20;
commit;   -- checked here

Indexes cannot be deferred. One catch: a deferrable constraint cannot be used as the arbiter for insert ... on conflict, so you choose between deferral and upserts on that key.

#Naming the conflict target

insert into users (email, username) values ('[email protected]', 'ada')
on conflict on constraint users_email_key
do update set username = excluded.username;

on conflict on constraint only accepts constraints. With an index, use column inference instead, which works for both:

insert into users (email, username) values ('[email protected]', 'ada')
on conflict (email) do update set username = excluded.username;

For a partial index, repeat its predicate so Postgres can infer it:

insert into users (email, username) values ('[email protected]', 'ada')
on conflict (email) where deleted_at is null
do update set username = excluded.username;

Without the where, you get there is no unique or exclusion constraint matching the ON CONFLICT specification.

#NULLs

By default, NULLs are distinct: a unique column can hold any number of NULLs. Since Postgres 15 you can change that for both forms:

alter table users add constraint users_phone_key unique nulls not distinct (phone);
-- or
create unique index users_phone_idx on users (phone) nulls not distinct;

Before Postgres 15, the usual workaround was a partial index or a unique index on coalesce(phone, '').

#Foreign keys

A foreign key must reference columns that are unique. A primary key or unique constraint is the documented way, and Postgres also accepts a plain unique index as long as it is not partial, has no expressions and is not deferrable. Prefer a constraint here anyway: it documents intent, and schema tools reading information_schema will understand the relationship.

#The best of both: build concurrently, then attach

You can have a declared constraint without the blocking build. Create the index concurrently, then promote it:

set lock_timeout = '5s';

create unique index concurrently users_email_uidx on users (email);

alter table users
  add constraint users_email_key unique using index users_email_uidx;

The alter table is fast because the index already exists; Postgres renames the index to the constraint's name. The index must be a plain, non-partial B-tree on columns (no expressions) with default sort order. The same trick works for primary keys with primary key using index.

#Which one should you use?

• Plain uniqueness on one or more columns: constraint. It is self-documenting and works with on conflict on constraint, foreign keys and schema tools.
• Uniqueness with a condition or on an expression: unique index.
• Adding uniqueness to a large table in production: index concurrently, then add constraint ... using index if the shape allows it.
• Values that must swap within a transaction: deferrable constraint.

To drop them, match the form: alter table users drop constraint users_email_key; for a constraint (you cannot drop index an index that backs a constraint), and drop index concurrently users_username_idx; for an index.

#On StitchDB

StitchDB projects run standard Postgres, so everything above applies as written. Two platform details help:

• Safe schema changes. The SQL Editor, Database → Migrations and stitchdb db push check each statement against your table's size before it runs. Adding a unique constraint or primary key to a large table is flagged because it blocks reads and writes while the index builds, and the safer version offered is exactly create unique index concurrently followed by add constraint ... using index (safe schema changes).
• Upserts through the API. The REST API and SDKs upsert on a unique constraint or unique index over the conflict columns. A partial index cannot be matched there, because the request cannot carry its where clause, so for API upserts keep a full unique constraint on the conflict columns:

const { data, error } = await stitchdb
  .from('users')
  .upsert({ email: '[email protected]', username: 'ada' }, { onConflict: 'email' })
  .select()

See writing data for the upsert options, and the Postgres upsert guide for more on conflict patterns.

#FAQ

#Is a unique constraint faster than a unique index?

No. Both use the same kind of B-tree index, so lookups and enforcement cost the same.

#Does a unique constraint create an index?

Yes. Postgres creates a unique B-tree index with the constraint's name, so you do not need a separate index on those columns.

#Can I have a unique constraint on lower(email)?

Not directly; constraints only take columns. Use a unique index on lower(email), or make the column citext and add a normal unique constraint.

#Why does my upsert say there is no matching constraint?

The on conflict columns must exactly match a unique constraint or index. For a partial unique index, add its where clause to the on conflict target.

#How do I add a unique constraint without locking the table?

Create a unique index concurrently, then run alter table ... add constraint ... unique using index.

Try it on a real database: create a free project and run your migrations through safe schema changes.