UNIQUE Constraint | PostgreSQL Tutorial

The UNIQUE constraint prevents duplicate values from being stored in a column or group of columns. Every time a row is inserted or updated, PostgreSQL checks the existing values and rejects the operation if a duplicate is found. PostgreSQL automatically creates a unique B-tree index to enforce this constraint efficiently.

Defining a UNIQUE constraint

As an inline column constraint:

CREATE TABLE person (
  id         SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  email      VARCHAR(50) UNIQUE
);

As a table-level constraint (equivalent, and required for multi-column uniqueness):

CREATE TABLE person (
  id         SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  email      VARCHAR(50),
  UNIQUE (email)
);

Duplicate value error

INSERT INTO person (first_name, last_name, email)
VALUES ('john', 'doe', '[email protected]');

-- Insert a second row with the same email:
INSERT INTO person (first_name, last_name, email)
VALUES ('jack', 'doe', '[email protected]');

-- ERROR:  duplicate key value violates unique constraint "person_email_key"
-- DETAIL:  Key (email)=([email protected]) already exists.

Composite UNIQUE constraint

Require that a combination of columns is unique (individual columns may repeat):

CREATE TABLE table_name (
  c1 data_type,
  c2 data_type,
  c3 data_type,
  UNIQUE (c2, c3)
);

Each individual value of c2 or c3 can repeat — only the pair must be unique.

Adding a UNIQUE constraint to an existing table

You can add the constraint directly with ALTER TABLE:

ALTER TABLE equipment ADD CONSTRAINT unique_equip_id UNIQUE (equip_id);

For tables with heavy concurrent traffic, create the unique index first with CONCURRENTLY (which avoids locking), then attach it as a constraint:

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id
ON equipment (equip_id);

ALTER TABLE equipment
ADD CONSTRAINT unique_equip_id
UNIQUE USING INDEX equipment_equip_id;

Note: ALTER TABLE ... ADD CONSTRAINT acquires an exclusive lock. Check pg_stat_activity for long-running transactions before running it on a production table.

Production tips

UNIQUE constraints allow multiple NULL values — NULL is not considered equal to NULL. If you want at most one NULL, use a partial unique index: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL;
Use the CONCURRENTLY index approach when adding uniqueness to large busy tables to avoid prolonged locks.
Name constraints explicitly (e.g. users_email_key) so migration rollbacks and error messages are self-descriptive.
A UNIQUE constraint automatically creates a unique index that can also serve lookup queries — no need to add a separate regular index on the same column.

Frequently Asked Questions

Does a PostgreSQL UNIQUE constraint allow NULL values?

Yes. UNIQUE constraints allow NULL values, and multiple rows can have NULL in a unique column because NULL is not considered equal to NULL. If you need to enforce at most one NULL, create a partial unique index with a WHERE col IS NOT NULL condition.

What is the difference between a UNIQUE constraint and a UNIQUE index in PostgreSQL?

They are functionally equivalent — PostgreSQL implements UNIQUE constraints by creating a unique index internally. The difference is declarative: a UNIQUE constraint is defined at the schema level and shows up in constraint listings, while a standalone UNIQUE index is just an index. You can promote a UNIQUE index to a constraint with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX.

Can I add a UNIQUE constraint on multiple columns?

Yes. Define it as a table constraint listing all columns: UNIQUE (col1, col2). The combination of values must be unique, but individual columns can repeat independently.

Does adding a UNIQUE constraint lock the table?

Yes. ALTER TABLE ... ADD CONSTRAINT UNIQUE acquires an ACCESS EXCLUSIVE lock and validates all existing rows. On large tables, create the unique index first using CREATE UNIQUE INDEX CONCURRENTLY (which uses a weaker lock), then attach it as a constraint to minimise downtime.

How do I remove a UNIQUE constraint in PostgreSQL?

Use ALTER TABLE table_name DROP CONSTRAINT constraint_name; Find the constraint name with \d table_name in psql.