<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Binary Data Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Data Types"
HREF="datatype.html"><LINK
REL="PREVIOUS"
TITLE="Character Types"
HREF="datatype-character.html"><LINK
REL="NEXT"
TITLE="Date/Time Types"
HREF="datatype-datetime.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype-character.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="DATATYPE-BINARY"
>8.4. Binary Data Types</A
></H1
><A
NAME="AEN4041"
></A
><A
NAME="AEN4043"
></A
><P
> The <TT
CLASS="TYPE"
>bytea</TT
> data type allows storage of binary strings;
see <A
HREF="datatype-binary.html#DATATYPE-BINARY-TABLE"
>Table 8-6</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="DATATYPE-BINARY-TABLE"
></A
><P
><B
>Table 8-6. Binary Data Types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Name</TH
><TH
>Storage Size</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="TYPE"
>bytea</TT
></TD
><TD
>4 bytes plus the actual binary string</TD
><TD
>variable-length binary string</TD
></TR
></TBODY
></TABLE
></DIV
><P
> 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 <SPAN
CLASS="QUOTE"
>"non-printable"</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"
>"raw bytes"</SPAN
>, whereas character
strings are appropriate for storing text.
</P
><P
> When entering <TT
CLASS="TYPE"
>bytea</TT
> values, octets of certain values
<SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be escaped (but all octet values
<SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>may</I
></SPAN
> be escaped) when used as part of a string
literal in an <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> 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. <A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>Table 8-7</A
> shows the
characters that must be escaped, and gives the alternate escape
sequences where applicable.
</P
><DIV
CLASS="TABLE"
><A
NAME="DATATYPE-BINARY-SQLESC"
></A
><P
><B
>Table 8-7. <TT
CLASS="TYPE"
>bytea</TT
> Literal Escaped Octets</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><COL><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
><TT
CLASS="LITERAL"
>'\\000'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\000'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\000</TT
></TD
></TR
><TR
><TD
>39</TD
><TD
>single quote</TD
><TD
><TT
CLASS="LITERAL"
>'\''</TT
> or <TT
CLASS="LITERAL"
>'\\047'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\''::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'</TT
></TD
></TR
><TR
><TD
>92</TD
><TD
>backslash</TD
><TD
><TT
CLASS="LITERAL"
>'\\\\'</TT
> or <TT
CLASS="LITERAL"
>'\\134'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\\\'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\\</TT
></TD
></TR
><TR
><TD
>0 to 31 and 127 to 255</TD
><TD
><SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
> octets</TD
><TD
><TT
CLASS="LITERAL"
>'\\<TT
CLASS="REPLACEABLE"
><I
>xxx'</I
></TT
></TT
> (octal value)</TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\001'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\001</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
> The requirement to escape <SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
> 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 <A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>Table 8-7</A
> was exactly one octet in
length, even though the output representation of the zero octet and
backslash are more than one character.
</P
><P
> The reason that you have to write so many backslashes, as shown in
<A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>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 and is therefore consumed, leaving the
second backslash of the pair. The remaining backslash is then
recognized by the <TT
CLASS="TYPE"
>bytea</TT
> input function as starting
either a three digit octal value or escaping another backslash.
For example, a string literal passed to the server as
<TT
CLASS="LITERAL"
>'\\001'</TT
> becomes <TT
CLASS="LITERAL"
>\001</TT
> after
passing through the string-literal parser. The
<TT
CLASS="LITERAL"
>\001</TT
> is then sent to the <TT
CLASS="TYPE"
>bytea</TT
>
input function, where it is converted to a single octet with a
decimal value of 1. Note that the apostrophe character is not
treated specially by <TT
CLASS="TYPE"
>bytea</TT
>, so it follows the normal
rules for string literals. (See also <A
HREF="sql-syntax.html#SQL-SYNTAX-STRINGS"
>Section 4.1.2.1</A
>.)
</P
><P
> <TT
CLASS="TYPE"
>Bytea</TT
> octets are also escaped in the output. In general, each
<SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
> octet is converted into
its equivalent three-digit octal value and preceded by one backslash.
Most <SPAN
CLASS="QUOTE"
>"printable"</SPAN
> 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 <A
HREF="datatype-binary.html#DATATYPE-BINARY-RESESC"
>Table 8-8</A
>.
</P
><DIV
CLASS="TABLE"
><A
NAME="DATATYPE-BINARY-RESESC"
></A
><P
><B
>Table 8-8. <TT
CLASS="TYPE"
>bytea</TT
> Output Escaped Octets</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><COL><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
><TT
CLASS="LITERAL"
>\\</TT
></TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\134'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\\</TT
></TD
></TR
><TR
><TD
>0 to 31 and 127 to 255</TD
><TD
><SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
> octets</TD
><TD
><TT
CLASS="LITERAL"
>\<TT
CLASS="REPLACEABLE"
><I
>xxx</I
></TT
></TT
> (octal value)</TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\001'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>\001</TT
></TD
></TR
><TR
><TD
>32 to 126</TD
><TD
><SPAN
CLASS="QUOTE"
>"printable"</SPAN
> octets</TD
><TD
>client character set representation</TD
><TD
><TT
CLASS="LITERAL"
>SELECT '\\176'::bytea;</TT
></TD
><TD
><TT
CLASS="LITERAL"
>~</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
> Depending on the front end to <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> you use,
you may have additional work to do in terms of escaping and
unescaping <TT
CLASS="TYPE"
>bytea</TT
> strings. For example, you may also
have to escape line feeds and carriage returns if your interface
automatically translates these.
</P
><P
> The <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> standard defines a different binary
string type, called <TT
CLASS="TYPE"
>BLOB</TT
> or <TT
CLASS="TYPE"
>BINARY LARGE
OBJECT</TT
>. The input format is different from
<TT
CLASS="TYPE"
>bytea</TT
>, but the provided functions and operators are
mostly the same.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype-character.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Character Types</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Date/Time Types</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
