-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 08/16/07 21:58, Phoenix Kiula wrote: > I am trying to force a column to have lowercase because Postgresql is > case-sensitive in queries. For the time being I've made an expression > index on lower(KEY). But I would like to have just lower case data and > then drop this expression index. > > However, I see some inconsisent behavior from Postgresql. When I issue > an UPDATE command , it shows me a duplicate violation (which could be > correct) -- > > -# update TABLE set ACOLUMN = lower(ACOLUMN); > ERROR: duplicate key violates unique constraint "TABLE_ACOLUMN_key" > > So I try to find out the offending values of this ACOLUMN that become > duplicated when lower(ACOLUMN) is issued: > > -# SELECT lower(ACOLUMN), count(*) FROM TABLE > GROUP BY lower(ACOLUMN) HAVING count(*) > 1 ; > -------+------- > lower | count > -------+------- > (0 rows) > > But this doesn't make sense! If there are no columns that get > repeated, how can it violate the UNIQUE constraint? > > I am not sure if the following helps, but I'm including the EXPLAIN on > this table. Penny for your thoughts! Whatever the issue, you can bet your car that it's not a bug in PostgreSQL, but you who is misunderstanding how PG works. Write a script that loops thru the records one by one, updating only one record per loop iteration. That will find the problem record. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGxRSUS9HxQb37XmcRApMyAKCGOmpm5xKkfuWR19OnbXLVZMMbkwCcCHmu 4OOXMnRnaixpp8lSjbrA/5w= =M3jw -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
