PostgreSQL Tutorial: 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.

Table of Contents

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, [...]);

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 NULLs as distinct values, 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
);

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';

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','john.doe@rockdata.net', '(408)-555-1234');

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

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Mary','Jane','mary.jane@rockdata.net', '(408)-555-1234');

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.

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');

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');

The statement works because the combination of values in the work_phone and extension column is 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');

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.

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.