On 09/01/10 16:13, Igor Neyman wrote:
-----Original Message-----
From: Raymond C. Rodgers [mailto:sinful...@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. "
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
