"Phoenix Kiula" <phoenix.kiula@gmail.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