Introduction to PostgreSQL UNIQUE index

The PostgreSQL unique index enforces the uniqueness of values in one or multiple columns.

To create a unique index, you use the followingCREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX index_nameON table_name (column [,...])[ NULLS [ NOT ] DISTINCT ];

In this syntax:

• First, specify the index name in theCREATE UNIQUE INDEX statement.
• Second, provide the name of the table along with a list of indexed columns in the ON clause.
• Third, theNULL NOT DISTINCT option treats nulls as equal, whereasNULLS DISTINCT treats nulls as distinct values. By default, the statement usesNULLS DISTINCT, meaning that the indexed column may contain multiple nulls.

PostgreSQL offersmultiple index types, but only the B-tree index type supports unique indexes.

When you define a unique index for a column, the column cannot store multiple rows with the same values.

If you define a unique index for two or more columns, the combined values in these columns cannot be duplicated in multiple rows.

When you define aprimary key or aunique constraint for a table, PostgreSQL automatically creates a corresponding unique index.

PostgreSQL UNIQUE index examples

Let's explore some examples of using the PostgreSQL unique indexes.

1) Unique indexes for a primary key column and a column with a unique constraint

First,create a table calledemployees :

CREATE TABLE employees (    employee_idSERIAL PRIMARY KEY,    first_nameVARCHAR(255)NOT NULL,    last_nameVARCHAR(255)NOT NULL,    emailVARCHAR(255)UNIQUE);

In this statement, theemployee_id is theprimary key column andemail column has aunique constraint, therefore, PostgreSQL creates twoUNIQUE indexes, one for each column.

Second, show the indexes of theemployees table:

SELECT    tablename,    indexname,    indexdefFROM    pg_indexesWHERE    tablename= 'employees';

Here is the output:

tablename |      indexname      |                                     indexdef-----------+---------------------+---------------------------------------------------------------------------------- employees | employees_pkey      | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (employee_id) employees | employees_email_key | CREATE UNIQUE INDEX employees_email_key ON public.employees USING btree (email)(2 rows)

2) Using PostgreSQL UNIQUE index for single column example

First,add a column namedmobile_phone to theemployees table:

ALTER TABLE employeesADD mobile_phone VARCHAR(20);

To ensure that the mobile phone numbers are distinct for all employees, you can define a unique index for themobile_phone column using theCREATE INDEX statement.

Second, create a unique index on themobile_phone column of theemployees table:

CREATE UNIQUE INDEX idx_employees_mobile_phoneON employees(mobile_phone);

Third,insert a new row into theemployees table:

INSERT INTO employees(first_name, last_name, email, mobile_phone)VALUES ('John','Doe','john.doe@example.com','(408)-555-1234');

Fourth, attempt to insert another row with the same phone number:

INSERT INTO employees(first_name, last_name, email, mobile_phone)VALUES ('Jane','Doe','jane.doe@example.com','(408)-555-1234');

PostgreSQL issues the following error due to the duplicate mobile phone number:

ERROR:  duplicatekey value violatesunique constraint "idx_employees_mobile_phone"DETAIL:Key (mobile_phone)=((408)-555-1234) alreadyexists.

3) Using PostgreSQL UNIQUE index for multiple columns

First,add two new columns calledwork_phone andextension to theemployees table:

ALTER TABLE employeesADD work_phoneVARCHAR(20),ADD extensionVARCHAR(5);

Multiple employees can share the same work phone number. However, they cannot have the same extension number.

To enforce this rule, you can define a unique index on bothwork_phone andextension columns.

Next, create a unique index that includes bothwork_phone andextension columns:

CREATE UNIQUE INDEX idx_employees_workphoneON employees(work_phone, extension);

Then, insert a row into theemployees table:

INSERT INTO employees(first_name, last_name, work_phone, extension)VALUES('Lily','Bush','(408)-333-1234','1212');

After that, insert another employee with the same work phone number but a different extension:

INSERT INTO employees(first_name, last_name, work_phone, extension)VALUES('Joan','Doe','(408)-333-1234','1211');

The statement works because the combination of values in thework_phone andextension column is unique.

Finally, attempt to insert a row with the same values in bothwork_phone andextension columns that already exist in theemployees table:

INSERT INTO employees(first_name, last_name, work_phone, extension)VALUES('Tommy','Stark','(408)-333-1234','1211');

PostgreSQL issued the following error:

ERROR:  duplicate key value violates unique constraint "idx_employees_workphone"DETAIL:  Key (work_phone,extension)=((408)-333-1234, 1211) already exists.

Summary

• Use a PostgreSQL unique index to enforce the uniqueness of values in a column or a set of columns.
• PostgreSQL automatically creates a unique index for a primary key column or a column with a unique constraint.

Last updated onFebruary 28, 2024

On this page

• Introduction to PostgreSQL UNIQUE index
• PostgreSQL UNIQUE index examples
• Summary