MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 10 Jun 2024 10:02:14 -0400
Message-ID: <CANzqJaD0=V+Dh+mLLfLj4ECLHriAH122LiYaqKyAnWCAKCfa6A@mail.gmail.com>
Subject: Escaping single quotes with backslash seems not to work
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000675ae6061a8999b9"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD0%3DV%2BDh%2BmLLfLj4ECLHriAH122LiYaqKyAnWCAKCfa6A%40mail.gmail.com>
Precedence: bulk

--000000000000675ae6061a8999b9
Content-Type: text/plain; charset="UTF-8"

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)

--000000000000675ae6061a8999b9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">PG 9.6 and PG 14<div><br><div><a href=3D"https://www.postg=
resql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS">https://www=
.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS</a><br=
></div></div><div><br></div><div><span style=3D"color:rgb(0,0,0);font-famil=
y:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">[quote]</span></div><d=
iv><span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-s=
erif;font-size:14.4px">Any other character following a backslash is taken l=
iterally. Thus, to include a backslash character, write two backslashes (</=
span><code class=3D"gmail-literal" style=3D"box-sizing:border-box;font-fami=
ly:monospace,monospace;font-size:14.4px;color:rgb(0,0,0);border-radius:0.25=
rem;margin:0.6rem 0px">\\</code><span style=3D"color:rgb(0,0,0);font-family=
:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">). Also, a single quote=
 can be included in an escape string by writing=C2=A0</span><code class=3D"=
gmail-literal" style=3D"box-sizing:border-box;font-family:monospace,monospa=
ce;font-size:14.4px;color:rgb(0,0,0);border-radius:0.25rem;margin:0.6rem 0p=
x">\&#39;</code><span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans=
&quot;,sans-serif;font-size:14.4px">, in addition to the normal way of=C2=
=A0</span><code class=3D"gmail-literal" style=3D"box-sizing:border-box;font=
-family:monospace,monospace;font-size:14.4px;color:rgb(0,0,0);border-radius=
:0.25rem;margin:0.6rem 0px">&#39;&#39;</code><span style=3D"color:rgb(0,0,0=
);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">.</span><b=
r></div><div><span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&qu=
ot;,sans-serif;font-size:14.4px">[/quote]</span></div><div><span style=3D"c=
olor:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4=
px"><br></span></div><div><span style=3D"color:rgb(0,0,0);font-family:&quot=
;Open Sans&quot;,sans-serif;font-size:14.4px">But it doesn&#39;t seem to wo=
rk.=C2=A0 Obviously there&#39;s some misconfiguration or , but I don&#39;t =
see what I did wrong.</span></div><div><span style=3D"color:rgb(0,0,0);font=
-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px"><br></span></div=
><div><font face=3D"monospace">TAP=3D# insert into foo (name, description) =
values (&#39;XYZ_Name &#39;, &#39;&#39;&#39;XYZ &#39;&#39;&#39;);<br>INSERT=
 0 1<br><br>TAP=3D# insert into foo (name, description) values (&#39;XYZ_Na=
me &#39;, &#39;\&#39;XYZ &#39;);<br>TAP&#39;# <br>TAP&#39;# &#39;);<br>ERRO=
R: =C2=A0syntax error at or near &quot;XYZ&quot;<br>LINE 1: ...into foo (na=
me, description) values (&#39;XYZ_Name &#39;, &#39;\&#39;XYZ &#39;);<br></f=
ont></div><div><font face=3D"monospace"><br></font></div><div><font face=3D=
"monospace">TAP=3D# show standard_conforming_strings;<br>=C2=A0standard_con=
forming_strings <br>-----------------------------<br>=C2=A0on<br>(1 row)<br=
><br>TAP=3D# <br>TAP=3D# show backslash_quote;<br>=C2=A0backslash_quote <br=
>-----------------<br>=C2=A0safe_encoding<br>(1 row)<br></font></div><div><=
br></div><div><br></div></div>

--000000000000675ae6061a8999b9--