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 use the following CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX index_name
ON table_name (column [, ...]) 
[ NULLS [ NOT ] DISTINCT ];Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the index name in the CREATE UNIQUE INDEX statement.
Second, provide the name of the table along with a list of indexed columns in the ON clause.
Third, the NULL NOT DISTINCT option treats nulls as equal, whereas NULLS DISTINCT treats nulls as distinct values. By default, the statement uses NULLS DISTINCT, meaning that the indexed column may contain multiple nulls.

PostgreSQL offers multiple index types, but only the B-tree index type supports unique indexes.

When you define a 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.

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

PostgreSQL UNIQUE index examples

Let’s explore some examples of using the PostgreSQL unique indexes.

1) Unique indexes for a primary key column and a column with a unique constraint

First, create 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: SQL (Structured Query Language) (sql)

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

Second, show the indexes of the employees table:

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

Here is the output:

 tablename |      indexname      |                                     indexdef
-----------+---------------------+----------------------------------------------------------------------------------
 employees | employees_pkey      | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (employee_id)
 employees | employees_email_key | CREATE UNIQUE INDEX employees_email_key ON public.employees USING btree (email)
(2 rows)Code language: PHP (php)

2) Using PostgreSQL UNIQUE index for single column example

First, add a column named mobile_phone to the employees table:

ALTER TABLE employees
ADD mobile_phone VARCHAR(20);Code language: SQL (Structured Query Language) (sql)

To ensure that the mobile phone numbers are distinct for all employees, you can define a unique index for the mobile_phone column using the CREATE INDEX statement.

Second, create a unique index on the mobile_phone column of the employees table:

CREATE UNIQUE INDEX idx_employees_mobile_phone
ON employees(mobile_phone);Code language: SQL (Structured Query Language) (sql)

Third, 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: SQL (Structured Query Language) (sql)

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

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Jane','Doe','[email protected]', '(408)-555-1234');Code language: SQL (Structured Query Language) (sql)

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)

3) Using PostgreSQL UNIQUE index for multiple columns

First, add two new columns called work_phone and extension to the employees table:

ALTER TABLE employees
ADD work_phone VARCHAR(20),
ADD extension VARCHAR(5);Code language: SQL (Structured Query Language) (sql)

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.

Next, create a unique index that includes both work_phone and extension columns:

CREATE UNIQUE INDEX idx_employees_workphone
ON employees(work_phone, extension);Code language: SQL (Structured Query Language) (sql)

Then, 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: SQL (Structured Query Language) (sql)

After that, 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: SQL (Structured Query Language) (sql)

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

Finally, 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: SQL (Structured Query Language) (sql)

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)

Summary

Use a PostgreSQL unique index to enforce the uniqueness of values in a column or a set of columns.
PostgreSQL automatically creates a unique index for a primary key column or a column with a unique constraint.

Was this tutorial helpful ?