Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem inserting binary data in bytea column of UTF-8 database

Problem inserting binary data in bytea column of UTF-8 database

From
Brice André
Date:
Dear all,

I have a problem when trying to insert binary data in a bytea column of my database. If the binary data contains non-UTF-8 characters, the insert operation is rejected with a "ERROR:  invalid byte sequence for encoding "UTF8"" result.

My database uses UTF-8 encoding and I try to insert from a Windows application written in C++ and using the psql connector to access the database.

I reproduced a minimal example of my problem with the following psql script:

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
\connect test;
CREATE TABLE test (test bytea);
insert into test (test) values (E'\xC744'::bytea);

This script generates same error when executed directly on the postgresql server with "psql" command.

I tried different ways of encoding my binary data (hex, escape, with or wihout the ::bytea cast) but none worked.

Ideally, I would want to find a solution that would not request to change the database schema.

My version of postgresql is a 9.6.7, running on a debian stretch.

Many thanks in advance for your help,

Brice

Re: Problem inserting binary data in bytea column of UTF-8 database

From
Alexey Bashtanov
Date:
Hi Brice,

On 03/07/18 05:28, Brice André wrote:
> insert into test (test) values (E'\xC744'::bytea);

I think double backslash needs to be used.
See 
https://www.postgresql.org/docs/current/static/datatype-binary.html#id-1.5.7.12.9

Best,
   Alex

Re: Problem inserting binary data in bytea column of UTF-8 database

From
"David G. Johnston"
Date:
On Thursday, July 5, 2018, Alexey Bashtanov <bashtanov@imap.cc> wrote:
Hi Brice,

On 03/07/18 05:28, Brice André wrote:
insert into test (test) values (E'\xC744'::bytea);

I think double backslash needs to be used.
See https://www.postgresql.org/docs/current/static/datatype-binary.html#id-1.5.7.12.9

Or lose the "E" marker to turn the escape string literal into an ordinary string literal.

David J.

Re: Problem inserting binary data in bytea column of UTF-8 database

From
Brice André
Date:
Hello Alexey,

You are right!

Double quote disapeared in my C++ program (because not properly escaped) and I performed copy paste of C++ generated SQL query for further tests without realising that there was a missing backslash.

Thanks for your help,
Brice


2018-07-05 15:48 GMT+02:00 Alexey Bashtanov <bashtanov@imap.cc>:
Hi Brice,

On 03/07/18 05:28, Brice André wrote:
insert into test (test) values (E'\xC744'::bytea);

 I think double backslash needs to be used.
See https://www.postgresql.org/docs/current/static/datatype-binary.html#id-1.5.7.12.9

Best,
  Alex