I have to run a series of queries on different versions of PostgreSQL (from 8.3 to 9.6), and I'm having issues with the backslash character because in the 8.x versions it needs to be escaped, in the 9.x versions it does not.

At the moment I run the query SELECT '\\' and check whether the result is "\" or "\\" so that I know if the backslash needs escaping or not.

I was wondering if there's some way, through a query, to tell postgres to treat backslashes always the same way (without tinkering with its configuration, because I do not have access to the configuration of these installations).

CodePudding user response：

This behavior is governed by the parameter standard_conforming_strings, which was introduced in version 8.2.
In version 9.1, the default value of that parameter was changed to on.

If standard_conforming_strings is off, the backslash is an escape character in string literals.

You can change the parameter for the database session using the SET SQL statement, and there is no need to change the configuration. An alternative is to always use &ldquo:extended” string literals (with a leading E in front of the starting single quote) – in such literals the backslash is always an escape character:

SELECT E'\\';

 ?column? 
══════════
 \
(1 row)