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

From: Brice André <brice(at)famille-andre(dot)be>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problem inserting binary data in bytea column of UTF-8 database
Date: 2018-07-03 04:28:48
Message-ID: CAOBG12mH+BnY=YNzgYyRvbb_cAoizsVSPrdFVZujv=AtSPuySw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Alexey Bashtanov 2018-07-05 13:48:59 Re: Problem inserting binary data in bytea column of UTF-8 database
Previous Message Gerardo Herzig 2018-06-13 15:22:14 Re: Window ?