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@w3wiki.net', '(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.