Обсуждение: BUG #15261: Insert binary data in bytea rejected due to UTF-8encoding errors

The following bug has been logged on the website:

Bug reference:      15261
Logged by:          Brice André
Email address:      brice@famille-andre.be
PostgreSQL version: 9.6.7
Operating system:   debian stretch
Description:

Dears,

I have a database using UTF-8 encoding and, in which I have bytea columns
where I store binary data.

I followed instructions in
https://www.postgresql.org/docs/9.6/static/datatype-binary.html to encode
the binary data, but it is still rejected due to a "ERROR:  invalid byte
sequence for encoding "UTF8"" error.

I wrote a small script that reproduces the problem in a small database. The
script can be simply copy-pasted in a psql session:

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);

Regards,

Brice

>
> I have a database using UTF-8 encoding and, in which I have bytea columns
> where I store binary data.
>
> I followed instructions in
> https://www.postgresql.org/docs/9.6/static/datatype-binary.html to encode
> the binary data, but it is still rejected due to a "ERROR:  invalid byte sequence
> for encoding "UTF8"" error.
>
> I wrote a small script that reproduces the problem in a small database. The
> script can be simply copy-pasted in a psql session:
>
> 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);
>

You'll notice in the link you provided for your example that you need an extra backslash ('\') for the escape format
youare using. 

Regards,
Gary.