In the default postgres 'standard_conforming_strings' is set to off.
This causes pg_escape to change a string which contains a backslash to two backslashes. (That is: from ' foo\ bar' into 'foo \\ bar')
This then updates the database with the correct data but a warning is shown in the postgres logs:
Code: Select all
WARNING: nonstandard use of \\ in a string literal at character 8
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
Long version:
postgresql < 8.1 interpreted backslashes in strings the same way as in C.
That is: 'foo \t bar' is interpreted as 'foo + tab + bar'
To insert a literal backslash followed by the letter t one has/had to use 'foo \\t bar'.
From postgres 8.1 new variables and new syntax are added:
- variable: 'standard_conforming_strings'
- variable: 'escape_string_warning'
- syntax: E''
When 'standard_conforming_strings' is set to off (the default) then strings are interpreted the same way as in C.
That is: 'foo \t bar' is interpreted as 'foo + tab + bar'
If one wants to insert a literal backslash then one needs to use 'foo \\t bar'.
The effect of this is:
a) database record inserted with 'foo + backslash + t + bar'
b) a warning is shown in the logfile
When 'standard_conforming_strings' is set to on then strings are not interpreted the same way as in C.
That is: 'foo \t bar' is interpreted as 'foo + backslash + t + bar'
If the string needs to be interpreted then one has to use E'foo \t bar' (which is interpreted as 'foo + tab + bar').
(Note: pg_escape correctly checks the value of standard_conforming_strings and behaves as expect when it is 'on' or 'off')