datatype.sgml

PostgreSQL7.4.6 for Linux

    linkend="datatype-money-table">.
    Input is accepted in a variety of formats, including integer and
    floating-point literals, as well as <quote>typical</quote>
    currency formatting, such as <literal>'$1,000.00'</literal>.
    Output is generally in the latter form but depends on the locale.
   </para>

    <table id="datatype-money-table">
     <title>Monetary Types</title>
     <tgroup cols="4">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Storage Size</entry>
        <entry>Description</entry>
        <entry>Range</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry>money</entry>
        <entry>4 bytes</entry>
        <entry>currency amount</entry>
        <entry>-21474836.48 to +21474836.47</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
  </sect1>


  <sect1 id="datatype-character">
   <title>Character Types</title>

   <indexterm zone="datatype-character">
    <primary>character string</primary>
    <secondary>data types</secondary>
   </indexterm>

   <indexterm>
    <primary>string</primary>
    <see>character string</see>
   </indexterm>

   <indexterm zone="datatype-character">
    <primary>character</primary>
   </indexterm>

   <indexterm zone="datatype-character">
    <primary>character varying</primary>
   </indexterm>

   <indexterm zone="datatype-character">
    <primary>text</primary>
   </indexterm>

   <indexterm zone="datatype-character">
    <primary>char</primary>
   </indexterm>

   <indexterm zone="datatype-character">
    <primary>varchar</primary>
   </indexterm>

    <table id="datatype-character-table">
     <title>Character Types</title>
     <tgroup cols="2">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>
        <entry>variable-length with limit</entry>
       </row>
       <row>
        <entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry>
        <entry>fixed-length, blank padded</entry>
       </row>
       <row>
        <entry><type>text</type></entry>
        <entry>variable unlimited length</entry>
       </row>
     </tbody>
     </tgroup>
    </table>

   <para>
    <xref linkend="datatype-character-table"> shows the
    general-purpose character types available in
    <productname>PostgreSQL</productname>.
   </para>

   <para>
    <acronym>SQL</acronym> defines two primary character types:
    <type>character varying(<replaceable>n</>)</type> and
    <type>character(<replaceable>n</>)</type>, where <replaceable>n</>
    is a positive integer.  Both of these types can store strings up to
    <replaceable>n</> characters in length.  An attempt to store a
    longer string into a column of these types will result in an
    error, unless the excess characters are all spaces, in which case
    the string will be truncated to the maximum length. (This somewhat
    bizarre exception is required by the <acronym>SQL</acronym>
    standard.) If the string to be stored is shorter than the declared
    length, values of type <type>character</type> will be space-padded;
    values of type <type>character varying</type> will simply store the
    shorter
    string.
   </para>

    <para>
     If one explicitly casts a value to <type>character
     varying(<replaceable>n</>)</type> or
     <type>character(<replaceable>n</>)</type>, then an over-length
     value will be truncated to <replaceable>n</> characters without
     raising an error. (This too is required by the
     <acronym>SQL</acronym> standard.)
    </para>

   <note>
    <para>
     Prior to <productname>PostgreSQL</> 7.2, strings that were too long were
     always truncated without raising an error, in either explicit or
     implicit casting contexts.
    </para>
   </note>

   <para>
    The notations <type>varchar(<replaceable>n</>)</type> and
    <type>char(<replaceable>n</>)</type> are aliases for <type>character
    varying(<replaceable>n</>)</type> and
    <type>character(<replaceable>n</>)</type>, respectively.
    <type>character</type> without length specifier is equivalent to
    <type>character(1)</type>; if <type>character varying</type> is used
    without length specifier, the type accepts strings of any size. The
    latter is a <productname>PostgreSQL</> extension.
   </para>

   <para>
    In addition, <productname>PostgreSQL</productname> provides the
    <type>text</type> type, which stores strings of any length.
    Although the type <type>text</type> is not in the
    <acronym>SQL</acronym> standard, several other SQL database
    management systems have it as well.
   </para>

   <para>
    The storage requirement for data of these types is 4 bytes plus the
    actual string, and in case of <type>character</type> plus the
    padding. Long strings are compressed by the system automatically, so
    the physical requirement on disk may be less. Long values are also
    stored in background tables so they do not interfere with rapid
    access to the shorter column values. In any case, the longest
    possible character string that can be stored is about 1 GB. (The
    maximum value that will be allowed for <replaceable>n</> in the data
    type declaration is less than that. It wouldn't be very useful to
    change this because with multibyte character encodings the number of
    characters and bytes can be quite different anyway. If you desire to
    store long strings with no specific upper limit, use
    <type>text</type> or <type>character varying</type> without a length
    specifier, rather than making up an arbitrary length limit.)
   </para>

   <tip>
    <para>
     There are no performance differences between these three types,
     apart from the increased storage size when using the blank-padded
     type.
    </para>
   </tip>

   <para>
    Refer to <xref linkend="sql-syntax-strings"> for information about
    the syntax of string literals, and to <xref linkend="functions">
    for information about available operators and functions.
   </para>

   <example>
    <title>Using the character types</title>

