One query increment of a set of values ​​in a field with a UNIQUE constraint, Postgres

Question

I have a table in which I have a number field A for which UNIQUE is set. This field is used to indicate the order in which an action should be performed. I want to do UPDATE of all values ​​that are larger, e.g. 3 . For example, I have

A 1 2 3 4 5 

Now I want to add 1 to all A values ​​greater than 3 . Thus, the result will be

 A 1 2 3 5 6 

The question is, can it be done using only one query? Remember that I have a UNIQUE constraint for column A.

Obviously i tried

 UPDATE my_table SET A = A + 1 WHERE A > 3; 

but this did not work, as I have a restriction on this field.

Answer 1

PostgreSQL 9.0 and later

PostgreSQL 9.0 has added deferred unique restrictions , which is exactly the function that you think you need. Thus, uniqueness is checked at the time of fixing, and not at the time of updating.

Create a UNIQUE constraint using the DEFERRABLE keyword:

 ALTER TABLE foo ADD CONSTRAINT foo_uniq (foo_id) DEFERRABLE; 

Later, before running the UPDATE statement, you perform the same transaction:

 SET CONSTRAINTS foo_uniq DEFERRED; 

Alternatively, you can create a constraint using the INITIALLY DEFERRED keyword on the most unique constraint - so you do not need to run SET CONSTRAINTS - but this can affect the performance of your other queries that are not needed to defer the constraint.

PostgreSQL 8.4 and later

If you want to use a unique constraint to ensure uniqueness - and not as a target for a foreign key, then this workaround can help:

First add a boolean column, such as is_temporary , to the table that temporarily distinguishes between updated and non-updated rows:

 CREATE TABLE foo (value int not null, is_temporary bool not null default false); 

Then create a partial unique index that only affects rows where is_temporary = false:

 CREATE UNIQUE INDEX ON foo (value) WHERE is_temporary=false; 

Now, every time you make the updates described by you, you launch them in two stages:

 UPDATE foo SET is_temporary=true, value=value+1 WHERE value>3; UPDATE foo SET is_temporary=false WHERE is_temporary=true; 

As long as these statements arise in one transaction, it will be absolutely safe - other sessions will never see temporary lines. The disadvantage is that you will write lines twice.

Please note that this is just a unique index, not a limitation, but in practice it does not matter.

Answer 2

You can do this in two queries with a simple trick:

First update the +1 column, but add it with a factor of x (-1):

 update my_table set A=(A+1)*-1 where A > 3. 

You will be swtich from 4,5,6 to -5, -6, -7

Secondly, return the operation to restore a positive result:

 update my_table set A=(A)*-1 where A < 0. 

You will have: 5,6,7

Answer 3

You can do this with a loop. I do not like this solution, but it works:

 CREATE TABLE update_unique (id INT NOT NULL); CREATE UNIQUE INDEX ux_id ON update_unique (id); INSERT INTO update_unique(id) SELECT a FROM generate_series(1,100) AS foo(a); DO $$ DECLARE v INT; BEGIN FOR v IN SELECT id FROM update_unique WHERE id > 3 ORDER BY id DESC LOOP UPDATE update_unique SET id = id + 1 WHERE id = v; END LOOP; END; $$ LANGUAGE 'plpgsql'; 

In PostgreSQL 8.4, you may need to create a function to do this, since you cannot arbitrarily run PL / PGSQL from a prompt using DO (at least not at best from my memory).