> This has nothing to do with triggers, it has to do with not being able
> to defer UNIQUE constraints. The classic solution is:
>
> update SET column = -column;
> update SET column = -column+1;
Thanks for the reply.
Is changing query the only way to do this in postgres?
I would expect some more. IMHO, Postgres should first update all the
values and then check for duplicacy (and not check for duplicacy while
the query is still executing) - kind of saying that it should fire
statement level trigger instead of row level trigger. Most other
databases (Oracle/DB2 etc) behave this way.
The problem is that changing the query may not *always* work. E.g. If
the values in the primary key in the tables are consecutive, not in
any order, and contains negative numbers as well, it will be hard to
come up with a query that would do the needful. Say the table is (col1
is the primary key).
col1 | col2
------+------
-2 | 100
-1 | 100
0 | 100
1 | 100
2 | 100
On this, queries "update temp set col1=col1+1" as well as "update temp
set col1=col1-1" would fail with duplicate key error. Moreover, if the
query is autogenerated, where manual intervention is not possible, it
would be even harder.
So, a robust (and clean) way would be to have postgres support such
statements which would be to say that postgres check for duplicacy at
the end of the statement execution and not in between.
Now, what I want to know is, is there any way to make postgres show
such behaviour using some user level trigger (or any other mechanism)
or this is something which would require changing the postgres code
itself (we are willing to do that as well, but may not be worth the
effort if is somehow possible in the current framework).
Thanks.
