On Fri, Feb 27, 2015 at 8:58 AM, Adam Hooper <adam@adamhooper.com> wrote:
> On Fri, Feb 27, 2015 at 9:39 AM, Melvin Call <melvincall979@gmail.com> wrote:
>> On 2/26/15, Vick Khera <vivek@khera.org> wrote:
>
>>> 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.
>>
>> 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?
>
> The reality is confusing: when moving text around, every step of the
> process involves a character set conversion.
>
> I'm guessing your MySQL client is defaulting to character_set_client =
> 'iso-8859-1' or some-such. Depending on your client, that could be
> because your _terminal_ is set to iso-8859-1 encoding. (If you're on
> Unix, type `locale` and if you don't see lots of "UTF-8"s your
> terminal probably isn't using UTF-8.)
Ah, that makes perfect sense.
>
> But really, there are so many variables it's only an issue if you're
> trying to change the way the MySQL client is behaving. And I don't
> think this is the list for that.
Agreed. I didn't realize that the MySQL client would be the issue, but I think
you've hit it. This is in preparation for moving away from MySQL so no need to
pursue it much further. I just need to get the information out once, and y'all
have helped me get there. Thanks!
>
> MySQL's encoding logic is written up here:
> http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html. (The
> way the data is stored is in a completely separate web page:
> http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html ...
> which only has a tiny hint at the bottom of the page about UTF-8 in
> the MySQL command-line client.)
>
> Enjoy life,
> Adam
>
> --
> Adam Hooper
> +1-613-986-3339
> http://adamhooper.com