4
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE
);

I have the data

 id | name | ordinal 
----+------+---------
  1 | A    |       1
  2 | B    |       2
  3 | C    |       3

I would like to update it to

 id | name | ordinal 
----+------+---------
  1 | A    |       3
  2 | B    |       2
  3 | C    |       1

Touching records as little as possible (i.e. don't rewrite the entire record set, don't kick off unnecessary triggers), what's the generally applicable approach to updating ordinal to be the target values?

A vanilla update just gives me constraint violations, even if it happens in a single statement.

And dropping and recreating the unique constraint is expensive and poor concurrency.

This seems like a common enough problem that there ought to be a good way to do this, that I just can't think of.

3 Answers 3

6

Define the constraint as deferrable:

CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);

Then you can update it in one statement:

update widget
  set ordinal = t.new_ordinal
from (
  values (1, 3), (3,1) 
) as t(id, new_ordinal)
where t.id = widget.id   
5

A plain UNIQUE constraint is NOT DEFERRABLE by default. Unique violations are checked after each row. The manual phrases this as:

checked immediately after every command

But it's really checked for every individual written row.

If you define the UNIQUE constraint DEFERRABLE (like a_horse provided), unique violations are checked after each statement. Multiple CTEs within the same query still count as a single statement in this respect.

CREATE TABLE widget_deferrable (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE  -- !
);

If you also actually set the constraint as DEFERRED, the check for unique violations is deferred until after each transaction.

CREATE TABLE widget_deferred (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED  --!
);

Comprehensive demo:

db<>fiddle here

Further reading:

0

I'm posting an answer because it's the way I solved this problem, and I found this question when I was looking for answers. If your constraint was not marked deferrable when you created it, and you don't have the luxury of modifying the table, there is a workaround using CTEs.

with z as (
    select 1 as id, -1 as ordinal
    union select 3, 1
    union select 1, 3
)
update widget
set ordinal = z.ordinal
from z
where widget.id = z.id

This requires the rows in the CTE to come out in the exact order they are defined. I think this example will work, but I could be wrong. If it does not work, you may have to assign a bogus value to each row first, followed by a correct value. It's a bit weird, but it shouldn't take long to get used to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.