Skip to content
Related Articles

Related Articles

PostgreSQL – UNIQUE Index
  • Last Updated : 28 Aug, 2020
GeeksforGeeks - Summer Carnival Banner

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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :