datatype.sgml

关系型数据库 Postgresql 6.5.2

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

  <abstract>
   <para>
    Describes the built-in data types available in 
    <productname>Postgres</productname>.
   </para>
  </abstract>

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

  <para>
   In the context of data types, the following sections will discuss 
   <acronym>SQL</acronym> standards compliance, porting issues, and usage.

   Some <productname>Postgres</productname> types correspond directly to 
   <acronym>SQL92</acronym>-compatible types. In other
   cases, data types defined by <acronym>SQL92</acronym> syntax are mapped directly
   into native <productname>Postgres</productname> types. 

   Many of the built-in types have obvious external formats. However, several
   types are either unique to <productname>Postgres</productname>, 
   such as open and closed paths, or have
   several possibilities for formats, such as the date and time types.
  </para>

  <para>
   <table tocentry="1">
    <title><productname>Postgres</productname> Data Types</title>
    <titleabbrev>Data Types</titleabbrev>
    <tgroup cols="3">
     <thead>
      <row>
       <entry><productname>Postgres</productname> Type</entry>
       <entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>bool</entry>
       <entry>boolean</entry>
       <entry>logical boolean (true/false)</entry>
      </row>
      <row>
       <entry>box</entry>
       <entry></entry>
       <entry>rectangular box in 2D plane</entry>
      </row>
      <row>
       <entry>char(n)</entry>
       <entry>character(n)</entry>
       <entry>fixed-length character string</entry>
      </row>
      <row>
       <entry>cidr</entry>
       <entry></entry>
       <entry>IP version 4 network or host address</entry>
      </row>
      <row>
       <entry>circle</entry>
       <entry></entry>
       <entry>circle in 2D plane</entry>
      </row>
      <row>
       <entry>date</entry>
       <entry>date</entry>
       <entry>calendar date without time of day</entry>
      </row>
      <row>
       <entry>float4/8</entry>
       <entry>float(p)</entry>
       <entry>floating-point number with precision p</entry>
      </row>
      <row>
       <entry>float8</entry>
       <entry>real, double precision</entry>
       <entry>double-precision floating-point number</entry>
      </row>
      <row>
       <entry>inet</entry>
       <entry></entry>
       <entry>IP version 4 network or host address</entry>
      </row>
      <row>
       <entry>int2</entry>
       <entry>smallint</entry>
       <entry>signed two-byte integer</entry>
      </row>
      <row>
       <entry>int4</entry>
       <entry>int, integer</entry>
       <entry>signed 4-byte integer</entry>
      </row>
      <row>
       <entry>int4</entry>
       <entry>decimal(p,s)</entry>
       <entry>exact numeric for p <= 9, s = 0</entry>
      </row>
      <row>
       <entry>int4</entry>
       <entry>numeric(p,s)</entry>
       <entry>exact numeric for p == 9, s = 0</entry>
      </row>
      <row>
       <entry>int8</entry>
       <entry></entry>
       <entry>signed 8-byte integer</entry>
      </row>
      <row>
       <entry>line</entry>
       <entry></entry>
       <entry>infinite line in 2D plane</entry>
      </row>
      <row>
       <entry>lseg</entry>
       <entry></entry>
       <entry>line segment in 2D plane</entry>
      </row>
      <row>
       <entry>money</entry>
       <entry>decimal(9,2)</entry>
       <entry>US-style currency</entry>
      </row>
      <row>
       <entry>path</entry>
       <entry></entry>
       <entry>open and closed geometric path in 2D plane</entry>
      </row>
      <row>
       <entry>point</entry>
       <entry></entry>
       <entry>geometric point in 2D plane</entry>
      </row>
      <row>
       <entry>polygon</entry>
       <entry></entry>
       <entry>closed geometric path in 2D plane</entry>
      </row>
      <row>
       <entry>serial</entry>
       <entry></entry>
       <entry>unique id for indexing and cross-reference</entry>
      </row>
      <row>
       <entry>time</entry>
       <entry>time</entry>
       <entry>time of day</entry>
      </row>
      <row>
       <entry>timespan</entry>
       <entry>interval</entry>
       <entry>general-use time span</entry>
      </row>
      <row>
       <entry>timestamp</entry>
       <entry>timestamp with time zone</entry>
       <entry>date/time</entry>
      </row>
      <row>
       <entry>varchar(n)</entry>
       <entry>character varying(n)</entry>
       <entry>variable-length character string</entry>
      </row>
     </tbody>
    </tgroup>
   </table>
  </para>

  <para>
   <note>
    <para>
     The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type 
     but only ipv4 is handled in the current implementation.  
     Everything here that talks about ipv4 will apply to ipv6 in a future release.</para>
   </note></para>

  <para>
   <table tocentry="1">
    <title><productname>Postgres</productname> Function Constants</title>
    <titleabbrev>Constants</titleabbrev>
    <tgroup cols="3">
     <thead>
      <row>
       <entry><productname>Postgres</productname> Function</entry>
       <entry><acronym>SQL92</acronym> Constant</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>getpgusername()</entry>
       <entry>current_user</entry>
       <entry>user name in current session</entry>
      </row>
      <row>
       <entry>date('now')</entry>
       <entry>current_date</entry>
       <entry>date of current transaction</entry>
      </row>
      <row>
       <entry>time('now')</entry>
       <entry>current_time</entry>
       <entry>time of current transaction</entry>
      </row>
      <row>
       <entry>timestamp('now')</entry>
       <entry>current_timestamp</entry>
       <entry>date and time of current transaction</entry>
      </row>
     </tbody>
    </tgroup>
   </table>
  </para>

  <para>
   <productname>Postgres</productname> has features at the forefront of 
   <acronym>ORDBMS</acronym> development. In addition to
   <acronym>SQL3</acronym> conformance, substantial portions 
   of <acronym>SQL92</acronym> are also supported.
   Although we strive for <acronym>SQL92</acronym> compliance, 
   there are some aspects of the standard
   which are ill considered and which should not live through subsequent standards.
   <productname>Postgres</productname> will not make great efforts to 
   conform to these features; however, these tend to apply in little-used 
   or obsure cases, and a typical user is not likely to run into them.</para>

  <para>
   Most of the input and output functions corresponding to the
   base types (e.g., integers and floating point numbers) do some
   error-checking.
   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 underflow or overflow.
  </para>

  <para>
   Note that some of the input and output functions are not invertible.  That is,
   the result of an output function may lose precision when compared to
   the original input.

   <note>
    <para>
     The original <productname>Postgres</productname> v4.2 code received from
     Berkeley rounded all double precision floating point results to six digits for
     output. Starting with v6.1, floating point numbers are allowed to retain
     most of the intrinsic precision of the type (typically 15 digits for doubles, 
     6 digits for 4-byte floats). 
     Other types with underlying floating point fields (e.g. geometric
     types) carry similar precision.</para>
   </note>
  </para>

  <sect1>
   <title>Numeric Types</title>

   <para>
    Numeric types consist of two- and four-byte integers and four- and eight-byte
    floating point numbers.</para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname> Numeric Types</title>
