PostgreSQL string escaping settings

Question

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[].

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

Now S2 works, but S1 says:

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

Is there some escaping settings somewhere in postgres?

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

Answer 1

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

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']);

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.

Answer 2

• double quotes are used to quote identifiers
• single quotes are used for string literals
• backticks have no meaning (except in the psql frontend)
• the VALUES statement is followed by a comma list of 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 outer quotes in the value list are still single quotes; if quotes are needed inside the '{ ... , ... }' these need to be double quotes, and backslash-escaping is enabled. (this is Ok, since the inside already is an extension, so no existing syntax is offended)

---

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;