首页 / 知识

PostgreSQL中的字符串文字和转义字符

2023-04-11 14:19:00

String literals and escape characters in postgresql

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

例如：

1
2
3
4

CREATE TABLE EscapeTest (text VARCHAR(50));

INSERT INTO EscapeTest (text) VALUES ('This is the first part
And this is the second');

产生警告：

1	WARNING: nonstandard USE OF escape IN a string literal

(使用PSQL 8.2)

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

部分地。插入了文本，但是仍然生成警告。

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

1 2	INSERT INTO EscapeTest (text) VALUES (E'This is the first part And this is the second');

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

因此：

1 2	INSERT INTO EscapeTest (text) VALUES (E'This is the first part \ 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,
is a newline,
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 ''.

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

我发现Postgres不太可能在输入时截断您的数据-它要么拒绝它，要么原样存储它。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

'.
INSERT 0 1
milen=> select * from EscapeTest;
text
------------------------
This will be inserted
This will not be
(1 row)

milen=>

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