PostgreSQL 字符串转义设置 [英] PostgreSQL string escaping settings

问题描述

我有 2 个服务器:S1、S2 具有相同的 SELECT version() 和相同的数据库 test 包含具有 text[] 类型列的表 t1.

I have 2 servers: S1, S2 with the same SELECT version() with the same databases test containing a table t1 that has column of type text[].

我尝试在其中一个插入带有符号 " 的 2 个字符串数组:

I try to insert array of 2 strings with symbol " in one of them:

INSERT into t1 (columnname) VALUES (`{"str1", "str2\"with quote symbol"}`)

S1 运行良好，但 S2 抛出错误:

S1 works good, but S2 throws an error:

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"}`)

现在 S2 有效，但 S1 说:

Now S2 works, but S1 says:

ERROR:  malformed array literal: "{"str1", "str2\\"with quote symbol"}"

postgres 中是否有一些转义设置?

Is there some escaping settings somewhere in postgres?

服务器是通过 2 个独立的 pgbouncer 实例访问的，但我认为这与问题无关.

The servers is accessed via 2 independent pgbouncer instances, but i think that is not related to question.

推荐答案

在(标准)SQL 中转义单引号是通过使用两个单引号完成的，例如
'Peter's house'

Escaping a single quote in (standard) SQL is done by using two single quotes, e.g.
'Peter''s house'

我更喜欢使用显式的 ARRAY[..] ，它也需要少一个引号，所以你的插入可以写成:

I prefer using the explicit ARRAY[..] which also needs one less quote, so your insert could be written as:

INSERT into t1 (columnname) 
VALUES (ARRAY['str1', 'str2''with quote symbol']);

在 9.1 之前的版本中，Postgres 允许使用 \ 作为替代转义字符，但如果使用则会记录警告.从 9.1 开始，配置参数 standard_conforming_strings 已启用，因此 \ 不能用作单引号的转义符.

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_strings is enabled and thus the \ can't be used as an escape a single quote.

这篇关于PostgreSQL 字符串转义设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！