Thread: LATIN2 'bssz' and 'bszsz' fails on unique index

Hello,

If you use Latin2 encoding, you can not have 'bssz' and 'bszsz' in an
unique column in the same time.

Is this  a known bug? Do you have any solution? (I use Latin2 encoding,
because I want to order by names, which contains accent characters.)

d43m0n@jerry:~> psql template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

template1=# CREATE DATABASE test WITH ENCODING = 'LATIN2';
CREATE DATABASE
template1=# \c test
You are now connected to database "test".
test=# CREATE TABLE test (name character varying (128) UNIQUE NOT NULL);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index
"test_name_key" for table "test"
CREATE TABLE
test=# INSERT INTO test (name) VALUES ('bssz');
INSERT 17798 1
test=# INSERT INTO test (name) VALUES ('bszsz');
ERROR:  duplicate key violates unique constraint "test_name_key"
test=#

I am running Debian Sid i386 with 2.6.6 kernel, libc6 2.3.2.ds1-12,
locales 2.3.2.ds1-12, postgresql 7.4.2-4 . Locale for postgres user is
"POSIX", locale for the psql client is "hu_HU". Both client and server
encoding for the above connections was Latin2.

Thanks,   d43m0n.

PS: Please CC me, I am not subscribed to the list.

--
Sun@True.hu

Sün <sun@true.hu> writes:
> If you use Latin2 encoding, you can not have 'bssz' and 'bszsz' in an
> unique column in the same time.

AFAICS this means that your locale definition considers these strings
equal.

It is possible that the real problem comes from using an encoding that's
not compatible with what the locale setting expects.  Locales generally
do require a specific character set encoding, though this is poorly
documented :-(
        regards, tom lane

hi,

Tom Lane wrote:
> Sün <sun@true.hu> writes:>>>If you use Latin2 encoding, you can not have 'bssz' and 'bszsz' in an>>unique column in
thesame time.>>> AFAICS this means that your locale definition considers these strings> equal.>> It is possible that
thereal problem comes from using an encoding that's> not compatible with what the locale setting expects.  Locales
generally>do require a specific character set encoding, though this is poorly> documented :-(> 

#createdb -U postgres -E=SQL_ASCII test
#psql test
test=# \encoding
SQL_ASCII
test=# \l
List of databases    Name     |   Owner    | Encoding
-------------+------------+----------- test         | postgres   | SQL_ASCII

test=#  create TEMP table lala (string varchar(20));
CREATE TABLE
test=# CREATE UNIQUE INDEX lala_idx on lala (string);
CREATE INDEX
test=# insert INTO lala values ('bssz');
INSERT 757927 1
test=# insert INTO lala values ('bszsz');
ERROR:  duplicate key violates unique constraint "lala_idx"

How? Ok, its locale "bug" (not just int LATIN2, LATIN1), but why?

thx
C.