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.