8.4. Binary Data Types

The bytea data type allows storage of binary strings; see Table 8-6.

A binary string is a sequence of octets (or bytes). Binary 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). 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.

When entering 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, convert it into its three-digit octal value and precede it by two backslashes. Table 8-7 shows the characters that must be escaped, and gives the alternative escape sequences where applicable.

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