Sophie: postgresql9.6-docs-9.6.22-1.mga7 noarch

postgresql9.6-docs-9.6.22-1.mga7.noarch.rpm

<!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 9.6.22 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="2021-05-18T09:16:10"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="4"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.6.22 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Character Types"
HREF="datatype-character.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Data Types</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Date/Time Types"
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
><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
>1 or 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 in two
    ways.  First, binary strings specifically allow storing
    octets of value zero and other <SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
>
    octets (usually, octets outside the decimal 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
>    The <TT
CLASS="TYPE"
>bytea</TT
> type supports two
    formats for input and output: <SPAN
CLASS="QUOTE"
>"hex"</SPAN
> format
    and <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>'s historical
    <SPAN
CLASS="QUOTE"
>"escape"</SPAN
> format.  Both
    of these are always accepted on input.  The output format depends
    on the configuration parameter <A
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 <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
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN5830"
>8.4.1. <TT
CLASS="TYPE"
>bytea</TT
> Hex Format</A
></H2
><P
>    The <SPAN
CLASS="QUOTE"
>"hex"</SPAN
> format encodes binary data as 2 hexadecimal digits
    per byte, most significant nibble first.  The entire string is
    preceded by the sequence <TT
CLASS="LITERAL"
>\x</TT
> (to distinguish it
    from the escape format).  In some contexts, the initial backslash may
    need to be escaped by doubling it
    (see <A
HREF="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS"
>Section 4.1.2.1</A
>).
    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
    <TT
CLASS="LITERAL"
>\x</TT
> 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 '\xDEADBEEF';</PRE
><P>
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN5840"
>8.4.2. <TT
CLASS="TYPE"
>bytea</TT
> Escape Format</A
></H2
><P
>    The <SPAN
CLASS="QUOTE"
>"escape"</SPAN
> format is the traditional
    <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> format for the <TT
CLASS="TYPE"
>bytea</TT
>
    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.
   </P
><P
>    When entering <TT
CLASS="TYPE"
>bytea</TT
> values in escape format,
    octets of certain
    values <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be escaped, while all octet
    values <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>can</I
></SPAN
> 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.
    <A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>Table 8-7</A
>
    shows the characters that must be escaped, and gives the alternative
    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
>Hex 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"
>\x00</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"
>\x27</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"
>\x5c</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"
>\x01</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    The requirement to escape <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>non-printable</I
></SPAN
> octets
    varies depending on locale settings. In some instances you can get away
    with leaving them unescaped.
   </P
><P
>    The reason that single quotes must be doubled, as shown
    in <A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>Table 8-7</A
>, is that this
    is true for any string literal in a SQL command.  The generic
    string-literal parser consumes the outermost single quotes
    and reduces any pair of single quotes to one data character.
    What the <TT
CLASS="TYPE"
>bytea</TT
> input function sees is just one
    single quote, which it treats as a plain data character.
    However, the <TT
CLASS="TYPE"
>bytea</TT
> input function treats
    backslashes as special, and the other behaviors shown in
    <A
HREF="datatype-binary.html#DATATYPE-BINARY-SQLESC"
>Table 8-7</A
> are implemented by
    that function.
   </P
><P
>    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;
    see <A
HREF="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS"
>Section 4.1.2.1</A
>.
   </P
><P
>    <TT
CLASS="TYPE"
>Bytea</TT
> octets are output in <TT
CLASS="LITERAL"
>hex</TT
>
    format by default.  If you change <A
HREF="runtime-config-client.html#GUC-BYTEA-OUTPUT"
>bytea_output</A
>
    to <TT
CLASS="LITERAL"
>escape</TT
>,
    <SPAN
CLASS="QUOTE"
>"non-printable"</SPAN
> octets are converted to their
    equivalent three-digit octal value and preceded by one backslash.
    Most <SPAN
CLASS="QUOTE"
>"printable"</SPAN
> octets are output by their standard
    representation in the client character set, e.g.:

</P><PRE
CLASS="PROGRAMLISTING"
>SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T</PRE
><P>

    The octet with decimal value 92 (backslash) is doubled in the output.
    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 might have additional work to do in terms of escaping and
    unescaping <TT
CLASS="TYPE"
>bytea</TT
> 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
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
>