Re: COPY 'invalid byte sequence for encoding "UTF8": 0xff'

On Tue, Apr 21, 2009 at 1:39 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)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
>
>