| Server IP : 94.16.115.136 / Your IP : 216.73.217.106 Web Server : Apache/2.4.52 (Ubuntu) System : Linux rogerrabbit 5.15.0-185-generic #195-Ubuntu SMP Fri Jun 19 17:11:50 UTC 2026 x86_64 User : bkillingseder ( 1001) PHP Version : 8.1.2-1ubuntu2.25 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /snap/postgresql10/current/usr/share/doc/postgresql/html/ |
Upload File : |
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.4. Binary Data Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="datatype-character.html" title="8.3. Character Types" /><link rel="next" href="datatype-datetime.html" title="8.5. Date/Time Types" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.4. Binary Data Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datatype-character.html" title="8.3. Character Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 10.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-datetime.html" title="8.5. Date/Time Types">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="DATATYPE-BINARY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.4. Binary Data Types</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="datatype-binary.html#id-1.5.7.12.9">8.4.1. <code class="type">bytea</code> Hex Format</a></span></dt><dt><span class="sect2"><a href="datatype-binary.html#id-1.5.7.12.10">8.4.2. <code class="type">bytea</code> Escape Format</a></span></dt></dl></div><a id="id-1.5.7.12.2" class="indexterm"></a><a id="id-1.5.7.12.3" class="indexterm"></a><p> The <code class="type">bytea</code> data type allows storage of binary strings;
see <a class="xref" href="datatype-binary.html#DATATYPE-BINARY-TABLE" title="Table 8.6. Binary Data Types">Table 8.6</a>.
</p><div class="table" id="DATATYPE-BINARY-TABLE"><p class="title"><strong>Table 8.6. Binary Data Types</strong></p><div class="table-contents"><table class="table" summary="Binary Data Types" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Storage Size</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">bytea</code></td><td>1 or 4 bytes plus the actual binary string</td><td>variable-length binary string</td></tr></tbody></table></div></div><br class="table-break" /><p> 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 <span class="quote">“<span class="quote">non-printable</span>”</span>
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 <span class="quote">“<span class="quote">raw bytes</span>”</span>, whereas character
strings are appropriate for storing text.
</p><p> The <code class="type">bytea</code> type supports two external formats for
input and output: <span class="productname">PostgreSQL</span>'s historical
<span class="quote">“<span class="quote">escape</span>”</span> format, and <span class="quote">“<span class="quote">hex</span>”</span> format. Both
of these are always accepted on input. The output format depends
on the configuration parameter <a class="xref" href="runtime-config-client.html#GUC-BYTEA-OUTPUT">bytea_output</a>;
the default is hex. (Note that the hex format was introduced in
<span class="productname">PostgreSQL</span> 9.0; earlier versions and some
tools don't understand it.)
</p><p> The <acronym class="acronym">SQL</acronym> standard defines a different binary
string type, called <code class="type">BLOB</code> or <code class="type">BINARY LARGE
OBJECT</code>. The input format is different from
<code class="type">bytea</code>, but the provided functions and operators are
mostly the same.
</p><div class="sect2" id="id-1.5.7.12.9"><div class="titlepage"><div><div><h3 class="title">8.4.1. <code class="type">bytea</code> Hex Format</h3></div></div></div><p> The <span class="quote">“<span class="quote">hex</span>”</span> format encodes binary data as 2 hexadecimal digits
per byte, most significant nibble first. The entire string is
preceded by the sequence <code class="literal">\x</code> (to distinguish it
from the escape format). In some contexts, the initial backslash may
need to be escaped by doubling it, in the same cases in which backslashes
have to be doubled in escape format; details appear below.
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
<code class="literal">\x</code> 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.
</p><p> Example:
</p><pre class="programlisting">SELECT E'\\xDEADBEEF';</pre><p>
</p></div><div class="sect2" id="id-1.5.7.12.10"><div class="titlepage"><div><div><h3 class="title">8.4.2. <code class="type">bytea</code> Escape Format</h3></div></div></div><p> The <span class="quote">“<span class="quote">escape</span>”</span> format is the traditional
<span class="productname">PostgreSQL</span> format for the <code class="type">bytea</code>
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. So this format should probably be avoided
for most new applications.
</p><p> When entering <code class="type">bytea</code> values in escape format,
octets of certain
values <span class="emphasis"><em>must</em></span> be escaped, while all octet
values <span class="emphasis"><em>can</em></span> 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
double backslashes.
<a class="xref" href="datatype-binary.html#DATATYPE-BINARY-SQLESC" title="Table 8.7. bytea Literal Escaped Octets">Table 8.7</a>
shows the characters that must be escaped, and gives the alternative
escape sequences where applicable.
</p><div class="table" id="DATATYPE-BINARY-SQLESC"><p class="title"><strong>Table 8.7. <code class="type">bytea</code> Literal Escaped Octets</strong></p><div class="table-contents"><table class="table" summary="bytea Literal Escaped Octets" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Decimal Octet Value</th><th>Description</th><th>Escaped Input Representation</th><th>Example</th><th>Output Representation</th></tr></thead><tbody><tr><td>0</td><td>zero octet</td><td><code class="literal">E'\\000'</code></td><td><code class="literal">SELECT E'\\000'::bytea;</code></td><td><code class="literal">\000</code></td></tr><tr><td>39</td><td>single quote</td><td><code class="literal">''''</code> or <code class="literal">E'\\047'</code></td><td><code class="literal">SELECT E'\''::bytea;</code></td><td><code class="literal">'</code></td></tr><tr><td>92</td><td>backslash</td><td><code class="literal">E'\\\\'</code> or <code class="literal">E'\\134'</code></td><td><code class="literal">SELECT E'\\\\'::bytea;</code></td><td><code class="literal">\\</code></td></tr><tr><td>0 to 31 and 127 to 255</td><td><span class="quote">“<span class="quote">non-printable</span>”</span> octets</td><td><code class="literal">E'\\<em class="replaceable"><code>xxx'</code></em></code> (octal value)</td><td><code class="literal">SELECT E'\\001'::bytea;</code></td><td><code class="literal">\001</code></td></tr></tbody></table></div></div><br class="table-break" /><p> The requirement to escape <span class="emphasis"><em>non-printable</em></span> 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 <a class="xref" href="datatype-binary.html#DATATYPE-BINARY-SQLESC" title="Table 8.7. bytea Literal Escaped Octets">Table 8.7</a> was exactly one octet in
length, even though the output representation is sometimes
more than one character.
</p><p> The reason multiple backslashes are required, as shown
in <a class="xref" href="datatype-binary.html#DATATYPE-BINARY-SQLESC" title="Table 8.7. bytea Literal Escaped Octets">Table 8.7</a>, is that an input
string written as a string literal must pass through two parse
phases in the <span class="productname">PostgreSQL</span> 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
<code class="type">bytea</code> input function as starting either a three
digit octal value or escaping another backslash. For example,
a string literal passed to the server as <code class="literal">E'\\001'</code>
becomes <code class="literal">\001</code> after passing through the
escape string parser. The <code class="literal">\001</code> is then sent
to the <code class="type">bytea</code> 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 <code class="type">bytea</code>,
so it follows the normal rules for string literals. (See also
<a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a>.)
</p><p> <code class="type">Bytea</code> octets are sometimes escaped when output. In general, each
<span class="quote">“<span class="quote">non-printable</span>”</span> octet is converted into
its equivalent three-digit octal value and preceded by one backslash.
Most <span class="quote">“<span class="quote">printable</span>”</span> 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 <a class="xref" href="datatype-binary.html#DATATYPE-BINARY-RESESC" title="Table 8.8. bytea Output Escaped Octets">Table 8.8</a>.
</p><div class="table" id="DATATYPE-BINARY-RESESC"><p class="title"><strong>Table 8.8. <code class="type">bytea</code> Output Escaped Octets</strong></p><div class="table-contents"><table class="table" summary="bytea Output Escaped Octets" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Decimal Octet Value</th><th>Description</th><th>Escaped Output Representation</th><th>Example</th><th>Output Result</th></tr></thead><tbody><tr><td>92</td><td>backslash</td><td><code class="literal">\\</code></td><td><code class="literal">SELECT E'\\134'::bytea;</code></td><td><code class="literal">\\</code></td></tr><tr><td>0 to 31 and 127 to 255</td><td><span class="quote">“<span class="quote">non-printable</span>”</span> octets</td><td><code class="literal">\<em class="replaceable"><code>xxx</code></em></code> (octal value)</td><td><code class="literal">SELECT E'\\001'::bytea;</code></td><td><code class="literal">\001</code></td></tr><tr><td>32 to 126</td><td><span class="quote">“<span class="quote">printable</span>”</span> octets</td><td>client character set representation</td><td><code class="literal">SELECT E'\\176'::bytea;</code></td><td><code class="literal">~</code></td></tr></tbody></table></div></div><br class="table-break" /><p> Depending on the front end to <span class="productname">PostgreSQL</span> you use,
you might have additional work to do in terms of escaping and
unescaping <code class="type">bytea</code> strings. For example, you might also
have to escape line feeds and carriage returns if your interface
automatically translates these.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datatype-character.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-datetime.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.3. Character Types </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 8.5. Date/Time Types</td></tr></table></div></body></html>