agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedProblem inserting binary data in bytea column of UTF-8 database
4+ messages / 3 participants
[nested] [flat]
* Problem inserting binary data in bytea column of UTF-8 database
@ 2018-07-03 04:28 Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 4+ messages in thread
From: Brice André @ 2018-07-03 04:28 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem inserting binary data in bytea column of UTF-8 database
@ 2018-07-05 13:48 Alexey Bashtanov <bashtanov@imap.cc>
parent: Brice André <brice@famille-andre.be>
0 siblings, 2 replies; 4+ messages in thread
From: Alexey Bashtanov @ 2018-07-05 13:48 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; pgsql-sql
Hi Brice,
On 03/07/18 05:28, Brice André wrote:
> insert into test (test) values (E'\xC744'::bytea);
I think double backslash needs to be used.
See
https://www.postgresql.org/docs/current/static/datatype-binary.html#id-1.5.7.12.9
Best,
Alex
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem inserting binary data in bytea column of UTF-8 database
@ 2018-07-05 14:04 David G. Johnston <david.g.johnston@gmail.com>
parent: Alexey Bashtanov <bashtanov@imap.cc>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2018-07-05 14:04 UTC (permalink / raw)
To: Alexey Bashtanov <bashtanov@imap.cc>; +Cc: Brice André <brice@famille-andre.be>; pgsql-sql
On Thursday, July 5, 2018, Alexey Bashtanov <bashtanov@imap.cc> wrote:
> Hi Brice,
>
> On 03/07/18 05:28, Brice André wrote:
>
>> insert into test (test) values (E'\xC744'::bytea);
>>
>
> I think double backslash needs to be used.
> See https://www.postgresql.org/docs/current/static/datatype-bina
> ry.html#id-1.5.7.12.9
>
Or lose the "E" marker to turn the escape string literal into an ordinary
string literal.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem inserting binary data in bytea column of UTF-8 database
@ 2018-07-07 06:42 Brice André <brice@famille-andre.be>
parent: Alexey Bashtanov <bashtanov@imap.cc>
1 sibling, 0 replies; 4+ messages in thread
From: Brice André @ 2018-07-07 06:42 UTC (permalink / raw)
To: Alexey Bashtanov <bashtanov@imap.cc>; +Cc: pgsql-sql
Hello Alexey,
You are right!
Double quote disapeared in my C++ program (because not properly escaped)
and I performed copy paste of C++ generated SQL query for further tests
without realising that there was a missing backslash.
Thanks for your help,
Brice
2018-07-05 15:48 GMT+02:00 Alexey Bashtanov <bashtanov@imap.cc>:
> Hi Brice,
>
> On 03/07/18 05:28, Brice André wrote:
>
>> insert into test (test) values (E'\xC744'::bytea);
>>
>
> I think double backslash needs to be used.
> See https://www.postgresql.org/docs/current/static/datatype-bina
> ry.html#id-1.5.7.12.9
>
> Best,
> Alex
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-07-07 06:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-07-03 04:28 Problem inserting binary data in bytea column of UTF-8 database Brice André <brice@famille-andre.be>
2018-07-05 13:48 ` Alexey Bashtanov <bashtanov@imap.cc>
2018-07-05 14:04 ` David G. Johnston <david.g.johnston@gmail.com>
2018-07-07 06:42 ` Brice André <brice@famille-andre.be>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox