Обсуждение:

UPDATE with SUBSELECT and ORDER BY
---------------------------------------------

Hi @ Postgres BUGS...

SITUATION:

I have a Table

id   groupid   orderby

1    1         10
2    1         20
3    1         30
4    1         40
5    2         10
6    2         20
10   2         30
11   2         40

and a UNIQUE INDEX on columns "groupid,orderby"

now i want to update

UPDATE
orderby SET orderby = orderby +10
WHERE groupid = 1;

-> FAILS because of UNIQUE INDEX

NOW I WANTED TO UPDATE REVERSE:

UPDATE
orderby SET orderby = orderby +10
WHERE groupid IN
( SELECT groupid
  FROM TABLE WHERE group_id = 1
  ORDER BY orderby DESC )
;

-> FAILS TOO... SEEMS LIKE THE ORDER BY "DESC" WONT BE USED ?!?


This is with Postgres Version 8.0.3


Regards,
Claus Pruefer


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

claus.pruefer@webcodex.de wrote:

> and a UNIQUE INDEX on columns "groupid,orderby"
>
> now i want to update
>
> UPDATE
> orderby SET orderby = orderby +10
> WHERE groupid = 1;
>
> -> FAILS because of UNIQUE INDEX

This is a Known Issue(tm).  (A "gotcha", some would say).

http://archives.postgresql.org/pgsql-sql/2004-10/msg00146.php
Surely you can find better references in the archives, but I'm too lazy.
(keywords: "deferrable unique constraint")

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


| UPDATE
| orderby SET orderby = orderby +10
| WHERE groupid = 1;
| -> FAILS because of UNIQUE INDEX

One workaround is to do it in two steps, assuming that
orderby is > 0 for all rows you are changing:

BEGIN;
UPDATE mytable SET orderby = -orderby WHERE groupid = 1;
UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1;
COMMIT;


| UPDATE
| orderby SET orderby = orderby +10
| WHERE groupid IN
| ( SELECT groupid
|  FROM TABLE WHERE group_id = 1
|  ORDER BY orderby DESC )

No idea what you are trying to do here: try posting the actual SQL
used. However, an ORDER BY has no meaning inside of a subselect
passed to IN, as IN treats the list as bag of values, and does not
care about the internal order.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200606011030
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEfvpFvJuQZxSWSsgRAjQlAKDTNIpwbSEk0gcQp2pI7LokG+qwWgCgt/b6
/7ZWYDb4gufE4b0zCHyFZgg=
=4LQ8
-----END PGP SIGNATURE-----

""Greg Sabino Mullane"" <greg@turnstep.com>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> | UPDATE
> | orderby SET orderby = orderby +10
> | WHERE groupid = 1;
> | -> FAILS because of UNIQUE INDEX
>
> One workaround is to do it in two steps, assuming that
> orderby is > 0 for all rows you are changing:
>
> BEGIN;
> UPDATE mytable SET orderby = -orderby WHERE groupid = 1;
> UPDATE mytable SET orderby = -orderby+10 WHERE groupid = 1;
> COMMIT;

Should we invent something to defer the check of uniqueness
to the end of the SQL command?

Regards,
William ZHANG