by Josh Branchaud on September 26, 2017

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.

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