PostgreSQL Unique Index

Summary: in this tutorial, you’ll learn how to use the PostgreSQL unique index to ensure the values in one or more table columns are unique.

PostgreSQL Unique Index Overview #

A unique index ensures values in one or more columns are unique across all rows in a table, maintaining the integrity of your data.

You’ll encounter an error if you attempt to insert a value already in a column with a unique index.

To create a unique index, you use the CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX [index_name]
ON table_name (column1[, column2, ...])
[ NULLS [ NOT ] DISTINCT ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the index name after the CREATE UNIQUE INDEX keywords. If you omit it, PostgreSQL will automatically generate a name.
• Second, provide the table name on which you want to create the index.
• Third, list one or more columns included in the index.
• Third, the NULL NOT DISTINCT treats NULLs equally, while the NULLS DISTINCT considers NULLs as distinct values. The default is NULLS DISTINCT, meaning the index column may contain multiple NULLs.

PostgreSQL Unique Index Example #

First, create a table warehouse_workers to store the warehouse worker data:

CREATE TABLE IF NOT EXISTS warehouse_workers (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(25),
  extension VARCHAR(25)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a unique index for the email column:

CREATE UNIQUE INDEX ON warehouse_workers (email);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This unique index ensures the uniqueness of values in the email column of the table.

Third, insert a new row into the warehouse_workers table:

INSERT INTO
  warehouse_workers (name, email, phone, extension)
VALUES
  (
    'Emma Smith',
    '[email protected]',
    '(408)-111-2222',
    '101'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, attempt to insert a new row with the same email address:

INSERT INTO
  warehouse_workers (name, email, phone, extension)
VALUES
  (
    'Emma Scott',
    '[email protected]',
    '(408)-111-2222',
    '102'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued an error:

ERROR: duplicate key value violates unique constraint "warehouse_workers_email_idx"
DETAIL: Key (email)=([email protected]) already exists.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The unique index warehouse_workers_email_idx does not allow duplicate emails in the email column.

Creating a unique index on multiple columns #

First, create a unique index that includes the phone and extension columns:

CREATE UNIQUE INDEX ON warehouse_workers (phone, extension);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert a new row into the warehouse_workers table:

INSERT INTO
  warehouse_workers (name, email, phone, extension)
VALUES
  (
    'Mary Scott',
    '[email protected]',
    '(408)-111-2222',
    '102'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Even though the phone number of Mary is the same as Emma’s, the statement successfully inserted a row into the table.

The reason is that the new unique index enforces the unique values in the phone and extension columns.

Third, attempt to insert a new row with the same phone and extension:

INSERT INTO
  warehouse_workers (name, email, phone, extension)
VALUES
  (
    'Ava Garcia ',
    '[email protected]',
    '(408)-111-2222',
    '102'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: duplicate key value violates unique constraint "warehouse_workers_phone_extension_idx"
DETAIL: Key (phone, extension)=((408)-111-2222, 102) already exists.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The reason is that the pair of values (408)-111-2222, 102) already exists in the warehouse_workers table.

Unique index vs. primary key #

In PostgreSQL, a primary key is a special kind of unique index that:

• Does not allow NULLs.
• Can be applied to one or more columns per table.
• Implies uniqueness inherently

In contrast, a unique index allows NULLs unless you specify NULL NOT DISTINCT option.

Unique index vs. unique constraint #

Both unique index and unique constraint ensure the uniqueness of values in one or more columns within a table.

However, a unique constraint is a logical schema rule, while a unique index is a physical structure that PostgreSQL uses to enforce the uniqueness.

PostgreSQL automatically generates a unique index when you create a unique constraint.

Summary #

• Use a unique index to enforce the uniqueness values in one or more columns.

Quiz #