Summary: in this tutorial, you will learn about PostgreSQL UNIQUE constraint to make sure that the value in a column or a group of columns is unique in a table.
Sometimes, you want to ensure that the value in a column or a group of columns is unique across the whole table such as email address, username, employee id, etc. PostgreSQL provides you with UNIQUE constraint to make that the uniqueness of the data is maintained correctly.
With UNIQUE constraint, every time you insert a new row, PostgreSQL checks if the value is already in the table. If it found that the new value is already there, it would give back an error message and reject the changes. The same process is carried out for the update existing data.
When you add a UNIQUE constraint to a column or a group of columns, PostgreSQL will create a btree index on the respective column or a group of columns automatically.
PostgreSQL UNIQUE constraint example
The following CREATE TABLE statement creates a new table named person with a UNIQUE constraint applied to the email column.
1 2 3 4 5 6 | CREATE TABLE person ( id serial PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (50) UNIQUE ); |
The UNIQUE constraint can be rewritten as the table constraint as following:
1 2 3 4 5 6 7 | CREATE TABLE person ( id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (50), UNIQUE(email) ); |
First, we insert a new row into the person table using INSERT statement:
1 2 3 4 5 6 7 |
Second, we insert another row with duplicate email.
1 2 3 4 5 6 7 |
PostgreSQL gives back an error message.
1 2 | [Err] ERROR: duplicate key value violates unique constraint "person_email_key" DETAIL: Key (email)=(j.doe@postgresqltutorial.com) already exists. |
Applying UNIQUE constraint on multiple columns
PostgreSQL allows you to apply a UNIQUE constraint to a group of columns using the following syntax:
1 2 3 4 5 6 | CREATE TABLE table ( c1 data_type, c2 data_type, c3 data_type, UNIQUE (c2, c3) ); |
The combination of values in column c2 and c3 will be unique across the whole table. The value of the column c2 or c3 needs not to be unique.
Adding unique constraint using unique index
Sometimes, you may want to add a unique constraint to a column or a group of columns using existing unique index. Let’s take a look at the following example.
First, suppose we have a table named equipment:
1 2 3 4 5 | CREATE TABLE equipment ( id serial PRIMARY KEY, name VARCHAR (50) NOT NULL, equip_id VARCHAR (16) NOT NULL ); |
Second, we create a unique index based on the equip_id column.
1 2 | CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id ON equipment (equip_id); |
Third, we add a unique constraint to the equipment table using the equipment_equip_id index.
1 2 3 | ALTER TABLE equipment ADD CONSTRAINT unique_equip_id UNIQUE USING INDEX equipment_equip_id; |
Notice that the ALTER TABLE statement needs an exclusive lock on the table. If you have many pending transactions, it will wait for those to complete before changing the table. You should check the pg_stat_activity table to see how many pending transactions are in place using the following query:
1 2 3 4 5 6 7 | SELECT datid, datname, usename, state FROM pg_stat_activity; |
You should look at the result to find the state column with the value idle in transaction. Those are the transactions that are pending to complete.
In this tutorial, we have shown you how to use UNIQUE constraint to make the value of a column or a group of columns unique across the table.