Re: COPY FROM with BYTEA fields - escaping doesn't work

Replying to my own question, but.. the solution seems to be in two stages.

1) Escape your binary data according to the BYTEA escape method.
Eg. "Test\ff\0\9Again" becomes \x54657374ff0009416761696e

2) Escape that string as per COPY escape method.
Eg. \x540065 becomes \\x540065

On 04/10/12 12:58, Toby Corkindale wrote:
> Hi,
> I'm trying to use the COPY .. FROM system with some data which includes
> binary values. They aren't large, but they include invalid UTF8 bytes,
> so I'm storing them into a BYTEA field.
>
> However I get errors when I try to do this..
>
>
> CREATE TABLE foo (id SERIAL PRIMARY KEY, bar BYTEA);
> COPY foo (bar) FROM STDIN;
> Test\xff\x0\x9Again
> \.
>
> The error given is:
> ERROR: invalid byte sequence for encoding "UTF8": 0xff
> CONTEXT: COPY foo, line 1: "Test\xff\x0\x9Again"
>
>
> As far as I can tell, I'm obeying the instructions about escaping here:
> http://www.postgresql.org/docs/current/static/sql-copy.html
>
> I'm on version 9.1.5 of PostgreSQL.
>
> Thanks,
> Toby
>
>

--
.signature