Putty is showing ISO-8858-1 which is Latin. I believe both client and server must be UTF-8.
From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of John Gunther
Sent: Friday, October 12, 2007 11:59 AM
Subject: [NOVICE] accented characters migraine
It seems to me this ought to be simple and clearly documented but I've
spent hours researching and experimenting to no avail.
PROBLEM: Entering accented characters in psql often results in the
error: invalid byte sequence for encoding "UTF8"
Client OS: Windows XP
Keyboard: United States-International
Terminal program: putty.exe, Translation: ISO-8859-1:1998 (Latin-1, West
Server OS: Ubuntu
Server client app: psql 8.2.4
Server db app: PostgreSQL 8.2.4
initdb defaulted to UTF-8, which I need because I want ORDER BY to sort
alphabetically, not by hex code.
When I try to insert a string with an accented character, I generally
get the above error. Simple example:
template1=# \d sorttest
id | integer
test | text
template1=# insert into sorttest (test) values ('ã');
ERROR: invalid byte sequence for encoding "UTF8": 0xe32729
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
The accented character (a-tilde) is entered from the Windows keyboard
with the ~a sequence and displays properly in psql. The problem is that
the server rejects it.
1) The Unicode hex value of a-tilde is C3 A3 but the error message says
the invalid sequence is E3 27 29. I don't know what the first byte means
but the second and third are the quote and right parenthesis characters
following the a-tilde in my insert statement.
2) At various times, data entry as above has started working in a
session but I can't figure out what I did to make it happen.
3) I tried entering the character in hex, as I understand it: insert
into sorttest (test) values (E'\xc3\xa3');
This avoids the error but the string value then displays as the 2
seemingly irrelevant characters Ã£ (A-tilde, British pound)
It looks like I'm caught in some interaction between putty, psql and pg.
The real problem is much more grave than just manual data entry-- I'm
trying to migrate a large existing database from another pg server with:
pg_dumpall -h nnn.nnn.nnn.nnn | psql
This throws errors each time the COPY commands encounter an accented
character in the dump.
Any ideas? Is this just a bonehead mistake on my part?
---------------------------(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
In response to
pgsql-novice by date
|Next:||From: Oliver Elphick||Date: 2007-10-12 16:45:17|
|Subject: Re: accented characters migraine|
|Previous:||From: Brett Maton||Date: 2007-10-12 16:19:45|
|Subject: Re: Problem with PG_GETARG_CSTRING |