<titleabbrev>Numerics</titleabbrev>
<tgroup cols="4">
<thead>
  <row>
    <entry>Numeric Type</entry>
    <entry>Storage</entry>
    <entry>Description</entry>
    <entry>Range</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>float4</entry>
    <entry>4 bytes</entry>
    <entry>Variable-precision</entry>
    <entry>6 decimal places</entry>
  </row>
  <row>
    <entry>float8</entry>
    <entry>8 bytes</entry>
    <entry>Variable-precision</entry>
    <entry>15 decimal places</entry>
  </row>
  <row>
    <entry>int2</entry>
    <entry>2 bytes</entry>
    <entry>Fixed-precision</entry>
    <entry>-32768 to +32767</entry>
  </row>
  <row>
    <entry>int4</entry>
    <entry>4 bytes</entry>
    <entry>Usual choice for fixed-precision</entry>
    <entry>-2147483648 to [phone]</entry>
  </row>
  <row>
    <entry>int8</entry>
    <entry>8 bytes</entry>
    <entry>Very large range fixed-precision</entry>
    <entry>+/- &gt; 18 decimal places</entry>
  </row>
  <row>
    <entry>serial</entry>
    <entry>4 bytes</entry>
    <entry>Identifer or cross-reference</entry>
    <entry>0 to [phone]</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>

