PostgreSQL – UNIQUE Index

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

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', '[email protected]', '(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', '[email protected]', '(408)-555-1234');

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

Output

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.