The following bug has been logged online:

Bug reference: 1811
Logged by: George Jansen
Email address: gjansen@aflcio.org
PostgreSQL version: 8.0.3
Operating system: RedHat ES 4
Description: unique index problem
Details:

Importing a database in preparation for a Lyris cutover, I received an error
creating a unique index. In psql I checked the table:

lyris=# CREATE UNIQUE INDEX ix_uniquewords_word ON uniquewords_ USING btree
(word_);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
lyris=# SELECT word_, Count(*)
lyris-# FROM uniquewords_
lyris-# GROUP BY word_
lyris-# HAVING Count(*) > 1;
word_ | count
-------+-------
(0 rows)

lyris=#


Then I listed the words out in order to a text file
and ran


-bash-3.00$ uniq -c uwords | grep -v "^ *1"
2 avendano
2 espanol
-bash-3.00$

Both old and new databases use SQL_ASCII. Is this a bug?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"George Jansen" <gjansen@aflcio.org> writes:
> [ inconsistent comparison behavior ]
> Both old and new databases use SQL_ASCII. Is this a bug?

I think it's probably a locale problem; did you initdb the database in C
locale? (Check SHOW LC_COLLATE and SHOW LC_CTYPE to be sure.) Usually
we see this sort of thing when the locale is expecting characters in a
different encoding than what's actually being stored.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly