Introduction to PostgreSQL UNIQUE constraint

Sometimes, you want to ensure that values stored in a column or a group of columns are unique across the whole table such as email addresses or usernames.

PostgreSQL provides you with theUNIQUE constraint that maintains the uniqueness of the data correctly.

When aUNIQUE constraint is in place, every time youinsert a new row, it checks if the value is already in the table. It rejects the change and issues an error if the value already exists. The same process is carried out forupdating existing data.

When you add aUNIQUE constraint to a column or a group of columns, PostgreSQL will automatically create aunique index on the column or the group of columns.

PostgreSQL UNIQUE constraint example

The following statementcreates a new table namedperson with aUNIQUE constraint for theemail column.

CREATE TABLE person (  idSERIAL PRIMARY KEY,  first_nameVARCHAR (50),  last_nameVARCHAR (50),  emailVARCHAR (50)UNIQUE);

Note that theUNIQUE constraint above can be rewritten as a table constraint as shown in the following query:

CREATE TABLE person (  idSERIAL PRIMARY KEY,  first_nameVARCHAR (50),  last_nameVARCHAR (50),  emailVARCHAR (50),  UNIQUE(email));

First, insert a new row into theperson table usingINSERT statement:

INSERT INTO person(first_name,last_name,email)VALUES('john','doe','john.doe@example.com');

Second, insert another row with a duplicate email.

INSERT INTO person(first_name,last_name,email)VALUES('jack','doe','john.doe@example.com');

PostgreSQL issued an error message.

[Err] ERROR:  duplicatekey value violatesunique constraint "person_email_key"DETAIL:Key (email)=(john.doe@example.com) alreadyexists.

Creating a UNIQUE constraint on multiple columns

PostgreSQL allows you to create aUNIQUE constraint to a group of columns using the following syntax:

CREATE TABLE table (    c1 data_type,    c2 data_type,    c3 data_type,    UNIQUE (c2, c3));

The combination of values in the columns 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 constraints using a unique index

Sometimes, you may want to add a unique constraint to an existing column or group of columns. Let's take a look at the following example.

First, suppose you have a table namedequipment:

CREATE TABLE equipment (  idSERIAL PRIMARY KEY,  name VARCHAR (50)NOT NULL,  equip_idVARCHAR (16)NOT NULL);

Second, create a unique index based on theequip_id column.

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_idON equipment (equip_id);

Third, add a unique constraint to theequipment table using theequipment_equip_id index.

ALTER TABLE equipmentADD CONSTRAINT unique_equip_idUNIQUE USING INDEX equipment_equip_id;

Notice that theALTER TABLE statement acquires an exclusive lock on the table. If you have any pending transactions, it will wait for all transactions to complete before changing the table. Therefore, you should check the pg_stat_activity table to see the current pending transactions that are ongoing using the following query:

SELECT  datid,  datname,  usename,  stateFROM  pg_stat_activity;

You should look at the result to find thestate column with the valueidle in transaction. Those are the transactions that are pending to complete.

Summary

• Use theUNIQUE constraints to enforce values stored in a column or a group of columns unique across rows within the same table.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL UNIQUE constraint
• PostgreSQL UNIQUE constraint example
• Creating a UNIQUE constraint on multiple columns
• Adding unique constraints using a unique index
• Summary