Re: Table update problem works on MySQL but not Postgres

On 09/01/10 16:13, Igor Neyman wrote:
>
>
>> -----Original Message-----
>> From: Raymond C. Rodgers [mailto:sinful622@gmail.com]
>> Sent: Tuesday, August 31, 2010 7:56 PM
>> To: pgsql-general@postgresql.org
>> Subject: Table update problem works on MySQL but not Postgres

>> update mydemo set cat_order = cat_order + 1 where client_id =
>> 1 and cat_order>= 0
>>
>> in order  to insert  categories at the top of the sorted list
>> for example. As you can probably guess, this query doesn't
>> work very well.
>> On both MySQL and PostgreSQL I get a constraint violation.
>> That makes sense; I screwed up.

> What you need for your update to work is "deferred" unique constraints.
> I think, this feature appears in 9.0.

Yes:

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

" Currently, only UNIQUE, PRIMARY KEY, REFERENCES  (foreign key), and
EXCLUDE constraints are affected by this setting. NOT NULL and CHECK
constraints are always checked immediately when a row is inserted or
modified (not at the end of the statement). Uniqueness and exclusion
constraints that have not been declared DEFERRABLE are also checked
immediately. "

In 8.4 it says:

" Currently, only foreign key constraints are affected by this setting.
Check and unique constraints are always effectively not deferrable.
Triggers that are declared as "constraint triggers" are also affected. "