PostgreSQL UNIQUE Constraint

Summary: in this tutorial, you will learn how to use the PostgreSQL UNIQUE constraint to ensure that all values in a specific column or a combination of columns are unique across the table.

Introduction to PostgreSQL UNIQUE constraint

In PostgreSQL, you can use a UNIQUE constraint to ensure that all values in a column or combination of columns are unique across the table.

Here’s the syntax for defining a UNIQUE constraint:

CREATE TABLE table_name (
  column1 data_type CONSTRAINT constraint_name UNIQUE,
  ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the constraint name for the UNIQUE constraint. When you insert or update a value to a new value that already exists in the table, PostgreSQL will issue an error that includes the constraint name. This constraint name helps you detect where the problem is faster.

Note that the CONSTRAINT constraint_name is optional. If you omit it, PostgreSQL will automatically generate a constraint name in the following format:

{table_name}_{column_name}_keyCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, use the UNIQUE keyword to instruct PostgreSQL that you are about to create a UNIQUE constraint for the column.

This syntax creates a column constraint because we define it as a part of a column definition.

You can define a UNIQUE constraint as a table constraint:

CREATE TABLE table_name (
  column1 data_type,
  ...,
  CONSTRAINT constraint_name UNIQUE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, we define the UNIQUE constraint after the column list.

In practice, you often define a UNIQUE table constraint when it includes two or more columns:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  CONSTRAINT constraint_name UNIQUE (column1, column2)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Defining a UNIQUE constraint that includes one column

First, create a table called brands to store brands:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The name column in this brands table has a UNIQUE constraint. This means that there will be no duplicate values in the name column.

If you insert a brand that already exists in the brands table, PostgreSQL will handle it by issuing an error, ensuring data integrity.

Second, insert a new row into the brands table:

INSERT INTO
  brands (name)
VALUES
  ('Apple') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 brand_id | name
----------+-------
        1 | AppleCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, attempt to insert the Apple brand into the brands table again:

INSERT INTO
  brands (name)
VALUES
  ('Apple') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: duplicate key value violates unique constraint "brands_name_key"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The error means you are trying to insert a value already in the brands table under the name column, violating the unique constraint brands_name_key.

Defining a UNIQUE constraint that includes multiple columns

First, create a table product_loans to store product loans:

CREATE TABLE product_loans (
  loan_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  loan_date DATE NOT NULL,
  return_date DATE,
  UNIQUE (product_id, user_id)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This example defines a UNIQUE constraint that includes the product_id and user_id columns.

Second, insert rows into the product_loans table:

INSERT INTO
  product_loans (product_id, user_id, loan_date)
VALUES
  (1, 1, '2024-11-23'),
  (1, 2, '2024-11-23') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 loan_id | product_id | user_id | loan_date  | return_date
---------+------------+---------+------------+-------------
       1 |          1 |       1 | 2024-11-23 | NULL
       2 |          1 |       2 | 2024-11-23 | NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, attempt to insert a new row with duplicate values in both product_id and user_id columns:

INSERT INTO
  product_loans (product_id, user_id, loan_date)
VALUES
  (1, 1, '2024-11-24');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

duplicate key value violates unique constraint "product_loans_product_id_user_id_key"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling NULL with UNIQUE constraint

In PostgreSQL, NULL is different from another NULL. Therefore, you can insert multiple NULLs into a UNIQUE constraint column. For example:

First, create a table profiles to store the user profiles:

CREATE TABLE profiles (
  user_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  phone VARCHAR(25) UNIQUE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The phone column is nullable and has a UNIQUE constraint.

Second, insert a new row into the profiles table:

INSERT INTO
  profiles (user_id, first_name, last_name, phone)
VALUES
  (1, 'John', 'Doe', NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 user_id | first_name | last_name | phone
---------+------------+-----------+-------
       1 | John       | Doe       | NULLCode language: PHP (php)

Third, attempt to insert a new row to the profiles table with the value in the phone column as NULL:

INSERT INTO
  profiles (user_id, first_name, last_name, phone)
VALUES
  (2, 'Jane', 'Doe', NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 user_id | first_name | last_name | phone
---------+------------+-----------+-------
       2 | Jane       | Doe       | NULLCode language: PHP (php)

Finally, retrieve data from the profiles table:

SELECT * FROM profiles;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 user_id | first_name | last_name | phone
---------+------------+-----------+-------
       1 | John       | Doe       | NULL
       2 | Jane       | Doe       | NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

• Utilize PostgreSQL UNIQUE constraints to ensure values in one or more columns are unique within the same table.