Partial unique index as a workaround for the nonexistent partial unique constraint

Introduction

Recently, I had to enforce the uniqueness of certain row values that meet certain conditions. First solution which came to my mind was to use unique constraint. Unfortunately, there is no something like partial (or conditional) unique constraint. After a quick research I realised that the fact that unique constraints are implemented using unique indexes might be useful here.

According to the PostgreSQL documentation

Partial indexes

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.

Unique indexes

When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

Having that I decided to give partial unique indexes a try!

Example

Let’s say that we have a products table where we store information about all products available in our store (in the demo example we have just two columns → name and status). In this example, we want to enforce the uniqueness of product names within all active products.

// create enum
create type product_status as enum ('active', 'inactive');

// create table
create table products (
    name text,
    status product_status
);

// add partial unique index to satisfy the requirement
create unique index unique_product_name on products name where status = 'active';

Now we are sure that the names of all active products are unique.

Important note

Why the title of this article stands that this solution is a workaround? There is no official documentation for it and I am not sure if it’s the best approach to solve this issue. Nevertheless, it worked in my case and I didn’t face any issues connected with it yet.