PostGIS driver: wrong bytea encoding for OFTBinary

[jeromeG]

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:

<?xml\040version=\0421.0\042?>

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 ​https://www.postgresql.org/docs/9.0/static/datatype-binary.html 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.

[Even Rouault]

In 34481:

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

[Even Rouault]

In 34482:

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

[Even Rouault]

In 34483:

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

[JeromeG]

Thanks for the quick reaction.

I am not sure, but it might depends on the Progresql/PostGIS version. As stated at ​https://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html 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 (​https://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS).

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.

[Even Rouault]

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.