Why & when you should use PostgreSQL deferred uniqueness constraints

Learn a trick that will allow you to manage item order in Postgres tables easier & faster.

Table of contents

    When you want to ensure that a certain column in a table has unique values, what you usually do is create a UNIQUE constraint for the column (or e.g. for a pair of columns, when you want the uniqueness to apply to pairs of values in each tuple).

    postgres=# CREATE TABLE numbers (number INTEGER, UNIQUE (number));
    CREATE TABLE
    
    postgres=# INSERT INTO numbers VALUES (0), (1), (2);
    INSERT 0 3

    Suppose that the number column represents an ordering, and you want all (or a subset of) tuples' index to be shifted by 1 to make way for a new item at a given index. Your first guess is probably this:

    postgres=# UPDATE numbers SET number = number + 1;
    ERROR:  duplicate key value violates unique constraint "numbers_number_key"
    DETAIL:  Key (number)=(1) already exists.

    Why did it fail? Because even though conceptually it would lead to the column's values of (1), (2), (3) - which would not violate the uniquenes constraing - the uniqueness constraint is checked immediately after updating each row. So, transitively, the column is briefly in a state of (1), (1), (2), which creates the error.

    To make it work, we have to instruct the database to enforce this constraint at transaction commit time. Here's how to create the table with such setting default for this constraint:

    postgres=# CREATE TABLE numbers (number INTEGER, UNIQUE (number) DEFERRABLE INITIALLY DEFERRED);
    CREATE TABLE
    
    postgres=# INSERT INTO numbers VALUES (0), (1), (2);
    INSERT 0 3
    
    postgres=# UPDATE numbers SET number = number + 1;
    UPDATE 3

    Clearly, it's working now. Read more on how to control the constraint's enforcing strategy on a per-transaction basis.

    Download our ebook
    Michał Buszkiewicz, Elixir Developer
    Michał Buszkiewicz Curiosum Founder & CTO

    Read more
    on #curiosum blog

    7 Reasons Why You Might Need a Custom Marketing Tool

    Managing a marketing agency or introducing a company to digital transformation in the area of marketing, requires an extraordinary focus on the tools that you should use. Usually, they facilitate the work of marketers and allow you to achieve the expected results of each project and advertising campaign you are working on.

    How to upload a file in Elixir with Waffle

    The ability to upload files is a key requirement for many todays web and mobile applications. In this tutorial, we will look at how we can accomplish file uploads to local storage and S3 server in Phoenix with the help of Waffle library.