On Aug4, 2011, at 22:54 , Tom Lane wrote: > "Petro Meier" <petr...@gmx.de> writes: >> INSERT INTO "testtable" ("ID", "BinaryContents") values (1, >> E'\xea2abd8ef3'); >> returns "invalid byte sequence". > >> '\xea2abd8ef3' is the string delivered by the PG 9.1 Beta3 server >> when calling PQescapeByteaConn(). It cannot be further processed by the >> server itself afterwards! There is a leading '\' missing. > > No, there isn't. What you are doing wrong is prepending an E to the > literal. You should not be doing that, neither in 9.1 nor any previous > version.
Just to clarify what's going on here, in case the OP is still puzzled. Postgres supports both a legacy mode where backslashes serve as an escape character in single-quotes strings, and an SQL standard-compliant mode where they don't. The mode is chosen by setting the GUC standard_conforming_strings to either on of off. Independent of the current standard_conforming_strings setting, once can always force a strings to be interpreted with legacy semantics (i.e. with backslash as an escape character) by prefixing the string literal with E. Thus, assuming that standard_conforming_strings is set to on, a string containing exactly one backslash can be written as either '\' or E'\\', while with standard_conforming_strings set to off, you'd have to use '\\' or E'\\' PQescapeByteaConn() emits one backslash if it detects that standard_conforming_strings is set to "on" for the given connection, and two if it detects "off". The string is thus always correctly interpreted by the backend as long as you *don't* prefix it with E. If you do, you force the backend to always interpret it with legacy semantics. Which of course causes trouble if standard_conforming_strings is set to "on", because then PQescapeByteAConn()'s expectation of the backend's behaviour (standard mode) and it's actual behaviour (legacy mode) no longer match. The reason that things appeared to work for you on 9.0 is that all versions before 9.1 have standard_conforming_strings set to "off" by default. If you try your code on 9.0, but with standard_conforming_strings set to "on", you'll observe the same breakage you observe on 9.1 Exactly the same is true for PQescapeStringConn(). best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers