Movatterモバイル変換

8.4.1.bytea Hex Format

8.4.2.bytea Escape Format

Table 8.6. Binary Data Types

The“hex” format encodes binary data as 2 hexadecimal digits per byte, most significant nibble first. The entire string is preceded by the sequence\x (to distinguish it from the escape format). In some contexts, the initial backslash may need to be escaped by doubling it (seeSection 4.1.2.1). For input, the hexadecimal digits can be either upper or lower case, and whitespace is permitted between digit pairs (but not within a digit pair nor in the starting\x sequence). The hex format is compatible with a wide range of external applications and protocols, and it tends to be faster to convert than the escape format, so its use is preferred.

Example:

SET bytea_output = 'hex';SELECT '\xDEADBEEF'::bytea;   bytea------------ \xdeadbeef

The“escape” format is the traditionalPostgreSQL format for thebytea type. It 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. Therefore, this format should probably be avoided for most new applications.

When enteringbytea values in escape format, octets of certain valuesmust be escaped, while all octet valuescan be escaped. In general, to escape an octet, convert it into its three-digit octal value and precede it by a backslash. Backslash itself (octet decimal value 92) can alternatively be represented by double backslashes.Table 8.7 shows the characters that must be escaped, and gives the alternative escape sequences where applicable.

The requirement to escapenon-printable octets varies depending on locale settings. In some instances you can get away with leaving them unescaped.

The reason that single quotes must be doubled, as shown inTable 8.7, is that this is true for any string literal in an SQL command. The generic string-literal parser consumes the outermost single quotes and reduces any pair of single quotes to one data character. What thebytea input function sees is just one single quote, which it treats as a plain data character. However, thebytea input function treats backslashes as special, and the other behaviors shown inTable 8.7 are implemented by that function.

In some contexts, backslashes must be doubled compared to what is shown above, because the generic string-literal parser will also reduce pairs of backslashes to one data character; seeSection 4.1.2.1.

Bytea octets are output inhex format by default. If you changebytea_output toescape,“non-printable” octets are converted to their 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 inTable 8.8.

Depending on the front end toPostgreSQL you use, you might have additional work to do in terms of escaping and unescapingbytea strings. For example, you might also have to escape line feeds and carriage returns if your interface automatically translates these.