Special case of updating a column with not null and unique constraints in PostgreSQL

Question

This is a toy example that illustrates a real problem in PostgreSQL. The below examples are using a PostgreSQL 8.4.3 server, but I suspect other versions have the same problem.

Given the following table:

=> create table tmp_foo (foo boolean not null unique, bar boolean not null unique);
=> insert into tmp_foo (foo, bar) values (true, true), (false, false);
=> select * from tmp_foo;
 foo | bar 
-----+-----
 t   | t
 f   | f

can the table be modified to look like this:

=> select * from tmp_foo;
 foo | bar 
-----+-----
 t   | f
 f   | t

without deleting rows or modifying the table schema? This:

=> update tmp_foo set bar = not bar;
ERROR:  duplicate key value violates unique constraint "tmp_foo_bar_key"

does not work.

If deletes are allowed, this:

=> create temp table tmp_foo_2 as select * from tmp_foo;
=> update tmp_foo_2 set bar = not bar;
=> delete from tmp_foo;
=> insert into tmp_foo select * from tmp_foo_2;

works. It is not the simplest solution for this example, but it is easily generalizable to more complex examples.

Answer 1

Doing this requires deferrable unique constraints.

We have as many rows as possible unique values in each column. So to mutate any row, either some rows must temporarily violate a unique constraint, or some row(s) must be deleted to avoid violating the constraint. Deferrable unique constraints let us do the former -- temporary violation (inside a transaction).

If you follow this far, and it sounds right, then the answer to your question depends on the Postgres version.

Postgres up to 8.4 only allows deferral of foreign key constraints. The corollary is that unique constraints cannot be deferred.

Postgres 9.0 betas theoretically offer deferrable unique constraints. I haven't tried it myself, but this feature has been a long time coming, so I'd bet they got it right when they decided to implement it.

Here are two morsels of relevant documentation on 9.0's unique indices and SET CONSTRAINTS. As you can see in the latter link, unique constraints are explicitly listed as supported for deferral via SET CONSTRAINTS in the 9.0 documentation. I haven't explored this new feature yet and I can't guarantee that the semantics are exactly what you need. But it seems like just the thing.