PostgreSQL 字符串转义设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19812597/
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
PostgreSQL string escaping settings
提问by pavelkolodin
I have 2 servers: S1, S2with the same SELECT version()with the same databases testcontaining a table t1that has column of type text[].
我有 2 个服务器：S1，S2具有SELECT version()相同的数据库，test其中包含一个t1具有类型列的表text[]。
I try to insert array of 2 strings with symbol "in one of them:
我尝试"在其中一个插入带有符号的 2 个字符串数组：
INSERT into t1 (columnname) VALUES (`{"str1", "str2\"with quote symbol"}`)
S1works good, but S2throws an error:
S1效果很好，但S2会引发错误：
ERROR: malformed array literal: "{"str1", "str2"with quote symbol"}"
Lets add one more \to the request:
让我们\在请求中再添加一个：
INSERT into t1 (columnname) VALUES (`{"str1", "str2\"with quote symbol"}`)
Now S2works, but S1says:
现在S2有效，但S1说：
ERROR: malformed array literal: "{"str1", "str2\"with quote symbol"}"
Is there some escaping settings somewhere in postgres?
postgres 中的某处是否有一些转义设置？
The servers is accessed via 2 independent pgbouncer instances, but i think that is not related to question.
服务器是通过 2 个独立的 pgbouncer 实例访问的，但我认为这与问题无关。
回答by a_horse_with_no_name
Escaping a single quote in (standard) SQL is done by using two single quotes, e.g.'Peter''s house'
在（标准）SQL 中转义单引号是通过使用两个单引号完成的，例如'Peter''s house'
I prefer using the explicit ARRAY[..]which also needs one less quote, so your insert could be written as:
我更喜欢使用显式ARRAY[..]，它也需要少一个引号，所以你的插入可以写成：
INSERT into t1 (columnname)
VALUES (ARRAY['str1', 'str2''with quote symbol']);
In versions before 9.1 Postgres allowed to use \as an alternate escaping character but would log a warning if being used. Since 9.1 the config parameter standard_conforming_stringsis enabled and thus the \can't be used as an escape a single quote.
在 9.1 之前的版本中，Postgres 允许\用作替代转义字符，但如果使用则会记录警告。从 9.1 开始，config 参数standard_conforming_strings被启用，因此\不能用作单引号的转义符。
回答by wildplasser
- double quotes are used to quote identifiers
- single quotes are used for string literals
- backticks have no meaning (except in the psql frontend)
- the
VALUESstatement is followed by a comma listof parenthesized expression lists, each expressionlist constitutes one literal row. E'string\'with a single quote'can be used to force C-style backslash escaping. It is a Postgres extension. (the existing SQL way to escape characters inside strings is barely usable)- arrays are also a (debatable) Postgres extension. The outerquotes in the value list are still single quotes; if quotes are needed insidethe
'{ ... , ... }'these need to be double quotes, and backslash-escaping is enabled. (this is Ok, since the insidealready is an extension, so no existing syntax is offended)
- 双引号用于引用标识符
- 单引号用于字符串文字
- 反引号没有意义（除了在 psql 前端）
- 该
VALUES语句后面是一个带括号的表达式列表的逗号列表，每个表达式列表构成一个文字行。 E'string\'with a single quote'可用于强制 C 风格的反斜杠转义。它是一个 Postgres 扩展。（现有的在字符串中转义字符的 SQL 方法几乎不可用）- 数组也是一个（有争议的）Postgres 扩展。值列表中的外引号仍然是单引号；是否需要引号内的
'{ ... , ... }'这些都需要加双引号，反斜杠逃逸被启用。（这很好，因为里面已经是一个扩展，所以没有冒犯现有的语法）
CREATE TABLE t1 (columnname varchar);
INSERT into t1 (columnname) VALUES ('str1') ,( E'str2\'with quote symbol');
CREATE TABLE t2 ("Columnname" varchar[] );
INSERT into t2 ("Columnname") VALUES ( '{ "str1" , "str2\"with a double quote" }' );
SELECT * FROM t1;
SELECT * FROM t2;
