File: datatype.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (1122 lines) | stat: -rw-r--r-- 15,325 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Data Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="PREVIOUS"
TITLE="SQL Syntax"
HREF="syntax.html"><LINK
REL="NEXT"
TITLE="Monetary Type"
HREF="datatype806.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="syntax.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="datatype806.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="DATATYPE"
>Chapter 5. Data Types</A
></H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
><A
HREF="datatype.html#AEN723"
>Numeric Types</A
></DT
><DT
><A
HREF="datatype806.html"
>Monetary Type</A
></DT
><DT
><A
HREF="datatype833.html"
>Character Types</A
></DT
><DT
><A
HREF="datatype897.html"
>Date/Time Types</A
></DT
><DT
><A
HREF="datatype1243.html"
>Boolean Type</A
></DT
><DT
><A
HREF="datatype1275.html"
>Geometric Types</A
></DT
><DT
><A
HREF="datatype1369.html"
>IP Version 4 Networks and Host Addresses</A
></DT
></DL
></DIV
><BLOCKQUOTE
CLASS="ABSTRACT"
><P
>Describes the built-in data types available in 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>.</P
></BLOCKQUOTE
><P
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> has a rich set of native data 
types available to users.
Users may add new types to <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> using the
<B
CLASS="COMMAND"
>define type</B
>
command described elsewhere.&#13;</P
><P
>In the context of data types, the following sections will discuss 
<SPAN
CLASS="ACRONYM"
>SQL</SPAN
> standards compliance, porting issues, and usage.

Some <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> types correspond directly to 
<SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>-compatible types. In other
cases, data types defined by <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> syntax are mapped directly
into native <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> types. 

Many of the built-in types have obvious external formats. However, several
types are either unique to <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>, 
such as open and closed paths, or have
several possibilities for formats, such as date and time types.</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-1. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Data Types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Type</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> or <SPAN
CLASS="ACRONYM"
>SQL3</SPAN
> Type</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>bool</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>boolean</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>logical boolean (true/false)</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>box</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>rectangular box in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>char(n)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>character(n)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>fixed-length character string</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>cidr</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>IP version 4 network or host address</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>circle</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>circle in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>calendar date without time of day</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>float4/8</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>float(p)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>floating-point number with precision p</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>float8</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>real, double precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>double-precision floating-point number</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>inet</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>IP version 4 network or host address</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int2</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>smallint</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>signed two-byte integer</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int4</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int, integer</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>signed 4-byte integer</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int4</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>decimal(p,s)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>exact numeric for p &#60;= 9, s = 0</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int4</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>numeric(p,s)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>exact numeric for p == 9, s = 0</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int8</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>signed 8-byte integer</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>line</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>infinite line in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>lseg</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>line segment in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>money</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>decimal(9,2)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>US-style currency</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>path</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>open and closed geometric path in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>point</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>geometric point in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>polygon</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>closed geometric path in 2D plane</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>serial</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>unique id for indexing and cross-reference</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time of day</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timespan</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>interval</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>general-use time span</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timestamp</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timestamp with time zone</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date/time</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>varchar(n)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>character varying(n)</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>variable-length character string</TD
></TR
></TABLE
></DIV
></P
><P
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>The <SPAN
CLASS="TYPE"
>cidr</SPAN
> and <SPAN
CLASS="TYPE"
>inet</SPAN
> 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.</P
></BLOCKQUOTE
>&#13;</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-2. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Function Constants</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Function</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> Constant</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>getpgusername()</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>current_user</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>user name in current session</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date('now')</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>current_date</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date of current transaction</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time('now')</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>current_time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time of current transaction</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timestamp('now')</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>current_timestamp</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date and time of current transaction</TD
></TR
></TABLE
></DIV
></P
><P
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> has features at the forefront of 
<SPAN
CLASS="ACRONYM"
>ORDBMS</SPAN
> development. In addition to
<SPAN
CLASS="ACRONYM"
>SQL3</SPAN
> conformance, substantial portions 
of <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> are also supported.
Although we strive for <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> compliance, 
there are some aspects of the standard
which are ill considered and which should not live through subsequent standards.
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 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.&#13;</P
><P
>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.</P
><P
>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.

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>The original <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 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.</P
></BLOCKQUOTE
></P
><H1
CLASS="SECT1"
><A
NAME="AEN723"
>Numeric Types</A
></H1
><P
>Numeric types consist of two- and four-byte integers and four- and eight-byte
floating point numbers.&#13;</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-3. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Numeric Types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Numeric Type</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Storage</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Range</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>float4</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Variable-precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>6 decimal places</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>float8</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>8 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Variable-precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>15 decimal places</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int2</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>2 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Fixed-precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-32768 to +32767</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int4</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Usual choice for fixed-precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-2147483648 to +2147483647</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>int8</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>8 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Very large range fixed-precision</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>+/- &gt; 18 decimal places</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>serial</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Identifer or cross-reference</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>0 to +2147483647</TD
></TR
></TABLE
></DIV
></P
><P
>The numeric types have a full set of corresponding arithmetic operators and
functions. Refer to <A
HREF="operators1630.html#MATH-OPERS"
><I
>Numerical Operators</I
></A
> 
and <A
HREF="functions.html#MATH-FUNCS"
><I
>Mathematical Functions</I
></A
> for more information.&#13;</P
><P
>The <SPAN
CLASS="TYPE"
>serial</SPAN
> type is a special-case type constructed by
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> from other existing components.
It is typically used to create unique identifiers for table entries.
In the current implementation, specifying

<PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
> (<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
> SERIAL);</PRE
>

is equivalent to specifying:

<PRE
CLASS="PROGRAMLISTING"
>CREATE SEQUENCE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
>_<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
>_seq;
CREATE TABLE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
>
    (<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
> INT4 DEFAULT nextval('<TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
>_<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
>_seq');
CREATE UNIQUE INDEX <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
>_<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
>_key on <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
> (<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
>);</PRE
>

<P
></P
><TABLE
CLASS="CAUTION"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Caution</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>The implicit sequence created for the <SPAN
CLASS="TYPE"
>serial</SPAN
> type will
<I
CLASS="EMPHASIS"
>not</I
> be automatically removed when the table is dropped.
So, the following commands executed in order will likely fail:

<PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
> (<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
> SERIAL);
DROP TABLE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
>;
CREATE TABLE <TT
CLASS="REPLACEABLE"
><I
>tablename</I
></TT
> (<TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
> SERIAL);</PRE
>

The sequence will remain in the database until explicitly dropped using
<B
CLASS="COMMAND"
>DROP SEQUENCE</B
>.</P
></TD
></TR
></TABLE
>&#13;</P
><P
>The <I
CLASS="FIRSTTERM"
>exact numerics</I
> <SPAN
CLASS="TYPE"
>decimal</SPAN
> and 
<SPAN
CLASS="TYPE"
>numeric</SPAN
>
have fully implemented syntax but currently 
(<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> v6.4)
 support only a small range of precision and/or range values.
The <SPAN
CLASS="TYPE"
>int8</SPAN
> type may not be available on all platforms since
it relies on compiler support for this.</P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="syntax.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype806.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>SQL Syntax</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
>;</TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Monetary Type</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>