尝试在表中插入转义字符会导致警告。

例如：

create table EscapeTest (text varchar(50));

insert into EscapeTest (text) values ('This is the first part \n And this is the second');

产生警告：

WARNING:  nonstandard use of escape in a string literal

(使用PSQL 8.2)

有人知道如何解决这个问题吗？

解决方案：

部分地。文本已插入，但仍会生成警告。

我发现有一个讨论表明该文本必须以'E'开头，例如：

insert into EscapeTest (text) values (E'This is the first part \n And this is the second');

这样可以消除警告，但是仍然无法正确返回文本。当我按照迈克尔的建议添加额外的斜杠时，它起作用了。

因此：

insert into EscapeTest (text) values (E'This is the first part \n And this is the second');

凉爽的。

我还找到了有关E的文档：

http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab. Also supported are \digits, where digits represents an octal byte value, and \xhexdigits, where hexdigits represents a hexadecimal byte value. (It is your responsibility that the byte sequences you create are valid characters in the server character set encoding.) Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.

真正愚蠢的问题：我们确定字符串已被截断，并且不仅在指定的换行符处中断了(并且可能未在界面中显示)？即，我们希望该字段显示为

This will be inserted \n This will not
  be

或者

This will be inserted 
  
  This will not be

另外，我们在使用什么接口？一路走来有可能吃掉了反斜杠吗？

我发现Postgres不太可能在输入时截断数据，或者拒绝它，或者原样存储它。

milen@dev:~$ psql
Welcome to psql 8.2.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

milen=> create table EscapeTest (text varchar(50));
CREATE TABLE
milen=> insert into EscapeTest (text) values ('This will be inserted \n This will not be');
WARNING:  nonstandard use of escape in a string literal
LINE 1: insert into EscapeTest (text) values ('This will be inserted...
                                              ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
INSERT 0 1
milen=> select * from EscapeTest;
          text
------------------------
 This will be inserted
  This will not be
(1 row)

milen=>

由于在字符串中使用反斜杠，因此发出警告。如果要避免显示此消息，请键入此命令" set standard_conforming_strings = on;"。然后在字符串(包括我们希望Postgresql解释的反斜杠)之前使用" E"。