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
name, and an
Here is a snapshot of the data in
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
We can see here in this snapshot of the data that
Red has an ordering of
Blue has an ordering of
Doing a swap based on the data that we have locally would be most easily
accomplished with a couple update statements.
update pages set ordering4 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
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
PostgreSQL is going to provide a single, consistent snapshot of our data. If
this wasn't the case, then the subqueries inside of the
would have data changing out from underneath them.
This technique not only massages our data as requested, it shows us the
update statements in PostgreSQL. We are able to utilize
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 table pages ( id serial primary key, name varchar not null, ordering integer not null );
Insert some records into the
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.