Re: copy from .. How to get rid of encoding check for bytea coumns

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Roman Golis *EXTERN*" <rgolis(at)aps-holding(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: copy from .. How to get rid of encoding check for bytea coumns
Date: 2012-09-10 08:10:55
Message-ID: D960CB61B694CF459DCFB4B0128514C2086250D9@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Roman Golis wrote:
> I am trying to load data into a rather simple table:
>
> CREATE TABLE "public"."files" (
> "id" SERIAL,
> "idchar" CHAR(32) NOT NULL,
> "content" BYTEA,
> CONSTRAINT "files_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
> with this command:
>
> copy files (idchar, content) from '/data/1.dat' delimiter '|';
>
> The database encoding is UTF-8.
>
> Here is an example of the data file content:
>
>
0C2CCE6941194369B020000B616F1301|\xFF\xD8\xFF\xE0\x00\x10\x4A\x46\x49\x4
6\x00\x01
>
> And I get this error:
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xff
>
> The command
>
> set client_encoding = 'SQL_ASCII';
>
> Does not helps at all, the result is the same error message.
>
> How can I turn off that annoying codepage checking during COPY FROM ?
>
> Isn't it a bug ?

I assume that you are using PostgreSQL 9.1.

There are two mistakes:
1) You didn't tell COPY that you want CSV format.
2) Your bytea is not properly encoded

Fix your data file to look like this:
0C2CCE6941194369B020000B616F1301|\xFFD8FFE000104A4649460001

Then load it like this:
COPY files (idchar, content) FROM '/data/1.dat' (FORMAT 'csv', DELIMITER
'|');

The error message you see is because COPY thinks that the \xFF
is part of the "idchar" character column.

Yours,
Laurenz Albe

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ireneusz Pluta 2012-09-10 14:55:37 Re: application for postgres Log
Previous Message Albe Laurenz 2012-09-10 07:31:29 Re: application for postgres Log