From joseph.conway@home.com Tue Nov 20 09:13:42 2001 Received: from femail13.sdc1.sfba.home.com (femail13.sdc1.sfba.home.com [24.0.95.140]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAK9Dfm83740 for ; Tue, 20 Nov 2001 04:13:42 -0500 (EST) (envelope-from joseph.conway@home.com) Received: from home.com ([24.0.42.111]) by femail13.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with ESMTP id <20011120091342.QRR12902.femail13.sdc1.sfba.home.com@home.com> for ; Tue, 20 Nov 2001 01:13:42 -0800 Message-ID: <3BFA1ED1.5080306@home.com> Date: Tue, 20 Nov 2001 01:13:53 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.6) Gecko/20011116 X-Accept-Language: en-us MIME-Version: 1.0 To: "pgsql-patches@postgresql.org" Subject: bytea datatype documentation patch Content-Type: multipart/mixed; boundary="------------020806020108010309070801" X-Archive-Number: 200111/193 X-Sequence-Number: 2624 This is a multi-part message in MIME format. --------------020806020108010309070801 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Here's a second bytea documentation patch. This one significantly expands the "Binary Data" section added by Bruce recently. -- Joe --------------020806020108010309070801 Content-Type: text/html; name="datatype.sgml.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="datatype.sgml.diff" *** sgml.orig/datatype.sgml Mon Nov 19 01:05:00 2001 --- sgml/datatype.sgml Tue Nov 20 01:04:54 2001 *************** *** 965,987 **** ! ! Binary Data ! The bytea data type allows storage of binary data, ! specifically allowing storage of NULLs which are entered as ! '\\000'. The first backslash is interpreted by the ! single quotes, and the second is recognized by bytea and ! precedes a three digit octal value. For a similar reason, a ! backslash must be entered into a field as '\\\\' or ! '\\134'. You may also have to escape line feeds and ! carriage return if your interface automatically translates these. It ! can store values of any length. Bytea is a non-standard ! data type. Date/Time Types --- 965,1260 ---- ! ! Binary Strings ! ! The bytea data type allows storage of binary strings. ! ! ! ! Binary String Types ! ! ! ! Type Name ! Storage ! Description ! ! ! ! ! bytea ! 4 bytes plus the actual string ! Variable (not specifically limited) ! length binary string ! ! ! !
! ! ! A binary string is a sequence of octets that does not have either a ! character set or collation associated with it. Bytea specifically ! allows storage of NULLs and other 'non-printable' ASCII ! characters. ! ! ! ! Certain ASCII characters MUST be escaped (but all ! characters MAY be escaped) when used as part of a string literal in an ! SQL statement. In general, to escape a character, it ! is converted into the three digit octal number equal to the decimal ! ASCII value, and preceeded by two backslashes. The ! single quote (') and backslash (\) characters have special alternate ! escape sequences. Details are in ! . ! ! ! ! <acronym>SQL</acronym> Literal Escaped <acronym>ASCII</acronym> ! Characters ! ! ! ! Decimal ASCII Value ! Description ! Input Escaped Representation ! Example ! Printed Result ! ! ! ! ! ! 0 ! null byte ! '\\000' ! select '\\000'::bytea; ! \000 ! ! ! ! 39 ! single quote ! '\\'' or '\\047' ! select '\''::bytea; ! ' ! ! ! ! 92 ! backslash ! '\\\\' or '\\134' ! select '\\\\'::bytea; ! \\ ! ! ! ! !
! ! ! Note that the result in each of the examples above was exactly one ! byte in length, even though the output representation of the null byte ! and backslash are more than one character. Bytea output characters ! are also escaped. In general, each "non-printable" character is ! converted into the three digit octal number equal to its decimal ! ASCII value, and preceeded by one backslash. Most ! "printable" characters are represented by their standard ! ASCII representation. The backslash (\) character ! has a special alternate output representation. Details are in ! . ! ! ! ! <acronym>SQL</acronym> Output Escaped <acronym>ASCII</acronym> ! Characters ! ! ! ! Decimal ASCII Value ! Description ! Output Escaped Representation ! Example ! Printed Result ! ! ! ! ! ! ! 39 ! single quote ! ' ! select '\\047'::bytea; ! ' ! ! ! ! 92 ! backslash ! \\ ! select '\\134'::bytea; ! \\ ! ! ! ! 0 to 31 and 127 to 255 ! non-printable characters ! \### (octal value) ! select '\\001'::bytea; ! \001 ! ! ! ! 32 to 126 ! printable characters ! ASCII representation ! select '\\176'::bytea; ! ~ ! ! ! ! !
! ! ! SQL string literals (input strings) must be ! preceeded with two backslashes due to the fact that they must pass ! through two parsers in the PostgreSQL backend. The first backslash ! is interpreted as an escape character by the string literal parser, ! and therefore is consumed, leaving the characters that follow it. ! The second backslash is recognized by bytea input function ! as the prefix of a three digit octal value. For example, a string ! literal passed to the backend as '\\001' becomes ! '\001' after passing through the string literal ! parser. The '\001' is then sent to the bytea ! input function, where it is converted to a single byte with a decimal ! ASCII value of 1. ! ! ! ! For a similar reason, a backslash must be input as ! '\\\\' (or '\\134'). The first ! and third backslashes are interpreted as escape characters by the ! string literal parser, and therefore are consumed, leaving the ! second and forth backslashes untouched. The second and forth ! backslashes are recognized by bytea input function as a single ! backslash. For example, a string literal passed to the backend as ! '\\\\' becomes '\\' after passing ! through the string literal parser. The '\\' is then ! sent to the bytea input function, where it is converted to a single ! byte with a decimal ASCII value of 92. ! ! ! ! A single quote is a bit different in that it must be input as ! '\'' (or '\\134'), NOT as ! '\\''. This is because, while the literal parser ! interprets the single quote as a special character, and will consume ! the single backslash, the bytea input function does NOT recognize ! a single quote as a special character. Therefore a string ! literal passed to the backend as '\'' becomes ! ''' after passing through the string literal ! parser. The ''' is then sent to the bytea ! input function, where it is retains its single byte decimal ! ASCII value of 39. ! ! Depending on the front end to PostgreSQL you use, you may have ! additional work to do in terms of escaping and unescaping bytea ! strings. For example, you may also have to escape line feeds and ! carriage return if your interface automatically translates these. ! Or you may have to double up on backslashes if the parser for your ! language or choice also treats them as an escape character. + + + Compatibility + + Bytea provides most of the functionality of the SQL99 binary string + type per SQL99 section 4.3. A comparison of PostgreSQL bytea and SQL99 + Binary Strings is presented in + . + + + + Comparison of SQL99 Binary String and BYTEA types + + + + SQL99 + BYTEA + + + + + + Name of data type BINARY LARGE OBJECT or BLOB + Name of data type BYTEA + + + + Sequence of octets that does not have either a character set + or collation associated with it. + same + + + + Described by a binary data type descriptor containing the + name of the data type and the maximum length + in octets + Described by a binary data type descriptor containing the + name of the data type with no specific maximum length + + + + + All binary strings are mutually comparable in accordance + with the rules of comparison predicates. + same + + + + Binary string values can only be compared for equality. + + Binary string values can be compared for equality, greater + than, greater than or equal, less than, less than or equal + + + + + Operators operating on and returning binary strings + include concatenation, substring, overlay, and trim + Operators operating on and returning binary strings + include concatenation, substring, and trim. The + 'leading' and 'trailing' + arguments for trim are not yet implemented. + + + + + Other operators involving binary strings + include length, position, and the like predicate + same + + + + A binary string literal is comprised of an even number of + hexidecimal digits, in single quotes, preceeded by "X", + e.g. X'1a43fe' + A binary string literal is comprised of ASCII characters + escaped according to the rules shown in + + + + +
+ + Date/Time Types --------------020806020108010309070801-- From pgman@candle.pha.pa.us Tue Nov 20 15:42:44 2001 Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAKFgim29892 for ; Tue, 20 Nov 2001 10:42:44 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id fAKFgfK09271; Tue, 20 Nov 2001 10:42:41 -0500 (EST) From: Bruce Momjian Message-Id: <200111201542.fAKFgfK09271@candle.pha.pa.us> Subject: Re: bytea datatype documentation patch In-Reply-To: <3BFA1ED1.5080306@home.com> "from Joe Conway at Nov 20, 2001 01:13:53 am" To: Joe Conway Date: Tue, 20 Nov 2001 10:42:41 -0500 (EST) CC: "pgsql-patches@postgresql.org" X-Mailer: ELM [version 2.4ME+ PL90 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200111/196 X-Sequence-Number: 2627 Patch applied. Thanks. --------------------------------------------------------------------------- > Here's a second bytea documentation patch. This one significantly > expands the "Binary Data" section added by Bruce recently. > > -- Joe [ text/html is unsupported, treating like TEXT/PLAIN ] > *** sgml.orig/datatype.sgml Mon Nov 19 01:05:00 2001 > --- sgml/datatype.sgml Tue Nov 20 01:04:54 2001 > *************** > *** 965,987 **** > > > > ! > ! Binary Data > > > ! The bytea data type allows storage of binary data, > ! specifically allowing storage of NULLs which are entered as > ! '\\000'. The first backslash is interpreted by the > ! single quotes, and the second is recognized by bytea and > ! precedes a three digit octal value. For a similar reason, a > ! backslash must be entered into a field as '\\\\' or > ! '\\134'. You may also have to escape line feeds and > ! carriage return if your interface automatically translates these. It > ! can store values of any length. Bytea is a non-standard > ! data type. > > > > > Date/Time Types > > --- 965,1260 ---- > > > > ! > ! Binary Strings > ! > ! The bytea data type allows storage of binary strings. > ! > ! > ! > ! Binary String Types > ! > ! > ! > ! Type Name > ! Storage > ! Description > ! > ! > ! > ! > ! bytea > ! 4 bytes plus the actual string > ! Variable (not specifically limited) > ! length binary string > ! > ! > ! > !
> ! > ! > ! A binary string is a sequence of octets that does not have either a > ! character set or collation associated with it. Bytea specifically > ! allows storage of NULLs and other 'non-printable' ASCII > ! characters. > ! > ! > ! > ! Certain ASCII characters MUST be escaped (but all > ! characters MAY be escaped) when used as part of a string literal in an > ! SQL statement. In general, to escape a character, it > ! is converted into the three digit octal number equal to the decimal > ! ASCII value, and preceeded by two backslashes. The > ! single quote (') and backslash (\) characters have special alternate > ! escape sequences. Details are in > ! . > ! > ! > ! > ! <acronym>SQL</acronym> Literal Escaped <acronym>ASCII</acronym> > ! Characters > ! > ! > ! > ! Decimal ASCII Value > ! Description > ! Input Escaped Representation > ! Example > ! Printed Result > ! > ! > ! > ! > ! > ! 0 > ! null byte > ! '\\000' > ! select '\\000'::bytea; > ! \000 > ! > ! > ! > ! 39 > ! single quote > ! '\\'' or '\\047' > ! select '\''::bytea; > ! ' > ! > ! > ! > ! 92 > ! backslash > ! '\\\\' or '\\134' > ! select '\\\\'::bytea; > ! \\ > ! > ! > ! > ! > !
> ! > ! > ! Note that the result in each of the examples above was exactly one > ! byte in length, even though the output representation of the null byte > ! and backslash are more than one character. Bytea output characters > ! are also escaped. In general, each "non-printable" character is > ! converted into the three digit octal number equal to its decimal > ! ASCII value, and preceeded by one backslash. Most > ! "printable" characters are represented by their standard > ! ASCII representation. The backslash (\) character > ! has a special alternate output representation. Details are in > ! . > ! > ! > ! > ! <acronym>SQL</acronym> Output Escaped <acronym>ASCII</acronym> > ! Characters > ! > ! > ! > ! Decimal ASCII Value > ! Description > ! Output Escaped Representation > ! Example > ! Printed Result > ! > ! > ! > ! > ! > ! > ! 39 > ! single quote > ! ' > ! select '\\047'::bytea; > ! ' > ! > ! > ! > ! 92 > ! backslash > ! \\ > ! select '\\134'::bytea; > ! \\ > ! > ! > ! > ! 0 to 31 and 127 to 255 > ! non-printable characters > ! \### (octal value) > ! select '\\001'::bytea; > ! \001 > ! > ! > ! > ! 32 to 126 > ! printable characters > ! ASCII representation > ! select '\\176'::bytea; > ! ~ > ! > ! > ! > ! > !
> ! > ! > ! SQL string literals (input strings) must be > ! preceeded with two backslashes due to the fact that they must pass > ! through two parsers in the PostgreSQL backend. The first backslash > ! is interpreted as an escape character by the string literal parser, > ! and therefore is consumed, leaving the characters that follow it. > ! The second backslash is recognized by bytea input function > ! as the prefix of a three digit octal value. For example, a string > ! literal passed to the backend as '\\001' becomes > ! '\001' after passing through the string literal > ! parser. The '\001' is then sent to the bytea > ! input function, where it is converted to a single byte with a decimal > ! ASCII value of 1. > ! > ! > ! > ! For a similar reason, a backslash must be input as > ! '\\\\' (or '\\134'). The first > ! and third backslashes are interpreted as escape characters by the > ! string literal parser, and therefore are consumed, leaving the > ! second and forth backslashes untouched. The second and forth > ! backslashes are recognized by bytea input function as a single > ! backslash. For example, a string literal passed to the backend as > ! '\\\\' becomes '\\' after passing > ! through the string literal parser. The '\\' is then > ! sent to the bytea input function, where it is converted to a single > ! byte with a decimal ASCII value of 92. > ! > ! > ! > ! A single quote is a bit different in that it must be input as > ! '\'' (or '\\134'), NOT as > ! '\\''. This is because, while the literal parser > ! interprets the single quote as a special character, and will consume > ! the single backslash, the bytea input function does NOT recognize > ! a single quote as a special character. Therefore a string > ! literal passed to the backend as '\'' becomes > ! ''' after passing through the string literal > ! parser. The ''' is then sent to the bytea > ! input function, where it is retains its single byte decimal > ! ASCII value of 39. > ! > > > ! Depending on the front end to PostgreSQL you use, you may have > ! additional work to do in terms of escaping and unescaping bytea > ! strings. For example, you may also have to escape line feeds and > ! carriage return if your interface automatically translates these. > ! Or you may have to double up on backslashes if the parser for your > ! language or choice also treats them as an escape character. > > + > + > + Compatibility > + > + Bytea provides most of the functionality of the SQL99 binary string > + type per SQL99 section 4.3. A comparison of PostgreSQL bytea and SQL99 > + Binary Strings is presented in > + . > + > + > + > + Comparison of SQL99 Binary String and BYTEA types > + > + > + > + SQL99 > + BYTEA > + > + > + > + > + > + Name of data type BINARY LARGE OBJECT or BLOB > + Name of data type BYTEA > + > + > + > + Sequence of octets that does not have either a character set > + or collation associated with it. > + same > + > + > + > + Described by a binary data type descriptor containing the > + name of the data type and the maximum length > + in octets > + Described by a binary data type descriptor containing the > + name of the data type with no specific maximum length > + > + > + > + > + All binary strings are mutually comparable in accordance > + with the rules of comparison predicates. > + same > + > + > + > + Binary string values can only be compared for equality. > + > + Binary string values can be compared for equality, greater > + than, greater than or equal, less than, less than or equal > + > + > + > + > + Operators operating on and returning binary strings > + include concatenation, substring, overlay, and trim > + Operators operating on and returning binary strings > + include concatenation, substring, and trim. The > + 'leading' and 'trailing' > + arguments for trim are not yet implemented. > + > + > + > + > + Other operators involving binary strings > + include length, position, and the like predicate > + same > + > + > + > + A binary string literal is comprised of an even number of > + hexidecimal digits, in single quotes, preceeded by "X", > + e.g. X'1a43fe' > + A binary string literal is comprised of ASCII characters > + escaped according to the rules shown in > + > + > + > + > +
> + > > > + > > Date/Time Types > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 From peter_e@gmx.net Tue Nov 20 21:35:20 2001 Received: from moutvdom01.kundenserver.de (moutvdom01.kundenserver.de [195.20.224.200]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAKLZJm45331 for ; Tue, 20 Nov 2001 16:35:20 -0500 (EST) (envelope-from peter_e@gmx.net) Received: from [195.20.224.208] (helo=mrvdom01.schlund.de) by moutvdom01.kundenserver.de with esmtp (Exim 2.12 #2) id 166IY2-0001u8-00; Tue, 20 Nov 2001 22:35:18 +0100 Received: from p3e9e7092.dip0.t-ipconnect.de ([62.158.112.146]) by mrvdom01.schlund.de with esmtp (Exim 2.12 #2) id 166IY0-0003xU-00; Tue, 20 Nov 2001 22:35:17 +0100 Date: Tue, 20 Nov 2001 22:42:26 +0100 (CET) From: Peter Eisentraut X-Sender: To: Joe Conway cc: "pgsql-patches@postgresql.org" Subject: Re: bytea datatype documentation patch In-Reply-To: <3BFA1ED1.5080306@home.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/207 X-Sequence-Number: 2638 Joe Conway writes: > Here's a second bytea documentation patch. This one significantly > expands the "Binary Data" section added by Bruce recently. This is good material, just a couple of comments. I feel that basing the whole discussion on what "ASCII" characters can be stored, are nonprintable, must be escaped, etc. is not appropriate. Bytea doesn't store characters, it stores bytes (or octets, as you wish). The correspondence between bytes and characters is old-fashioned and pretty much does not exist in SQL except for a few places such as OCTET_LENGTH. Conversely, the discussion about whether text should be able to cope with zero bytes is nonsensical because text stores characters and not bytes. So I would base this discussion on the premise "bytea stores binary data" (insert examples). Some stylistic issues: bytea => bytea NULLs => zero bytes/bytes of value zero ("NULL" is too overloaded) 'non-printable' => nonprintable MUST => must -- Peter Eisentraut peter_e@gmx.net From joseph.conway@home.com Wed Nov 21 02:41:29 2001 Received: from femail3.sdc1.sfba.home.com (femail3.sdc1.sfba.home.com [24.0.95.83]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAL2fTm56994 for ; Tue, 20 Nov 2001 21:41:29 -0500 (EST) (envelope-from joseph.conway@home.com) Received: from home.com ([24.0.42.111]) by femail3.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with ESMTP id <20011121024131.HDKU18556.femail3.sdc1.sfba.home.com@home.com>; Tue, 20 Nov 2001 18:41:31 -0800 Message-ID: <3BFB1465.3070607@home.com> Date: Tue, 20 Nov 2001 18:41:41 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.6) Gecko/20011116 X-Accept-Language: en-us MIME-Version: 1.0 To: Peter Eisentraut CC: "pgsql-patches@postgresql.org" Subject: Re: bytea datatype documentation patch References: Content-Type: multipart/mixed; boundary="------------050307040805060808030302" X-Archive-Number: 200111/210 X-Sequence-Number: 2641 This is a multi-part message in MIME format. --------------050307040805060808030302 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Peter Eisentraut wrote: > So I would base this discussion on the premise "bytea stores binary data" > (insert examples). > > Some stylistic issues: > > bytea => bytea > > NULLs => zero bytes/bytes of value zero ("NULL" is too overloaded) > > 'non-printable' => nonprintable > > MUST => must > Here's a patch against *CVS tip* to address Peter's comments. Please let me know what you think! Thanks, Joe --------------050307040805060808030302 Content-Type: text/plain; name="datatype-2.sgml.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="datatype-2.sgml.diff" *** sgml.orig/datatype.sgml Tue Nov 20 07:42:44 2001 --- sgml/datatype.sgml Tue Nov 20 18:07:22 2001 *************** *** 984,990 **** bytea ! 4 bytes plus the actual string Variable (not specifically limited) length binary string --- 984,990 ---- bytea ! 4 bytes plus the actual binary string Variable (not specifically limited) length binary string *************** *** 994,1022 **** A binary string is a sequence of octets that does not have either a ! character set or collation associated with it. Bytea specifically ! allows storage of NULLs and other 'non-printable' ASCII ! characters. ! Certain ASCII characters MUST be escaped (but all ! characters MAY be escaped) when used as part of a string literal in an ! SQL statement. In general, to escape a character, it ! is converted into the three digit octal number equal to the decimal ! ASCII value, and preceeded by two backslashes. The ! single quote (') and backslash (\) characters have special alternate ! escape sequences. Details are in . ! <acronym>SQL</acronym> Literal Escaped <acronym>ASCII</acronym> ! Characters ! Decimal ASCII Value Description Input Escaped Representation Example --- 994,1021 ---- A binary string is a sequence of octets that does not have either a ! character set or collation associated with it. Bytea ! specifically allows storing octets of zero value and other ! non-printable octets. ! Octets of certain values must be escaped (but all ! octet values may be escaped) when used as part of ! a string literal in an SQL statement. In general, ! to escape an octet, it is converted into the three digit octal number ! equivalent of its decimal octet value, and preceeded by two ! backslashes. Octets with the decimal values 39 (single quote), and 92 ! (backslash), have special alternate escape sequences. Details are in .
! <acronym>SQL</acronym> Literal Escaped Octets ! Decimal Octet Value Description Input Escaped Representation Example *************** *** 1027,1033 **** 0 ! null byte '\\000' select '\\000'::bytea; \000 --- 1026,1032 ---- 0 ! zero octet '\\000' select '\\000'::bytea; \000 *************** *** 1055,1078 **** Note that the result in each of the examples above was exactly one ! byte in length, even though the output representation of the null byte ! and backslash are more than one character. Bytea output characters ! are also escaped. In general, each "non-printable" character is ! converted into the three digit octal number equal to its decimal ! ASCII value, and preceeded by one backslash. Most ! "printable" characters are represented by their standard ! ASCII representation. The backslash (\) character ! has a special alternate output representation. Details are in ! .
! <acronym>SQL</acronym> Output Escaped <acronym>ASCII</acronym> ! Characters ! Decimal ASCII Value Description Output Escaped Representation Example --- 1054,1076 ---- Note that the result in each of the examples above was exactly one ! octet in length, even though the output representation of the zero ! octet and backslash are more than one character. Bytea ! output octets are also escaped. In general, each ! non-printable octet decimal value is converted into ! its equivalent three digit octal value, and preceeded by one backslash. ! Most printable octets are represented by their standard ! representation in the client character set. The octet with decimal ! value 92 (backslash) has a special alternate output representation. ! Details are in .
! <acronym>SQL</acronym> Output Escaped Octets ! Decimal Octet Value Description Output Escaped Representation Example *************** *** 1100,1106 **** 0 to 31 and 127 to 255 ! non-printable characters \### (octal value) select '\\001'::bytea; \001 --- 1098,1104 ---- 0 to 31 and 127 to 255 ! non-printable octets \### (octal value) select '\\001'::bytea; \001 *************** *** 1108,1115 **** 32 to 126 ! printable characters ! ASCII representation select '\\176'::bytea; ~ --- 1106,1113 ---- 32 to 126 ! printable octets ! client character set representation select '\\176'::bytea; ~ *************** *** 1123,1198 **** preceeded with two backslashes due to the fact that they must pass through two parsers in the PostgreSQL backend. The first backslash is interpreted as an escape character by the string literal parser, ! and therefore is consumed, leaving the characters that follow it. ! The second backslash is recognized by bytea input function as the prefix of a three digit octal value. For example, a string literal passed to the backend as '\\001' becomes '\001' after passing through the string literal ! parser. The '\001' is then sent to the bytea ! input function, where it is converted to a single byte with a decimal ! ASCII value of 1. For a similar reason, a backslash must be input as '\\\\' (or '\\134'). The first ! and third backslashes are interpreted as escape characters by the string literal parser, and therefore are consumed, leaving the second and forth backslashes untouched. The second and forth ! backslashes are recognized by bytea input function as a single ! backslash. For example, a string literal passed to the backend as ! '\\\\' becomes '\\' after passing ! through the string literal parser. The '\\' is then ! sent to the bytea input function, where it is converted to a single ! byte with a decimal ASCII value of 92. A single quote is a bit different in that it must be input as ! '\'' (or '\\134'), NOT as ! '\\''. This is because, while the literal parser ! interprets the single quote as a special character, and will consume ! the single backslash, the bytea input function does NOT recognize ! a single quote as a special character. Therefore a string literal passed to the backend as '\'' becomes ''' after passing through the string literal ! parser. The ''' is then sent to the bytea ! input function, where it is retains its single byte decimal ! ASCII value of 39. Depending on the front end to PostgreSQL you use, you may have ! additional work to do in terms of escaping and unescaping bytea ! strings. For example, you may also have to escape line feeds and ! carriage return if your interface automatically translates these. ! Or you may have to double up on backslashes if the parser for your ! language or choice also treats them as an escape character. Compatibility ! Bytea provides most of the functionality of the SQL99 binary string ! type per SQL99 section 4.3. A comparison of PostgreSQL bytea and SQL99 ! Binary Strings is presented in .
! Comparison of SQL99 Binary String and BYTEA types SQL99 ! BYTEA ! Name of data type BINARY LARGE OBJECT or BLOB ! Name of data type BYTEA --- 1121,1201 ---- preceeded with two backslashes due to the fact that they must pass through two parsers in the PostgreSQL backend. The first backslash is interpreted as an escape character by the string literal parser, ! and therefore is consumed, leaving the octets that follow. ! The second backslash is recognized by bytea input function as the prefix of a three digit octal value. For example, a string literal passed to the backend as '\\001' becomes '\001' after passing through the string literal ! parser. The '\001' is then sent to the ! bytea input function, where it is converted to a single ! octet with a decimal value of 1. For a similar reason, a backslash must be input as '\\\\' (or '\\134'). The first ! and third backslashes are interpreted as escape octets by the string literal parser, and therefore are consumed, leaving the second and forth backslashes untouched. The second and forth ! backslashes are recognized by the bytea input function ! as a single backslash. For example, a string literal passed to the ! backend as '\\\\' becomes '\\' ! after passing through the string literal parser. The ! '\\' is then sent to the bytea input ! function, where it is converted to a single octet with a decimal ! value of 92. A single quote is a bit different in that it must be input as ! '\'' (or '\\134'), ! not as '\\''. This is because, ! while the literal parser interprets the single quote as a special ! character, and will consume the single backslash, the ! bytea input function does not ! recognize a single quote as a special octet. Therefore a string literal passed to the backend as '\'' becomes ''' after passing through the string literal ! parser. The ''' is then sent to the ! bytea input function, where it is retains its single ! octet decimal value of 39. Depending on the front end to PostgreSQL you use, you may have ! additional work to do in terms of escaping and unescaping ! bytea strings. For example, you may also have to escape ! line feeds and carriage returns if your interface automatically ! translates these. Or you may have to double up on backslashes if ! the parser for your language or choice also treats them as an ! escape octet. Compatibility ! Bytea provides most of the functionality of the binary ! string type per SQL99 section 4.3. A comparison of SQL99 Binary ! Strings and PostgreSQL bytea is presented in .
! Comparison of SQL99 Binary String and PostgreSQL ! <type>BYTEA</type> types SQL99 ! BYTEA ! Name of data type BINARY LARGE OBJECT ! or BLOB ! Name of data type BYTEA *************** *** 1242,1250 **** A binary string literal is comprised of an even number of ! hexidecimal digits, in single quotes, preceeded by "X", ! e.g. X'1a43fe' ! A binary string literal is comprised of ASCII characters escaped according to the rules shown in --- 1245,1253 ---- A binary string literal is comprised of an even number of ! hexidecimal digits, in single quotes, preceeded by X, ! e.g. X'1a43fe' ! A binary string literal is comprised of octets escaped according to the rules shown in --------------050307040805060808030302-- From pgman@candle.pha.pa.us Wed Nov 21 03:17:50 2001 Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAL3Hom58226 for ; Tue, 20 Nov 2001 22:17:50 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id fAL3HlM13139; Tue, 20 Nov 2001 22:17:47 -0500 (EST) From: Bruce Momjian Message-Id: <200111210317.fAL3HlM13139@candle.pha.pa.us> Subject: Re: bytea datatype documentation patch In-Reply-To: <3BFB1465.3070607@home.com> "from Joe Conway at Nov 20, 2001 06:41:41 pm" To: Joe Conway Date: Tue, 20 Nov 2001 22:17:47 -0500 (EST) CC: Peter Eisentraut , "pgsql-patches@postgresql.org" X-Mailer: ELM [version 2.4ME+ PL90 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200111/212 X-Sequence-Number: 2643 Patch applied. Thanks. --------------------------------------------------------------------------- > Peter Eisentraut wrote: > > > So I would base this discussion on the premise "bytea stores binary data" > > (insert examples). > > > > Some stylistic issues: > > > > bytea => bytea > > > > NULLs => zero bytes/bytes of value zero ("NULL" is too overloaded) > > > > 'non-printable' => nonprintable > > > > MUST => must > > > > Here's a patch against *CVS tip* to address Peter's comments. Please let > me know what you think! > > Thanks, -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026