UNIQUE index in PostgreSQL ensures that all values in the indexed column (or combination of columns) are unique.
- Prevent duplicate values in a column.
- Enforce data integrity and Improve query performance.
- Create unique indexes on single or multiple columns.
Syntax
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
Where:
- index_name: The name of the unique index.
- table_name: The table on which the index is created.
- column_name: The column or columns whose values must remain unique.
Working
Consider the following employees table for the examples below:
Example 1: Create a UNIQUE Index on a Single Column
The following statement creates a unique index on the email column.
Query:
CREATE UNIQUE INDEX idx_email
ON employees (email);
Output:
- The statement creates a unique index on the Email column, preventing duplicate email addresses.
Example 2: Create a UNIQUE Index on Multiple Columns
The following statement creates a unique index on the department and email columns.
Query:
CREATE UNIQUE INDEX idx_department_email
ON employees (department, email);
Output:
Example 3: Insert a Duplicate Value
The following statement attempts to insert a duplicate email address.
Query:
INSERT INTO employees
VALUES
(105, 'Olivia Johnson', 'HR', 'john@example.com');
Output:
