PostgreSQL Unique Indexes

PostgreSQL UNIQUE indexes are used to enforce uniqueness of values ​​in one or more columns.

Introduction to PostgreSQL UNIQUE Indexes

Only indexes of index type B-Tree can be declared as unique indexes. To create an UNIQUE index, you can use the following syntax:

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

The keyword UNIQUE is used to declare an index as unique.

If a column is defined as a unique index, then the column cannot store duplicated values.

If two or more columns are defined as unique indexes, the combined values ​​in those columns cannot be duplicated.

However, you can use multiple NULL values ​​in a column with an UNIQUE index.

By default, a unique index treats NULL values as distinct, so a column can contain multiple NULL values. For composite indexes, rows with a NULL in any key column are also considered distinct by default. PostgreSQL 15 and later support NULLS NOT DISTINCT when NULL should compare as equal:

CREATE UNIQUE INDEX contacts_email_unique
ON contacts (email) NULLS NOT DISTINCT;

If the table already contains duplicate NULL values, creating this index fails until those rows are corrected.

Unique indexes can also enforce uniqueness on an expression or on only some rows by using a WHERE predicate. When you define a primary key or unique constraint on a table, PostgreSQL automatically creates the corresponding UNIQUE index.

PostgreSQL UNIQUE indexes Examples

The following statement creates a table named staff:

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE
);

In this statement, the id column is a primary key and email has a unique constraint, so PostgreSQL creates two UNIQUE indexes.

To display indexes of the staff table, use the following statement:

SELECT
  tablename,
  indexname,
  indexdef
FROM
  pg_indexes
WHERE
  tablename = 'staff';

 tablename |    indexname    |                                indexdef
-----------+-----------------+-------------------------------------------------------------------------
 staff     | staff_pkey      | CREATE UNIQUE INDEX staff_pkey ON public.staff USING btree (id)
 staff     | staff_email_key | CREATE UNIQUE INDEX staff_email_key ON public.staff USING btree (email)
(2 rows)

Single Column Example

The following statement adds a column named mobile_phone to the staff table:

ALTER TABLE staff
ADD mobile_phone VARCHAR(20);

To ensure that all employees’ mobile numbers are different, you can define an UNIQUE index on this mobile_phone column as follows:

CREATE UNIQUE INDEX idx_staff_mobile_phone
ON staff(mobile_phone);

Let’s test it out.

First, insert a new row into the staff table:

INSERT INTO staff(first_name, last_name, email, mobile_phone)
VALUES ('Adam','Z','adam@sqliz.com', '13333333333');

Second, try inserting another row with the same phone number:

INSERT INTO staff(first_name, last_name, email, mobile_phone)
VALUES ('Jack','W','jack@sqliz.com', '13333333333');

Due to the duplicate mobile phone number, PostgreSQL has the following error:

ERROR:  duplicate key value violates unique constraint "idx_staff_mobile_phone"
DETAIL:  Key (mobile_phone)=(13333333333) already exists.

For a step-by-step way to locate the conflicting row and decide whether to reject, skip, or update a duplicate, see PostgreSQL Error 23505 troubleshooting. Browse the PostgreSQL error troubleshooting index for more fixes.

Multiple Column Example

The following statement will add two columns work_phone and extension to the staff table:

ALTER TABLE staff
ADD work_phone VARCHAR(20),
ADD extension VARCHAR(5);

Multiple employees can share the same work phone number. However, they cannot have the same extension number. To enforce this rule, you can define an UNIQUE index on work_phone and extension columns:

CREATE UNIQUE INDEX idx_staff_workphone
ON staff(work_phone, extension);

To test this index, first insert a row into the staff table:

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Lily', 'Bush', '1234567','3564');

Second, insert another employee with the same work phone number but a different extension:

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Joan', 'Doe', '1234567','3565');

The statement works because the combination of values ​​in work_phone and extension columns is unique.

Third, try inserting a row with the same value in the work_phone and extension columns in the staff table:

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Tommy', 'Stark', '1234567','3565');

PostgreSQL issues the following error:

ERROR:  duplicate key value violates unique constraint "idx_staff_workphone"
DETAIL:  Key (work_phone, extension)=(1234567, 3565) already exists.

Conclusion

This article discusses the use of PostgreSQL UNIQUE indexes, which are used to enforce uniqueness of values ​​in one or more columns.