<programlisting>
CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">
<computeroutput>
  a   | char_length
------+-------------
 ok   |           4
</computeroutput>

CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');
<computeroutput>ERROR:  value too long for type character varying(5)</computeroutput>
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;
<computeroutput>
   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5
</computeroutput>
</programlisting>
    <calloutlist>
     <callout arearefs="co.datatype-char">
      <para>
       The <function>char_length</function> function is discussed in
       <xref linkend="functions-string">.
      </para>
     </callout>
    </calloutlist>
   </example>

   <para>
    There are two other fixed-length character types in
    <productname>PostgreSQL</productname>, shown in <xref
    linkend="datatype-character-special-table">. The <type>name</type>
    type exists <emphasis>only</emphasis> for storage of identifiers
    in the internal system catalogs and is not intended for use by the general user. Its
    length is currently defined as 64 bytes (63 usable characters plus
    terminator) but should be referenced using the constant
    <symbol>NAMEDATALEN</symbol>. The length is set at compile time (and
    is therefore adjustable for special uses); the default maximum
    length may change in a future release. The type <type>"char"</type>
    (note the quotes) is different from <type>char(1)</type> in that it
    only uses one byte of storage. It is internally used in the system
    catalogs as a poor-man's enumeration type.
   </para>

    <table id="datatype-character-special-table">
     <title>Special Character Types</title>
     <tgroup cols="3">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Storage Size</entry>
        <entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><type>"char"</type></entry>
        <entry>1 byte</entry>
        <entry>single-character internal type</entry>
       </row>
       <row>
        <entry><type>name</type></entry>
        <entry>64 bytes</entry>
        <entry>internal type for object names</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

  </sect1>

 <sect1 id="datatype-binary">
  <title>Binary Data Types</title>

  <indexterm zone="datatype-binary">
   <primary>binary data</primary>
  </indexterm>

  <indexterm zone="datatype-binary">
   <primary>bytea</primary>
  </indexterm>

   <para>
    The <type>bytea</type> data type allows storage of binary strings;
    see <xref linkend="datatype-binary-table">.
   </para>

   <table id="datatype-binary-table">
    <title>Binary Data Types</title>
    <tgroup cols="3">
     <thead>
      <row>
       <entry>Name</entry>
       <entry>Storage Size</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry><type>bytea</type></entry>
       <entry>4 bytes plus the actual binary string</entry>
       <entry>variable-length binary string</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    A binary string is a sequence of octets (or bytes).  Binary
    strings are distinguished from characters strings by two
    characteristics: First, binary strings specifically allow storing
    octets of value zero and other <quote>non-printable</quote>
    octets (defined as octets outside the range 32 to 126).
    Second, operations on binary strings process the actual bytes,
    whereas the encoding and processing of character strings depends
    on locale settings.
   </para>

   <para>
    When entering <type>bytea</type> values, octets of certain values
    <emphasis>must</emphasis> be escaped (but all octet values
    <emphasis>may</emphasis> be escaped) when used as part of a string
    literal in an <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. <xref linkend="datatype-binary-sqlesc"> contains the
    characters which must be escaped, and gives the alternate escape
    sequences where applicable.
   </para>

   <table id="datatype-binary-sqlesc">
    <title><type>bytea</> Literal Escaped Octets</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Decimal Octet Value</entry>
       <entry>Description</entry>
       <entry>Escaped Input Representation</entry>
       <entry>Example</entry>
       <entry>Output Representation</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry>0</entry>
       <entry>zero octet</entry>
       <entry><literal>'\\000'</literal></entry>
       <entry><literal>SELECT '\\000'::bytea;</literal></entry>
       <entry><literal>\000</literal></entry>
      </row>

      <row>
       <entry>39</entry>
       <entry>single quote</entry>
       <entry><literal>'\''</literal> or <literal>'\\047'</literal></entry>
       <entry><literal>SELECT '\''::bytea;</literal></entry>
       <entry><literal>'</literal></entry>
      </row>

      <row>
       <entry>92</entry>
       <entry>backslash</entry>
       <entry><literal>'\\\\'</literal> or <literal>'\\134'</literal></entry>
       <entry><literal>SELECT '\\\\'::bytea;</literal></entry>
       <entry><literal>\\</literal></entry>
      </row>

      <row>
       <entry>0 to 31 and 127 to 255</entry>
       <entry><quote>non-printable</quote> octets</entry>
       <entry><literal>'\\<replaceable>xxx'</></literal> (octal value)</entry>
       <entry><literal>SELECT '\\001'::bytea;</literal></entry>
       <entry><literal>\001</literal></entry>
      </row>

     </tbody>
    </tgroup>
   </table>

   <para>
    The requirement to escape <quote>non-printable</quote> 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 <xref linkend="datatype-binary-sqlesc"> was exactly one octet in
    length, even though the output representation of the zero octet and
    backslash are more than one character.
   </para>

   <para>
    The reason that you have to write so many backslashes, as shown in
    <xref linkend="datatype-binary-sqlesc">, is that an input string
    written as a string literal must pass through two parse phases in
    the <productname>PostgreSQL</productname> 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 <type>bytea</type> input function as starting