Thread: BUG #1367: Unique constraint fails with some values

The following bug has been logged online:

Bug reference:      1367
Logged by:          Hong Yuan
Email address:      hongyuan@homemaster.cn
PostgreSQL version: 8.0
Operating system:   Windows XP Chinese Simplified
Description:        Unique constraint fails with some values
Details:

I found that the unique constraint fails sometimes when the varchar column
contain some specific Chinese characters. Here is an example:

CREATE TABLE test
(
  col1 varchar(10),
  CONSTRAINT col1 UNIQUE (col1)
)
WITHOUT OIDS;

insert into test values('大');
insert into test values('大');
insert into test values('大');

While the second and third inserts should fail, they do not. With some other
Chinese characters, the constraint is observed.

The character '大' has utf-8 encoding '\xe5\xa4\xa7'.

The database is created with UNICODE encoding. The problem is present with
version 8.0.0-rc2.

"Hong Yuan" <hongyuan@homemaster.cn> writes:
> I found that the unique constraint fails sometimes when the varchar column
> contain some specific Chinese characters. Here is an example:

The usual cause of this sort of thing is that you've selected a database
encoding that is not compatible with the locale setting you used during
initdb, causing strcoll() to produce bogus answers --- and since varchar
comparisons depend on strcoll(), that makes everything go south.

It would be good if we could enforce consistency between the two
settings, but at the moment it's on the user's shoulders to get it right
:-(

            regards, tom lane

>Bug reference:      1367
>Logged by:          Hong Yuan
>Email address:      hongyuan@homemaster.cn
>PostgreSQL version: 8.0
>Operating system:   Windows XP Chinese Simplified
>Description:        Unique constraint fails with some values
>Details:=20
>
>I found that the unique constraint fails sometimes when the=20
>varchar column
>contain some specific Chinese characters. Here is an example:
>
>CREATE TABLE test
>(
>  col1 varchar(10),
>  CONSTRAINT col1 UNIQUE (col1)
>)=20
>WITHOUT OIDS;
>
>insert into test values('=E5=A4=A7');
>insert into test values('=E5=A4=A7');
>insert into test values('=E5=A4=A7');
>
>While the second and third inserts should fail, they do not.=20
>With some other
>Chinese characters, the constraint is observed.
>
>The character '=E5=A4=A7' has utf-8 encoding '\xe5\xa4\xa7'.
>
>The database is created with UNICODE encoding. The problem is=20
>present with
>version 8.0.0-rc2.


UNICODE encoding is not properly supported on Win32 at this time.=20

//Magnus