Problem with the accents

I have a problem at the time of requete containing accents with
PostgreSQL version 6.5.3 :

Via psql, the following requete does not function :

SELECT id_dico, name
FROM dico_fr
WHERE name ~* '^b�'
ORDER BY name;

The tables are coded in UNICODE.

Are you sure that you put the query in UTF-8 encoding?
--
Tatsuo Ishii

Tatsuo Ishii wrote:

I have a problem at the time of requete containing accents with
PostgreSQL version 6.5.3 :

Via psql, the following requete does not function :

SELECT id_dico, name
FROM dico_fr
WHERE name ~* '^b�'
ORDER BY name;

The tables are coded in UNICODE.

Are you sure that you put the query in UTF-8 encoding?

Yes :

essai=> SHOW CLIENT_ENCODING;
NOTICE: Current client encoding is UNICODE
SHOW VARIABLE

essai=> \l
datname |datdba|encoding|datpath
------------------+------+--------+------------
template1 | 31| 5|template1
essai | 1000| 5|essai

Encoding 5 corresponds has the UNICODE.

Here the sequence of command :

essai=> SELECT * FROM dico_fr WHERE nom ~* '^b�';
essai'>
essai'> '
essai-> ;
id_dico|nom
-------+---
(0 rows)

I am obliged to add a quote and a semicolon to finish the request. :-(

I have the same problem when the requests are sent via PHP, even by
making them precede by:
pg_exec($db_conn, "SET NAMES 'UNICODE'");

Thank you for your assistance.
--
==============================================
| FREDERIC MASSOT |
| http://www.juliana-multimedia.com |
| mailto:frederic@juliana-multimedia.com |
===========================Debian=GNU/Linux===

Are you sure that you put the query in UTF-8 encoding?

Yes :

essai=> SHOW CLIENT_ENCODING;
NOTICE: Current client encoding is UNICODE
SHOW VARIABLE

My point is whether you put the character in UTF-8 or not. Try:

$ echo 'e' > e.txt (e is actually e + accent)
$ od -x e.txt

and show me the result of "cat e.txt"
--
Tatsuo Ishii

You are definitely inputting ISO 8859-1 characters, not UTF-8. That's
the source of your problem.

Tatsuo Ishii wrote:

You are definitely inputting ISO 8859-1 characters, not UTF-8. That's
the source of your problem.

Hello, In fact, we create and lodge Web sites and we use
PostgreSQL/Apache/PHP.

I parameterized the encoding in "UNICODE" thinking that it was most
flexible.

We are French, but we have also Brazilian customers.

So you need to have an Unicode database but your client apps does not
have the capability to input Unicode, right?

Then the only solution would be upgrading to 7.1 and turning on
--enable-unicode-conversion. 7.1 would do the conversion between ISO
8859 and Unicode on the server side.

According to you, by using PostgreSQL 6.5.3 (the passage to version 7.1
is planned for the end of the year) can encoding MULE_INTERNAL solve the
problem ?

Yes. create a MULE_INTERNAL database and use set client_encoding to
whatever...

Thank you.

PS: Sorry for my English: -)

Me too:-)
--
Tatsuo Ishii