"Phoenix Kiula" <phoenix.kiula@xxxxxxxxx> writes: > 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) Yeah, that *is* pretty bizarre. We have seen some cases where strcoll() yields inconsistent answers (leading to arbitrarily silly behavior on Postgres' part) if it is expecting a character set encoding different from what Postgres is using. What is your lc_collate setting, and are you sure it matches the database encoding? Another possibility is that there's something corrupt about the TABLE_ACOLUMN_key index ... does reindexing it change the outcome? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
