-<chapter 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 +2147483647</entry>
- </row>
- <row>
- <entry>int8</entry>
- <entry>8 bytes</entry>
- <entry>Very large range fixed-precision</entry>
- <entry>+/- > 18 decimal places</entry>
- </row>
- <row>
- <entry>serial</entry>
- <entry>4 bytes</entry>
- <entry>Identifer or cross-reference</entry>
- <entry>0 to +2147483647</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.
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.26 2000/03/31 03:27:40 thomas Exp $
+-->
+
+ <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.
+ </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>decimal</entry>
+ <entry>decimal(p,s)</entry>
+ <entry>exact numeric for p <= 9, s = 0</entry>
+ </row>
+ <row>
+ <entry>float4</entry>
+ <entry>float(<replaceable>p</replaceable>), <replaceable>p</replaceable> < 7</entry>
+ <entry>floating-point number with precision <replaceable>p</replaceable></entry>
+ </row>
+ <row>
+ <entry>float8</entry>
+ <entry>float(<replaceable>p</replaceable>), 7 <= <replaceable>p</replaceable> < 16</entry>
+ <entry>floating-point number with precision <replaceable>p</replaceable></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>int8</entry>
+ <entry></entry>
+ <entry>signed 8-byte integer</entry>
+ </row>
+ <row>
+ <entry>interval</entry>
+ <entry>interval</entry>
+ <entry>general-use time span</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>numeric</entry>
+ <entry>numeric(p,s)</entry>
+ <entry>exact numeric for p == 9, s = 0</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>timetz</entry>
+ <entry>time with time zone</entry>
+ <entry>time of day, including time zone</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>
+ 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>
-<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
+ <sect1>
+ <title>Numeric Types</title>
-<programlisting>
+ <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>decimal</entry>
+ <entry>variable</entry>
+ <entry>User-specified precision</entry>
+ <entry>no limit</entry>
+ </row>
+ <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 +2147483647</entry>
+ </row>
+ <row>
+ <entry>int8</entry>
+ <entry>8 bytes</entry>
+ <entry>Very large range fixed-precision</entry>
+ <entry>+/- > 18 decimal places</entry>
+ </row>
+ <row>
+ <entry>numeric</entry>
+ <entry>variable</entry>
+ <entry>User-specified precision</entry>
+ <entry>no limit</entry>
+ </row>
+ <row>
+ <entry>serial</entry>
+ <entry>4 bytes</entry>
+ <entry>Identifer or cross-reference</entry>
+ <entry>0 to +2147483647</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>int8</type> type may not be available on all platforms since
+ it relies on compiler support for this.
+ </para>
+
+ <sect2>
+ <title>The Serial Type</title>
+
+ <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>
+ </programlisting>
-is equivalent to specifying:
+ 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>
+ </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:
+ <caution>
+ <para>
+ The implicit sequence created for the <type>serial</type> type will
+ <emphasis>not</emphasis> be automatically removed when the
+ table is dropped.
+ </para>
+ </caution>
-<programlisting>
+ Implicit sequences supporting the <type>serial</type> are
+ not automatically dropped when a table containing a serial type
+ 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>
+ </programlisting>
-The sequence will remain in the database until explicitly dropped using
-<command>DROP SEQUENCE</command>.</para>
-</caution>
+ The sequence will remain in the database until explicitly dropped using
+ <command>DROP SEQUENCE</command>.
+ </para>
+ </sect2>
+ </sect1>
+
+ <sect1>
+ <title>Monetary Type</title>
+
+ <note>
+ <title>Obsolete Type</title>
+ <para>
+ The <type>money</type> is now deprecated. Use <type>numeric</type>
+ or <type>decimal</type> instead. The money type may become a
+ locale-aware layer over the numeric type in a future release.
+ </para>
+ </note>
+
+ <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>
-The <firstterm>exact numerics</firstterm> <type>decimal</type> and
-<type>numeric</type>
-have fully implemented syntax but currently
-(<productname>Postgres</productname> v6.4)
- support only a small range of precision and/or range values.
-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>
+ <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 explicit declared upper
+ limit 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 in <productname>Postgres</productname>.
+ The <type>name</type> type
+ only has one purpose and that is for storage of internal catalog
+ names.
+ It is not intended for use by the general user.
+ Its length is currently defined as 32 bytes (31 characters plus terminator)
+ but should be reference using NAMEDATALEN.
+ The length is set at compile time (and is therefore adjustable for
+ special uses); the default maximum length may change in a future release.
+ </para>
+
+ <para>
+ <table tocentry="1">
+ <title><productname>Postgres</productname> Specialty Character Type</title>
+ <titleabbrev>Specialty Characters</titleabbrev>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Character Type</entry>
+ <entry>Storage</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>name</entry>
+ <entry>32 bytes</entry>
+ <entry>Thirty-one character internal type</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+
+ </sect1>
+
+ <sect1>
+ <title>Date/Time Types</title>
+
+ <para>
+ <productname>PostgreSQL</productname> supports the full set of
+ <acronym>SQL</acronym> date and time types.
+ </para>
+
+ <para>
+ <table tocentry="1">
+ <title><productname>PostgreSQL</productname> Date/Time Types</title>
+ <titleabbrev>Date/Time</titleabbrev>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ <entry>Storage</entry>
+ <entry>Earliest</entry>
+ <entry>Latest</entry>
+ <entry>Resolution</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><type>timestamp</type></entry>
+ <entry>for data containing both date and time</entry>
+ <entry>8 bytes</entry>
+ <entry>4713 BC</entry>
+ <entry>AD 1465001</entry>
+ <entry>1 microsec / 14 digits</entry>
+ </row>
+ <row>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>date and time including time zone</entry>
+ <entry>8 bytes</entry>
+ <entry>1903 AD</entry>
+ <entry>2037 AD</entry>
+ <entry>1 microsec / 14 digits</entry>
+ </row>
+ <row>
+ <entry><type>interval</type></entry>
+ <entry>for time intervals</entry>
+ <entry>12 bytes</entry>
+ <entry>-178000000 years</entry>
+ <entry>178000000 years</entry>
+ <entry>1 mircosecond</entry>
+ </row>
+ <row>
+ <entry><type>date</type></entry>
+ <entry>for data containing only dates</entry>
+ <entry>4 bytes</entry>
+ <entry>4713 BC</entry>
+ <entry>32767 AD</entry>
+ <entry>1 day</entry>
+ </row>
+ <row>
+ <entry><type>time</type></entry>
+ <entry>for data containing only times of the day</entry>
+ <entry>4 bytes</entry>
+ <entry>00:00:00.00</entry>
+ <entry>23:59:59.99</entry>
+ <entry>1 microsecond</entry>
+ </row>
+ <row>
+ <entry><type>time with time zone</type></entry>
+ <entry>times of the day</entry>
+ <entry>4 bytes</entry>
+ <entry>00:00:00.00+12</entry>
+ <entry>23:59:59.99-12</entry>
+ <entry>1 microsecond</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ To ensure compatibility to earlier versions of <productname>PostgreSQL</productname>
+ we also continue to provide <type>datetime</type> (equivalent to <type>timestamp</type>) and
+ <type>timespan</type> (equivalent to <type>interval</type>). The types <type>abstime</type>
+ and <type>reltime</type> are lower precision types which are used internally.
+ You are discouraged from using any of these types in new
+ applications and are encouraged to move any old
+ ones over when appropriate. Any or all of these types might disappear in a future release.
+ </para>
+ </note>
</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>
-should eventually replace the money type. It has a
-fully implemented syntax but currently
-(<productname>Postgres</productname> v6.4)
- support only a small range of precision and/or range values
-and cannot adequately substitute for the money type.
-</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.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname> Specialty Character Type</title>
-<titleabbrev>Specialty Characters</titleabbrev>
-<tgroup cols="3">
-<thead>
- <row>
- <entry>Character Type</entry>
- <entry>Storage</entry>
- <entry>Description</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>name</entry>
- <entry>32 bytes</entry>
- <entry>Thirty-two character internal type</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-</sect1>
-
-<sect1>
-<title>Date/Time Types</title>
-
-<para>
-There are two fundamental kinds of date and time measurements:
- absolute clock times and relative time intervals.
-Both kinds of quantities should have behaviors demonstrating both
-continuity and smoothness.
-<productname>Postgres</productname> supplies two primary user-oriented
-date and time types,
-<type>datetime</type> and <type>timespan</type>, as well as
-the related <acronym>SQL92</acronym> types <type>timestamp</type>,
-<type>interval</type>,
-<type>date</type> and <type>time</type>.
-</para>
-
-<para>
-In a future release, <type>datetime</type> and <type>timespan</type> are likely
-to merge with the <acronym>SQL92</acronym> types <type>timestamp</type>,
-<type>interval</type>.
-Other date and time types are also available, mostly
-for historical reasons.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname> Date/Time Types</title>
-<titleabbrev>Date/Time</titleabbrev>
-<tgroup cols="4">
-<thead>
- <row>
- <entry>Date/Time Type</entry>
- <entry>Storage</entry>
- <entry>Recommendation</entry>
- <entry>Description</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>abstime</entry>
- <entry>4 bytes</entry>
- <entry>original date and time</entry>
- <entry>limited range</entry>
- </row>
- <row>
- <entry>date</entry>
- <entry>4 bytes</entry>
- <entry><acronym>SQL92</acronym> type</entry>
- <entry>wide range</entry>
- </row>
- <row>
- <entry>datetime</entry>
- <entry>8 bytes</entry>
- <entry>best general date and time</entry>
- <entry>wide range, high precision</entry>
- </row>
- <row>
- <entry>interval</entry>
- <entry>12 bytes</entry>
- <entry><acronym>SQL92</acronym> type</entry>
- <entry>equivalent to timespan</entry>
- </row>
- <row>
- <entry>reltime</entry>
- <entry>4 bytes</entry>
- <entry>original time interval</entry>
- <entry>limited range, low precision</entry>
- </row>
- <row>
- <entry>time</entry>
- <entry>4 bytes</entry>
- <entry><acronym>SQL92</acronym> type</entry>
- <entry>wide range</entry>
- </row>
- <row>
- <entry>timespan</entry>
- <entry>12 bytes</entry>
- <entry>best general time interval</entry>
- <entry>wide range, high precision</entry>
- </row>
- <row>
- <entry>timestamp</entry>
- <entry>4 bytes</entry>
- <entry><acronym>SQL92</acronym> type</entry>
- <entry>limited range</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-
-<type>timestamp</type> is currently implemented separately from
-<type>datetime</type>, although they share input and output routines.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname> Date/Time Ranges</title>
-<titleabbrev>Ranges</titleabbrev>
-<tgroup cols="4">
-<thead>
- <row>
- <entry>Date/Time Type</entry>
- <entry>Earliest</entry>
- <entry>Latest</entry>
- <entry>Resolution</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>abstime</entry>
- <entry>1901-12-14</entry>
- <entry>2038-01-19</entry>
- <entry>1 sec</entry>
- </row>
- <row>
- <entry>date</entry>
- <entry>4713 BC</entry>
- <entry>32767 AD</entry>
- <entry>1 day</entry>
- </row>
- <row>
- <entry>datetime</entry>
- <entry>4713 BC</entry>
- <entry>1465001 AD</entry>
- <entry>1 microsec to 14 digits</entry>
- </row>
- <row>
- <entry>interval</entry>
- <entry>-178000000 years</entry>
- <entry>178000000 years</entry>
- <entry>1 microsec</entry>
- </row>
- <row>
- <entry>reltime</entry>
- <entry>-68 years</entry>
- <entry>+68 years</entry>
- <entry>1 sec</entry>
- </row>
- <row>
- <entry>time</entry>
- <entry>00:00:00.00</entry>
- <entry>23:59:59.99</entry>
- <entry>1 microsec</entry>
- </row>
- <row>
- <entry>timespan</entry>
- <entry>-178000000 years</entry>
- <entry>178000000 years</entry>
- <entry>1 microsec (14 digits)</entry>
- </row>
- <row>
- <entry>timestamp</entry>
- <entry>1901-12-14</entry>
- <entry>2038-01-19</entry>
- <entry>1 sec</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-<para>
-<productname>Postgres</productname> endeavors to be compatible with
-<acronym>SQL92</acronym> definitions for typical usage.
-The <acronym>SQL92</acronym> standard has an odd mix of date and
-time types and capabilities. Two obvious problems are:
-
-<itemizedlist>
-<listitem>
-<para>
-Although the <type>date</type> type
-does not have an associated time zone, the
-<type>time</type> type can or does.</para></listitem>
-
-<listitem>
-<para>
-The default time zone is specified as a constant integer offset
-from GMT/UTC.</para></listitem>
-
-</itemizedlist>
-
-However, time zones in the real world can have no meaning unless
-associated with a date as well as a time
-since the offset may vary through the year with daylight savings
-time boundaries.
-</para>
-
-<para>
-To address these difficulties, <productname>Postgres</productname>
-associates time zones only with date and time
-types which contain both date and time,
- and assumes local time for any type containing only
-date or time. Further, time zone support is derived from
-the underlying operating system
-time zone capabilities, and hence can handle daylight savings time
-and other expected behavior.
-</para>
-
-<para>
-In future releases, the number of date/time types will decrease,
-with the current implementation of
-<type>datetime</type> becoming <type>timestamp</type>,
-<type>timespan</type> becoming <type>interval</type>,
-and (possibly) <type>abstime</type> and <type>reltime</type>
-being deprecated in favor of <type>timestamp</type> and <type>interval</type>.
-The more arcane features of the date/time definitions from
-the <acronym>SQL92</acronym> standard are not likely to be pursued.
-</para>
-
-<sect2>
-<title>Date/Time Styles</title>
-
-<para>
-Output formats can be set to one of four styles:
-ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
-Postgres, and German.
-
-<table tocentry="1">
-<title><productname>Postgres</productname> Date Styles</title>
-<titleabbrev>Styles</titleabbrev>
-<tgroup cols="3">
-<thead>
- <row>
- <entry>Style Specification</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>ISO</entry>
- <entry>ISO-8601 standard</entry>
- <entry>1997-12-17 07:37:16-08</entry>
- </row>
- <row>
- <entry><acronym>SQL</acronym></entry>
- <entry>Traditional style</entry>
- <entry>12/17/1997 07:37:16.00 PST</entry>
- </row>
- <row>
- <entry>Postgres</entry>
- <entry>Original style</entry>
- <entry>Wed Dec 17 07:37:16 1997 PST</entry>
- </row>
- <row>
- <entry>German</entry>
- <entry>Regional style</entry>
- <entry>17.12.1997 07:37:16.00 PST</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-<para>
-The <acronym>SQL</acronym> style has European and non-European (US) variants,
-which determines whether month follows day or vica versa.
-
-<table tocentry="1">
-<title><productname>Postgres</productname> Date Order Conventions</title>
-<titleabbrev>Order</titleabbrev>
-<tgroup cols="3">
-<thead>
- <row>
- <entry>Style Specification</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>European</entry>
- <entry>Regional convention</entry>
- <entry>17/12/1997 15:37:16.00 MET</entry>
- </row>
- <row>
- <entry>NonEuropean</entry>
- <entry>Regional convention</entry>
- <entry>12/17/1997 07:37:16.00 PST</entry>
- </row>
- <row>
- <entry>US</entry>
- <entry>Regional convention</entry>
- <entry>12/17/1997 07:37:16.00 PST</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-<para>
-There are several ways to affect the appearance of date/time types:
-
-<itemizedlist spacing="compact" mark="bullet">
-<listitem>
-<para>
-The PGDATESTYLE environment variable used by the backend directly
-on postmaster startup.
-</para>
-</listitem>
-<listitem>
-<para>
-The PGDATESTYLE environment variable used by the frontend libpq
-on session startup.
-</para>
-</listitem>
-<listitem>
-<para>
-<command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
-</para>
-</listitem>
-</itemizedlist>
-</para>
-
-<para>
-For <productname>Postgres</productname> v6.4 (and earlier)
-the default date/time style is
-"non-European traditional Postgres".
-In future releases, the default may become "ISO" (compatible with ISO-8601),
-which alleviates date specification ambiguities and Y2K collation problems.
-</para>
-
-</sect2>
-
-<sect2>
-<title>Time Zones</title>
-
-<para>
-<productname>Postgres</productname> obtains time zone support
-from the underlying operating system.
-All dates and times are stored internally in Universal Coordinated Time (UTC),
- alternately known as Greenwich Mean Time (GMT).
-Times are converted to local time on the database server before being
-sent to the client frontend, hence by default are in the server time zone.</para>
-
-<para>
-There are several ways to affect the time zone behavior:
-
-<itemizedlist spacing="compact" mark="bullet">
-<listitem>
-<para>
-The TZ environment variable used by the backend directly
- on postmaster startup as the default time zone.
-</para>
-</listitem>
-<listitem>
-<para>
-The PGTZ environment variable set at the client used by libpq
-to send time zone information to the backend upon connection.
-</para>
-</listitem>
-<listitem>
-<para>
-The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
-sets the time zone for the session.
-</para>
-</listitem>
-</itemizedlist></para>
-
-<para>
- If an invalid time zone is specified,
-the time zone becomes GMT (on most systems anyway).
-</para>
-</sect2>
<sect2>
<title>Date/Time Input</title>
<para>
- General-use date and time is input using a wide range of
- styles, including ISO-compatible, <acronym>SQL</acronym>-compatible,
- traditional <productname>Postgres</productname>
- and other permutations of date and time. In cases where interpretation
- can be ambiguous (quite possible with many traditional styles of date
- specification) <productname>Postgres</productname> uses a style setting
- to resolve the ambiguity.
+ Date and time input is accepted in almost any reasonable format, including
+ <acronym>ISO-8601</acronym>, <acronym>SQL</acronym>-compatible,
+ traditional <productname>Postgres</productname>, and others.
+ The ordering of month and day in date input can be ambiguous, therefore a setting
+ exists to specify how it should be interpreted. The command
+ <literal>SET DateStyle TO 'US'</literal> or <literal>SET DateStyle TO 'NonEuropean'</literal>
+ specifies the variant <quote>month before day</quote>, the command
+ <literal>SET DateStyle TO 'European'</literal> sets the variant
+ <quote>day before month</quote>. The <literal>US</literal> style
+ is the default but this default can be changed at compile time or at run time.
</para>
<para>
- Most date and time types share code for data input. For those types
- the input can have any of a wide variety of styles. For numeric date
- representations,
- European and US conventions can differ, and the proper interpretation
- is obtained by using the <command>SET DATESTYLE</command>
- command before entering data.
- Note that the style setting does not preclude use of various styles for input;
- it is used primarily to determine the output style and to resolve ambiguities.
+ See <xref endterm="datetime-appendix-title" linkend="datetime-appendix-title">
+ for the exact parsing rules of date/time input and for the recognized time zones.
</para>
<para>
- The special values <literal>current</literal>,
- <literal>infinity</literal> and <literal>-infinity</literal> are provided.
- <literal>infinity</literal> specifies a time later than any other valid time, and
- <literal>-infinity</literal> specifies a time earlier than any other valid time.
- <literal>current</literal> indicates that the current time should be
- substituted whenever this value appears in a computation.
+ Remember that any date or time input needs to be enclosed into single quotes,
+ like text strings.
</para>
+ <sect3>
+ <title>date</title>
<para>
- The strings
- <literal>now</literal>,
- <literal>today</literal>,
- <literal>yesterday</literal>,
- <literal>tomorrow</literal>,
- and <literal>epoch</literal>
- can be used to specify time values.
- <literal>now</literal>
- means the current transaction time, and differs from
- <literal>current</literal>
- in that the current time is immediately substituted for it.
- <literal>epoch</literal> means <literal>Jan 1 00:00:00 1970 GMT</literal>.
- </para>
-
- <para>
- <table tocentry="1">
- <title><productname>Postgres</productname> Date/Time Special Constants</title>
- <titleabbrev>Constants</titleabbrev>
- <tgroup cols="2">
- <thead>
- <row>
- <entry>Constant</entry>
- <entry>Description</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry>current</entry>
- <entry>Current transaction time, deferred</entry>
- </row>
- <row>
- <entry>epoch</entry>
- <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
- </row>
- <row>
- <entry>infinity</entry>
- <entry>Later than other valid times</entry>
- </row>
- <row>
- <entry>-infinity</entry>
- <entry>Earlier than other valid times</entry>
- </row>
- <row>
- <entry>invalid</entry>
- <entry>Illegal entry</entry>
- </row>
- <row>
- <entry>now</entry>
- <entry>Current transaction time</entry>
- </row>
- <row>
- <entry>today</entry>
- <entry>Midnight today</entry>
- </row>
- <row>
- <entry>tomorrow</entry>
- <entry>Midnight tomorrow</entry>
- </row>
- <row>
- <entry>yesterday</entry>
- <entry>Midnight yesterday</entry>
- </row>
- </tbody>
- </tgroup>
- </table>
- </para>
+ The following are possible inputs for the <type>date</type> type.
- <para>
<table tocentry="1">
- <title><productname>Postgres</productname> Date Input</title>
+ <title><productname>PostgreSQL</productname> Date Input</title>
<titleabbrev>Date Inputs</titleabbrev>
<tgroup cols="2">
<thead>
<tbody>
<row>
<entry>January 8, 1999</entry>
- <entry>Unambiguous text month</entry>
+ <entry>Unambiguous</entry>
</row>
<row>
<entry>1999-01-08</entry>
- <entry>ISO-8601</entry>
+ <entry>ISO-8601 format, preferred</entry>
</row>
<row>
<entry>1/8/1999</entry>
</row>
<row>
<entry>January 8, 99 BC</entry>
- <entry>Year 99 before the Christian Era</entry>
+ <entry>Year 99 before the common era</entry>
</row>
</tbody>
</tgroup>
<para>
<table tocentry="1">
- <title><productname>Postgres</productname> Month Abbreviations</title>
+ <title><productname>PostgreSQL</productname> Month Abbreviations</title>
<titleabbrev>Month Abbreviations</titleabbrev>
<tgroup cols="2">
<thead>
<para>
<table tocentry="1">
- <title><productname>Postgres</productname> Day of Week Abbreviations</title>
+ <title><productname>PostgreSQL</productname> Day of Week Abbreviations</title>
<titleabbrev>Day of Week Abbreviations</titleabbrev>
<tgroup cols="2">
<thead>
</tgroup>
</table>
</para>
+ </sect3>
- <para>
- <table tocentry="1">
- <title><productname>Postgres</productname> Time Input</title>
- <titleabbrev>Time Inputs</titleabbrev>
- <tgroup cols="2">
+ <sect3>
+ <title>time</title>
+ <para>
+ The following are valid <type>time</type> inputs.
+
+ <table tocentry="1">
+ <title><productname>PostgreSQL</productname> Time Input</title>
+ <titleabbrev>Time Inputs</titleabbrev>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Example</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>04:05:06.789</entry>
+ <entry>ISO-8601</entry>
+ </row>
+ <row>
+ <entry>04:05:06</entry>
+ <entry>ISO-8601</entry>
+ </row>
+ <row>
+ <entry>04:05</entry>
+ <entry>ISO-8601</entry>
+ </row>
+ <row>
+ <entry>040506</entry>
+ <entry>ISO-8601</entry>
+ </row>
+ <row>
+ <entry>04:05 AM</entry>
+ <entry>Same as 04:05; AM does not affect value</entry>
+ </row>
+ <row>
+ <entry>04:05 PM</entry>
+ <entry>Same as 16:05; input hour must be <= 12</entry>
+ </row>
+ <row>
+ <entry>z</entry>
+ <entry>Same as 00:00:00</entry>
+ </row>
+ <row>
+ <entry>zulu</entry>
+ <entry>Same as 00:00:00</entry>
+ </row>
+ <row>
+ <entry>allballs</entry>
+ <entry>Same as 00:00:00</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>time with time zone</title>
+ <para>
+ This type is defined by SQL92, but the definition exhibits
+ fundamental deficiencies which renders the type near useless. In
+ most cases, a combination of <type>date</type>,
+ <type>time</type>, and <type>timestamp with time zone</type>
+ should provide a complete range of date/time functionality
+ required by an application.
+ </para>
+
+ <para>
+ <type>time with time zone</type> accepts all input also legal
+ for the <type>time</type> type, appended with a legal time zone,
+ as follows:
+
+ <table tocentry="1">
+ <title><productname>PostgreSQL</productname> Time With Time
+ Zone Input</title>
+ <titleabbrev>Time With Time Zone Inputs</titleabbrev>
+ <tgroup cols="2">
<thead>
<row>
<entry>Example</entry>
</thead>
<tbody>
<row>
- <entry>04:05:06.789</entry>
- <entry>ISO-8601, with all time fields</entry>
- </row>
- <row>
- <entry>04:05:06</entry>
+ <entry>04:05:06.789-8</entry>
<entry>ISO-8601</entry>
</row>
<row>
- <entry>04:05</entry>
+ <entry>04:05:06-08:00</entry>
<entry>ISO-8601</entry>
</row>
<row>
- <entry>040506</entry>
+ <entry>04:05-08:00</entry>
<entry>ISO-8601</entry>
</row>
<row>
- <entry>04:05 AM</entry>
- <entry>Same as 04:05; AM does not affect value</entry>
- </row>
- <row>
- <entry>04:05 PM</entry>
- <entry>Same as 16:05; input hour must be <= 12</entry>
- </row>
- <row>
- <entry>z</entry>
- <entry>Same as 00:00:00</entry>
- </row>
- <row>
- <entry>zulu</entry>
- <entry>Same as 00:00:00</entry>
- </row>
- <row>
- <entry>allballs</entry>
- <entry>Same as 00:00:00</entry>
+ <entry>040506-08</entry>
+ <entry>ISO-8601</entry>
</row>
</tbody>
</tgroup>
</table>
</para>
+ <para>
+ Refer to <xref endterm="timezone-title" linkend="timezone"> for
+ more examples of time zones.
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>timestamp</title>
+ <para>
+ Valid input for the <type>timestamp</type> type consists of a concatenation
+ of a date and a time, followed by an optional <literal>AD</literal> or
+ <literal>BC</literal>, followed by an optional time zone. (See below.)
+ Thus
+<programlisting>
+1999-01-08 04:05:06 -8:00
+</programlisting>
+ is a valid <type>timestamp</type> value, which is <acronym>ISO</acronym>-compliant.
+ In addition, the wide-spread format
+<programlisting>
+January 8 04:05:06 1999 PST
+</programlisting>
+ is supported.
+ </para>
+
<para>
- <table tocentry="1">
- <title><productname>Postgres</productname> Time Zone Input</title>
+ <table tocentry="1" id="timezone">
+ <title id="timezone-title"><productname>PostgreSQL</productname> Time Zone Input</title>
<titleabbrev>Time Zone Inputs</titleabbrev>
<tgroup cols="2">
<thead>
</tgroup>
</table>
</para>
+ </sect3>
+
+ <sect3>
+ <title>interval</title>
+ <para>
+ <type>interval</type>s can be specified with the following syntax:
+<programlisting>
+ Quantity Unit [Quantity Unit...] [Direction]
+@ Quantity Unit [Direction]
+</programlisting>
+ where: <literal>Quantity</literal> is ..., <literal>-1</literal>,
+ <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...;
+ <literal>Unit</literal> is <literal>second</literal>,
+ <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
+ <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
+ <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>,
+ or abbreviations or plurals of these units;
+ <literal>Direction</literal> can be <literal>ago</literal> or
+ empty.
+ </para>
+ </sect3>
+ <sect3>
+ <title>Special values</title>
<para>
+ The following <acronym>SQL</acronym>-compatible functions can be used as date or time
+ input for the corresponding datatype: <literal>CURRENT_DATE</literal>,
+ <literal>CURRENT_TIME</literal>, <literal>CURRENT_TIMESTAMP</literal>.
+ </para>
+ <para>
+ <productname>PostgreSQL</productname> also supports several special constants for
+ convenience.
+
<table tocentry="1">
- <title><productname>Postgres</productname> Recognized Time Zones</title>
- <titleabbrev>Time Zones</titleabbrev>
- <tgroup cols="3">
+ <title><productname>PostgresSQL</productname> Special Date/Time Constants</title>
+ <titleabbrev>Constants</titleabbrev>
+ <tgroup cols="2">
<thead>
<row>
- <entry>Time Zone</entry>
- <entry>Offset from UTC</entry>
+ <entry>Constant</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
- <entry>NZDT</entry>
- <entry>+13:00</entry>
- <entry>New Zealand Daylight Time</entry>
- </row>
- <row>
- <entry>IDLE</entry>
- <entry>+12:00</entry>
- <entry>International Date Line, East</entry>
- </row>
- <row>
- <entry>NZST</entry>
- <entry>+12:00</entry>
- <entry>New Zealand Std Time</entry>
- </row>
- <row>
- <entry>NZT</entry>
- <entry>+12:00</entry>
- <entry>New Zealand Time</entry>
- </row>
- <row>
- <entry>AESST</entry>
- <entry>+11:00 </entry>
- <entry>Australia Eastern Summer Std Time</entry>
- </row>
- <row>
- <entry>ACSST</entry>
- <entry>+10:30 </entry>
- <entry>Central Australia Summer Std Time</entry>
- </row>
- <row>
- <entry>CADT</entry>
- <entry>+10:30 </entry>
- <entry>Central Australia Daylight Savings Time</entry>
- </row>
- <row>
- <entry>SADT</entry>
- <entry>+10:30</entry>
- <entry>South Australian Daylight Time</entry>
- </row>
- <row>
- <entry>AEST</entry>
- <entry>+10:00 </entry>
- <entry>Australia Eastern Std Time</entry>
- </row>
- <row>
- <entry>EAST</entry>
- <entry>+10:00 </entry>
- <entry>East Australian Std Time</entry>
- </row>
- <row>
- <entry>GST</entry>
- <entry>+10:00</entry>
- <entry>Guam Std Time, USSR Zone 9</entry>
- </row>
- <row>
- <entry>LIGT</entry>
- <entry>+10:00</entry>
- <entry>Melbourne, Australia</entry>
- </row>
- <row>
- <entry>ACST</entry>
- <entry>+09:30 </entry>
- <entry>Central Australia Std Time</entry>
- </row>
- <row>
- <entry>CAST</entry>
- <entry>+09:30 </entry>
- <entry>Central Australia Std Time</entry>
- </row>
- <row>
- <entry>SAT</entry>
- <entry>+9:30</entry>
- <entry>South Australian Std Time</entry>
- </row>
- <row>
- <entry>AWSST</entry>
- <entry>+9:00 </entry>
- <entry>Australia Western Summer Std Time</entry>
- </row>
- <row>
- <entry>JST</entry>
- <entry>+9:00</entry>
- <entry>Japan Std Time,USSR Zone 8</entry>
- </row>
- <row>
- <entry>KST</entry>
- <entry>+9:00</entry>
- <entry>Korea Standard Time</entry>
- </row>
- <row>
- <entry>WDT</entry>
- <entry>+9:00</entry>
- <entry>West Australian Daylight Time</entry>
- </row>
- <row>
- <entry>MT</entry>
- <entry>+8:30</entry>
- <entry>Moluccas Time</entry>
- </row>
- <row>
- <entry>AWST</entry>
- <entry>+8:00 </entry>
- <entry>Australia Western Std Time</entry>
- </row>
- <row>
- <entry>CCT</entry>
- <entry>+8:00 </entry>
- <entry>China Coastal Time</entry>
- </row>
- <row>
- <entry>WADT</entry>
- <entry>+8:00</entry>
- <entry>West Australian Daylight Time</entry>
- </row>
- <row>
- <entry>WST</entry>
- <entry>+8:00</entry>
- <entry>West Australian Std Time</entry>
- </row>
- <row>
- <entry>JT</entry>
- <entry>+7:30</entry>
- <entry>Java Time</entry>
- </row>
- <row>
- <entry>WAST</entry>
- <entry>+7:00</entry>
- <entry>West Australian Std Time</entry>
- </row>
- <row>
- <entry>IT</entry>
- <entry>+3:30</entry>
- <entry>Iran Time</entry>
- </row>
- <row>
- <entry>BT</entry>
- <entry>+3:00 </entry>
- <entry>Baghdad Time</entry>
- </row>
- <row>
- <entry>EETDST</entry>
- <entry>+3:00 </entry>
- <entry>Eastern Europe Daylight Savings Time</entry>
- </row>
- <row>
- <entry>CETDST</entry>
- <entry>+2:00 </entry>
- <entry>Central European Daylight Savings Time</entry>
- </row>
- <row>
- <entry>EET</entry>
- <entry>+2:00 </entry>
- <entry>Eastern Europe, USSR Zone 1</entry>
- </row>
- <row>
- <entry>FWT</entry>
- <entry>+2:00</entry>
- <entry>French Winter Time</entry>
- </row>
- <row>
- <entry>IST</entry>
- <entry>+2:00</entry>
- <entry>Israel Std Time</entry>
- </row>
- <row>
- <entry>MEST</entry>
- <entry>+2:00</entry>
- <entry>Middle Europe Summer Time</entry>
- </row>
- <row>
- <entry>METDST</entry>
- <entry>+2:00</entry>
- <entry>Middle Europe Daylight Time</entry>
- </row>
- <row>
- <entry>SST</entry>
- <entry>+2:00</entry>
- <entry>Swedish Summer Time</entry>
- </row>
- <row>
- <entry>BST</entry>
- <entry>+1:00 </entry>
- <entry>British Summer Time</entry>
- </row>
- <row>
- <entry>CET</entry>
- <entry>+1:00 </entry>
- <entry>Central European Time</entry>
- </row>
- <row>
- <entry>DNT</entry>
- <entry>+1:00 </entry>
- <entry>Dansk Normal Tid</entry>
- </row>
- <row>
- <entry>DST</entry>
- <entry>+1:00 </entry>
- <entry>Dansk Standard Time (?)</entry>
- </row>
- <row>
- <entry>FST</entry>
- <entry>+1:00 </entry>
- <entry>French Summer Time</entry>
- </row>
- <row>
- <entry>MET</entry>
- <entry>+1:00</entry>
- <entry>Middle Europe Time</entry>
- </row>
- <row>
- <entry>MEWT</entry>
- <entry>+1:00</entry>
- <entry>Middle Europe Winter Time</entry>
- </row>
- <row>
- <entry>MEZ</entry>
- <entry>+1:00</entry>
- <entry>Middle Europe Zone</entry>
- </row>
- <row>
- <entry>NOR</entry>
- <entry>+1:00</entry>
- <entry>Norway Standard Time</entry>
- </row>
- <row>
- <entry>SET</entry>
- <entry>+1:00</entry>
- <entry>Seychelles Time</entry>
- </row>
- <row>
- <entry>SWT</entry>
- <entry>+1:00</entry>
- <entry>Swedish Winter Time</entry>
- </row>
- <row>
- <entry>WETDST</entry>
- <entry>+1:00</entry>
- <entry>Western Europe Daylight Savings Time</entry>
- </row>
- <row>
- <entry>GMT</entry>
- <entry>0:00</entry>
- <entry>Greenwish Mean Time</entry>
- </row>
- <row>
- <entry>WET</entry>
- <entry>0:00</entry>
- <entry>Western Europe</entry>
- </row>
- <row>
- <entry>WAT</entry>
- <entry>-1:00</entry>
- <entry>West Africa Time</entry>
- </row>
- <row>
- <entry>NDT</entry>
- <entry>-2:30</entry>
- <entry>Newfoundland Daylight Time</entry>
- </row>
- <row>
- <entry>ADT</entry>
- <entry>-03:00 </entry>
- <entry>Atlantic Daylight Time</entry>
- </row>
- <row>
- <entry>NFT</entry>
- <entry>-3:30</entry>
- <entry>Newfoundland Standard Time</entry>
- </row>
- <row>
- <entry>NST</entry>
- <entry>-3:30</entry>
- <entry>Newfoundland Standard Time</entry>
- </row>
- <row>
- <entry>AST</entry>
- <entry>-4:00 </entry>
- <entry>Atlantic Std Time (Canada)</entry>
- </row>
- <row>
- <entry>EDT</entry>
- <entry>-4:00 </entry>
- <entry>Eastern Daylight Time</entry>
- </row>
- <row>
- <entry>ZP4</entry>
- <entry>-4:00</entry>
- <entry>GMT +4 hours</entry>
+ <entry>current</entry>
+ <entry>Current transaction time, deferred</entry>
</row>
<row>
- <entry>CDT</entry>
- <entry>-5:00 </entry>
- <entry>Central Daylight Time</entry>
+ <entry>epoch</entry>
+ <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
</row>
<row>
- <entry>EST</entry>
- <entry>-5:00 </entry>
- <entry>Eastern Standard Time</entry>
+ <entry>infinity</entry>
+ <entry>Later than other valid times</entry>
</row>
<row>
- <entry>ZP5</entry>
- <entry>-5:00</entry>
- <entry>GMT +5 hours</entry>
+ <entry>-infinity</entry>
+ <entry>Earlier than other valid times</entry>
</row>
<row>
- <entry>CST</entry>
- <entry>-6:00 </entry>
- <entry>Central Std Time</entry>
+ <entry>invalid</entry>
+ <entry>Illegal entry</entry>
</row>
<row>
- <entry>MDT</entry>
- <entry>-6:00</entry>
- <entry>Mountain Daylight Time</entry>
+ <entry>now</entry>
+ <entry>Current transaction time</entry>
</row>
<row>
- <entry>ZP6</entry>
- <entry>-6:00</entry>
- <entry>GMT +6 hours</entry>
+ <entry>today</entry>
+ <entry>Midnight today</entry>
</row>
<row>
- <entry>MST</entry>
- <entry>-7:00</entry>
- <entry>Mountain Standard Time</entry>
- </row>
+ <entry>tomorrow</entry>
+ <entry>Midnight tomorrow</entry>
+ </row>
<row>
- <entry>PDT</entry>
- <entry>-7:00</entry>
- <entry>Pacific Daylight Time</entry>
+ <entry>yesterday</entry>
+ <entry>Midnight yesterday</entry>
</row>
+ </tbody>
+ </tgroup>
+ </table>
+ <literal>'now'</literal> is resolved when the value is inserted, <literal>'current'</literal>
+ is resolved everytime the value is retrieved. So you probably want to use <literal>'now'</literal>
+ in most applications. (Of course you <emphasis>really</emphasis> want to use
+ <literal>CURRENT_TIMESTAMP</literal>, which is equivalent to <literal>'now'</literal>.)
+ </para>
+ </sect3>
+
+ </sect2>
+
+
+ <sect2>
+ <title>Date/Time Output</title>
+
+ <para>
+ Output formats can be set to one of the four styles
+ ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
+ Postgres, and German, using the <command>SET DateStyle</command>.
+ The default is the <acronym>ISO</acronym> format.
+
+ <table tocentry="1">
+ <title><productname>PostgreSQL</productname> Date/Time Output Styles</title>
+ <titleabbrev>Styles</titleabbrev>
+ <tgroup cols="3">
+ <thead>
<row>
- <entry>PST</entry>
- <entry>-8:00</entry>
- <entry>Pacific Std Time</entry>
+ <entry>Style Specification</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
</row>
+ </thead>
+ <tbody>
<row>
- <entry>YDT</entry>
- <entry>-8:00</entry>
- <entry>Yukon Daylight Time</entry>
+ <entry>'ISO'</entry>
+ <entry>ISO-8601 standard</entry>
+ <entry>1997-12-17 07:37:16-08</entry>
</row>
<row>
- <entry>HDT</entry>
- <entry>-9:00</entry>
- <entry>Hawaii/Alaska Daylight Time</entry>
+ <entry>'SQL'</entry>
+ <entry>Traditional style</entry>
+ <entry>12/17/1997 07:37:16.00 PST</entry>
</row>
<row>
- <entry>YST</entry>
- <entry>-9:00</entry>
- <entry>Yukon Standard Time</entry>
+ <entry>'Postgres'</entry>
+ <entry>Original style</entry>
+ <entry>Wed Dec 17 07:37:16 1997 PST</entry>
</row>
<row>
- <entry>AHST</entry>
- <entry>-10:00 </entry>
- <entry>Alaska-Hawaii Std Time</entry>
+ <entry>'German'</entry>
+ <entry>Regional style</entry>
+ <entry>17.12.1997 07:37:16.00 PST</entry>
</row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+
+ <para>
+ The output of the <type>date</type> and <type>time</type> styles is of course
+ only the date or time part in accordance with the above examples
+ </para>
+
+ <para>
+ The <acronym>SQL</acronym> style has European and non-European (US) variants,
+ which determines whether month follows day or vica versa. (See also above
+ at Date/Time Input, how this setting affects interpretation of input values.)
+
+ <table tocentry="1">
+ <title><productname>PostgreSQL</productname> Date Order Conventions</title>
+ <titleabbrev>Order</titleabbrev>
+ <tgroup cols="3">
+ <thead>
<row>
- <entry>CAT</entry>
- <entry>-10:00 </entry>
- <entry>Central Alaska Time</entry>
+ <entry>Style Specification</entry>
+ <entry>Example</entry>
</row>
+ </thead>
+ <tbody>
<row>
- <entry>NT</entry>
- <entry>-11:00</entry>
- <entry>Nome Time</entry>
+ <entry>European</entry>
+ <entry>17/12/1997 15:37:16.00 MET</entry>
</row>
<row>
- <entry>IDLW</entry>
- <entry>-12:00</entry>
- <entry>International Date Line, West</entry>
+ <entry>US</entry>
+ <entry>12/17/1997 07:37:16.00 PST</entry>
</row>
</tbody>
</tgroup>
</table>
- <note>
- <para>
- If the compiler option USE_AUSTRALIAN_RULES is set
- then <literal>EST</literal> refers to Australia Eastern Std Time,
- which has an offset of +10:00 hours from UTC.
- </para>
- </note>
+ </para>
<para>
- Australian time zones and their naming variants
- account for fully one quarter of all time zones in the
- <productname>Postgres</productname> time zone lookup table.
+ <type>interval</type> output looks like the input format, expect that units like
+ <literal>week</literal> or <literal>century</literal> are converted to years and days.
+ In ISO mode the output looks like
+<programlisting>
+[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
+</programlisting>
</para>
- <procedure>
- <title>Date/Time Input Interpretation</title>
+ <para>
+ There are several ways to affect the appearance of date/time types:
+
+ <itemizedlist spacing="compact" mark="bullet">
+ <listitem>
+ <para>
+ The <envar>PGDATESTYLE</envar> environment variable used by the backend directly
+ on postmaster startup.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The <envar>PGDATESTYLE</envar> environment variable used by the frontend libpq
+ on session startup.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
- <para>
- The date/time types are all decoded using a common set of routines.
- </para>
+ </sect2>
- <step>
- <para>
- Break the input string into tokens and categorize each token as
- a string, time, time zone, or number.
- </para>
+ <sect2>
+ <title>Time Zones</title>
- <substeps>
- <step>
- <para>
- If the token contains a colon (":"), this is a time string.
- </para>
- </step>
-
- <step>
- <para>
- If the token contains a dash ("-"), slash ("/"), or dot ("."),
- this is a date string which may have a text month.
- </para>
- </step>
-
- <step>
- <para>
- If the token is numeric only, then it is either a single field
- or an ISO-8601 concatenated date (e.g. "19990113" for January 13, 1999)
- or time (e.g. 141516 for 14:15:16).
- </para>
- </step>
- <step>
- <para>
- If the token starts with a plus ("+") or minus ("-"),
- then it is either a time zone or a special field.
- </para>
- </step>
- </substeps>
- </step>
-
- <step>
- <para>
- If the token is a text string, match up with possible strings.
- </para>
+ <para>
+ <productname>PostgreSQL</productname> endeavors to be compatible with
+ <acronym>SQL92</acronym> definitions for typical usage.
+ However, the <acronym>SQL92</acronym> standard has an odd mix of date and
+ time types and capabilities. Two obvious problems are:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Although the <type>date</type> type
+ does not have an associated time zone, the
+ <type>time</type> type can or does.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The default time zone is specified as a constant integer offset
+ from GMT/UTC.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ Time zones in the real world can have no meaning unless
+ associated with a date as well as a time
+ since the offset may vary through the year with daylight savings
+ time boundaries.
+ </para>
- <substeps>
- <step>
- <para>
- Do a binary-search table lookup for the token
- as either a special string (e.g. <literal>today</literal>),
- day (e.g. <literal>Thursday</literal>),
- month (e.g. <literal>January</literal>), or noise word (e.g. <literal>on</literal>).
- </para>
- <para>
- Set field values and bit mask for fields.
- For example, set year, month, day for <literal>today</literal>, and additionally
- hour, minute, second for <literal>now</literal>.
- </para>
- </step>
-
- <step>
- <para>
- If not found, do a similar binary-search table lookup to match
- the token with a time zone.
- </para>
- </step>
-
- <step>
- <para>
- If not found, throw an error.
- </para>
- </step>
- </substeps>
- </step>
-
- <step>
- <para>
- The token is a number or number field.
- If there are more than 4 digits,
- and if no other date fields have been previously read, then interpret
- as a "concatenated date" (e.g. <literal>19990118</literal>).
- </para>
+ <para>
+ To address these difficulties, <productname>PostgreSQL</productname>
+ associates time zones only with date and time
+ types which contain both date and time,
+ and assumes local time for any type containing only
+ date or time. Further, time zone support is derived from
+ the underlying operating system
+ time zone capabilities, and hence can handle daylight savings time
+ and other expected behavior.
+ </para>
- <substeps>
- <step>
- <para>
- If there are more than 4 digits,
- and if no other date fields have been previously read, then interpret
- as a "concatenated date" (e.g. <literal>19990118</literal>).
- </para>
- </step>
-
- <step>
- <para>
- If three digits and a year has already been decoded, then interpret as day of year.
- </para>
- </step>
-
- <step>
- <para>
- If longer than two digits, then interpret as a year.
- </para>
- </step>
-
- <step>
- <para>
- If in European date mode, and if the day field has not yet been read,
- and if the value is less than or equal to 31, then interpret as a day.
- </para>
- </step>
-
- <step>
- <para>
- If in non-European (US) date mode, and if the month field has not yet been read,
- and if the value is less than or equal to 12, then interpret as a month.
- </para>
- </step>
-
- <step>
- <para>
- If the day field has not yet been read,
- and if the value is less than or equal to 31, then interpret as a month.
- </para>
- </step>
-
- <step>
- <para>
- If the month field has not yet been read,
- and if the value is less than or equal to 12, then interpret as a month.
- </para>
- </step>
-
- <step>
- <para>
- Otherwise, interpret as a year.
- </para>
- </step>
- </substeps>
- </step>
-
- <step>
- <para>
- If BC has been specified, negate the year and offset by one
- (there is no year zero in the Gregorian calendar).
- </para>
- </step>
+ <para>
+ <productname>PostgreSQL</productname> obtains time zone support
+ from the underlying operating system for dates between 1902 and
+ 2038 (near the typical date limits for Unix-style
+ systems). Outside of this range, all dates are assumed to be
+ specified and used in Universal Coordinated Time (UTC).
+ </para>
+
+ <para>
+ All dates and times are stored internally in Universal UTC,
+ alternately known as Greenwich Mean Time (GMT).
+ Times are converted to local time on the database server before being
+ sent to the client frontend, hence by default are in the server
+ time zone.
+ </para>
- <step>
+ <para>
+ There are several ways to affect the time zone behavior:
+
+ <itemizedlist spacing="compact" mark="bullet">
+ <listitem>
+ <para>
+ The TZ environment variable used by the backend directly
+ on postmaster startup as the default time zone.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The PGTZ environment variable set at the client used by libpq
+ to send time zone information to the backend upon connection.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
+ sets the time zone for the session.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ If an invalid time zone is specified,
+ the time zone becomes GMT (on most systems anyway).
+ </para>
+
+ <para>
+
+ <note>
<para>
- If BC was not specified, and if the year field was two digits in length, then
- adjust the year to 4 digits. If the field was less than 70, then add 2000;
- otherwise, add 1900.
+ If the compiler option USE_AUSTRALIAN_RULES is set
+ then <literal>EST</literal> refers to Australia Eastern Std Time,
+ which has an offset of +10:00 hours from UTC.
</para>
- </step>
+ </note>
+ </para>
- </procedure>
</sect2>
<sect2>
- <title>datetime</title>
-
-<para>
-General-use date and time is input using a wide range of
-styles, including ISO-compatible, <acronym>SQL</acronym>-compatible, traditional
-<productname>Postgres</productname> (see section on "absolute time")
-and other permutations of date and time. Output styles can be ISO-compatible,
-<acronym>SQL</acronym>-compatible, or traditional
-<productname>Postgres</productname>, with the default set to be compatible
-with <productname>Postgres</productname> v6.0.
-</para>
+ <title>Internals</title>
-<para>
-<type>datetime</type> is specified using the following syntax:
-
-<programlisting>
-Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
- YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
- Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
-where
- Year is 4013 BC, ..., very large
- Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
- Day is 1, 2, ..., 31
- Hour is 00, 02, ..., 23
- Minute is 00, 01, ..., 59
- Second is 00, 01, ..., 59 (60 for leap second)
- Timezone is 3 characters or ISO offset to GMT
-</programlisting>
+ <para>
+ <productname>PostgreSQL</productname> uses Julian dates
+ for all date/time calculations. They have the nice property of correctly
+ predicting/calculating any date more recent than 4713BC
+ to far into the future, using the assumption that the length of the
+ year is 365.2425 days.
</para>
-<para>
-Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
-Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
-offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
-Dates are stored internally in Greenwich Mean Time. Input and output routines
-translate time to the local time zone of the server.
-</para></sect2>
-
-<sect2>
-<title><type>timespan</type></title>
-
-<para>
-General-use time span is input using a wide range of
-syntaxes, including ISO-compatible, <acronym>SQL</acronym>-compatible,
-traditional
-<productname>Postgres</productname> (see section on "relative time")
- and other permutations of time span. Output formats can be ISO-compatible,
-<acronym>SQL</acronym>-compatible, or traditional
-<productname>Postgres</productname>,
-with the default set to be <productname>Postgres</productname>-compatible.
-Months and years are a "qualitative" time interval, and are stored separately
-from the other "quantitative" time intervals such as day or hour.
-For date arithmetic,
-the qualitative time units are instantiated in the context of the
-relevant date or time.
+ <para>
+ Date conventions before the 19th century make for interesting reading,
+ but are not consistant enough to warrant coding into a date/time handler.
</para>
+ </sect2>
-<para>
-Time span is specified with the following syntax:
+ </sect1>
-<programlisting>
- Quantity Unit [Quantity Unit...] [Direction]
-@ Quantity Unit [Direction]
-where
- Quantity is ..., <literal>-1</literal>, <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...
- Unit is <literal>second</literal>, <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>, <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
- <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>, or abbreviations or plurals of these units.
- Direction is <literal>ago</literal>.
-</programlisting>
-</para>
-</sect2>
+ <sect1>
+ <title>Boolean Type</title>
-<sect2>
-<title>abstime</title>
+ <para>
+ <productname>Postgres</productname> supports <type>bool</type> as
+ the <acronym>SQL3</acronym> boolean type.
+ <type>bool</type> can have one of only two states: 'true' or 'false'.
+ A third state, 'unknown', is not
+ implemented and is not suggested in <acronym>SQL3</acronym>;
+ <acronym>NULL</acronym> is an
+ effective substitute. <type>bool</type> can be used in any boolean expression,
+ and boolean expressions
+ always evaluate to a result compatible with this type.</para>
- <para>
- Absolute time (<type>abstime</type>) is a limited-range (+/- 68 years) and
- limited-precision (1 sec)
- date data type. <type>datetime</type> may be preferred, since it
- covers a larger range with greater precision.
- </para>
+ <para>
+ <type>bool</type> uses 1 byte of storage.
+ </para>
-<para>
-Absolute time is specified using the following syntax:
+ <para>
+ <table tocentry="1">
+ <title><productname>Postgres</productname> Boolean Type</title>
+ <titleabbrev>Booleans</titleabbrev>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>State</entry>
+ <entry>Output</entry>
+ <entry>Input</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>True</entry>
+ <entry>'t'</entry>
+ <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
+ </row>
+ <row>
+ <entry>False</entry>
+ <entry>'f'</entry>
+ <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+ </sect1>
-<programlisting>
-Month Day [ Hour : Minute : Second ] Year [ Timezone ]
-where
- Month is Jan, Feb, ..., Dec
- Day is 1, 2, ..., 31
- Hour is 01, 02, ..., 24
- Minute is 00, 01, ..., 59
- Second is 00, 01, ..., 59
- Year is 1901, 1902, ..., 2038
-</programlisting>
-</para>
-
-<para>
- Valid dates are from <literal>Dec 13 20:45:53 1901 GMT</literal>
- to <literal>Jan 19 03:14:04 2038 GMT</literal>.
-
-<note>
-<title>Historical Note</title>
-<para>
-As of Version 3.0, times are no longer read and written
-using Greenwich Mean Time; the input and output routines default to
-the local time zone.</para>
-</note>
-
-All special values allowed for <type>datetime</type> are also
-allowed for "absolute time".
-</para>
-
-</sect2>
-
-<sect2>
-<title>reltime</title>
-
-<para>
-Relative time <type>reltime</type> is a limited-range (+/- 68 years)
- and limited-precision (1 sec) time span data type.
-<type>timespan</type> should be preferred, since it
-covers a larger range with greater precision and, more importantly,
-can distinguish between
-relative units (months and years) and quantitative units (days, hours, etc).
-Instead, reltime
-must force months to be exactly 30 days, so time arithmetic does not
-always work as expected.
- For example, adding one reltime <literal>year</literal> to abstime <literal>today</literal> does not
-produce today's date one year from
-now, but rather a date 360 days from today.
-</para>
-
-<para>
-<type>reltime</type> shares input and output routines with the other
-time span types.
-The section on <type>timespan</type> covers this in more detail.
-</para>
-
-</sect2>
-
-<sect2>
-<title><type>timestamp</type></title>
-
-<para>
-This is currently a limited-range absolute time which closely resembles the
-abstime
-data type. It shares the general input parser with the other date/time types.
-In future releases this type will absorb the capabilities of the
-<type>datetime</type> type
-and will move toward <acronym>SQL92</acronym> compliance.
-</para>
-
-<para>
-<type>timestamp</type> is specified using the same syntax as for
-<type>datetime</type>.
-</para>
-</sect2>
-
-<sect2>
-<title><type>interval</type></title>
-
-<para>
-<type>interval</type> is an <acronym>SQL92</acronym> data type which is
-currently mapped to the <type>timespan</type>
-<productname>Postgres</productname> data type.
-</para>
-</sect2>
-
-<sect2>
-<title>tinterval</title>
-
-<para>
-Time ranges are specified as:
+ <sect1>
+ <title>Geometric Types</title>
-<programlisting>
-[ 'abstime' 'abstime']
-where
- abstime is a time in the absolute time format.
-</programlisting>
+ <para>
+ Geometric types represent two-dimensional spatial objects.
+ The most fundamental type,
+ the point, forms the basis for all of the other types.
+ </para>
-Special abstime values such as
-<literal>current', <literal>infinity' and <literal>-infinity' can be used.</literal></literal></literal>
-</para></sect2>
-
-</sect1>
-
-<sect1>
-<title>Boolean Type</title>
-
-<para>
-<productname>Postgres</productname> supports <type>bool</type> as
-the <acronym>SQL3</acronym> boolean type.
-<type>bool</type> can have one of only two states: 'true' or 'false'.
-A third state, 'unknown', is not
-implemented and is not suggested in <acronym>SQL3</acronym>;
-<acronym>NULL</acronym> is an
-effective substitute. <type>bool</type> can be used in any boolean expression,
-and boolean expressions
-always evaluate to a result compatible with this type.</para>
-
-<para>
-<type>bool</type> uses 4 bytes of storage.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname> Boolean Type</title>
-<titleabbrev>Booleans</titleabbrev>
-<tgroup cols="3">
-<thead>
- <row>
- <entry>State</entry>
- <entry>Output</entry>
- <entry>Input</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>True</entry>
- <entry>'t'</entry>
- <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
- </row>
- <row>
- <entry>False</entry>
- <entry>'f'</entry>
- <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-</sect1>
-
-<sect1>
-<title>Geometric Types</title>
-
-<para>
-Geometric types represent two-dimensional spatial objects.
-The most fundamental type,
-the point, forms the basis for all of the other types.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname> Geometric Types</title>
-<titleabbrev>Geometrics</titleabbrev>
-<tgroup cols="4">
-<thead>
- <row>
- <entry>Geometric Type</entry>
- <entry>Storage</entry>
- <entry>Representation</entry>
- <entry>Description</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>point</entry>
- <entry>16 bytes</entry>
- <entry>(x,y)</entry>
- <entry>Point in space</entry>
- </row>
- <row>
- <entry>line</entry>
- <entry>32 bytes</entry>
- <entry>((x1,y1),(x2,y2))</entry>
- <entry>Infinite line</entry>
- </row>
- <row>
- <entry>lseg</entry>
- <entry>32 bytes</entry>
- <entry>((x1,y1),(x2,y2))</entry>
- <entry>Finite line segment</entry>
- </row>
- <row>
- <entry>box</entry>
- <entry>32 bytes</entry>
- <entry>((x1,y1),(x2,y2))</entry>
- <entry>Rectangular box</entry>
- </row>
- <row>
- <entry>path</entry>
- <entry>4+32n bytes</entry>
- <entry>((x1,y1),...)</entry>
- <entry>Closed path (similar to polygon)</entry>
- </row>
- <row>
- <entry>path</entry>
- <entry>4+32n bytes</entry>
- <entry>[(x1,y1),...]</entry>
- <entry>Open path</entry>
- </row>
- <row>
- <entry>polygon</entry>
- <entry>4+32n bytes</entry>
- <entry>((x1,y1),...)</entry>
- <entry>Polygon (similar to closed path)</entry>
- </row>
- <row>
- <entry>circle</entry>
- <entry>24 bytes</entry>
- <entry><(x,y),r></entry>
- <entry>Circle (center and radius)</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-<para>
-A rich set of functions and operators is available to perform various geometric
-operations such as scaling, translation, rotation, and determining
-intersections.
-</para>
-
-<sect2>
-<title>Point</title>
+ <para>
+ <table tocentry="1">
+ <title><productname>Postgres</productname> Geometric Types</title>
+ <titleabbrev>Geometrics</titleabbrev>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Geometric Type</entry>
+ <entry>Storage</entry>
+ <entry>Representation</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>point</entry>
+ <entry>16 bytes</entry>
+ <entry>(x,y)</entry>
+ <entry>Point in space</entry>
+ </row>
+ <row>
+ <entry>line</entry>
+ <entry>32 bytes</entry>
+ <entry>((x1,y1),(x2,y2))</entry>
+ <entry>Infinite line</entry>
+ </row>
+ <row>
+ <entry>lseg</entry>
+ <entry>32 bytes</entry>
+ <entry>((x1,y1),(x2,y2))</entry>
+ <entry>Finite line segment</entry>
+ </row>
+ <row>
+ <entry>box</entry>
+ <entry>32 bytes</entry>
+ <entry>((x1,y1),(x2,y2))</entry>
+ <entry>Rectangular box</entry>
+ </row>
+ <row>
+ <entry>path</entry>
+ <entry>4+32n bytes</entry>
+ <entry>((x1,y1),...)</entry>
+ <entry>Closed path (similar to polygon)</entry>
+ </row>
+ <row>
+ <entry>path</entry>
+ <entry>4+32n bytes</entry>
+ <entry>[(x1,y1),...]</entry>
+ <entry>Open path</entry>
+ </row>
+ <row>
+ <entry>polygon</entry>
+ <entry>4+32n bytes</entry>
+ <entry>((x1,y1),...)</entry>
+ <entry>Polygon (similar to closed path)</entry>
+ </row>
+ <row>
+ <entry>circle</entry>
+ <entry>24 bytes</entry>
+ <entry><(x,y),r></entry>
+ <entry>Circle (center and radius)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+
+ <para>
+ A rich set of functions and operators is available to perform various geometric
+ operations such as scaling, translation, rotation, and determining
+ intersections.
+ </para>
+
+ <sect2>
+ <title>Point</title>
<para>
Points are the fundamental two-dimensional building block for geometric types.
</para>
-<para>
-<type>point</type> is specified using the following syntax:
+ <type>point</type> is specified using the following syntax:
-<programlisting>
( x , y )
x , y
where
x is the x-axis coordinate as a floating point number
y is the y-axis coordinate as a floating point number
-</programlisting>
-</para>
-</sect2>
+ </programlisting>
+ </para>
+ </sect2>
-<sect2>
-<title>Line Segment</title>
+ <sect2>
+ <title>Line Segment</title>
-<para>
-Line segments (lseg) are represented by pairs of points.
-</para>
+ Line segments (<type>lseg</type>) are represented by pairs of points.
+ </para>
-<para>
-<type>lseg</type> is specified using the following syntax:
-<programlisting>
+ <type>lseg</type> is specified using the following syntax:
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
(x1,y1) and (x2,y2) are the endpoints of the segment
-</programlisting>
-</para>
-</sect2>
+ </programlisting>
+ </para>
+ </sect2>
-<sect2>
-<title>Box</title>
+ <sect2>
+ <title>Box</title>
-<para>
-Boxes are represented by pairs of points which are opposite
-corners of the box.
-</para>
+ Boxes are represented by pairs of points which are opposite
+ corners of the box.
+ </para>
-<para>
<type>box</type> is specified using the following syntax:
-<programlisting>
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
(x1,y1) and (x2,y2) are opposite corners
-</programlisting>
+ </programlisting>
-Boxes are output using the first syntax.
-The corners are reordered on input to store
-the lower left corner first and the upper right corner last.
-Other corners of the box can be entered, but the lower
-left and upper right corners are determined from the input and stored.
-</para>
-</sect2>
-
-<sect2>
-<title>Path</title>
-
-<para>
-Paths are represented by connected sets of points. Paths can be "open", where
-the first and last points in the set are not connected, and "closed",
-where the first and last point are connected. Functions
-<function>popen(p)</function>
-and
-<function>pclose(p)</function>
-are supplied to force a path to be open or closed, and functions
-<function>isopen(p)</function>
-and
-<function>isclosed(p)</function>
-are supplied to select either type in a query.
-</para>
-
-<para>
-path is specified using the following syntax:
+ Boxes are output using the first syntax.
+ The corners are reordered on input to store
+ the lower left corner first and the upper right corner last.
+ Other corners of the box can be entered, but the lower
+ left and upper right corners are determined from the input and stored.
+ </para>
+ </sect2>
-<programlisting>
+ <sect2>
+ <title>Path</title>
+
+ <para>
+ Paths are represented by connected sets of points. Paths can be "open", where
+ the first and last points in the set are not connected, and "closed",
+ where the first and last point are connected. Functions
+ <function>popen(p)</function>
+ and
+ <function>pclose(p)</function>
+ are supplied to force a path to be open or closed, and functions
+ <function>isopen(p)</function>
+ and
+ <function>isclosed(p)</function>
+ are supplied to select either type in a query.
+ </para>
+
+ <para>
+ <type>path</type> is specified using the following syntax:
+
+ <programlisting>
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( x1 , y1 ) , ... , ( xn , yn )
(x1,y1),...,(xn,yn) are points 1 through n
a leading "[" indicates an open path
a leading "(" indicates a closed path
-</programlisting>
-Paths are output using the first syntax.
-Note that <productname>Postgres</productname> versions prior to
-v6.1 used a format for paths which had a single leading parenthesis,
-a "closed" flag,
-an integer count of the number of points, then the list of points followed by a
-closing parenthesis.
-The built-in function <function>upgradepath</function> is supplied to convert
-paths dumped and reloaded from pre-v6.1 databases.
-</para>
-</sect2>
-
-<sect2>
-<title>Polygon</title>
-
-<para>
-Polygons are represented by sets of points. Polygons should probably be
-considered equivalent to closed paths, but are stored differently
-and have their own set of support routines.
-</para>
-
-<para>
-<type>polygon</type> is specified using the following syntax:
+ </programlisting>
+
+ Paths are output using the first syntax.
+ Note that <productname>Postgres</productname> versions prior to
+ v6.1 used a format for paths which had a single leading parenthesis,
+ a "closed" flag,
+ an integer count of the number of points, then the list of points followed by a
+ closing parenthesis.
+ The built-in function <function>upgradepath</function> is supplied to convert
+ paths dumped and reloaded from pre-v6.1 databases.
+ </para>
+ </sect2>
-<programlisting>
+ <sect2>
+ <title>Polygon</title>
+
+ <para>
+ Polygons are represented by sets of points. Polygons should probably be
+ considered equivalent to closed paths, but are stored differently
+ and have their own set of support routines.
+ </para>
+
+ <para>
+ <type>polygon</type> is specified using the following syntax:
+
+ <programlisting>
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
where
(x1,y1),...,(xn,yn) are points 1 through n
-</programlisting>
-
-Polygons are output using the first syntax.
-Note that <productname>Postgres</productname> versions prior to
-v6.1 used a format for polygons which had a single leading parenthesis, the list
-of x-axis coordinates, the list of y-axis coordinates,
-followed by a closing parenthesis.
-The built-in function <function>upgradepoly</function> is supplied to convert
-polygons dumped and reloaded from pre-v6.1 databases.
-</para>
-</sect2>
+ </programlisting>
+
+ Polygons are output using the first syntax.
+
Note that <productname>Postgres</productname> versions prior to
+ v6.1 used a format for polygons which had a single leading parenthesis, the list
+ of x-axis coordinates, the list of y-axis coordinates,
+ followed by a closing parenthesis.
+ The built-in function <function>upgradepoly</function> is supplied to convert
+ polygons dumped and reloaded from pre-v6.1 databases.
+ </para>
+ </sect2>
-<sect2>
-<title>Circle</title>
+ <sect2>
+ <title>Circle</title>
-<para>
-Circles are represented by a center point and a radius.
-</para>
+ Circles are represented by a center point and a radius.
+ </para>
-<para>
-circle is specified using the following syntax:
+ <type>circle</type> is specified using the following syntax:
-<programlisting>
< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
where
(x,y) is the center of the circle
r is the radius of the circle
-</programlisting>
+ </programlisting>
-Circles are output using the first syntax.
-</para>
-</sect2>
-
-</sect1>
-
-<sect1>
-<title>IP Version 4 Networks and Host Addresses</title>
-
-<para>
-The <type>cidr</type> type stores networks specified
-in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
-The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
-variation in representation to represent simple host TCP/IP addresses.
-</para>
-
-<para>
-<table tocentry="1">
-<title><productname>Postgres</productname>IP Version 4 Types</title>
-<titleabbrev>IPV4</titleabbrev>
-<tgroup cols="4">
-<thead>
- <row>
- <entry>IPV4 Type</entry>
- <entry>Storage</entry>
- <entry>Description</entry>
- <entry>Range</entry>
- </row>
-</thead>
-<tbody>
- <row>
- <entry>cidr</entry>
- <entry>variable</entry>
- <entry>CIDR networks</entry>
- <entry>Valid IPV4 CIDR blocks</entry>
- </row>
- <row>
- <entry>inet</entry>
- <entry>variable</entry>
- <entry>nets and hosts</entry>
- <entry>Valid IPV4 CIDR blocks</entry>
- </row>
-</tbody>
-</tgroup>
-</table>
-</para>
-
-<sect2>
-<title>CIDR</title>
-
-<para>
-The <type>cidr</type> type holds a CIDR network.
-The format for specifying classless networks is <replaceable class="parameter">x.x.x.x/y</replaceable>
- where <replaceable class="parameter">x.x.x.x</replaceable> is the
-network and <replaceable class="parameter">/y</replaceable> is the number of bits in the netmask.
-If <replaceable class="parameter">/y</replaceable> omitted, it is calculated using assumptions from
-the older classfull naming system except that it is extended to include at least
-all of the octets in the input.
-</para>
-
-<para>
-Here are some examples:
+ Circles are output using the first syntax.
+ </para>
+ </sect2>
+
+ </sect1>
+
+ <sect1>
+ <title>IP Version 4 Networks and Host Addresses</title>
+
+ <para>
+ The <type>cidr</type> type stores networks specified
+ in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
+ The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
+ variation in representation to represent simple host TCP/IP addresses.
+ </para>
+
+ <para>
+ <table tocentry="1">
+ <title><productname>Postgres</productname>IP Version 4 Types</title>
+ <titleabbrev>IPV4</titleabbrev>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>IPV4 Type</entry>
+ <entry>Storage</entry>
+ <entry>Description</entry>
+ <entry>Range</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>cidr</entry>
+ <entry>variable</entry>
+ <entry>CIDR networks</entry>
+ <entry>Valid IPV4 CIDR blocks</entry>
+ </row>
+ <row>
+ <entry>inet</entry>
+ <entry>variable</entry>
+ <entry>nets and hosts</entry>
+ <entry>Valid IPV4 CIDR blocks</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+
+ <sect2>
+ <title>CIDR</title>
+
+ <para>
+ The <type>cidr</type> type holds a CIDR network.
+ The format for specifying classless networks is
+ <replaceable class="parameter">x.x.x.x/y</replaceable>
+ where <replaceable class="parameter">x.x.x.x</replaceable> is the
+ network and <replaceable class="parameter">/y</replaceable> is
+ the number of bits in the netmask.
+ If <replaceable class="parameter">/y</replaceable> omitted, it is
+ calculated using assumptions from
+ the older classfull naming system except that it is extended to include at least
+ all of the octets in the input.
+ </para>
+
+ <para>
+ Here are some examples:
<table tocentry="1">
<title><productname>Postgres</productname>IP Types Examples</title>
<sect2>
<title id="inet-type"><type>inet</type></title>
-<para>
-The <type>inet</type> type is designed to hold, in one field, all of the information
-about a host including the CIDR-style subnet that it is in.
-Note that if you want to store proper CIDR networks,
-you should use the <type>cidr</type> type.
-The <type>inet</type> type is similar to the <type>cidr</type> type except that the bits in the
-host part can be non-zero.
-Functions exist to extract the various elements of the field.
-</para>
-
-<para>
+ <para>
+ The <type>inet</type> type is designed to hold, in one field, all of the information
+ about a host including the CIDR-style subnet that it is in.
+ Note that if you want to store proper CIDR networks,
+ you should use the <type>cidr</type> type.
+ The <type>inet</type> type is similar to the <type>cidr</type>
+ type except that the bits in the
+ host part can be non-zero.
+ Functions exist to extract the various elements of the field.
+ </para>
+
+ <para>
The input format for this function is
- <replaceable class="parameter">x.x.x.x/y</replaceable>
-where <replaceable class="parameter">x.x.x.x</replaceable> is
-an internet host and <replaceable class="parameter">y</replaceable>
-is the number of bits in the netmask.
-If the <replaceable class="parameter">/y</replaceable> part is left off,
-it is treated as <literal>/32</literal>.
-On output, the <replaceable class="parameter">/y</replaceable> part is not printed
-if it is <literal>/32</literal>.
-This allows the type to be used as a straight host type by just leaving off
-the bits part.
-</para></sect2>
-</sect1>
-
-</chapter>
+ <replaceable class="parameter">x.x.x.x/y</replaceable>
+ where <replaceable class="parameter">x.x.x.x</replaceable> is
+ an internet host and <replaceable class="parameter">y</replaceable>
+ is the number of bits in the netmask.
+ If the <replaceable class="parameter">/y</replaceable> part is left off,
+ it is treated as <literal>/32</literal>.
+ On output, the <replaceable class="parameter">/y</replaceable> part is not printed
+ if it is <literal>/32</literal>.
+ This allows the type to be used as a straight host type by just leaving off
+ the bits part.
+ </para>
+ </sect2>
+ </sect1>
+
+ </chapter>
<!-- Keep this comment at the end of the file
Local variables:
-mode: sgml
+mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
-sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
projects / postgresql / blobdiff