Skip to content

Commit 9b3477c

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 29196e1 commit 9b3477c

File tree

5 files changed

+72
-80
lines changed

5 files changed

+72
-80
lines changed

‎doc/src/sgml/array.sgml

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

739-
<note>
740-
<para>
741-
Remember that what you write in an SQL command will first be interpreted
742-
as a string literal, and then as an array. This doubles the number of
743-
backslashes you need. For example, to insert a <type>text</> array
744-
value containing a backslash and a double quote, you'd need to write:
745-
<programlisting>
746-
INSERT ... VALUES (E'{"\\\\","\\""}');
747-
</programlisting>
748-
The escape string processor removes one level of backslashes, so that
749-
what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
750-
In turn, the strings fed to the <type>text</> data type's input routine
751-
become <literal>\</> and <literal>"</> respectively. (If we were working
752-
with a data type whose input routine also treated backslashes specially,
753-
<type>bytea</> for example, we might need as many as eight backslashes
754-
in the command to get one backslash into the stored array element.)
755-
Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
756-
used to avoid the need to double backslashes.
757-
</para>
758-
</note>
759-
760739
<tip>
761740
<para>
762741
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
@@ -1255,7 +1255,7 @@ SELECT b, char_length(b) FROM test2;
12551255
strings are distinguished from character strings in two
12561256
ways. First, binary strings specifically allow storing
12571257
octets of value zero and other <quote>non-printable</quote>
1258-
octets (usually, octets outside the range 32 to 126).
1258+
octets (usually, octets outside the decimal range 32 to 126).
12591259
Character strings disallow zero octets, and also disallow any
12601260
other octet values and sequences of octet values that are invalid
12611261
according to the database's selected character set encoding.
@@ -1267,9 +1267,10 @@ SELECT b, char_length(b) FROM test2;
12671267
</para>
12681268

