From: | Ivan Voras <ivoras(at)freebsd(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Table update problem works on MySQL but not Postgres |
Date: | 2010-09-01 15:36:49 |
Message-ID: | i5lrug$dke$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 09/01/10 16:13, Igor Neyman wrote:
>
>
>> -----Original Message-----
>> From: Raymond C. Rodgers [mailto:sinful622(at)gmail(dot)com]
>> Sent: Tuesday, August 31, 2010 7:56 PM
>> To: pgsql-general(at)postgresql(dot)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. "
From | Date | Subject | |
---|---|---|---|
Next Message | Arjen Nienhuis | 2010-09-01 16:03:57 | How to defer ON DELETE CASCADE |
Previous Message | Jonathan Tripathy | 2010-09-01 15:24:37 | Re: Connection question |