Skip to content

Commit dabdc7e

Browse files
bmomjianbmomjian
committed
docs: remove use of escape strings and use bytea hex output
standard_conforming_strings defaulted to 'on' in PG 9.1. bytea_output defaulted to 'hex' in PG 9.0. Reported-by: André Hänsel Discussion: https://postgr.es/m/12e601d447ac$345994a0$9d0cbde0$@webkr.de Backpatch-through: 9.3
1 parent 978515d commit dabdc7e

File tree

4 files changed

+70
-78
lines changed

4 files changed

+70
-78
lines changed

‎doc/src/sgml/array.sgml

Lines changed: 3 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -708,9 +708,9 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
708708
For example, elements containing curly braces, commas (or the data type's
709709
delimiter character), double quotes, backslashes, or leading or trailing
710710
whitespace must be double-quoted. Empty strings and strings matching the
711-
word <literal>NULL</> must be quoted, too. To put a double quote or
712-
backslash in a quoted array element value, use escape string syntax
713-
and precede it with a backslash. Alternatively, you can avoid quotes and use
711+
word <literal>NULL</literal> must be quoted, too. To put a double
712+
quote or backslash in a quoted array element value, precede it
713+
with a backslash. Alternatively, you can avoid quotes and use
714714
backslash-escaping to protect all data characters that would otherwise
715715
be taken as array syntax.
716716
</para>
@@ -723,27 +723,6 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
723723
non-whitespace characters of an element, is not ignored.
724724
</para>
725725

