PostgreSQL – UNIQUE Index

In PostgreSQL, the UNIQUE index to ensure the uniqueness of values in one or more columns. To create a UNIQUE index, you can use the following syntax:

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

Note: Only B-tree indexes can be declared as unique indexes.

Example 1:

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 the indexes of the employees table, you use the following statement:

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

Output:

Example 2:

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

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);

Now let’s test it. First, 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');

Second, 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.

Output:

As we can observe the behaviour is exactly what is expected of the unique index.