PostgreSQL UNIQUE Constraint (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, the **UNIQUE constraint is a powerful tool used to ensure that values stored in a column or a group of columns are unique across rows in a table. This constraint is essential for maintaining data integrity, especially when certain data should not be duplicated. For instance, if you're storing email addresses, you wouldn't want the same email to be associated with multiple users. The **UNIQUE constraint helps you enforce this rule at the database level.

How the UNIQUE Constraint Works

Every time you insert a new row into a table with a **UNIQUE **constraint, **PostgreSQL checks if the value already exists in the table. If the value is found to be a duplicate, PostgreSQL denies the insertion or update and issues an error. This ensures that no duplicate data can be entered, keeping your data consistent and reliable.

**Syntax:

**UNIQUE(column);

or,

variable_name Data Type **UNIQUE;

PostgreSQL UNIQUE Constraint Examples

Now let's look into some examples of the UNIQUE Constraint in PostgreSQL to better understand the concept.

**Example 1: Applying a UNIQUE Constraint to a Single Column

In this example we will create a new table named 'person' with a UNIQUE constraint for the email column using the below command:

PostgreSQL `

CREATE TABLE person ( id serial PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (50) UNIQUE ); INSERT INTO person(first_name, last_name, email) VALUES ( 'Raju', 'Kumar', 'rajukumar@gmail.com' );

`

Now if we try to insert the same email, PostgreSQL should raise an error. So let's do so.

**INSERT INTO person(first_name, last_name, email) **VALUES ( 'Nikhil', 'Aggarwal', 'rajukumar@gmail.com' );

**Output:

**ERROR: duplicate key value violates unique constraint "person_email_key" **DETAIL: Key (email)=(rajukumar@gmail.com) already exists.

PostgreSQL UNIQUE Constraint Example

**Example 2: Applying a UNIQUE Constraint to a Different Column

PostgreSQL also allows users to create a UNIQUE constraint to the '**first_name' **using the below commands:

PostgreSQL `

CREATE TABLE person ( id SERIAL PRIMARY KEY, first_name VARCHAR (50), last_name VARCHAR (50), email VARCHAR (50), UNIQUE( first_name) ); INSERT INTO person(first_name, last_name, email) VALUES ( 'Raju', 'Kumar', 'rajukumar@gmail.com' );

`

Now if we try to insert the same email, PostgreSQL should raise an error. So let's do so.

**INSERT INTO person(first_name, last_name, email) **VALUES ( 'Nikhil', 'Aggarwal', 'nikhilagg@gmail.com' ), ( 'Raju', 'Verma', 'rajuverma@gmail.com' );

This should raise an error for the '**first_name' of Raju Verma as Raju Kumar already exists.

**Output:

**ERROR: duplicate key value violates unique constraint "person_first_name_key" **DETAIL: Key (first_name)=(Raju) already exists.

PostgreSQL UNIQUE Constraint Example

Important Points About PostgreSQL UNIQUE Constraint