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.
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 );
To show the indexes of the employees table, you use the following statement:
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';
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', 'email@example.com', '(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', 'firstname.lastname@example.org', '(408)-555-1234');
This should raise an error.
As we can observe the behaviour is exactly what is expected of the unique index.