Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0diw-0000Jx-Vi for pgsql-bugs@arkaria.postgresql.org; Fri, 14 Sep 2018 02:20:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g0div-0006XB-3Y for pgsql-bugs@arkaria.postgresql.org; Fri, 14 Sep 2018 02:20:45 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0diu-0006Wh-LW for pgsql-bugs@lists.postgresql.org; Fri, 14 Sep 2018 02:20:44 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g0din-0003lZ-4g for pgsql-bugs@lists.postgresql.org; Fri, 14 Sep 2018 02:20:43 +0000 Received: from bruce by momjian.us with local (Exim 4.84_2) (envelope-from ) id 1g0dij-0004fH-Te; Thu, 13 Sep 2018 22:20:33 -0400 Date: Thu, 13 Sep 2018 22:20:33 -0400 From: 'Bruce Momjian' To: =?iso-8859-1?Q?Andr=E9_H=E4nsel?= Cc: orchideric@yahoo.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #15373: null / utf-8 Message-ID: <20180914022033.GD23686@momjian.us> References: <153642745785.23144.1699125571356845227@wrigleys.postgresql.org> <20180908173648.GJ28811@momjian.us> <12e601d447ac$345994a0$9d0cbde0$@webkr.de> <20180908203619.GA8672@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="LZvS9be/3tNcYl/X" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <20180908203619.GA8672@momjian.us> User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --LZvS9be/3tNcYl/X Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Sat, Sep 8, 2018 at 04:36:19PM -0400, 'Bruce Momjian' wrote: > On Sat, Sep 8, 2018 at 09:43:18PM +0200, André Hänsel wrote: > > For some reason the example given in the documentation for the "hex" > > format uses such an "escape string literal": > > > > SELECT E'\\xDEADBEEF'; > > > > I found this very confusing. Can this example be changed to a normal > > string literal, like this? > > > > SELECT '\xDEADBEEF'; > > You know, I 100% agree with you. We used the E'' syntax so we would > produce the same results whether standard_conforming_strings was true or > false. However, we changed the standard_conforming_strings default to > true in Postgres 9.1 on 2011-09-12, and that release has been > end-of-life for a year. > > I think it is time to clarify our documentation examples by assuming > that standard_conforming_strings is true. I will work on a patch. > Thanks. I have developed the attached documentation patch to remove the E'' syntax. standard_conforming_strings defaulted to 'on' in PG 9.1. I also found that our bytea data type section wasn't properly adjusted when we changed the the default bytea_output to 'hex' in PG 9.0. I think the only question is how far back to apply this patch. I am thinking through 9.3. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + --LZvS9be/3tNcYl/X Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="escape.diff" diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index f4d4a61..038599d 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -788,7 +788,7 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 backslashes you need. For example, to insert a text array value containing a backslash and a double quote, you'd need to write: -INSERT ... VALUES (E'{"\\\\","\\""}'); +INSERT ... VALUES ('{"\\","\""}'); The escape string processor removes one level of backslashes, so that what arrives at the array-value parser looks like {"\\","\""}. diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 3d36cca..f9458c9 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1296,7 +1296,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 non-printable - 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. @@ -1308,9 +1308,10 @@ SELECT b, char_length(b) FROM test2; - The bytea type supports two external formats for - input and output: PostgreSQL's historical - escape format, and hex format. Both + The bytea type supports two + formats for input and output: hex format + and PostgreSQL's historical + escape format. Both of these are always accepted on input. The output format depends on the configuration parameter ; the default is hex. (Note that the hex format was introduced in @@ -1348,7 +1349,7 @@ SELECT b, char_length(b) FROM test2; Example: -SELECT E'\\xDEADBEEF'; +SELECT '\xDEADBEEF'; @@ -1368,7 +1369,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. @@ -1381,7 +1382,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. shows the characters that must be escaped, and gives the alternative @@ -1405,33 +1406,33 @@ SELECT E'\\xDEADBEEF'; 0 zero octet - E'\\000' - SELECT E'\\000'::bytea; - \000 + '\000' + SELECT '\000'::bytea; + \x00 39 single quote - '''' or E'\\047' - SELECT E'\''::bytea; - ' + '''' or '\047' + SELECT ''''::bytea; + \x27 92 backslash - E'\\\\' or E'\\134' - SELECT E'\\\\'::bytea; - \\ + '\' or '\\134' + SELECT '\\'::bytea; + \x5c 0 to 31 and 127 to 255 non-printable octets - E'\\xxx' (octal value) - SELECT E'\\001'::bytea; - \001 + '\xxx' (octal value) + SELECT '\001'::bytea; + \x01 @@ -1459,7 +1460,7 @@ SELECT E'\\xDEADBEEF'; of escaping.) The remaining backslash is then recognized by the bytea input function as starting either a three digit octal value or escaping another backslash. For example, - a string literal passed to the server as E'\\001' + a string literal passed to the server as '\001' becomes \001 after passing through the escape string parser. The \001 is then sent to the bytea input function, where it is converted @@ -1470,12 +1471,24 @@ SELECT E'\\xDEADBEEF'; - Bytea octets are sometimes escaped when output. In general, each - non-printable octet is converted into - its equivalent three-digit octal value and preceded by one backslash. - Most printable octets are represented by their standard - representation in the client character set. The octet with decimal - value 92 (backslash) is doubled in the output. + Bytea octets are output in hex + format by default. If you change + to escape, + non-printable octet are converted to + equivalent three-digit octal value and preceded by one backslash. + Most printable octets are output by their standard + representation in the client character set, e.g.: + + +SET bytea_output = 'escape'; + +SELECT 'abc \153\154\155 \052\251\124'::bytea; + bytea +---------------- + abc klm *\251T + + + The octet with decimal value 92 (backslash) is doubled in the output. Details are in . @@ -1498,7 +1511,7 @@ SELECT E'\\xDEADBEEF'; 92 backslash \\ - SELECT E'\\134'::bytea; + SELECT '\134'::bytea; \\ @@ -1506,7 +1519,7 @@ SELECT E'\\xDEADBEEF'; 0 to 31 and 127 to 255 non-printable octets \xxx (octal value) - SELECT E'\\001'::bytea; + SELECT '\001'::bytea; \001 @@ -1514,7 +1527,7 @@ SELECT E'\\xDEADBEEF'; 32 to 126 printable octets client character set representation - SELECT E'\\176'::bytea; + SELECT '\176'::bytea; ~ diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 4331beb..c44417d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1776,7 +1776,7 @@ octal sequences (\nnn) and doubles backslashes. - encode(E'123\\000\\001', 'base64') + encode('123\000\001', 'base64') MTIzAAE= @@ -2100,7 +2100,7 @@ the delimiter. See for more information. - regexp_split_to_array('hello world', E'\\s+') + regexp_split_to_array('hello world', '\s+') {hello,world} @@ -2117,7 +2117,7 @@ the delimiter. See for more information. - regexp_split_to_table('hello world', E'\\s+') + regexp_split_to_table('hello world', '\s+') helloworld (2 rows) @@ -3301,8 +3301,8 @@ SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three'); SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly'); Result: INSERT INTO "Foo bar" VALUES('O''Reilly') -SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files'); -Result: INSERT INTO locations VALUES(E'C:\\Program Files') +SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files'); +Result: INSERT INTO locations VALUES('C:\Program Files') @@ -3429,7 +3429,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); concatenation - E'\\\\Post'::bytea || E'\\047gres\\000'::bytea + '\\Post'::bytea || '\047gres\000'::bytea \\Post'gres\000 @@ -3442,7 +3442,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); int Number of bytes in binary string - octet_length(E'jo\\000se'::bytea) + octet_length('jo\000se'::bytea) 5 @@ -3457,7 +3457,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Replace substring - overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) + overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3) T\\002\\003mas @@ -3470,7 +3470,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); int Location of specified substring - position(E'\\000om'::bytea in E'Th\\000omas'::bytea) + position('\000om'::bytea in 'Th\000omas'::bytea) 3 @@ -3485,7 +3485,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Extract substring - substring(E'Th\\000omas'::bytea from 2 for 3) + substring('Th\000omas'::bytea from 2 for 3) h\000o @@ -3504,7 +3504,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); bytes from the start and end of string - trim(E'\\000\\001'::bytea from E'\\000Tom\\001'::bytea) + trim('\000\001'::bytea from '\000Tom\001'::bytea) Tom @@ -3547,7 +3547,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); bytes from the start and end of string - btrim(E'\\000trim\\001'::bytea, E'\\000\\001'::bytea) + btrim('\000trim\001'::bytea, '\000\001'::bytea) trim @@ -3564,7 +3564,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Decode binary data from textual representation in string. Options for format are same as in encode. - decode(E'123\\000456', 'escape') + decode('123\000456', 'escape') 123\000456 @@ -3584,7 +3584,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); octal sequences (\nnn) and doubles backslashes. - encode(E'123\\000456'::bytea, 'escape') + encode('123\000456'::bytea, 'escape') 123\000456 @@ -3599,7 +3599,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Extract bit from string - get_bit(E'Th\\000omas'::bytea, 45) + get_bit('Th\000omas'::bytea, 45) 1 @@ -3614,7 +3614,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Extract byte from string - get_byte(E'Th\\000omas'::bytea, 4) + get_byte('Th\000omas'::bytea, 4) 109 @@ -3638,7 +3638,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); binary strings, length - length(E'jo\\000se'::bytea) + length('jo\000se'::bytea) 5 @@ -3654,7 +3654,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Calculates the MD5 hash of string, returning the result in hexadecimal - md5(E'Th\\000omas'::bytea) + md5('Th\000omas'::bytea) 8ab2d3c9689aaf18​b4958c334c82d8b1 @@ -3670,7 +3670,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Set bit in string - set_bit(E'Th\\000omas'::bytea, 45, 0) + set_bit('Th\000omas'::bytea, 45, 0) Th\000omAs @@ -3686,7 +3686,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three'); Set byte in string - set_byte(E'Th\\000omas'::bytea, 4, 64) + set_byte('Th\000omas'::bytea, 4, 64) Th\000o@as @@ -4377,7 +4377,7 @@ regexp_replace('foobarbaz', 'b..', 'X') fooXbaz regexp_replace('foobarbaz', 'b..', 'X', 'g') fooXX -regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g') +regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g') fooXarYXazY @@ -4513,7 +4513,7 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab; Some examples: -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 @@ -4527,13 +4527,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 f56aeeb..7a9af9c 100644 --- a/doc/src/sgml/lobj.sgml +++ b/doc/src/sgml/lobj.sgml @@ -568,7 +568,7 @@ int lo_unlink(PGconn *conn, Oid lobjId); Create a large object and store data there, returning its OID. Pass 0 to have the system choose an OID. - lo_from_bytea(0, E'\\xffffff00') + lo_from_bytea(0, '\xffffff00') 24528 @@ -583,7 +583,7 @@ int lo_unlink(PGconn *conn, Oid lobjId); Write data at the given offset. - lo_put(24528, 1, E'\\xaa') + lo_put(24528, 1, '\xaa') diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml index 2f924b1..a6f4f67 100644 --- a/doc/src/sgml/rowtypes.sgml +++ b/doc/src/sgml/rowtypes.sgml @@ -510,7 +510,7 @@ SELECT c.somefunc FROM inventory_item c; containing a double quote and a backslash in a composite value, you'd need to write: -INSERT ... VALUES (E'("\\"\\\\")'); +INSERT ... VALUES ('("\"\\")'); The string-literal processor removes one level of backslashes, so that what arrives at the composite-value parser looks like --LZvS9be/3tNcYl/X--