Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff' - Mailing list pgsql-general

From Chris Worley
Subject Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff'
Date
Msg-id 2c73a7ae0904211115m6d8566e4t353475a6db932546@mail.gmail.com
Whole thread Raw
In response to Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff'  (Steve Crawford <scrawford@pinpointresearch.com>)
Responses Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff'
List pgsql-general
On Tue, Apr 21, 2009 at 1:39 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Chris Worley wrote:
>>
>> Hello,
>>
>> I get the following error when running a sql script containing a COPY
>> command:
>>
>> ERROR:  invalid byte sequence for encoding "UTF8": 0xff...
>>
>> The data I have contains binary data from a tcp dump....
>>
>> Does anybody know how the dump pulls a column with binary data?  It is
>> a varchar column
>
> No, if it it binary data, you need a column of type bytea and you need to
> escape the appropriate characters in your input. For example, if I create a
> table with a single bytea column called "foo" and insert the a record with
> value "^F^O^O" then dump it, the dump will have the following:
>
> COPY byteatest (foo) FROM stdin;
> \\006\\017\\017
> \.

How does pg_dump and pg_restonre handle everything with no errors?
The column is basically telnet buffers which contains escape sequences
so i have bytes such as 0xff, 0x1b...   Piping the output through
iconv helped formatting some of the data, but it appears I am still
left with some, such as 0xff.

It would be nice to know what sql pg_dump uses to create the contents
inside of the copy command.  Any idea what that is?

-chris worley

>
> See http://www.postgresql.org/docs/8.3/static/datatype-binary.html for the
> characters that need to be escaped.
>
> Cheers,
> Steve
>
>

pgsql-general by date:

Previous
From: Steve Crawford
Date:
Subject: Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff'
Next
From: marek.patrzek@gmail.com
Date:
Subject: Yet another "drop table vs delete" question