August 2, 2023
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 NULL
s 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.