12691269
<para>
1270-
The <type>bytea</type> type supports two external formats for
1271-
input and output: <productname>PostgreSQL</productname>'s historical
1272-
<quote>escape</quote> format, and <quote>hex</quote> format. Both
1270+
The <type>bytea</type> type supports two
1271+
formats for input and output: <quote>hex</quote> format
1272+
and <productname>PostgreSQL</productname>'s historical
1273+
<quote>escape</quote> format. Both
12731274
of these are always accepted on input. The output format depends
12741275
on the configuration parameter <xref linkend="guc-bytea-output">;
12751276
the default is hex. (Note that the hex format was introduced in
@@ -1307,7 +1308,7 @@ SELECT b, char_length(b) FROM test2;
13071308
<para>
13081309
Example:
13091310
<programlisting>
1310-
SELECT E'\\xDEADBEEF';
1311+
SELECT '\xDEADBEEF';
13111312
</programlisting>
13121313
</para>
13131314
</sect2>
@@ -1327,7 +1328,7 @@ SELECT E'\\xDEADBEEF';
13271328
convenient. But in practice it is usually confusing because it
13281329
fuzzes up the distinction between binary strings and character
13291330
strings, and also the particular escape mechanism that was chosen is
1330-
somewhat unwieldy. So this format should probably be avoided
1331+
somewhat unwieldy. Therefore, this format should probably be avoided
13311332
for most new applications.
13321333
</para>
13331334

@@ -1340,7 +1341,7 @@ SELECT E'\\xDEADBEEF';
13401341
octal value and precede it
13411342
by a backslash (or two backslashes, if writing the value as a
13421343
literal using escape string syntax).
1343-
Backslash itself (octet value 92) can alternatively be represented by
1344+
Backslash itself (octet decimal value 92) can alternatively be represented by
13441345
double backslashes.
13451346
<xref linkend="datatype-binary-sqlesc">
13461347
shows the characters that must be escaped, and gives the alternative
@@ -1364,33 +1365,33 @@ SELECT E'\\xDEADBEEF';
13641365
<row>
13651366
<entry>0</entry>
13661367
<entry>zero octet</entry>
1367-
<entry><literal>E'\\000'</literal></entry>
1368-
<entry><literal>SELECT E'\\000'::bytea;</literal></entry>
1369-
<entry><literal>\000</literal></entry>
1368+
<entry><literal>'\000'</literal></entry>
1369+
<entry><literal>SELECT '\000'::bytea;</literal></entry>
1370+
<entry><literal>\x00</literal></entry>
13701371
</row>
13711372

13721373
<row>
13731374
<entry>39</entry>
13741375
<entry>single quote</entry>
1375-
<entry><literal>''''</literal> or <literal>E'\\047'</literal></entry>
1376-
<entry><literal>SELECT E'\''::bytea;</literal></entry>
1377-
<entry><literal>'</literal></entry>
1376+
<entry><literal>''''</literal> or <literal>'\047'</literal></entry>
1377+
<entry><literal>SELECT ''''::bytea;</literal></entry>
1378+
<entry><literal>\x27</literal></entry>
13781379
</row>
13791380

13801381
<row>
13811382
<entry>92</entry>
13821383
<entry>backslash</entry>
1383-
<entry><literal>E'\\\\'</literal> or <literal>E'\\134'</literal></entry>
1384-
<entry><literal>SELECT E'\\\\'::bytea;</literal></entry>
1385-
<entry><literal>\\</literal></entry>
1384+
<entry><literal>'\'</literal> or <literal>'\\134'</literal></entry>
1385+
<entry><literal>SELECT '\\'::bytea;</literal></entry>
1386+
<entry><literal>\x5c</literal></entry>
13861387
</row>
13871388

13881389
<row>
13891390
<entry>0 to 31 and 127 to 255</entry>
13901391
<entry><quote>non-printable</quote> octets</entry>
1391-
<entry><literal>E'\\<replaceable>xxx'</></literal> (octal value)</entry>
1392-
<entry><literal>SELECT E'\\001'::bytea;</literal></entry>
1393-
<entry><literal>\001</literal></entry>
1392+
<entry><literal>'\<replaceable>xxx'</replaceable></literal> (octal value)</entry>
1393+
<entry><literal>SELECT '\001'::bytea;</literal></entry>
1394+
<entry><literal>\x01</literal></entry>
13941395
</row>
13951396

13961397
</tbody>
@@ -1418,7 +1419,7 @@ SELECT E'\\xDEADBEEF';
14181419
of escaping.) The remaining backslash is then recognized by the
14191420
<type>bytea</type> input function as starting either a three
14201421
digit octal value or escaping another backslash. For example,
1421-
a string literal passed to the server as <literal>E'\\001'</literal>
1422+
a string literal passed to the server as <literal>'\001'</literal>
14221423
becomes <literal>\001</literal> after passing through the
14231424
escape string parser. The <literal>\001</literal> is then sent
14241425
to the <type>bytea</type> input function, where it is converted
@@ -1429,12 +1430,24 @@ SELECT E'\\xDEADBEEF';
14291430
</para>
14301431

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

@@ -1457,23 +1470,23 @@ SELECT E'\\xDEADBEEF';
14571470
<entry>92</entry>
14581471
<entry>backslash</entry>
14591472
<entry><literal>\\</literal></entry>
1460-
<entry><literal>SELECT E'\\134'::bytea;</literal></entry>
1473+
<entry><literal>SELECT '\134'::bytea;</literal></entry>
14611474
<entry><literal>\\</literal></entry>
14621475
</row>
14631476

14641477
<row>
14651478
<entry>0 to 31 and 127 to 255</entry>
14661479
<entry><quote>non-printable</quote> octets</entry>
14671480
<entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
1468-
<entry><literal>SELECT E'\\001'::bytea;</literal></entry>
1481+
<entry><literal>SELECT '\001'::bytea;</literal></entry>
14691482
<entry><literal>\001</literal></entry>
14701483
</row>
14711484

14721485
<row>
14731486
<entry>32 to 126</entry>
14741487
<entry><quote>printable</quote> octets</entry>
14751488
<entry>client character set representation</entry>
1476-
<entry><literal>SELECT E'\\176'::bytea;</literal></entry>
1489+
<entry><literal>SELECT '\176'::bytea;</literal></entry>
14771490
<entry><literal>~</literal></entry>
14781491
</row>
14791492

0 commit comments

Comments
 (0)