Re: Hex characters in COPY input

On 02/27/2015 06:39 AM, Melvin Call wrote:
> On 2/26/15, Vick Khera <vivek(at)khera(dot)org> wrote:
>> On Thu, Feb 26, 2015 at 9:50 PM, Melvin Call <melvincall979(at)gmail(dot)com>
>> wrote:
>>
>>> Montreal where the e is an accented e. The output ends up in the text
>>> file
>>> as
>>> Montr\xe9al, where the xe9 is a single character. When I try to copy that
>>> into
>>> my PostgreSQL table, I get an error "ERROR: invalid byte sequence for
>>> encoding
>>>
>>
>> Character code E9 is not UTF8. Don't tell Postgres you're importing UTF8 if
>> you're not.
>>
> Thank you Vic, adding the ENCODING 'latin1' option to the COPY command worked
> perfectly.
>
> If you don't mind a follow up to your reply, I have tried to understand the
> different character sets and collations, but I guess I still have a lot to
> learn. Your suggestion did not even come close to crossing my mind because the
> MySQL table and database are encoded in UTF8. I assume the conversion to latin1
> happened because I was putting the MySQL query output into a locally stored
> text file? Regardless, can you point me to some reading that would have clued
> me in that e9 is not a UTF8 character? Or is the clue the fact that it was not
> preceeded with 0x00?

For UTF8 characters see here:

http://www.utf8-chartable.de/

For the MySQL part, you are going to detail how you got the data out?

>
> Regards,
> Melvin
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com