Обсуждение: incrementing without violating a constraint

Hi,

I have a uniqueness constraint on an integer value in a table where I would
like to mass increment all of the existing rows.

ie.
update rules set rule_number = rule_number + 1;

This violates the uniquness constraint. Is there a way to say, turn off the
constraint, run the update and then turn it back on?

Thanks,
Mike

On 20/01/14 Michael P. Soulier said:

> This violates the uniquness constraint. Is there a way to say, turn off the
> constraint, run the update and then turn it back on?

Hmm. This didn't work.

tugdb=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
tugdb=# update siptrunkroutingrules set rule_number = rule_number + 1;
ERROR:  duplicate key value violates unique constraint
"siptrunkroutingrules_unique_trunk_rule_number"

Mike

"Michael P. Soulier" <michael_soulier@mitel.com> hat am 20. Januar 2014 um 15:33
geschrieben:
> Hi,
>
> I have a uniqueness constraint on an integer value in a table where I would
> like to mass increment all of the existing rows.
>
> ie.
> update rules set rule_number = rule_number + 1;
>
> This violates the uniquness constraint. Is there a way to say, turn off the
> constraint, run the update and then turn it back on?
>
> Thanks,
> Mike

test=# \d mike
     Table "public.mike"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
Indexes:
    "mike_pkey" PRIMARY KEY, btree (id)

test=# begin;
BEGIN
test=*# alter table mike drop constraint "mike_pkey";
ALTER TABLE
test=*# update mike set id=id+1;
UPDATE 3
test=*# alter table mike add primary key (id);
ALTER TABLE
test=*# select * from mike ;
 id
----
  2
  3
  4
(3 rows)

On 01/20/2014 06:40 AM, Michael P. Soulier wrote:
> On 20/01/14 Michael P. Soulier said:
>
>> This violates the uniquness constraint. Is there a way to say, turn off the
>> constraint, run the update and then turn it back on?
>
> Hmm. This didn't work.
>
> tugdb=# SET CONSTRAINTS ALL DEFERRED;
> SET CONSTRAINTS
> tugdb=# update siptrunkroutingrules set rule_number = rule_number + 1;
> ERROR:  duplicate key value violates unique constraint
> "siptrunkroutingrules_unique_trunk_rule_number"

Probably because by default deferrable constraints are set NOT DEFERRABLE:

http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that
is not deferrable will be checked immediately after every command.
Checking of constraints that are deferrable can be postponed until the
end of the transaction (using the SET CONSTRAINTS command). NOT
DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE,
and REFERENCES (foreign key) constraints accept this clause. NOT NULL
and CHECK constraints are not deferrable.

and:

http://www.postgresql.org/docs/9.3/static/sql-set-constraints.html

Upon creation, a constraint is given one of three characteristics:
DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT
DEFERRABLE. The third class is always IMMEDIATE and is not affected by
the SET CONSTRAINTS command. The first two classes start every
transaction in the indicated mode, but their behavior can be changed
within a transaction by SET CONSTRAINTS.


I do not believe there is a way to ALTER the DEFERRED state of an
existing constraint. So if this is a one time thing, you could do what
Andreas suggests, drop the unique constraint for the update. If this is
something you think will happen again you can recreate the unique
constraint with one of the DEFERRABLE clauses that allow SET DEFERRED.

>
> Mike
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 20 January 2014 15:33, Michael P. Soulier <michael_soulier@mitel.com> wrote:
> Hi,
>
> I have a uniqueness constraint on an integer value in a table where I would
> like to mass increment all of the existing rows.
>
> ie.
> update rules set rule_number = rule_number + 1;
>
> This violates the uniquness constraint. Is there a way to say, turn off the
> constraint, run the update and then turn it back on?

You can work around this by doing the update in 2 phases:

First, update all the numbers to an increment of their value that's
guaranteed to be unique across the entire table. For example by
incrementing with the current MAX value +1.
After that, you can decrement them again so that they fall into the
desired range.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

"Michael P. Soulier" <michael_soulier@mitel.com> writes:
> On 20/01/14 Michael P. Soulier said:
>> This violates the uniquness constraint. Is there a way to say, turn off the
>> constraint, run the update and then turn it back on?

> Hmm. This didn't work.

> tugdb=# SET CONSTRAINTS ALL DEFERRED;
> SET CONSTRAINTS
> tugdb=# update siptrunkroutingrules set rule_number = rule_number + 1;
> ERROR:  duplicate key value violates unique constraint
> "siptrunkroutingrules_unique_trunk_rule_number"

You'd have had to mark the index constraint as deferrable for that
to have any effect on it.

            regards, tom lane