by Josh Branchaud on September 19, 2017

I recently needed to write a PostgreSQL snippet that would massage some data. More specifically, I needed to swap the ordering of two different records in the pages table. These records are made up of an id, a name, and an ordering.

Here is a snapshot of the data in pages.

select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  3 | Green  |        1
  1 | Red    |        2
  4 | Yellow |        3
  2 | Blue   |        4

We've been informed that we need to swap the ordering of Red and Blue. We can see here in this snapshot of the data that Red has an ordering of 2 and Blue has an ordering of 4.

Doing a swap based on the data that we have locally would be most easily accomplished with a couple update statements.

update pages set ordering = 4 where name = 'Red';
update pages set ordering = 2 where name = 'Blue';

Unfortunately, we've been informed that these exact ordering values cannot be guaranteed in the primary data set. All we know is that Red and Blue need to be swapped.

So, how do we write a general purpose swapping statement that works both with our local dataset and with the primary data set?

We can combine the updates into a single statement doing the value swapping based on the evaluation of a case statement that utilize subqueries.

update pages
set ordering = case name
                 when 'Red' then (select ordering from pages where name = 'Blue')
                 when 'Blue' then (select ordering from pages where name = 'Red')
               end
where name = 'Red' or name = 'Blue';

We can run it and then check that it works:

select * from pages order by ordering;
 id |  name  | ordering
----+--------+----------
  3 | Green  |        1
  2 | Blue   |        2
  4 | Yellow |        3
  1 | Red    |        4

But how does it work?

At first glance, it seems like updating one record's ordering could mean that we clobber the ordering value that we need in order to update the other. Shouldn't we need some sort of temporary variable? We've probably seen imperative code statements that look something like the following:

let temp = a
a = b
b = temp

Why don't we need a temporary value with our PostgreSQL statement?

The answer has to do with the consistency guarantees that PostgreSQL provides us. Throughout the entire course of a single update statement, PostgreSQL is going to provide a single, consistent snapshot of our data. If this wasn't the case, then the subqueries inside of the case statement would have data changing out from underneath them.

This technique not only massages our data as requested, it shows us the dynamism of update statements in PostgreSQL. We are able to utilize case statements and subqueries to get exactly what we need out of the statement. This statement isn't flawless though. In part 2 of this post, we'll explore another use case and see how we can build on this technique.

Create the pages table.

create table pages (
  id serial primary key,
  name varchar not null,
  ordering integer not null
);

Insert some records into the pages table.

insert into pages (name, ordering)
values
  ('Red', 2),
  ('Blue', 4),
  ('Green', 1),
  ('Yellow', 3);

Cover image by Dmitri Popov on Unsplash.com

Shout out to Jake Worth with whom I worked on this particular solution.