PostgreSQL UNIQUE Index (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, a **UNIQUE index is used to ensure that the values in one or more columns are unique across the rows in a table. This is essential for maintaining **data integrity and avoiding **duplicate entries.

This article will provide a detailed overview of **UNIQUE indexes, including **syntax, **examples, and best practices. By using a **UNIQUE index, we enforce that each value in one or more specified columns is distinct across all rows, which is essential for consistent **data management.

What is the PostgreSQL UNIQUE Index ?

A UNIQUE index is a **database constraint that enforces the **uniqueness of values in one or more columns within a **PostgreSQL table. If any attempt is made to **insert or **update rows such that **duplicate values exist in a column (or columns) defined with a **UNIQUE index, PostgreSQL will raise an error. This ensures the integrity of data, preventing unwanted duplicates.

**Syntax

CREATE UNIQUE INDEX index_name
ON table_name(column_name, [...]);

**Note: Only **B-tree indexes can be declared as unique indexes. B-tree is the default and most commonly used indexing method.

Why Use UNIQUE Indexes?

Using UNIQUE indexes in PostgreSQL offers several benefits:

**Data Integrity: Ensures no duplicate values are allowed in the specified columns.
**Performance: Speeds up search queries for unique fields by reducing the need for additional duplicate data checks.
**Efficient Queries: Enables efficient lookups for unique entries like email addresses or user IDs, often used in WHERE or JOIN clauses.

Examples of PostgreSQL UNIQUE Index

Let us take a look at some of the examples of the **UNIQUE **Index in **PostgreSQL to better understand the concept. These examples illustrate how to **create, **manage, and test **unique constraints in real scenarios.

**Example 1: Creating a Table with UNIQUE Constraints

The following statement creates a table called employees. The '**employee_id' column is defined as the primary key. The '**email' column has a unique constraint, ensuring that no two employees can have the same email address. Therefore, PostgreSQL created two **UNIQUE indexes, one for each column.

**Query:

CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
);.

Query to View Indexes:

To show the indexes of the employees table, use the following statement.

SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'employees';

**Output

PostgreSQL UNIQUE Index Example

**Explanation:

This will show the indexes associated with the employees table, including the automatically created **unique indexes.

**Example 2: Adding a UNIQUE Index to an Existing Column

The following statement adds the '****mobile_phone'**column to the '**employees' table that we created in the above example:

**1. Add the New Column:

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:

**2. Create a UNIQUE Index on mobile_phone:

CREATE UNIQUE INDEX idx_employees_mobile_phone
ON employees(mobile_phone);

**3. Insert Data to Test the UNIQUE Constraint:

Now let's test the **UNIQUE constraint by inserting new rows:

**Insert a new row into the employees table:

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Raju', 'kumar', 'raju.kumar@geeksforgeeks.org', '(408)-555-1234');

**Attempt to insert another row with the same phone number:

INSERT INTO employees(first_name, last_name, email, mobile_phone)
VALUES ('Nikhil', 'Aggarwal', 'nikhil.aggarwal@gfg.org', '(408)-555-1234');

This should raise an error indicating a violation of the unique constraint on the '**mobile_phone' column.

**Output

PostgreSQL UNIQUE Index Example

**Explanation:

The behavior is exactly as expected for a '**UNIQUE' index, preventing duplicate entries.

Important Points About PostgreSQL UNIQUE Index

**UNIQUE index guarantees that the values in one or more columns are unique across all rows in a table.
Only **B-tree indexes can be declared as unique indexes.
If an attempt is made to insert or update a row that would result in duplicate values in a **UNIQUE index column, PostgreSQL raises an error and the operation fails.
**UNIQUE indexes can be combined with other constraints, such as NOT NULL, to enforce stricter data integrity rules.

Conclusion

**UNIQUE indexes in PostgreSQL provide a powerful mechanism for maintaining **data integrity, enforcing **unique constraints, and improving query performance. They are especially useful for fields that should not have **duplicate values, such as **primary keys, **email addresses, and usernames. By understanding how to create, apply, and manage unique indexes, we can ensure the reliability of our PostgreSQL database.