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.

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.

A girl receiving new message

Sign to our Newsletter

We're committed to your privacy. Curiosum uses the information you provide to us to contact you about our relevant content. You may unsubscribe from these communications at any time. For more information, check out our privacy policy.

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

Read more
on #curiosum blog

Phoenix LiveView Tutorial: Adding Phoenix PubSub and Pow Authentication to Messenger
  • Elixir

Phoenix LiveView Tutorial: Adding Phoenix PubSub and Pow Authentication to Messenger

We've already bootstrapped our Phoenix LiveView-based Messenger app's database structure and a first LiveView page.

This time, we're going to improve real-time communication between the app's users using Phoenix PubSub, and use the Pow library to add secure user authentication.

As of November 2020, the latest Phoenix LiveView version is 0.14.8 - and the series has been updated to match it!

5 top-tier companies that use Elixir
  • Elixir

5 top-tier companies that use Elixir

Elixir is a pretty capable language - and it consistently ranks near the top of most loved and wanted languages rankings. It has a large following and some very persuasive preachers as well. But that would not be enough to make me like it – what I need as real proof of its strengths is real businesses that strive with Elixir.

That’s what this list is all about – a bunch of stories from top companies that chose Elixir and never looked back. Let us show you how its power and versatility shows in practice.