Обсуждение: Problem of capital case-insensitive letter with accent

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.

Lan

---

Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail

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
> 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.
>
> Lan
>
>
>  ________________________________
> Be smarter than spam. See how smart SpamGuard is at giving junk email the
> boot with the All-new Yahoo! Mail
>
>

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

>
> Colin
>
>

"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