PostgreSQL
Swap Two Column Values in SQL: Part 1
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.
Supporting SQL
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.