|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.4.2 bytea escape format
The “escape” format is the traditional
PostgreSQL format for the
takes the approach of representing a binary string as a sequence
of ASCII characters, while converting those bytes that cannot be
represented as an ASCII character into special escape sequences.
If, from the point of view of the application, representing bytes
as characters makes sense, then this representation can be
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
for most new applications.
bytea values in escape format,
octets of certain
values must be escaped, while all octet
values can be escaped. In
general, to escape an octet, convert it into its three-digit
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
shows the characters that must be escaped, and gives the alternative
escape sequences where applicable.
|Decimal Octet Value||Description||Escaped Input Representation||Example|| Output Representation
|0||zero octet|| || || |
|39||single quote|| || || |
|92||backslash|| || || |
|0 to 31 and 127 to 255||“non-printable” octets|| || || |
The requirement to escape non-printable octets varies depending on locale settings. In some instances you can get away with leaving them unescaped. Note that the result in each of the examples in Table 6-7 was exactly one octet in length, even though the output representation is sometimes more than one character.
The reason multiple backslashes are required, as shown
in Table 6-7, is that an input
string written as a string literal must pass through two parse
phases in the PostgreSQL server.
The first backslash of each pair is interpreted as an escape
character by the string-literal parser (assuming escape string
syntax is used) and is therefore consumed, leaving the second backslash of the
pair. (Dollar-quoted strings can be used to avoid this level
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
\001 after passing through the
escape string parser. The
\001 is then sent
bytea input function, where it is converted
to a single octet with a decimal value of 1. Note that the
single-quote character is not treated specially by
so it follows the normal rules for string literals. (See also
section 188.8.131.52 String Constants.)
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.
Details are in Table 6-8.
|Decimal Octet Value||Description||Escaped Output Representation|| Example
|92||backslash|| || |
|0 to 31 and 127 to 255||“non-printable” octets|| || |
|32 to 126||“printable” octets||client character set representation|| |
Depending on the front end to PostgreSQL you use,
you might have additional work to do in terms of escaping and
bytea strings. For example, you might also
have to escape line feeds and carriage returns if your interface
automatically translates these.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|