|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.4 Binary Data Types
bytea data type allows storage of binary strings;
see Table 6-6.
|Name||Storage Size|| Description
||4 bytes plus the actual binary string||variable-length binary string|
A binary string is a sequence of octets (or bytes). Binary strings are distinguished from character strings by two characteristics: First, binary strings specifically allow storing octets of value zero and other “non-printable” octets (usually, octets outside the 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. Second, operations on binary strings process the actual bytes, whereas the processing of character strings depends on locale settings. In short, binary strings are appropriate for storing data that the programmer thinks of as “raw bytes”, whereas character strings are appropriate for storing text.
bytea values, octets of certain
values must be escaped (but all octet
values can 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 preceded
by two backslashes. Table 6-7
shows the characters that must be escaped, and gives the alternate
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 actually 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 of the zero octet and backslash are more than one character.
The reason that you have to write so many backslashes, 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 22.214.171.124 String Constants.)
Bytea octets are also escaped in the 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) has a special alternative output representation.
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 may have additional work to do in terms of escaping and
bytea strings. For example, you may also
have to escape line feeds and carriage returns if your interface
automatically translates these.
The SQL standard defines a different binary
string type, called
OBJECT. The input format is different from
bytea, but the provided functions and operators are
mostly the same.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|