Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#6566 closed defect (fixed)

PostGIS driver: wrong bytea encoding for OFTBinary

Reported by: jeromeG Owned by: warmerdam
Priority: normal Milestone: 2.0.3
Component: OGR_SF Version: 2.1.0
Severity: normal Keywords: PostGIS, binary, BLOB


I have a PostGIS table with a column from type "bytea" where I want to store an xml file.

First, using OGR_F_SetFieldBinary() I write:

<?xml version="1.0"?>

then using OGR_F_GetFieldAsBinary() I read it back and get:


I didn't expect the spaces and quotation marks to be encoded!

I had a look at the usage of the function GByteArrayToBYTEA in ogrpgdumplayer.cpp of GDAL 2.1. The code seems to generate the following sql statement:

INSERT INTO ... VALUES (..., '<?xml\\040version=\\0421.0\\042?>', ...)

According to this is no correct. Correct would be either without double "\":

INSERT INTO ... VALUES (..., '<?xml\040version=\0421.0\042?>', ...)

or with an "E" before:

INSERT INTO ... VALUES (..., E'<?xml\\040version=\\0421.0\\042?>', ...)

This is probably the cause of the problem.

Change History (6)

comment:1 by Even Rouault, 6 years ago

Resolution: fixed
Status: newclosed

In 34481:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:2 by Even Rouault, 6 years ago

In 34482:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:3 by Even Rouault, 6 years ago

In 34483:

PG: fix insertion of binary/bytea content in non-copy mode (fixes #6566)

comment:4 by Even Rouault, 6 years ago

Milestone: 2.0.3

comment:5 by JeromeG, 6 years ago

Thanks for the quick reaction.

I am not sure, but it might depends on the Progresql/PostGIS version. As stated at the setting standard_conforming_strings was changed from "off" to "on" in Progresql 9.1.

Some older versions do already have this setting and do understand the syntax with the "E". For example I could see that in the documentation of Progresql 8.1 (

Much older versions might not understand it. Depending on the policy of GDAL about supporting older version of Progresql/PostGIS, it might necessary to react differently for older versions.

comment:6 by Even Rouault, 6 years ago

Yes, I'm aware this might break ancient Postgres. But I've verified this does work at least on Postgres 8.4 which is already quite ancient. Perhaps the 7.X series might be broken, but anyway I doubt folks still use that, and binary fields aren't commonly used.

Note: See TracTickets for help on using tickets.