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

Brice André <[email protected]>
Newsgroups gmane.comp.db.postgresql.sql
Message-ID <CAOBG12mH+BnY=YNzgYyRvbb_cAoizsVSPrdFVZujv=AtSPuySw@mail.gmail.com>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.