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

diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index 4385a09cd9798326afa5da38ca94460bbe1d8178..081ba929988dedc6666a994749ba57dcc4a1b49b 100644 (file)
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -740,9 +740,9 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
    For example, elements containing curly braces, commas (or the data type's
    delimiter character), double quotes, backslashes, or leading or trailing
    whitespace must be double-quoted.  Empty strings and strings matching the
-   word <literal>NULL</> must be quoted, too.  To put a double quote or
-   backslash in a quoted array element value, use escape string syntax
-   and precede it with a backslash. Alternatively, you can avoid quotes and use
+   word <literal>NULL</literal> must be quoted, too.  To put a double
+   quote or backslash in a quoted array element value, precede it
+   with a backslash. Alternatively, you can avoid quotes and use
    backslash-escaping to protect all data characters that would otherwise
    be taken as array syntax.
   </para>
@@ -755,27 +755,6 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
    non-whitespace characters of an element, is not ignored.
   </para>
 
- <note>
-  <para>
-   Remember that what you write in an SQL command will first be interpreted
-   as a string literal, and then as an array.  This doubles the number of
-   backslashes you need.  For example, to insert a <type>text</> array
-   value containing a backslash and a double quote, you'd need to write:
-<programlisting>
-INSERT ... VALUES (E'{"\\\\","\\""}');
-</programlisting>
-   The escape string processor removes one level of backslashes, so that
-   what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
-   In turn, the strings fed to the <type>text</> data type's input routine
-   become <literal>\</> and <literal>"</> respectively.  (If we were working
-   with a data type whose input routine also treated backslashes specially,
-   <type>bytea</> for example, we might need as many as eight backslashes
-   in the command to get one backslash into the stored array element.)
-   Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
-   used to avoid the need to double backslashes.
-  </para>
- </note>
-
  <tip>
   <para>
    The <literal>ARRAY</> constructor syntax (see

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1078dde53c8552f3e55dd5bdea6b8d3dcf47c7c2..ed61146e64c887beb3b9e1f111eb6a3c17d6234f 100644 (file)
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1258,7 +1258,7 @@ SELECT b, char_length(b) FROM test2;
     strings are distinguished from character strings in two
     ways.  First, binary strings specifically allow storing
     octets of value zero and other <quote>non-printable</quote>
-    octets (usually, octets outside the range 32 to 126).
+    octets (usually, octets outside the decimal range 32 to 126).
     Character strings disallow zero octets, and also disallow any
     other octet values and sequences of octet values that are invalid
     according to the database's selected character set encoding.
@@ -1270,9 +1270,10 @@ SELECT b, char_length(b) FROM test2;
    </para>
 
    <para>
-    The <type>bytea</type> type supports two external formats for
-    input and output: <productname>PostgreSQL</productname>'s historical
-    <quote>escape</quote> format, and <quote>hex</quote> format.  Both
+    The <type>bytea</type> type supports two
+    formats for input and output: <quote>hex</quote> format
+    and <productname>PostgreSQL</productname>'s historical
+    <quote>escape</quote> format.  Both
     of these are always accepted on input.  The output format depends
     on the configuration parameter <xref linkend="guc-bytea-output">;
     the default is hex.  (Note that the hex format was introduced in
@@ -1310,7 +1311,7 @@ SELECT b, char_length(b) FROM test2;
    <para>
     Example:
 <programlisting>
-SELECT E'\\xDEADBEEF';
+SELECT '\xDEADBEEF';
 </programlisting>
    </para>
   </sect2>
@@ -1330,7 +1331,7 @@ SELECT E'\\xDEADBEEF';
     convenient.  But in practice it is usually confusing because it
     fuzzes up the distinction between binary strings and character
     strings, and also the particular escape mechanism that was chosen is
-    somewhat unwieldy.  So this format should probably be avoided
+    somewhat unwieldy.  Therefore, this format should probably be avoided
     for most new applications.
    </para>
 
@@ -1343,7 +1344,7 @@ SELECT E'\\xDEADBEEF';
     octal value and precede it
     by a backslash (or two backslashes, if writing the value as a
     literal using escape string syntax).
-    Backslash itself (octet value 92) can alternatively be represented by
+    Backslash itself (octet decimal value 92) can alternatively be represented by
     double backslashes.
     <xref linkend="datatype-binary-sqlesc">
     shows the characters that must be escaped, and gives the alternative
@@ -1367,33 +1368,33 @@ SELECT E'\\xDEADBEEF';
       <row>
        <entry>0</entry>
        <entry>zero octet</entry>
-       <entry><literal>E'\\000'</literal></entry>
-       <entry><literal>SELECT E'\\000'::bytea;</literal></entry>
-       <entry><literal>\000</literal></entry>
+       <entry><literal>'\000'</literal></entry>
+       <entry><literal>SELECT '\000'::bytea;</literal></entry>
+       <entry><literal>\x00</literal></entry>
       </row>
 
       <row>
        <entry>39</entry>
        <entry>single quote</entry>
-       <entry><literal>''''</literal> or <literal>E'\\047'</literal></entry>
-       <entry><literal>SELECT E'\''::bytea;</literal></entry>
-       <entry><literal>'</literal></entry>
+       <entry><literal>''''</literal> or <literal>'\047'</literal></entry>
+       <entry><literal>SELECT ''''::bytea;</literal></entry>
+       <entry><literal>\x27</literal></entry>
       </row>
 
       <row>
        <entry>92</entry>
        <entry>backslash</entry>
-       <entry><literal>E'\\\\'</literal> or <literal>E'\\134'</literal></entry>
-       <entry><literal>SELECT E'\\\\'::bytea;</literal></entry>
-       <entry><literal>\\</literal></entry>
+       <entry><literal>'\'</literal> or <literal>'\\134'</literal></entry>
+       <entry><literal>SELECT '\\'::bytea;</literal></entry>
+       <entry><literal>\x5c</literal></entry>
       </row>
 
       <row>
        <entry>0 to 31 and 127 to 255</entry>
        <entry><quote>non-printable</quote> octets</entry>
-       <entry><literal>E'\\<replaceable>xxx'</></literal> (octal value)</entry>
-       <entry><literal>SELECT E'\\001'::bytea;</literal></entry>
-       <entry><literal>\001</literal></entry>
+       <entry><literal>'\<replaceable>xxx'</replaceable></literal> (octal value)</entry>
+       <entry><literal>SELECT '\001'::bytea;</literal></entry>
+       <entry><literal>\x01</literal></entry>
       </row>
 
      </tbody>
@@ -1421,7 +1422,7 @@ SELECT E'\\xDEADBEEF';
     of escaping.)  The remaining backslash is then recognized by the
     <type>bytea</type> input function as starting either a three
     digit octal value or escaping another backslash.  For example,
-    a string literal passed to the server as <literal>E'\\001'</literal>
+    a string literal passed to the server as <literal>'\001'</literal>
     becomes <literal>\001</literal> after passing through the
     escape string parser. The <literal>\001</literal> is then sent
     to the <type>bytea</type> input function, where it is converted
@@ -1432,12 +1433,24 @@ SELECT E'\\xDEADBEEF';
    </para>
 
    <para>
-    <type>Bytea</type> octets are sometimes escaped when output. In general, each
-    <quote>non-printable</quote> octet is converted into
-    its equivalent three-digit octal value and preceded by one backslash.
-    Most <quote>printable</quote> octets are represented by their standard
-    representation in the client character set. The octet with decimal
-    value 92 (backslash) is doubled in the output.
+    <type>Bytea</type> octets are output in <literal>hex</literal>
+    format by default.  If you change <xref linkend="guc-bytea-output">
+    to <literal>escape</literal>,
+    <quote>non-printable</quote> octet are converted to
+    equivalent three-digit octal value and preceded by one backslash.
+    Most <quote>printable</quote> octets are output by their standard
+    representation in the client character set, e.g.:
+
+<programlisting>
+SET bytea_output = 'escape';
+
+SELECT 'abc \153\154\155 \052\251\124'::bytea;
+     bytea
+----------------
+ abc klm *\251T
+</programlisting>
+
+    The octet with decimal value 92 (backslash) is doubled in the output.
     Details are in <xref linkend="datatype-binary-resesc">.
    </para>
 
@@ -1460,7 +1473,7 @@ SELECT E'\\xDEADBEEF';
        <entry>92</entry>
        <entry>backslash</entry>
        <entry><literal>\\</literal></entry>
-       <entry><literal>SELECT E'\\134'::bytea;</literal></entry>
+       <entry><literal>SELECT '\134'::bytea;</literal></entry>
        <entry><literal>\\</literal></entry>
       </row>
 
@@ -1468,7 +1481,7 @@ SELECT E'\\xDEADBEEF';
        <entry>0 to 31 and 127 to 255</entry>
        <entry><quote>non-printable</quote> octets</entry>
        <entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
-       <entry><literal>SELECT E'\\001'::bytea;</literal></entry>
+       <entry><literal>SELECT '\001'::bytea;</literal></entry>
        <entry><literal>\001</literal></entry>
       </row>
 
@@ -1476,7 +1489,7 @@ SELECT E'\\xDEADBEEF';
        <entry>32 to 126</entry>
        <entry><quote>printable</quote> octets</entry>
        <entry>client character set representation</entry>
-       <entry><literal>SELECT E'\\176'::bytea;</literal></entry>
+       <entry><literal>SELECT '\176'::bytea;</literal></entry>
        <entry><literal>~</literal></entry>
       </row>
 

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6531b811d495f386bce9d70380975fc4ad2a752f..8bf467577d1259404a39316540444ee0783a031b 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1563,7 +1563,7 @@
         octal sequences (<literal>\</><replaceable>nnn</>) and
         doubles backslashes.
        </entry>
-       <entry><literal>encode(E'123\\000\\001', 'base64')</literal></entry>
+       <entry><literal>encode('123\000\001', 'base64')</literal></entry>
        <entry><literal>MTIzAAE=</literal></entry>
       </row>
 
@@ -1847,7 +1847,7 @@
         the delimiter.  See <xref linkend="functions-posix-regexp"> for more
         information.
        </entry>
-       <entry><literal>regexp_split_to_array('hello world', E'\\s+')</literal></entry>
+       <entry><literal>regexp_split_to_array('hello world', '\s+')</literal></entry>
        <entry><literal>{hello,world}</literal></entry>
       </row>
 
@@ -1864,7 +1864,7 @@
         the delimiter.  See <xref linkend="functions-posix-regexp"> for more
         information.
        </entry>
-       <entry><literal>regexp_split_to_table('hello world', E'\\s+')</literal></entry>
+       <entry><literal>regexp_split_to_table('hello world', '\s+')</literal></entry>
        <entry><literal>hello</literal><para><literal>world</literal></para> (2 rows)</entry>
       </row>
 
@@ -3033,8 +3033,8 @@ SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
 SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
 <lineannotation>Result: </lineannotation><computeroutput>INSERT INTO "Foo bar" VALUES('O''Reilly')</computeroutput>
 
-SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
-<lineannotation>Result: </lineannotation><computeroutput>INSERT INTO locations VALUES(E'C:\\Program Files')</computeroutput>
+SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
+<lineannotation>Result: </lineannotation><computeroutput>INSERT INTO locations VALUES('C:\Program Files')</computeroutput>
 </screen>
     </para>
 
@@ -3161,7 +3161,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
          <secondary>concatenation</secondary>
         </indexterm>
        </entry>
-       <entry><literal>E'\\\\Post'::bytea || E'\\047gres\\000'::bytea</literal></entry>
+       <entry><literal>'\\Post'::bytea || '\047gres\000'::bytea</literal></entry>
        <entry><literal>\\Post'gres\000</literal></entry>
       </row>
 
@@ -3174,7 +3174,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </entry>
        <entry><type>int</type></entry>
        <entry>Number of bytes in binary string</entry>
-       <entry><literal>octet_length(E'jo\\000se'::bytea)</literal></entry>
+       <entry><literal>octet_length('jo\000se'::bytea)</literal></entry>
        <entry><literal>5</literal></entry>
       </row>
 
@@ -3189,7 +3189,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Replace substring
        </entry>
-       <entry><literal>overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3)</literal></entry>
+       <entry><literal>overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3)</literal></entry>
        <entry><literal>T\\002\\003mas</literal></entry>
       </row>
 
@@ -3202,7 +3202,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </entry>
        <entry><type>int</type></entry>
        <entry>Location of specified substring</entry>
-      <entry><literal>position(E'\\000om'::bytea in E'Th\\000omas'::bytea)</literal></entry>
+      <entry><literal>position('\000om'::bytea in 'Th\000omas'::bytea)</literal></entry>
        <entry><literal>3</literal></entry>
       </row>
 
@@ -3217,7 +3217,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Extract substring
        </entry>
-       <entry><literal>substring(E'Th\\000omas'::bytea from 2 for 3)</literal></entry>
+       <entry><literal>substring('Th\000omas'::bytea from 2 for 3)</literal></entry>
        <entry><literal>h\000o</literal></entry>
       </row>
 
@@ -3236,7 +3236,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
         <parameter>bytes</parameter> from the start
         and end of <parameter>string</parameter>
        </entry>
-       <entry><literal>trim(E'\\000\\001'::bytea from E'\\000Tom\\001'::bytea)</literal></entry>
+       <entry><literal>trim('\000\001'::bytea from '\000Tom\001'::bytea)</literal></entry>
        <entry><literal>Tom</literal></entry>
       </row>
      </tbody>
@@ -3279,7 +3279,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
         <parameter>bytes</parameter> from the start and end of
         <parameter>string</parameter>
       </entry>
-      <entry><literal>btrim(E'\\000trim\\001'::bytea, E'\\000\\001'::bytea)</literal></entry>
+      <entry><literal>btrim('\000trim\001'::bytea, '\000\001'::bytea)</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>
 
@@ -3296,7 +3296,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        Decode binary data from textual representation in <parameter>string</>.
        Options for <parameter>format</> are same as in <function>encode</>.
       </entry>
-      <entry><literal>decode(E'123\\000456', 'escape')</literal></entry>
+      <entry><literal>decode('123\000456', 'escape')</literal></entry>
       <entry><literal>123\000456</literal></entry>
      </row>
 
@@ -3316,7 +3316,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        octal sequences (<literal>\</><replaceable>nnn</>) and
        doubles backslashes.
       </entry>
-      <entry><literal>encode(E'123\\000456'::bytea, 'escape')</literal></entry>
+      <entry><literal>encode('123\000456'::bytea, 'escape')</literal></entry>
       <entry><literal>123\000456</literal></entry>
      </row>
 
@@ -3331,7 +3331,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Extract bit from string
        </entry>
-       <entry><literal>get_bit(E'Th\\000omas'::bytea, 45)</literal></entry>
+       <entry><literal>get_bit('Th\000omas'::bytea, 45)</literal></entry>
        <entry><literal>1</literal></entry>
       </row>
 
@@ -3346,7 +3346,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Extract byte from string
        </entry>
-       <entry><literal>get_byte(E'Th\\000omas'::bytea, 4)</literal></entry>
+       <entry><literal>get_byte('Th\000omas'::bytea, 4)</literal></entry>
        <entry><literal>109</literal></entry>
       </row>
 
@@ -3370,7 +3370,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
         <see>binary strings, length</see>
        </indexterm>
       </entry>
-      <entry><literal>length(E'jo\\000se'::bytea)</literal></entry>
+      <entry><literal>length('jo\000se'::bytea)</literal></entry>
       <entry><literal>5</literal></entry>
      </row>
 
@@ -3386,7 +3386,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        Calculates the MD5 hash of <parameter>string</parameter>,
        returning the result in hexadecimal
       </entry>
-      <entry><literal>md5(E'Th\\000omas'::bytea)</literal></entry>
+      <entry><literal>md5('Th\000omas'::bytea)</literal></entry>
       <entry><literal>8ab2d3c9689aaf18 b4958c334c82d8b1</literal></entry>
      </row>
 
@@ -3402,7 +3402,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Set bit in string
        </entry>
-       <entry><literal>set_bit(E'Th\\000omas'::bytea, 45, 0)</literal></entry>
+       <entry><literal>set_bit('Th\000omas'::bytea, 45, 0)</literal></entry>
        <entry><literal>Th\000omAs</literal></entry>
       </row>
 
@@ -3418,7 +3418,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        <entry>
         Set byte in string
        </entry>
-       <entry><literal>set_byte(E'Th\\000omas'::bytea, 4, 64)</literal></entry>
+       <entry><literal>set_byte('Th\000omas'::bytea, 4, 64)</literal></entry>
        <entry><literal>Th\000o@as</literal></entry>
       </row>
     </tbody>
@@ -4031,7 +4031,7 @@ regexp_replace('foobarbaz', 'b..', 'X')
                                    <lineannotation>fooXbaz</lineannotation>
 regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                    <lineannotation>fooXX</lineannotation>
-regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
+regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                    <lineannotation>fooXarYXazY</lineannotation>
 </programlisting>
    </para>
@@ -4125,7 +4125,7 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
     Some examples:
 <programlisting>
 
-SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
+SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
   foo   
 -------
  the    
@@ -4139,13 +4139,13 @@ SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy d
  dog    
 (9 rows)
 
-SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
+SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
               regexp_split_to_array             
 -----------------------------------------------
  {the,quick,brown,fox,jumps,over,the,lazy,dog}
 (1 row)
 
-SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
+SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
  foo 
 -----
  t         

diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml
index 818e59e32a31a49c33d37f1504d66a6375438040..5fa50f4457926a50b078efda13619f7acfcd46e7 100644 (file)
--- a/doc/src/sgml/lobj.sgml
+++ b/doc/src/sgml/lobj.sgml
@@ -556,7 +556,7 @@ int lo_unlink(PGconn *conn, Oid lobjId);
        Create a large object and store data there, returning its OID.
        Pass <literal>0</> to have the system choose an OID.
       </entry>
-      <entry><literal>lo_from_bytea(0, E'\\xffffff00')</literal></entry>
+      <entry><literal>lo_from_bytea(0, '\xffffff00')</literal></entry>
       <entry><literal>24528</literal></entry>
      </row>
 
@@ -571,7 +571,7 @@ int lo_unlink(PGconn *conn, Oid lobjId);
       <entry>
        Write data at the given offset.
       </entry>
-      <entry><literal>lo_put(24528, 1, E'\\xaa')</literal></entry>
+      <entry><literal>lo_put(24528, 1, '\xaa')</literal></entry>
       <entry></entry>
      </row>
 

diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml
index 9d6768e00624c435b87fb010b5f9632563f23367..f96df693bf4a4f47c0e1b1f89d77a0b2100f5d2c 100644 (file)
--- a/doc/src/sgml/rowtypes.sgml
+++ b/doc/src/sgml/rowtypes.sgml
@@ -501,7 +501,7 @@ SELECT c.somefunc FROM inventory_item c;
    containing a double quote and a backslash in a composite
    value, you'd need to write:
 <programlisting>
-INSERT ... VALUES (E'("\\"\\\\")');
+INSERT ... VALUES ('("\"\\")');
 </programlisting>
    The string-literal processor removes one level of backslashes, so that
    what arrives at the composite-value parser looks like