string postgresql 中的字符串文字和转义字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
String literals and escape characters in postgresql
提问by rjohnston
Attempting to insert an escape character into a table results in a warning.
尝试在表中插入转义字符会导致警告。
For example:
例如：
create table EscapeTest (text varchar(50));
insert into EscapeTest (text) values ('This is the first part \n And this is the second');
Produces the warning:
产生警告：
WARNING: nonstandard use of escape in a string literal
(Using PSQL 8.2)
（使用 PSQL 8.2）
Anyone know how to get around this?
有谁知道如何解决这个问题？
回答by rjohnston
Partially. The text is inserted, but the warning is still generated.
部分。文本已插入，但仍会生成警告。
I found a discussion that indicated the text needed to be preceded with 'E', as such:
我发现了一个讨论，指出文本需要以“E”开头，如下所示：
insert into EscapeTest (text) values (E'This is the first part \n And this is the second');
This suppressed the warning, but the text was still not being returned correctly. When I added the additional slash as Michael suggested, it worked.
这抑制了警告，但仍然没有正确返回文本。当我按照迈克尔的建议添加额外的斜杠时，它起作用了。
As such:
像这样：
insert into EscapeTest (text) values (E'This is the first part \n And this is the second');
回答by Michael Stum
Cool.
凉爽的。
I also found the documentation regarding the E:
我还找到了有关 E 的文档：
http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
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 ''.
PostgreSQL 还接受“转义”字符串常量，它是 SQL 标准的扩展。转义字符串常量通过在开始单引号之前写入字母 E（大写或小写）来指定，例如 E'foo'。（当跨行继续转义字符串常量时，只在第一个左引号之前写 E。）在转义字符串中，反斜杠字符 (\) 开始一个类似 C 的反斜杠转义序列，其中反斜杠和后面的字符 ( s) 表示一个特殊的字节值。\b 是退格符，\f 是换页符，\n 是换行符，\r 是回车符，\t 是制表符。还支持\digits，其中digits 表示八进制字节值，和\xhexdigits，其中hexdigits 表示十六进制字节值。（您有责任确保您创建的字节序列是服务器字符集编码中的有效字符。）反斜杠后面的任何其他字符都按字面意思处理。因此，要包含反斜杠字符，请编写两个反斜杠 (\\)。此外，除了正常的 '' 方式外，还可以通过写入 \' 将单引号包含在转义字符串中。
回答by eppesuig
The warning is issued since you are using backslashes in your strings. If you want to avoid the message, type this command "set standard_conforming_strings=on;". Then use "E" before your string including backslashes that you want postgresql to intrepret.
发出警告是因为您在字符串中使用了反斜杠。如果您想避免该消息，请键入此命令“set standard_conforming_strings=on;”。然后在您的字符串之前使用“E”，包括您希望 postgresql 解释的反斜杠。
回答by Milen A. Radev
I find it highly unlikely for Postgres to truncate your data on input - it either rejects it or stores it as is.
我发现 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=>
回答by Milen A. Radev
Really stupid question: Are you sure the string is being truncated, and not just broken at the linebreak you specify (and possibly not showing in your interface)? Ie, do you expect the field to show as
真的很愚蠢的问题：你确定字符串被截断了，而不仅仅是在你指定的换行符处被破坏（并且可能没有显示在你的界面中）？即，您是否希望该字段显示为
This will be inserted \n This will not be
这将被插入\n 这将不会被插入
or
或者
This will be inserted
This will not be
这将被插入
这不会
Also, what interface are you using? Is it possible that something along the way is eating your backslashes?
另外，你用的是什么接口？是否有可能沿途的某些东西正在吞噬您的反斜杠？
