Re: Translate function and strange results ...

On Mon, Nov 2, 2009 at 12:07 AM, Hervé Piedvache <bill(dot)footcow(at)gmail(dot)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(at)ub64:~$ cat > test.sql
select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
postgres(at)ub64:~$ file test.sql
test.sql: UTF-8 Unicode text
postgres(at)ub64:~$ psql test < test.sql
translate
-----------
Hervai
(1 row)

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