Problem of capital case-insensitive letter with accent

lan ping wrote:

Hi, there

In our database, some customers' names contain French accent like
É,é,È,è. In one server, case-insensitive search works for capital
letters. For example,
SELECT lower('ÉÈ') could return éè . But the other server cannot.

It is very strange, as the two server use the same Fedora linux, and
the same
version of Postgres(8.2.4). We all use LATIN1 encoding.

Could you give me a hand? Thank you very much.

Are you sure you're using LATIN1 on both?

js=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
js | cww | UTF8
test | cww | LATIN1
[ ...snip... ]

js=# SELECT lower('ÉÈ');
lower
-------
éè
(1 row)

js=# \c test
You are now connected to database "test".
test=# SELECT lower('ÉÈ');
lower
-------
ÉÈ
(1 row)

It seems the UTF8 database performs lower() correctly, but the LATIN1
database does not.

Colin

Hello

On 21/01/2008, lan ping <pinglanmtl@yahoo.ca> wrote:

Hi, there

In our database, some customers' names contain French accent like
É,é,È,è. In one server, case-insensitive search works for capital letters.
For example,
SELECT lower('ÉÈ') could return éè . But the other server cannot.

check locales. Functions lower and upper works only with correct locales.

show all;
....
lc_collate | cs_CZ.UTF-8
...

cs_CZ is for czech and UTF-8 for utf8 encoding

Regards
Pavel Stehule

Hello

Are you sure you're using LATIN1 on both?

js=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
js | cww | UTF8
test | cww | LATIN1
[ ...snip... ]

js=# SELECT lower('ÉÈ');
lower
-------
éè
(1 row)

js=# \c test
You are now connected to database "test".
test=# SELECT lower('ÉÈ');
lower
-------
ÉÈ
(1 row)

It seems the UTF8 database performs lower() correctly, but the LATIN1
database does not.

Colin

when database uses different encoding, than is specified in cluster's
initialization, then lower, upper doesn't work.

Pavel

Pavel Stehule wrote:

when database uses different encoding, than is specified in cluster's
initialization, then lower, upper doesn't work.

Oooh. That's... confusing.

Colin

On 21/01/2008, Colin Wetherbee <cww@denterprises.org> wrote:

Pavel Stehule wrote:

when database uses different encoding, than is specified in cluster's
initialization, then lower, upper doesn't work.

Oooh. That's... confusing.

yes, postgresql support different encodings, but this configuration is
useless with non C locale.

Pavel

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

On 21/01/2008, Colin Wetherbee <cww@denterprises.org> wrote:

Pavel Stehule wrote:

when database uses different encoding, than is specified in cluster's
initialization, then lower, upper doesn't work.

Oooh. That's... confusing.

yes, postgresql support different encodings, but this configuration is
useless with non C locale.

FWIW, I think we've got 8.3 fixed so that it will reject combinations
of locale and encoding that don't work. It's a bit tricky because of
the fact that locale processing isn't too well standardized :-(

regards, tom lane

Yes. I'm sure that we are using LATIN1 on both. ServerA works, but ServerB not. We changed the local of ServerA(as it is a testing server) the same as ServerB, but ServerA still works. Quite strange.

Pavel Stehule <pavel.stehule@gmail.com> wrote: Hello

Are you sure you're using LATIN1 on both?

js=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
js | cww | UTF8
test | cww | LATIN1
[ ...snip... ]

js=# SELECT lower('��');
lower
-------
��
(1 row)

js=# \c test
You are now connected to database "test".
test=# SELECT lower('��');
lower
-------
��
(1 row)

It seems the UTF8 database performs lower() correctly, but the LATIN1
database does not.

Colin

when database uses different encoding, than is specified in cluster's
initialization, then lower, upper doesn't work.

Pavel

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---------------------------------
Instant message from any web browser! Try the new Yahoo! Canada Messenger for the Web BETA