datatype.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.128.2.6 2004/09/18 15:28:16 tgl Exp $
-->

 <chapter id="datatype">
  <title id="datatype-title">Data Types</title>

  <indexterm zone="datatype">
   <primary>data type</primary>
  </indexterm>

  <indexterm>
   <primary>type</primary>
   <see>data type</see>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> has a rich set of native data 
   types available to users.
   Users may add new types to <productname>PostgreSQL</productname> using the
   <command>CREATE TYPE</command> command.
  </para>

  <para>
   <xref linkend="datatype-table"> shows all built-in general-purpose data types.
   Most of the alternative names
   listed in the 
   <quote>Aliases</quote> column are the names used internally by
   <productname>PostgreSQL</productname> for historical reasons.  In
   addition, some internally used or deprecated types are available,
   but they are not listed here.
  </para>

   <table id="datatype-table">
    <title>Data Types</title>
    <tgroup cols="3">
     <thead>
      <row>
       <entry>Name</entry>
       <entry>Aliases</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><type>bigint</type></entry>
       <entry><type>int8</type></entry>
       <entry>signed eight-byte integer</entry>
      </row>

      <row>
       <entry><type>bigserial</type></entry>
       <entry><type>serial8</type></entry>
       <entry>autoincrementing eight-byte integer</entry>
      </row>

      <row>
       <entry><type>bit</type></entry>
       <entry></entry>
       <entry>fixed-length bit string</entry>
      </row>

      <row>
       <entry><type>bit varying(<replaceable>n</replaceable>)</type></entry>
       <entry><type>varbit(<replaceable>n</replaceable>)</type></entry>
       <entry>variable-length bit string</entry>
      </row>

      <row>
       <entry><type>boolean</type></entry>
       <entry><type>bool</type></entry>
       <entry>logical Boolean (true/false)</entry>
      </row>

      <row>
       <entry><type>box</type></entry>
       <entry></entry>
       <entry>rectangular box in the plane</entry>
      </row>

      <row>
       <entry><type>bytea</type></entry>
       <entry></entry>
       <entry>binary data</entry>
      </row>

      <row>
       <entry><type>character varying(<replaceable>n</replaceable>)</type></entry>
       <entry><type>varchar(<replaceable>n</replaceable>)</type></entry>
       <entry>variable-length character string</entry>
      </row>

      <row>
       <entry><type>character(<replaceable>n</replaceable>)</type></entry>
       <entry><type>char(<replaceable>n</replaceable>)</type></entry>
       <entry>fixed-length character string</entry>
      </row>

      <row>
       <entry><type>cidr</type></entry>
       <entry></entry>
       <entry>IPv4 or IPv6 network address</entry>
      </row>

      <row>
       <entry><type>circle</type></entry>
       <entry></entry>
       <entry>circle in the plane</entry>
      </row>

      <row>
       <entry><type>date</type></entry>
       <entry></entry>
       <entry>calendar date (year, month, day)</entry>
      </row>

      <row>
       <entry><type>double precision</type></entry>
       <entry><type>float8</type></entry>
       <entry>double precision floating-point number</entry>
      </row>

      <row>
       <entry><type>inet</type></entry>
       <entry></entry>
       <entry>IPv4 or IPv6 host address</entry>
      </row>

      <row>
       <entry><type>integer</type></entry>
       <entry><type>int</type>, <type>int4</type></entry>
       <entry>signed four-byte integer</entry>
      </row>

      <row>
       <entry><type>interval(<replaceable>p</replaceable>)</type></entry>
       <entry></entry>
       <entry>time span</entry>
      </row>

      <row>
       <entry><type>line</type></entry>
       <entry></entry>
       <entry>infinite line in the plane (not fully implemented)</entry>
      </row>

      <row>
       <entry><type>lseg</type></entry>
       <entry></entry>
       <entry>line segment in the plane</entry>
      </row>

      <row>
       <entry><type>macaddr</type></entry>
       <entry></entry>
       <entry>MAC address</entry>
      </row>

      <row>
       <entry><type>money</type></entry>
       <entry></entry>
       <entry>currency amount</entry>
      </row>

      <row>
       <entry><type>numeric [ (<replaceable>p</replaceable>,
         <replaceable>s</replaceable>) ]</type></entry>
       <entry><type>decimal [ (<replaceable>p</replaceable>,
         <replaceable>s</replaceable>) ]</type></entry>
       <entry>exact numeric with selectable precision</entry>
      </row>

      <row>
       <entry><type>path</type></entry>
       <entry></entry>
       <entry>open and closed geometric path in the plane</entry>
      </row>

      <row>
       <entry><type>point</type></entry>
       <entry></entry>
       <entry>geometric point in the plane</entry>
      </row>

      <row>
       <entry><type>polygon</type></entry>
       <entry></entry>
       <entry>closed geometric path in the plane</entry>
      </row>

      <row>
       <entry><type>real</type></entry>
       <entry><type>float4</type></entry>
       <entry>single precision floating-point number</entry>
      </row>

      <row>
       <entry><type>smallint</type></entry>
       <entry><type>int2</type></entry>
       <entry>signed two-byte integer</entry>
      </row>

      <row>
       <entry><type>serial</type></entry>
       <entry><type>serial4</type></entry>
       <entry>autoincrementing four-byte integer</entry>
      </row>

      <row>
       <entry><type>text</type></entry>
       <entry></entry>
       <entry>variable-length character string</entry>
      </row>

      <row>
       <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
       <entry></entry>
       <entry>time of day</entry>
      </row>

      <row>
       <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
       <entry><type>timetz</type></entry>
       <entry>time of day, including time zone</entry>
      </row>

      <row>
       <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
       <entry><type>timestamp</type></entry>
       <entry>date and time</entry>
      </row>

      <row>
       <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
       <entry><type>timestamptz</type></entry>
       <entry>date and time, including time zone</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <note>
   <title>Compatibility</title>
   <para>
    The following types (or spellings thereof) are specified by
    <acronym>SQL</acronym>: <type>bit</type>, <type>bit
    varying</type>, <type>boolean</type>, <type>char</type>,
    <type>character varying</type>, <type>character</type>,
    <type>varchar</type>, <type>date</type>, <type>double
    precision</type>, <type>integer</type>, <type>interval</type>,
    <type>numeric</type>, <type>decimal</type>, <type>real</type>,
    <type>smallint</type>, <type>time</type> (with or without time zone),
    <type>timestamp</type> (with or without time zone).
   </para>
  </note>

  <para>
   Each data type has an external representation determined by its input
   and output functions.  Many of the built-in types have
   obvious external formats.  However, several types are either unique
   to <productname>PostgreSQL</productname>, such as open and closed
   paths, or have several possibilities for formats, such as the date
   and time types.
   Some of the input and output functions are not invertible.  That is,
   the result of an output function may lose accuracy when compared to
   the original input.
  </para>

  <para>
   Some of the operators and functions (e.g.,
   addition and multiplication) do not perform run-time error-checking in the
   interests of improving execution speed.
   On some systems, for example, the numeric operators for some data types may
   silently cause underflow or overflow.
  </para>

  <sect1 id="datatype-numeric">
   <title>Numeric Types</title>

   <indexterm zone="datatype-numeric">
    <primary>data type</primary>
    <secondary>numeric</secondary>
   </indexterm>

   <para>
    Numeric types consist of two-, four-, and eight-byte integers,
    four- and eight-byte floating-point numbers, and fixed-precision
    decimals.  <xref linkend="datatype-numeric-table"> lists the
    available types.
   </para>

    <table id="datatype-numeric-table">
     <title>Numeric 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><type>smallint</></entry>
        <entry>2 bytes</entry>
        <entry>small-range integer</entry>
        <entry>-32768 to +32767</entry>
       </row>
       <row>
        <entry><type>integer</></entry>
        <entry>4 bytes</entry>
        <entry>usual choice for integer</entry>
        <entry>-2147483648 to [phone]</entry>
       </row>
       <row>
        <entry><type>bigint</></entry>
        <entry>8 bytes</entry>
        <entry>large-range integer</entry>
        <entry>-9223372036854775808 to 9223372036854775807</entry>
       </row>

       <row>
        <entry><type>decimal</></entry>
        <entry>variable</entry>
        <entry>user-specified precision, exact</entry>
        <entry>no limit</entry>
       </row>
       <row>
        <entry><type>numeric</></entry>
        <entry>variable</entry>
        <entry>user-specified precision, exact</entry>
        <entry>no limit</entry>
       </row>

       <row>
        <entry><type>real</></entry>
        <entry>4 bytes</entry>
        <entry>variable-precision, inexact</entry>
        <entry>6 decimal digits precision</entry>
       </row>
       <row>
        <entry><type>double precision</></entry>
        <entry>8 bytes</entry>
        <entry>variable-precision, inexact</entry>
        <entry>15 decimal digits precision</entry>
       </row>

       <row>
        <entry><type>serial</></entry>
        <entry>4 bytes</entry>
        <entry>autoincrementing integer</entry>
        <entry>1 to 2147483647</entry>
       </row>

       <row>
        <entry><type>bigserial</type></entry>
        <entry>8 bytes</entry>
        <entry>large autoincrementing integer</entry>
        <entry>1 to 9223372036854775807</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
    The syntax of constants for the numeric types is described in
    <xref linkend="sql-syntax-constants">.  The numeric types have a
    full set of corresponding arithmetic operators and
    functions. Refer to <xref linkend="functions"> for more
    information.  The following sections describe the types in detail.
   </para>

   <sect2 id="datatype-int">
    <title>Integer Types</title>

    <indexterm zone="datatype-int">
     <primary>integer</primary>
    </indexterm>

    <indexterm zone="datatype-int">
     <primary>smallint</primary>
    </indexterm>

    <indexterm zone="datatype-int">
     <primary>bigint</primary>