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.

Don't use backticks; they mean nothing.

wildplasser
– wildplasser

2013-11-06 12:56:56 +00:00
Commented Nov 6, 2013 at 12:56 — wildplasser
– wildplasser, Commented Nov 6, 2013 at 12:56

user330315 · Accepted Answer · 2013-11-06 14:00:14Z

8

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.

answered Nov 6, 2013 at 14:00

user330315

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2013-11-06 13:28:47Z

6

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;

edited Nov 6, 2013 at 13:28

answered Nov 6, 2013 at 13:00

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

pavelkolodin Over a year ago

What about array of strings? I cannot write '{'a', 'b'}' because second ' will conflict with first '

user330315 Over a year ago

Note that the \ escape character is deprecated and disabled by default since 9.1. The behaviour depends on the value of the configuration parameter standard_conforming_strings: postgresql.org/docs/current/static/…

Collectives™ on Stack Overflow

PostgreSQL string escaping settings

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related