In PostgreSQL, both UNIQUE indexes and UNIQUE constraints can be used to enforce uniqueness on columns.

So you may be wondering; if that’s true, why have both? And are there any circumstances that we should choose one over the other?

Let’s find out.

UNIQUE Index vs. UNIQUE Constraint

Here’s a quick rundown of the main differences between the UNIQUE constraint and UNIQUE index in PostgreSQL:

• UNIQUE Constraint:
  • Part of the ANSI SQL standard.
  • Ensures that all values in the specified columns are unique across all rows of the table.
  • Automatically creates a UNIQUE index in the background to enforce this uniqueness.
  • Ideal when we want to guarantee data integrity at the schema level.
• UNIQUE Index:
  • An index that enforces uniqueness on a column or set of columns, created explicitly by the user using the CREATE UNIQUE INDEX statement.
  • Not part of the SQL standard (but widely implemented across major RDBMSs).
  • Often used for more advanced scenarios, such as partial indexes or indexing expressions (not possible with a UNIQUE constraint).
  • Gives more control over how uniqueness is enforced and is flexible in scenarios where a constraint isn’t ideal.

Code Comparison

Here’s a quick comparison of the code used to create a UNIQUE constraint vs a UNIQUE index.

UNIQUE Constraint

When we create a table, we can create a UNIQUE constraint by simply using the UNIQUE keyword in the column’s definition. For example:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

That single keyword creates a UNIQUE constraint, as well as a UNIQUE index to enforce the uniqueness of the constraint.

Let’s use the \d command in psql to see the table definition:

\d employees

Output:

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default                     
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 email       | character varying(255) |           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

We can see that an index was automatically created for the UNIQUE constraint (and for the primary key constraint). The index for the UNIQUE constraint is defined as UNIQUE CONSTRAINT, and uses a b-tree index (which at the time of writing, is the only index type that can be declared unique).

UNIQUE Index

Here’s how we might do it with an index-only solution:

-- Drop the existing table
DROP TABLE employees;

-- Create the table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

-- Create the index
CREATE UNIQUE INDEX unique_lower_email
ON employees (LOWER(email));

One immediate observation might be that we can get the UNIQUE constraint implemented with a lot less code. That said, creating a UNIQUE constraint can become more verbose when using the CONSTRAINT clause to define further options (including an index to use for the constraint).

Anyway, let’s check the table definition:

\d employees

Output:

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default                     
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 email       | character varying(255) |           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "unique_lower_email" UNIQUE, btree (lower(email::text))

This time the index only has UNIQUE (not UNIQUE CONSTRAINT like with the constraint).

Why Use One Over the Other?

Most of the time we should probably choose the UNIQUE constraint to enforce uniqueness in our tables. This creates a UNIQUE index behind the scenes anyway.

But there may be times where creating a UNIQUE index by itself would make more sense. Here are some suggestions:

• Use a UNIQUE constraint when you want to enforce data integrity as part of the database schema.
  • It clearly conveys that the column(s) must be unique and should be constrained as such.
  • It aligns with SQL standards, which could be beneficial for compatibility and portability to other RDBMSs.
  • It creates a UNIQUE index behind the scenes anyway, so there’s no need to explicitly create an index separately (or at all).
• Use a UNIQUE index when you need more control over indexing behavior, such as:
  • Creating a partial unique index (e.g., only enforce uniqueness on “active” rows).
  • Enforcing uniqueness on an expression (e.g., LOWER(column_name)).
  • Fine-tuning performance in specific scenarios, since indexes directly affect how the query optimizer chooses execution plans.

Effect on the Query Optimizer

Both UNIQUE constraints and UNIQUE indexes help the query optimizer by providing information about the uniqueness of values, which can influence query plans. PostgreSQL can use this information to choose faster execution paths, especially when performing joins, aggregations, or lookups that rely on knowing whether certain rows are unique.

UNIQUE indexes can offer more flexibility to the optimizer due to partial indexes or indexing on expressions, which can lead to more optimized queries for specific workloads.

Unique Partial Index

As alluded to, a UNIQUE constraint cannot be partial, meaning it applies to all rows in the table.

A UNIQUE index, on the other hand, can be partial, meaning it applies only to a subset of rows based on a condition.

Here’s an example of a UNIQUE index that enforces uniqueness only on active users in a table:

CREATE UNIQUE INDEX unique_active_user_email
ON users (email)
WHERE status = 'active';

This provides flexibility to enforce unique rules only where needed, optimizing performance and allowing exceptions in data where appropriate.

PostgreSQL Recommendation

PostgreSQL advises against creating both a UNIQUE constraint and a UNIQUE index for the same column(s). The PostgreSQL documentation for UNIQUE indexes states:

There’s no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

The documentation suggests using UNIQUE indexes in more advanced cases where you need partial indexing:

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.