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.

    Michał Buszkiewicz, Elixir Developer
    Michał Buszkiewicz Curiosum Founder & CTO

    Read more
    on #curiosum blog

    Introducing Kanta - the ultimate solution to translations in Elixir web apps

    Introducing Kanta - the ultimate solution to translations in Elixir & Phoenix web apps

    Handling translations in web applications can be a challenging and time-consuming task. The process entails managing various files, tracking modifications, and ensuring that translations remain current as the project progresses. These complexities can slow down development and introduce errors. That’s why we decided to create an open source solution: Kanta. In this blog post, we’ll explore the problem of managing translations in web apps, and how Kanta simplifies the process. We’ll also compare it with other translation management tools and delve into its powerful plugins.