PostgreSQL
Swap Two Column Values in SQL: Part 2
In the first part of this post we looked at a technique for swapping two column values for existing records in a single statement. It is a relatively sound solution that takes advantage of subqueries and a case statement. Unfortunately, it has a couple drawbacks which we will address in this post.
This post builds off of Part 1. If you haven't already, give it a read.
The first drawback can be demonstrated by building on our existing example,
the pages
table which has the following data.
select * from pages order by ordering;
id | name | ordering
----+--------+----------
3 | Green | 1
1 | Red | 2
4 | Yellow | 3
2 | Blue | 4
In the interest of maintaining data integrity, it is reasonable to expect
that a table like this would have a uniqueness constraint on ordering
.
Let's add that to ensure that no two pages can end up with the same
ordering value.
alter table pages add constraint orderings_are_unique unique (ordering);
ALTER TABLE
With that in place, let's see how our update
statement from the previous
post holds up.
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';
ERROR: duplicate key value violates unique constraint "orderings_are_unique"
DETAIL: Key (ordering)=(4) already exists.
Though this single query may have been able to rotate those values in place, it runs into trouble when confronted with a uniqueness constraint. What to do?
One possible solution to this I picked up from my colleague, Jack
Christensen. It will allow us to swap the values despite the uniqueness
constraint and still maintains that we don't need to know the exact
ordering
values at the time we write the query.
update pages set ordering = -ordering where name = 'Red' or name = 'Blue';
UPDATE 2
select * from pages order by ordering;
id | name | ordering
----+--------+----------
2 | Blue | -4
1 | Red | -2
3 | Green | 1
4 | Yellow | 3
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';
UPDATE 2
select * from pages order by ordering;
id | name | ordering
----+--------+----------
3 | Green | 1
2 | Blue | 2
4 | Yellow | 3
1 | Red | 4
This solution uses two statements. The first negates the values that will be swapped. The second makes them positive again as it swaps them using the same technique from the previous approach. The negative values act as lossless placeholders to prevent violation of the uniqueness constraint.
This is a bit of a trick that takes advantage of two key facts. First,
ordering
is an integer
column, so it can be negated. Second, we are
assuming that only the range of positive numbers represented by this integer
column are being utilized. If this is the case, then at any point in time we
can use the range of negative numbers as a temporary scratch pad for
swapping these values.
This frames the second drawback. What do we do if the column we are swapping has a unique constraint but is not an integer column?
To illustrate this, let's also put a uniqueness constraint on the name
column which we will now treat as the target of the swap.
alter table pages add constraint names_are_unique unique (name);
ALTER TABLE
In this scenario, we want to swap the name
values of the pages that have
ordering
values of 2
and 4
. This is going to take a number of steps,
so let's walk through them one by one.
First, we create a temporary table that we call the swap_table
. The
intention of this table is to serve as the temporary scratch pad that holds
on to the values that need swapping. In fact, in the subsequent insert
statement into swap_table
, the values and their keys (the ordering
value) are inserted in the swapped order.
create temporary table swap_table (swap_key integer, swap_value varchar);
CREATE TABLE
insert into swap_table (swap_key, swap_value)
select p1.ordering, p2.name
from pages p1
join pages p2 on p1.name != p2.name
where p1.ordering in (2,4)
and p2.ordering in (2,4);
INSERT 0 2
table swap_table;
swap_key | swap_value
----------+------------
2 | Blue
4 | Red
To see really clearly how this swapped order is achieved for the
swap_table
, let's isolate just the select
statement. Because we are
only dealing with two records and we know the values are unique, we can join
the table against itself based on the name
values not being equal.
select
p1.ordering,
p1.name as original,
p2.name as swapped
from pages p1
join pages p2 on p1.name != p2.name
where p1.ordering in (2,4) andp2.ordering in (2,4);
ordering | original | swapped
----------+----------+---------
2 | Red | Blue
4 | Blue | Red
This swap_table
is necessary in order to deal with the uniqueness of the
name
column as well as the possibility that it is a not null
column.
Because we cannot simply negate the original name
values as we did in the
integer example, we need to find another way to uniquely obfuscate them.
Postgres' built-in
md5()
function is a great choice here since we are dealing with string values. We
update the target records in the pages
table accordingly. Again, this is
so that we avoid violating the uniqueness constraint on name
in the
subsequent update
statement.
update pages set name = md5(name) where ordering in (2,4);
UPDATE 2
select * from pages order by ordering;
id | name | ordering
----+----------------------------------+----------
3 | Green | 1
1 | ee38e4d5dd68c4e440825018d549cb47 | 2
4 | Yellow | 3
2 | 9594eec95be70e7b1710f730fdda33d9 | 4
Then we update the target records in pages
with the swapped values from
the swap_table
based on corresponding ordering
values.
update pages
set name = swap_table.swap_value
from swap_table
where swap_table.swap_key = ordering;
UPDATE 2
select * from pages order by ordering;
id | name | ordering
----+--------+----------
3 | Green | 1
1 | Blue | 2
4 | Yellow | 3
2 | Red | 4
Lastly, we can drop our temporary table in an effort to be tidy. Because it is a temporary table, we can also count on it being cleaned up when the current connection is terminated.
drop table swap_table;
DROP TABLE
The queries needed for this scenario may feel overwhelming, but they demonstrate the power and flexibility of SQL and, in particular, PostgreSQL.
In this post we saw a couple tricky ways of swapping column values when
constrained by uniqueness constraints in the case of both integers and
strings. We took advantage of the range of negative numbers that is often
ignored for an integer column, we created a temporary table, and even used
the md5()
string function provided by Postgres. Surely none of these
solutions are a perfect fit for every situation, but what they demonstrate
is the capacity that SQL has for solving all kinds of problems that arise.
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