<para>
The numeric types have a full set of corresponding arithmetic operators and
functions. Refer to <xref endterm="math-opers" linkend="math-opers"> 
and <xref endterm="math-funcs" linkend="math-funcs"> for more information.
   </para>

<para>
The <type>serial</type> type is a special-case type constructed by
<productname>Postgres</productname> from other existing components.
It is typically used to create unique identifiers for table entries.
In the current implementation, specifying

<programlisting>
CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
</programlisting>

is equivalent to specifying:

<programlisting>
CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
CREATE TABLE <replaceable class="parameter">tablename</replaceable>
    (<replaceable class="parameter">colname</replaceable> INT4 DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq');
CREATE UNIQUE INDEX <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_key on <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable>);
</programlisting>

<caution>
<para>
The implicit sequence created for the <type>serial</type> type will
<emphasis>not</emphasis> be automatically removed when the table is dropped.
So, the following commands executed in order will likely fail:

<programlisting>
CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
DROP TABLE <replaceable class="parameter">tablename</replaceable>;
CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
</programlisting>

The sequence will remain in the database until explicitly dropped using
<command>DROP SEQUENCE</command>.</para>
</caution>
   </para>

   <para>
    The <type>int8</type> type may not be available on all platforms since
    it relies on compiler support for this.
   </para>

</sect1>

<sect1>
<title>Monetary Type</title>

<para>
The <type>money</type> type supports US-style currency with 
fixed decimal point representation.
If <productname>Postgres</productname> is compiled with USE_LOCALE 
then the money type should use the monetary conventions defined for
<citetitle>locale(7)</citetitle>.
   </para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname> Monetary Types</title>
<titleabbrev>Money</titleabbrev>
<tgroup cols="4">
<thead>
  <row>
    <entry>Monetary Type</entry>
    <entry>Storage</entry>
    <entry>Description</entry>
    <entry>Range</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>money</entry>
    <entry>4 bytes</entry>
    <entry>Fixed-precision</entry>
    <entry>-21474836.48 to +21474836.47</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>

   <para>
    <type>numeric</type>
    will replace the money type, and should be preferred.
   </para>

</sect1>

<sect1>
<title>Character Types</title>

<para>
<acronym>SQL92</acronym> defines two primary character types:
 <type>char</type> and <type>varchar</type>. 
<productname>Postgres</productname> supports these types, in
addition to the more general <type>text</type> type, 
which unlike <type>varchar</type>
does not require an upper
limit to be declared on the size of the field.
</para>

<para>
<table tocentry="1">
<title><productname>Postgres</productname> Character Types</title>
<titleabbrev>Characters</titleabbrev>
<tgroup cols="4">
<thead>
  <row>
    <entry>Character Type</entry>
    <entry>Storage</entry>
    <entry>Recommendation</entry>
    <entry>Description</entry>
  </row>
</thead>
<tbody>
  <row>
    <entry>char</entry>
    <entry>1 byte</entry>
    <entry><acronym>SQL92</acronym>-compatible</entry>
    <entry>Single character</entry>
  </row>
  <row>
    <entry>char(n)</entry>
    <entry>(4+n) bytes</entry>
    <entry><acronym>SQL92</acronym>-compatible</entry>
    <entry>Fixed-length blank padded</entry>
  </row>
  <row>
    <entry>text</entry>
    <entry>(4+x) bytes</entry>
    <entry>Best choice</entry>
    <entry>Variable-length</entry>
  </row>
  <row>
    <entry>varchar(n)</entry>
    <entry>(4+n) bytes</entry>
    <entry><acronym>SQL92</acronym>-compatible</entry>
    <entry>Variable-length with limit</entry>
  </row>
</tbody>
</tgroup>
</table>
</para>

<para>
There is one other fixed-length character type. 
The <type>name</type> type
only has one purpose and that is to provide 
<productname>Postgres</productname> with a
special type to use for internal names.  
It is not intended for use by the general user.
It's length is currently defined as 32 chars 
but should be reference using NAMEDATALEN.
This is set at compile time and may change in a future release.