726-
<note>
727-
<para>
728-
Remember that what you write in an SQL command will first be interpreted
729-
as a string literal, and then as an array. This doubles the number of
730-
backslashes you need. For example, to insert a <type>text</> array
731-
value containing a backslash and a double quote, you'd need to write:
732-
<programlisting>
733-
INSERT ... VALUES (E'{"\\\\","\\""}');
734-
</programlisting>
735-
The escape string processor removes one level of backslashes, so that
736-
what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
737-
In turn, the strings fed to the <type>text</> data type's input routine
738-
become <literal>\</> and <literal>"</> respectively. (If we were working
739-
with a data type whose input routine also treated backslashes specially,
740-
<type>bytea</> for example, we might need as many as eight backslashes
741-
in the command to get one backslash into the stored array element.)
742-
Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
743-
used to avoid the need to double backslashes.
744-
</para>
745-
</note>
746-
747726
<tip>
748727
<para>
749728
The <literal>ARRAY</> constructor syntax (see

‎doc/src/sgml/datatype.sgml

Lines changed: 42 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1253,7 +1253,7 @@ SELECT b, char_length(b) FROM test2;
12531253
strings are distinguished from character strings in two
12541254
ways. First, binary strings specifically allow storing
12551255
octets of value zero and other <quote>non-printable</quote>
1256-
octets (usually, octets outside the range 32 to 126).
1256+
octets (usually, octets outside the decimal range 32 to 126).
12571257
Character strings disallow zero octets, and also disallow any
12581258
other octet values and sequences of octet values that are invalid
12591259
according to the database's selected character set encoding.
@@ -1265,9 +1265,10 @@ SELECT b, char_length(b) FROM test2;
12651265
</para>
12661266

12671267
<para>
1268-
The <type>bytea</type> type supports two external formats for
1269-
input and output: <productname>PostgreSQL</productname>'s historical
1270-
<quote>escape</quote> format, and <quote>hex</quote> format. Both
1268+
The <type>bytea</type> type supports two
1269+
formats for input and output: <quote>hex</quote> format
1270+
and <productname>PostgreSQL</productname>'s historical
1271+
<quote>escape</quote> format. Both
12711272
of these are always accepted on input. The output format depends
12721273
on the configuration parameter <xref linkend="guc-bytea-output">;
12731274
the default is hex. (Note that the hex format was introduced in
@@ -1305,7 +1306,7 @@ SELECT b, char_length(b) FROM test2;
13051306
<para>
13061307
Example:
13071308
<programlisting>
1308-
SELECT E'\\xDEADBEEF';
1309+
SELECT '\xDEADBEEF';
13091310
</programlisting>
13101311
</para>
13111312
</sect2>
@@ -1325,7 +1326,7 @@ SELECT E'\\xDEADBEEF';
13251326
convenient. But in practice it is usually confusing because it
13261327
fuzzes up the distinction between binary strings and character
13271328
strings, and also the particular escape mechanism that was chosen is
1328-
somewhat unwieldy. So this format should probably be avoided
1329+
somewhat unwieldy. Therefore, this format should probably be avoided
13291330
for most new applications.
13301331
</para>
13311332

@@ -1338,7 +1339,7 @@ SELECT E'\\xDEADBEEF';
13381339
octal value and precede it
13391340
by a backslash (or two backslashes, if writing the value as a
13401341
literal using escape string syntax).
1341-
Backslash itself (octet value 92) can alternatively be represented by
1342+
Backslash itself (octet decimal value 92) can alternatively be represented by
13421343
double backslashes.
13431344
<xref linkend="datatype-binary-sqlesc">
13441345
shows the characters that must be escaped, and gives the alternative
@@ -1362,33 +1363,33 @@ SELECT E'\\xDEADBEEF';
13621363
<row>
13631364
<entry>0</entry>
13641365
<entry>zero octet</entry>
1365-
<entry><literal>E'\\000'</literal></entry>
1366-
<entry><literal>SELECT E'\\000'::bytea;</literal></entry>
1367-
<entry><literal>\000</literal></entry>
1366+
<entry><literal>'\000'</literal></entry>
1367+
<entry><literal>SELECT '\000'::bytea;</literal></entry>
1368+
<entry><literal>\x00</literal></entry>
13681369
</row>
13691370

13701371
<row>
13711372
<entry>39</entry>
13721373
<entry>single quote</entry>
1373-
<entry><literal>''''</literal> or <literal>E'\\047'</literal></entry>
1374-
<entry><literal>SELECT E'\''::bytea;</literal></entry>
1375-
<entry><literal>'</literal></entry>
1374+
<entry><literal>''''</literal> or <literal>'\047'</literal></entry>
1375+
<entry><literal>SELECT ''''::bytea;</literal></entry>
1376+
<entry><literal>\x27</literal></entry>
13761377
</row>
13771378

13781379
<row>
13791380
<entry>92</entry>
13801381
<entry>backslash</entry>
1381-
<entry><literal>E'\\\\'</literal> or <literal>E'\\134'</literal></entry>
1382-
<entry><literal>SELECT E'\\\\'::bytea;</literal></entry>
1383-
<entry><literal>\\</literal></entry>
1382+
<entry><literal>'\'</literal> or <literal>'\\134'</literal></entry>
1383+
<entry><literal>SELECT '\\'::bytea;</literal></entry>
1384+
<entry><literal>\x5c</literal></entry>
13841385
</row>
13851386

13861387
<row>
13871388
<entry>0 to 31 and 127 to 255</entry>
13881389
<entry><quote>non-printable</quote> octets</entry>
1389-
<entry><literal>E'\\<replaceable>xxx'</></literal> (octal value)</entry>
1390-
<entry><literal>SELECT E'\\001'::bytea;</literal></entry>
1391-
<entry><literal>\001</literal></entry>
1390+
<entry><literal>'\<replaceable>xxx'</replaceable></literal> (octal value)</entry>
1391+
<entry><literal>SELECT '\001'::bytea;</literal></entry>
1392+
<entry><literal>\x01</literal></entry>
13921393
</row>
13931394

13941395
</tbody>
@@ -1416,7 +1417,7 @@ SELECT E'\\xDEADBEEF';
14161417
of escaping.) The remaining backslash is then recognized by the
14171418
<type>bytea</type> input function as starting either a three
14181419
digit octal value or escaping another backslash. For example,
1419-
a string literal passed to the server as <literal>E'\\001'</literal>
1420+
a string literal passed to the server as <literal>'\001'</literal>
14201421
becomes <literal>\001</literal> after passing through the
14211422
escape string parser. The <literal>\001</literal> is then sent
14221423
to the <type>bytea</type> input function, where it is converted
@@ -1427,12 +1428,24 @@ SELECT E'\\xDEADBEEF';
14271428
</para>
14281429

14291430
<para>
1430-
<type>Bytea</type> octets are sometimes escaped when output. In general, each
1431-
<quote>non-printable</quote> octet is converted into
1432-
its equivalent three-digit octal value and preceded by one backslash.
1433-
Most <quote>printable</quote> octets are represented by their standard
1434-
representation in the client character set. The octet with decimal
1435-
value 92 (backslash) is doubled in the output.
1431+
<type>Bytea</type> octets are output in <literal>hex</literal>
1432+
format by default. If you change <xref linkend="guc-bytea-output">
1433+
to <literal>escape</literal>,
1434+
<quote>non-printable</quote> octet are converted to
1435+
equivalent three-digit octal value and preceded by one backslash.
1436+
Most <quote>printable</quote> octets are output by their standard
1437+
representation in the client character set, e.g.:
1438+
1439+
<programlisting>
1440+
SET bytea_output = 'escape';
1441+
1442+
SELECT 'abc \153\154\155 \052\251\124'::bytea;
1443+
bytea
1444+
----------------
1445+
abc klm *\251T
1446+
</programlisting>
1447+
1448+
The octet with decimal value 92 (backslash) is doubled in the output.
14361449
Details are in <xref linkend="datatype-binary-resesc">.
14371450
</para>
14381451

@@ -1455,23 +1468,23 @@ SELECT E'\\xDEADBEEF';
14551468
<entry>92</entry>
14561469
<entry>backslash</entry>
14571470
<entry><literal>\\</literal></entry>
1458-
<entry><literal>SELECT E'\\134'::bytea;</literal></entry>
1471+
<entry><literal>SELECT '\134'::bytea;</literal></entry>
14591472
<entry><literal>\\</literal></entry>
14601473
</row>
14611474

14621475
<row>
14631476
<entry>0 to 31 and 127 to 255</entry>
14641477
<entry><quote>non-printable</quote> octets</entry>
14651478
<entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
1466-
<entry><literal>SELECT E'\\001'::bytea;</literal></entry>
1479+
<entry><literal>SELECT '\001'::bytea;</literal></entry>
14671480
<entry><literal>\001</literal></entry>
14681481
</row>
14691482

14701483
<row>
14711484
<entry>32 to 126</entry>
14721485
<entry><quote>printable</quote> octets</entry>
14731486
<entry>client character set representation</entry>
1474-
<entry><literal>SELECT E'\\176'::bytea;</literal></entry>
1487+
<entry><literal>SELECT '\176'::bytea;</literal></entry>
14751488
<entry><literal>~</literal></entry>
14761489
</row>
14771490

0 commit comments

Comments
 (0)