Re: Libpq: copy file to bytea column

seiliki wrote:
> The data types of tableout.c1 and tablein.c1 are both bytea.
> I first export tableout.c1 to a file:
>
> db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t';
>
> Then I try to import the file to another table.
>
> This works without flaw:
>
> db1=# COPY tablein FROM '/tmp/t';
>
> However, I get the following errors from log when calling
> libpq functions PQputCopyData() and PQputCopyEnd().
>
> 2010-03-06 20:47:42 CST ERROR: invalid byte sequence for
> encoding "UTF8": 0x00
> 2010-03-06 20:47:42 CST HINT: This error can also happen if
> the byte sequence does not match the encoding expected by the
> server, which is controlled by "client_encoding".
> 2010-03-06 20:47:42 CST CONTEXT: COPY in, line 1:
> "TPF0\011Report\000\003Tag\002\365\016ExportFromPage\002\000\014ExportToPage\002\000\006Values\016..."
> 2010-03-06 20:47:42 CST STATEMENT: COPY in FROM STDIN
>
>
> Helps will be much appreciated!

The difference here is that your first examples are COPY TO/FROM
files on the database server, while your failing C code uses
COPY FROP STDIN.

You will not meet encoding problems with the first because
the file /tmp/t is created in the server encoding, so if you read
it later with a COPY FROM, it will automatically be in the encoding
that COPY FROM <file> expects.

When you use COPY FROM STDIN, you send data from the client to the
server, which is something quite different. Data sent from the client
are expected to be in the client encoding, not in the server encoding.

You can use the SQL statement "SET client_encoding = ..." to set the
client encoding to the encoding of your data, or you can use the
PGCLIENTENCODING environment variable to set it automatically.

The error message you are getting indicates that you client_encoding
is currently UTF8.
By the way, the line quoted in your error message does not look like
valid text at all - it contains "zero" bytes, and preceeding each
string there seems to be a byte indicating the length of the string
that follows.

Yours,
Laurenz Albe