Обсуждение: copy from .. How to get rid of encoding check for bytea coumns
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\x46\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 ?
In my humble opinion, tt definitely should not check the encoding for columns of type bytea.
Cheers, R.G.
Disclaimer: http://www.aps-holding.com/disclaimer.html
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\x46\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 ?
In my humble opinion, tt definitely should not check the encoding for columns of type bytea.
Cheers, R.G.
Disclaimer: http://www.aps-holding.com/disclaimer.html
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