Re: Strange inconsistency with UPDATE

"Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> 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