Re: incrementing without violating a constraint

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(dot)klaver(at)gmail(dot)com