COPY-ing ASCII file into UTF-8 database

Поиск
Список
Период
Сортировка
Искать
От
Toomas Vendelin
Тема
COPY-ing ASCII file into UTF-8 database
Дата
Msg-id
2FAE86BD-E366-4F25-9CE0-3C089F761960@vendelin.com
Список
Дерево обсуждения
COPY-ing ASCII file into UTF-8 database Toomas Vendelin <pg@vendelin.com>
Re: COPY-ing ASCII file into UTF-8 database Tommy Gildseth <tommy.gildseth@usit.uio.no>
Re: COPY-ing ASCII file into UTF-8 database Emanuel Calvo Franco <postgres.arg@gmail.com>
THE ISSUE:
I need to import a text file (ASCII) dumped from MySQL 3.23 database  
into Postgre table (UTF-8). The file contains "specifically Estonian  
characters" Õ, Ä, Ö, Ü. My attempt to COPY this file directly  
failed. Postgre said:

ERROR:  invalid byte sequence for encoding "UTF8": 0xf5706509
HINT:  This error can also happen if the byte sequence does not match  
the encoding expected by the server, which is controlled by  
"client_encoding".

OK... The file isn't big, and there are just 4 characters causing  
problems, so I substitued each of them with a distinctive pattern of  
ASCII characters and successfully COPY-ied the file into Postgre  
table. With PgAdmin I've changed 1 record back to Estonian – worked  
fine. I then exported this table into a file, opened the file in  
UltraEdit (the status bar showed it's a UTF-8 indeed) and changed a  
few temporary substitutions with "specifically Estonian characters".  
Then I cleared the table and tried to COPY data from my now UTF-8 file  
again. I've got an error:

ERROR:  invalid input syntax for integer: "2"
CONTEXT:  COPY school, line 1, column sch_id: "2"

PgAdmin actually displayed it with something like chopped hyphen,  
which I've failed to copy into this message and which wasn't visible  
in the text editor even with "Show hidden characters".

MY QUESTIONS:

1. What is the proper solution for importing data from plain ASCII  
files, that contain the type of characters mentioned above?

2. I'd like to fill the gaps (huge, obviously) in my knowlege about  
handling different charsets. Can you point me to some sane  
resource(s)? I'm sure, there's a lot of them. Sorry for slipping off  
the topic here.

Regards,

Toomas



В списке pgsql-admin по дате отправления
От: Scott Marlowe
Дата:
От: Tommy Gildseth
Дата:
FAQ