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.

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','[email protected]', '13333333333');

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

INSERT INTO staff(first_name, last_name, email, mobile_phone)
VALUES ('Jack','W','[email protected]', '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.

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.