PostgreSQL UNIQUE Index

In this section, we are going to understand the working of the UNIQUE Index, which is used to make sure the individuality of data in various columns.

The examples of the PostgreSQL Unique Index, and see example of using single and multi-Column PostgreSQL UNIQUE index.

What is PostgreSQL Unique Index?

In PostgreSQL, the UNIQUE index is used to ensure data value’s uniqueness in one or several columns.

In other words, we can say that the Unique Index is generated to get data integrity and improve performance. And it doesn’t allow us to insert a duplicate value in the table.

If we specify the UNIQUE index for one column, then the column cannot store various rows with a similar value.

If we specify the UNIQUE index for more than one column, then the combined values in these columns cannot be replicated in various rows.

And, PostgreSQL generates a similar UNIQUE index automatically if we specify a unique constraint or a primary key for a particular table.

As we already know, PostgreSQL treats NULL as a different value. Hence, it can contain several NULL values in a column with a UNIQUE index.

Syntax of PostgreSQL Create Unique Index command

The syntax for creating a unique Index command is as follows:

CREATE UNIQUE INDEX index_name    

on table_name (column_name);

Example of PostgreSQL Unique Index

Let us see a sample example to understand the working of the PostgreSQL CREATE Unique Index command.

We are creating one new table as Consumer with the CREATE command’s help and inserting some values using the INSERT command.

To create Consumer table into an Organization database, we use the CREATE command.

But, before creating the Consumer table, we will use the DROP TABLE command if a similar table is already existing in the Organization database.

1. DROP TABLE IF EXISTS Consumer;  

Output

After executing the above command, we will get the following window message: The Consumer table does not exist.

The Consumer table contains various columns such as consumer_id, first_name, last_name, and Mobile_number column where the consumer_id is the Primary key column. And for the Mobile_number column, we have used the Unique constraint.

Hence, PostgreSQL will create the two UNIQUE indexes, one for each column.

CREATE TABLE Consumer (  

    consumer_id SERIAL PRIMARY KEY,  

    first_name VARCHAR(255) NOT NULL,  

    last_name VARCHAR(255) NOT NULL,  

    Mobile_number VARCHAR(20) UNIQUE  

);

Output

On executing the above command, we will get the following message: the Consumer table has been created successfully into the Organization database.

If we want to show indexes of the Consumer table, we can use the below command:

SELECT tablename, indexname, indexdef   

FROM pg_indexes   

WHERE tablename = 'consumer';

Output

After executing the above command, we will get the following output, which displays all the index details in the Consumer table.

In PostgreSQL unique index section, we will see an example of the single and multi-column index as well:

Example of using Single-Column PostgreSQL UNIQUE index

In the below example, we are trying to add one new column as the Email column to the Consumer table using the ALTER table command, as shown in the following command:

ALTER TABLE Consumer  

ADD email VARCHAR(255);

Output

We will get the following message on executing the above command: The email column has been added to the Consumer table successfully.

We will specify a UNIQUE index for the email column to ensure that the email is different for all consumers, as we can see in the below command:

CREATE UNIQUE INDEX idex_consumer_email  

ON Consumer(email);

Output

After implementing the above command, we will get the following message: The idex_consumer_email index has been created successfully.

After creating the particular index successfully, we will follow the below steps to check the following:

Step1: Inserting a new row

Firstly, we will insert a new row into the Consumer table with the INSERT command’s help, as shown in the following command:

INSERT INTO Consumer(first_name, last_name, mobile_number, email)  

VALUES ('Mike','Ross', '(305)-444-5432','[email protected]');

Output

After implementing the above command, we will get the following message window, which displays that the values have been inserted successfully into the Consumer table.

Step2: Inserting another new row

Now, we will try to insert another row with the same email id as [email protected]:

INSERT INTO Consumer(first_name, last_name, mobile_number, email)  

VALUES ('Mike','Taylor','(305)-333-9876','[email protected]');

Output

On executing the above command, the PostgreSQL will raise an error because of the replica of email id, which means that the duplicate key value violates unique constraint “idex_consumer_email” as the Key (email)=([email protected]) already exists.

Example of using multi-Column PostgreSQL UNIQUE index

In the below example, we will add two new columns as address and work_address column to the Consumer table with the ALTER table command’s help, as shown in the following command:

ALTER TABLE Consumer  

ADD address VARCHAR(255),  

ADD Work_address VARCHAR(200);

Output

After executing the above command, we will get the following message: address and work_address. The column has been added to the Consumer table successfully.

Note: Several consumers can have a similar work address, but they cannot have a similar address and here, the address refers to the home address.

So, in such a case, we can specify a UNIQUE index on both address and work_address columns, as shown in below command:

CREATE UNIQUE INDEX idex_consumer_address  

ON Consumer(address, work_address);

Output

After implementing the above command, we will get the following message: the idex_consumer_address unique index has been created successfully.

Now, we will follow the below steps to test the working of the index:

Step1: inserting a new row

Firstly, we will insert a new row into the Consumer table with the INSERT command’s help, as shown in the following command:

INSERT INTO Consumer(first_name, last_name, address, work_address)  

VALUES ('Olivia','Smith','Boston','Brookline'); 

Output

On executing the above command, we will get the following message window, which displays that the values have been inserted successfully into the Consumer table.

Step2: Inserting another new row

Now, we will try to insert another row with the same work_address and different address, as we can see in the below command:

INSERT INTO Consumer(first_name, last_name, address, work_address)  

VALUES ('Olivia','Smith','Los Angeles','Brookline');

Output

After implementing the above command, we will get the below message window: the particular values have been inserted successfully as the combination of values in address and work_address columns are unique.

Step3: Inserting another new row again

In this step, we will insert another new row, which has a similar value for both the address and work_address columns that are already present in the Consumer table as we can see in the below command:

INSERT INTO Consumer(first_name, last_name, address, work_address)  

VALUES ('William','Smith','Los Angeles','Brookline'); 

Output

On executing the above command, the PostgreSQL will raise an error because of the replica of address and work_address, which means that the duplicate key value violates unique constraint “idex_consumer_address” as the Key (address, work_address)=(Los Angeles, Brookline) already exists.

Overview

In the PostgreSQL Unique Index section, we have learned the following topics:

• The PostgreSQL Unique index is used to ensure the uniqueness of data values in one or several columns.
• The PostgreSQL Create Unique index command is used to create a unique index with an example of it.
• We have also seen the example of using single and multi-Column PostgreSQL UNIQUE index.