[GENERAL] Translate function and strange results ...
Hi,
Can someone can explain me why it's run like this with PostgreSQL v8.3.8 ?
base=# select translate('Hervé', 'é', 'e');
translate
---
Herve
(1 row)
base=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaioouuu');
translate
---
Hervai
(1 row)
base=# \encoding
SQL_ASCII
Thanks,
--
Hervé Piedvache
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Translate function and strange results ...
On Sun, Nov 1, 2009 at 4:56 PM, Hervé Piedvache bill.foot...@gmail.comwrote:
base=# select translate('Hervé', 'é', 'e');
translate
---
Herve
(1 row)
base=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaioouuu');
translate
---
Hervai
(1 row)
You are actually doing something like:
select translate(E'Herv\xc3\xa9',
E'\xc3\xa2\xc3\xa0\xc3\xa4\xc3\xa9\xc3\xa8\xc3\xaa\xc3\xab\xc3\xaf\xc3\xb6\xc3\xb4\xc3\xb9\xc3\xbc\xc3\xbb',
E'aaaioouuu');
Which apparently translates \xc3 - a and \xa9 - i.
I don't know why it does that though. Maybe it's the server_encoding. What
does SHOW server_encoding; tell you?
Re: [GENERAL] Translate function and strange results ...
Arjen Nienhuis a.g.nienh...@gmail.com writes: I don't know why it does that though. Maybe it's the server_encoding. What does SHOW server_encoding; tell you? He said SQL_ASCII. translate() will definitely not work nicely with multibyte characters if it doesn't know they are multibyte :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Translate function and strange results ...
Hi Tom, Thar's mean I need to convert my database in other enconding ? Regards, Le dimanche 01 novembre 2009, Tom Lane a écrit : Arjen Nienhuis a.g.nienh...@gmail.com writes: I don't know why it does that though. Maybe it's the server_encoding. What does SHOW server_encoding; tell you? He said SQL_ASCII. translate() will definitely not work nicely with multibyte characters if it doesn't know they are multibyte :-( regards, tom lane -- Hervé Piedvache -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Translate function and strange results ...
On Mon, Nov 2, 2009 at 12:07 AM, Hervé Piedvache bill.foot...@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',
'aaaioouuu');
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é', 'âàäéèêëïöôùüû', 'aaaioouuu');
translate
---
Herve
(1 row)
Or even iconv can help:
postg...@ub64:~$ cat test.sql
select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaioouuu');
postg...@ub64:~$ file test.sql
test.sql: UTF-8 Unicode text
postg...@ub64:~$ psql test test.sql
translate
---
Hervai
(1 row)
postg...@ub64:~$ iconv -t iso-8859-1 test.sql latin.sql
postg...@ub64:~$ file latin.sql
latin.sql: ISO-8859 text
postg...@ub64:~$ psql test latin.sql
translate
---
Herve
(1 row)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
