PostgreSQL standard_conforming_strings and escape_string_warning

背景

escape_string_warning (boolean)  
  
When on, a warning is issued if a backslash (\) appears in an   
ordinary string literal ('...' syntax) and standard_conforming_strings is off.   
The default is on.  
  
Applications that wish to use backslash as escape should be modified   
to use escape string syntax (E'...'), because the default behavior of   
ordinary strings is now to treat backslash as an ordinary character,   
per SQL standard.   
This variable can be enabled to help locate code that needs to be changed.  
  
standard_conforming_strings (boolean)  
  
This controls whether ordinary string literals ('...') treat backslashes literally,   
as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on   
(prior releases defaulted to off). Applications can check this parameter to   
determine how string literals will be processed. The presence of this parameter   
can also be taken as an indication that the escape string syntax (E'...') is supported.   
Escape string syntax (Section 4.1.2.2) should be used if an application desires   
backslashes to be treated as escape characters.  

注意这句话. 在SQL标准中, \作为普通字符使用.

the default behavior of ordinary strings is now to treat backslash as an ordinary character, per SQL standard.  

所以standard_conforming_strings=on时,  \作为普通字符.   
  
standard_conforming_strings=off时,  \作为逃逸字符使用. 但是建议使用E''格式, escape_string_warning 就是用来检测格式的配置.  

例子

1. standard_conforming_strings=on时, \作为普通字符.
如果要将\作为逃逸字符, 必须使用E’‘格式.

digoal=# \set VERBOSITY verbose  
digoal=# set standard_conforming_strings=on;  
SET  
digoal=# set escape_string_warning=on;  
SET  
digoal=# select '\nabc';  
 ?column?   
----------  
 \nabc  
(1 row)  
digoal=# select E'\nabc';  
 ?column?   
----------  
         +  
 abc  
(1 row)  
digoal=# set standard_conforming_strings=on;  
SET  
digoal=# set escape_string_warning=off;  
SET  
digoal=# select '\nabc';  
 ?column?   
----------  
 \nabc  
(1 row)  
digoal=# select E'\nabc';  
 ?column?   
----------  
         +  
 abc  
(1 row)  

2. standard_conforming_strings=off时, \作为逃逸字符. 但是这么使用, escape_string_warning=on时会提示警告.
使用E’‘格式这不会告警.

digoal=# set standard_conforming_strings=off;  
SET  
digoal=# set escape_string_warning=off;  
SET  
digoal=# select '\nabc';  
 ?column?   
----------  
         +  
 abc  
(1 row)  
digoal=# select E'\nabc';  
 ?column?   
----------  
         +  
 abc  
(1 row)  
digoal=# set standard_conforming_strings=off;  
SET  
digoal=# set escape_string_warning=on;  
SET  
digoal=# select '\nabc';  
WARNING:  22P06: nonstandard use of escape in a string literal  
LINE 1: select '\nabc';  
               ^  
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.  
LOCATION:  check_escape_warning, scan.l:1458  
 ?column?   
----------  
         +  
 abc  
(1 row)  
  
digoal=# select E'\nabc';  
 ?column?   
----------  
         +  
 abc  
(1 row)  

参考

1. http://www.postgresql.org/docs/9.3/static/runtime-config-compatible.html

2. http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

digoal’s 大量PostgreSQL文章入口