Re: encoding confusion with \copy command

On 09/17/2014 03:03 AM, Martin Waite wrote:
> Hi,
>
> I have a postgresql 7.4 server and client on Centos 6.4.  The database
> server is using UTF-8 encoding.

First I think we need to establish what version of Postgres you using.
Are you really using 7.4?

>
> I have been exploring the use of the \copy command for importing CSV
> data generated by SQL Server 2008.  SQL Server 2008 export tool does not
> escape quotes that are in the content of fields, and so it is useful to
> be able to specify obscure characters in the quote option in the \copy
> command to work around this issue.
>
> When I run the following commands in psql, I am surprised that QUOTE is
> limited to characters in the range 0x01 - 0x7f, and that UTF8 is
> mentioned in the error message if characters outside the range are chosen:
>
>     \encoding WIN1252
>     \copy yuml from '/tmp/yuml.csv'  WITH CSV HEADER ENCODING 'WIN1252'
>     QUOTE as E'\xff';
>     ERROR:  invalid byte sequence for encoding "UTF8": 0xff


If you are actually on Postgres 7.4 the above would not be a viable command.

>
>
>
> I thought that if the client (psql) is WIN1252, and the CSV file is
> specified as WIN1252, then I could specify any valid WIN1252 character
> as the quote character.   Instead, I am limited to the range of
> characters that can be encoded as a single byte in UTF-8. Actually, 0x00
> is not accepted either, so the range is 0x01 - 0x7F.
>
> Is this a bug or expected behaviour ?
>
> Is it the case that the server does the actual CSV parsing, and that
> given that my server is in UTF8, I am therefore limited to single-byte
> UTF8 characters ?

Actually depending on version you may be limited to ASCII.

>
> regards,
> Martin


--
Adrian Klaver
adrian.klaver@aklaver.com