Subject: Escaping single quotes with backslash seems not to work

From: Ron Johnson <[email-masked]>
Date: Mon, 10 Jun 2024 10:02:14 -0400

PG 9.6 and PG 14

https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

[quote]
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 ''.
[/quote]

But it doesn't seem to work.  Obviously there's some misconfiguration or ,
but I don't see what I did wrong.

TAP=# insert into foo (name, description) values ('XYZ_Name ', '''XYZ ''');
INSERT 0 1

TAP=# insert into foo (name, description) values ('XYZ_Name ', '\'XYZ ');
TAP'#
TAP'# ');
ERROR:  syntax error at or near "XYZ"
LINE 1: ...into foo (name, description) values ('XYZ_Name ', '\'XYZ ');

TAP=# show standard_conforming_strings;
 standard_conforming_strings
-----------------------------
 on
(1 row)

TAP=#
TAP=# show backslash_quote;
 backslash_quote
-----------------
 safe_encoding
(1 row)

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 10 Jun 2024 07:07:44 -0700

On Mon, Jun 10, 2024 at 7:02 AM Ron Johnson <[email-masked]> wrote:

> quote can be included in an escape string by writing \', in addition to
> the normal way of ''.
> [/quote]
>
>
The link you provided goes to the wrong subsection.  The following
subsection, which discusses, String Constants With C-Style Escapes,
requires that you write the literal as E'abc\'def'

Note the E prefix on the literal, which is the thing that enables
considering backslash as an escape.

David J.