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