在postgresql中字符串文字和转义字符。

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?

有人知道怎么避开这个吗?

5 个解决方案

#1

105

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.

这抑制了警告，但文本仍然没有正确返回。当我添加了Michael建议的额外的斜杠时，它成功了。

As such:

是这样的:

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

#2

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还接受“escape”字符串常量，这是对SQL标准的扩展。一个转义字符串常数是通过写字母E(大写或小写)来指定的，就在打开单引号之前，例如“E'foo”。(当在一行中继续执行一个转义字符串时，在第一个开头引用之前写E)。在一个转义字符串中，一个反斜杠字符(\)开始一个c类的反斜杠转义序列，其中反斜杠和跟随字符的组合表示一个特殊的字节值。b是一个backspace， \f是一个form feed， \n是一个换行，\r是一个回车，\t是一个标签。还支持的是\数字，其中的数字表示八进制字节值，以及\ xhex位数，其中的六位数表示十六进制字节的值。(您的职责是您创建的字节序列是服务器字符集编码中的有效字符。)在反斜杠后面的任何其他字符都是按字面意思执行的。因此，要包含反斜杠字符，可以写两个反斜杠(\\)。另外，一个单引号可以包含在一个转义字符串中，除了正常的方式外，还可以写“\”。

#3

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到intrepret的反斜杠。

#4

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=>

#5

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

非常愚蠢的问题:您确定字符串被截断了，而不是在您指定的linebreak中(可能没有在您的接口中显示)断开吗?你希望这个领域能显示出来吗?

This will be inserted \n This will not be

这将被插入，这将不会。

或

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?

另外，您使用的是什么接口?有没有可能在你的背后有什么东西在吃你的反斜杠?

#1

105