Claudio Nieder <claudio.nieder.lists@inodes.ch> writes:
>>> postgres=# update t1 set c1 = c1 +1;
>> This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked
immediatelyafter every command.
>>
> My understanding would have been, that "update t1 set c1=c1+1;" is ONE command, so even in the NOT DEFERRABLE case
thecheck would occur only after both values were changed and as such not find any violation of the constraint.
The important detail is a bit further down the page, in the
"Compatibility" section:
Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
checks for uniqueness immediately whenever a row is inserted or
modified. The SQL standard says that uniqueness should be enforced only at
the end of the statement; this makes a difference when, for example, a
single command updates multiple key values. To obtain standard-compliant
behavior, declare the constraint as DEFERRABLE but not deferred (i.e.,
INITIALLY IMMEDIATE). Be aware that this can be significantly slower than
immediate uniqueness checking.
regards, tom lane