In bug # 14739 Tom Lane wrote the following. My response follows but I decided to create a new thread since the topic for 14739 is about encoding and not the constraint itself.
On Mon, Jul 10, 2017 at 9:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
fbd@datasapiens.com writes: > I am testing PG on this query : > CREATE TABLE T_UNIK (ID INT UNIQUE); > INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5); > UPDATE T_UNIK SET ID = ID + 1;
> I know that PG is unable to do this set based operation properly because it > does it row by row (which is a nonsense since it works on many RDBMS)
The solution for that is documented: declare the unique constraint as deferrable.
regression=# CREATE TABLE T_UNIK (ID INT UNIQUE deferrable ); CREATE TABLE regression=#
INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5); INSERT 0 5 regression=# U
PDATE T_UNIK SET ID = ID + 1; UPDATE 5
I was expecting the above to require a "set constraints deferred" since "initially immediate" is the default.
create table t_unik (id int unique deferrable initially immediate);
It appears to be useful, but undocumented, that changing the primary mode to "deferrable" also changes the default timing to "initially deferred" - irrespective of whether the constraint itself is defined as initially immediate or initially deferred. i.e., changing just the create table to explicitly "deferrable initially immediate" doesn't provoke the duplicate key error like I was expecting it to.
Thinking on it further I believe the issue is that regardless of whether the timing is immediate or deferred a deferrable constraint never validates during the execution of an individual command while an immediate constraint does.
From "CREATE TABLE":
"A constraint that is not deferrable will be checked immediately after every command."
I think the above should be "after every row" instead of "after every command". My reading of this is that "command" and "statement" are the same thing and since the only way to get a unique violation is to be checking intra-command the above is wrong.
I read the sequence "deferrable initially immediate" as "deferrable initially "not deferred"" and expect the same behavior as a constraint not defined as "deferrable" unless some other action, at the transaction level, is taken. In this case the example doesn't "set constraints" and so the original failure should persist.
Ultimately my interpretation ends up working just fine because issuing set constraints in a transaction is just a more liberal directive.
