PostgreSQL UNIQUE Index

Summary: in this tutorial, you will learn how to create a PostgreSQL UNIQUE index to ensure the uniqueness of values in one or more columns.

Introduction to PostgreSQL UNIQUE index

The PostgreSQL UNIQUE index enforces the uniqueness of values in one or multiple columns. To create a UNIQUE index, you can use the following syntax:

CREATE UNIQUE INDEX index_name
ON table_name(column_name, [...]);
Code language: CSS (css)

Note that only B-tree indexes can be declared as unique indexes.

When you define an UNIQUE index for a column, the column cannot store multiple rows with the same values.

If you define a UNIQUE index for two or more columns, the combined values in these columns cannot be duplicated in multiple rows.

PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with a UNIQUE index.

When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index.

PostgreSQL UNIQUE index examples

The following statement creates a table called employees :

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);
Code language: PHP (php)

In this statement, the employee_id is the primary key column and email column has a unique constraint, therefore, PostgreSQL created two UNIQUE indexes, one for each column.

To show indexes of the employees table, you use the following statement:

SELECT 
    tablename, 
    indexname, 
    indexdef 
FROM 
    pg_indexes 
WHERE 
    tablename = 'employees';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

PostgreSQL UNIQUE index – single column example

The following statement adds the mobile_phone column to the employees table:

ALTER TABLE employees
ADD mobile_phone VARCHAR(20);

To ensure that the mobile phone numbers are distinct for all employees, you define a UNIQUE index for the mobile_phone column as follows:

CREATE UNIQUE INDEX idx_employees_mobile_phone
ON employees(mobile_phone);

Let’s take a test.

First, insert a new row into the employees table:

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('John','Doe','[email protected]', '(408)-555-1234');
Code language: JavaScript (javascript)

Second, attempt to insert another row with the same phone number::

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Mary','Jane','[email protected]', '(408)-555-1234');
Code language: JavaScript (javascript)

PostgreSQL issues the following error due to the duplicate mobile phone number:

ERROR:  duplicate key value violates unique constraint "idx_employees_mobile_phone"
DETAIL:  Key (mobile_phone)=((408)-555-1234) already exists.
Code language: SQL (Structured Query Language) (sql)

PostgreSQL UNIQUE index – multiple columns example

The following statement adds two new columns called work_phone and extension to the employees table:

ALTER TABLE employees
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 a UNIQUE index on both work_phone and extension columns:

CREATE UNIQUE INDEX idx_employees_workphone
ON employees(work_phone, extension);

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

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Lily', 'Bush', '(408)-333-1234','1212');
Code language: JavaScript (javascript)

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

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Joan', 'Doe', '(408)-333-1234','1211');
Code language: JavaScript (javascript)

The statement works because the combination of values in the work_phone and extension column are unique.

Third, attempt to insert a row with the same values in both work_phone and extension columns that already exist in the employees table:

INSERT INTO employees(first_name, last_name, work_phone, extension)
VALUES('Tommy', 'Stark', '(408)-333-1234','1211');
Code language: JavaScript (javascript)

PostgreSQL issued the following error:

ERROR:  duplicate key value violates unique constraint "idx_employees_workphone"
DETAIL:  Key (work_phone, extension)=((408)-333-1234, 1211) already exists.
Code language: HTTP (http)

In this tutorial, you have learned how to use the PostgreSQL UNIQUE index to enforce the uniqueness of values in a column or a set of columns.