Re: Translate function and strange results ...

On Mon, Nov 2, 2009 at 12:07 AM, Hervé Piedvache <bill.footcow@gmail.com> wrote:
> Hi Tom,
>
> Thar's mean I need to convert my database in other enconding ?
>

No you don't. The problem is with the encoding of the query:

test=# \encoding
SQL_ASCII
test=# SELECT convert('Hervé', 'UTF-8', 'LATIN1');
 convert
----------
 Herv\351
(1 row)

test=# SELECT translate(E'Herv\351',
E'\342\340\344\351\350\352\353\357\366\364\371\374\373',
'aaaeeeeioouuu');
 translate
-----------
 Herve
(1 row)

Or you can also change your terminal character encoding to ISO-8859-1 encoding:

test=# \encoding
SQL_ASCII
test=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
 translate
-----------
 Herve
(1 row)

Or even iconv can help:

postgres@ub64:~$ cat > test.sql
select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
postgres@ub64:~$ file test.sql
test.sql: UTF-8 Unicode text
postgres@ub64:~$ psql test < test.sql
 translate
-----------
 Hervai
(1 row)

postgres@ub64:~$ iconv -t iso-8859-1 < test.sql > latin.sql
postgres@ub64:~$ file latin.sql
latin.sql: ISO-8859 text
postgres@ub64:~$ psql test < latin.sql
 translate
-----------
 Herve
(1 row)