granicus.if.org Git - postgresql/blob - doc/src/sgml/datatype.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.26 2000/03/31 03:27:40 thomas Exp $
   3 -->
   4
   5  <chapter id="datatype">
   6   <title id="datatype-title">Data Types</title>
   7
   8   <abstract>
   9    <para>
  10     Describes the built-in data types available in
  11     <productname>Postgres</productname>.
  12    </para>
  13   </abstract>
  14
  15   <para>
  16    <productname>Postgres</productname> has a rich set of native data
  17    types available to users.
  18    Users may add new types to <productname>Postgres</productname> using the
  19    <command>DEFINE TYPE</command> command.
  20   </para>
  21
  22   <para>
  23    In the context of data types, the following sections will discuss
  24    <acronym>SQL</acronym> standards compliance, porting issues, and usage.
  25
  26    Some <productname>Postgres</productname> types correspond directly to
  27    <acronym>SQL92</acronym>-compatible types. In other
  28    cases, data types defined by <acronym>SQL92</acronym> syntax are mapped directly
  29    into native <productname>Postgres</productname> types.
  30
  31    Many of the built-in types have obvious external formats. However, several
  32    types are either unique to <productname>Postgres</productname>,
  33    such as open and closed paths, or have
  34    several possibilities for formats, such as the date and time types.
  35   </para>
  36
  37   <para>
  38    <table tocentry="1">
  39     <title><productname>Postgres</productname> Data Types</title>
  40     <titleabbrev>Data Types</titleabbrev>
  41     <tgroup cols="3">
  42      <thead>
  43       <row>
  44        <entry><productname>Postgres</productname> Type</entry>
  45        <entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>
  46        <entry>Description</entry>
  47       </row>
  48      </thead>
  49      <tbody>
  50       <row>
  51        <entry>bool</entry>
  52        <entry>boolean</entry>
  53        <entry>logical boolean (true/false)</entry>
  54       </row>
  55       <row>
  56        <entry>box</entry>
  57        <entry></entry>
  58        <entry>rectangular box in 2D plane</entry>
  59       </row>
  60       <row>
  61        <entry>char(n)</entry>
  62        <entry>character(n)</entry>
  63        <entry>fixed-length character string</entry>
  64       </row>
  65       <row>
  66        <entry>cidr</entry>
  67        <entry></entry>
  68        <entry>IP version 4 network or host address</entry>
  69       </row>
  70       <row>
  71        <entry>circle</entry>
  72        <entry></entry>
  73        <entry>circle in 2D plane</entry>
  74       </row>
  75       <row>
  76        <entry>date</entry>
  77        <entry>date</entry>
  78        <entry>calendar date without time of day</entry>
  79       </row>
  80       <row>
  81        <entry>decimal</entry>
  82        <entry>decimal(p,s)</entry>
  83        <entry>exact numeric for p <= 9, s = 0</entry>
  84       </row>
  85       <row>
  86        <entry>float4</entry>
  87        <entry>float(<replaceable>p</replaceable>), <replaceable>p</replaceable> < 7</entry>
  88        <entry>floating-point number with precision <replaceable>p</replaceable></entry>
  89       </row>
  90       <row>
  91        <entry>float8</entry>
  92        <entry>float(<replaceable>p</replaceable>), 7 <= <replaceable>p</replaceable> < 16</entry>
  93        <entry>floating-point number with precision <replaceable>p</replaceable></entry>
  94       </row>
  95       <row>
  96        <entry>inet</entry>
  97        <entry></entry>
  98        <entry>IP version 4 network or host address</entry>
  99       </row>
 100       <row>
 101        <entry>int2</entry>
 102        <entry>smallint</entry>
 103        <entry>signed two-byte integer</entry>
 104       </row>
 105       <row>
 106        <entry>int4</entry>
 107        <entry>int, integer</entry>
 108        <entry>signed 4-byte integer</entry>
 109       </row>
 110       <row>
 111        <entry>int8</entry>
 112        <entry></entry>
 113        <entry>signed 8-byte integer</entry>
 114       </row>
 115       <row>
 116        <entry>interval</entry>
 117        <entry>interval</entry>
 118        <entry>general-use time span</entry>
 119       </row>
 120       <row>
 121        <entry>line</entry>
 122        <entry></entry>
 123        <entry>infinite line in 2D plane</entry>
 124       </row>
 125       <row>
 126        <entry>lseg</entry>
 127        <entry></entry>
 128        <entry>line segment in 2D plane</entry>
 129       </row>
 130       <row>
 131        <entry>money</entry>
 132        <entry>decimal(9,2)</entry>
 133        <entry>US-style currency</entry>
 134       </row>
 135       <row>
 136        <entry>numeric</entry>
 137        <entry>numeric(p,s)</entry>
 138        <entry>exact numeric for p == 9, s = 0</entry>
 139       </row>
 140       <row>
 141        <entry>path</entry>
 142        <entry></entry>
 143        <entry>open and closed geometric path in 2D plane</entry>
 144       </row>
 145       <row>
 146        <entry>point</entry>
 147        <entry></entry>
 148        <entry>geometric point in 2D plane</entry>
 149       </row>
 150       <row>
 151        <entry>polygon</entry>
 152        <entry></entry>
 153        <entry>closed geometric path in 2D plane</entry>
 154       </row>
 155       <row>
 156        <entry>serial</entry>
 157        <entry></entry>
 158        <entry>unique id for indexing and cross-reference</entry>
 159       </row>
 160       <row>
 161        <entry>time</entry>
 162        <entry>time</entry>
 163        <entry>time of day</entry>
 164       </row>
 165       <row>
 166        <entry>timetz</entry>
 167        <entry>time with time zone</entry>
 168        <entry>time of day, including time zone</entry>
 169       </row>
 170       <row>
 171        <entry>timestamp</entry>
 172        <entry>timestamp with time zone</entry>
 173        <entry>date/time</entry>
 174       </row>
 175       <row>
 176        <entry>varchar(n)</entry>
 177        <entry>character varying(n)</entry>
 178        <entry>variable-length character string</entry>
 179       </row>
 180      </tbody>
 181     </tgroup>
 182    </table>
 183   </para>
 184
 185   <para>
 186    <note>
 187     <para>
 188      The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type
 189      but only ipv4 is handled in the current implementation.
 190      Everything here that talks about ipv4 will apply to ipv6 in a
 191      future release.
 192     </para>
 193    </note>
 194   </para>
 195
 196   <para>
 197    <table tocentry="1">
 198     <title><productname>Postgres</productname> Function Constants</title>
 199     <titleabbrev>Constants</titleabbrev>
 200     <tgroup cols="3">
 201      <thead>
 202       <row>
 203        <entry><productname>Postgres</productname> Function</entry>
 204        <entry><acronym>SQL92</acronym> Constant</entry>
 205        <entry>Description</entry>
 206       </row>
 207      </thead>
 208      <tbody>
 209       <row>
 210        <entry>getpgusername()</entry>
 211        <entry>current_user</entry>
 212        <entry>user name in current session</entry>
 213       </row>
 214       <row>
 215        <entry>date('now')</entry>
 216        <entry>current_date</entry>
 217        <entry>date of current transaction</entry>
 218       </row>
 219       <row>
 220        <entry>time('now')</entry>
 221        <entry>current_time</entry>
 222        <entry>time of current transaction</entry>
 223       </row>
 224       <row>
 225        <entry>timestamp('now')</entry>
 226        <entry>current_timestamp</entry>
 227        <entry>date and time of current transaction</entry>
 228       </row>
 229      </tbody>
 230     </tgroup>
 231    </table>
 232   </para>
 233
 234   <para>
 235    <productname>Postgres</productname> has features at the forefront of
 236    <acronym>ORDBMS</acronym> development. In addition to
 237    <acronym>SQL3</acronym> conformance, substantial portions
 238    of <acronym>SQL92</acronym> are also supported.
 239    Although we strive for <acronym>SQL92</acronym> compliance,
 240    there are some aspects of the standard
 241    which are ill considered and which should not live through subsequent standards.
 242    <productname>Postgres</productname> will not make great efforts to
 243    conform to these features; however, these tend to apply in little-used
 244    or obsure cases, and a typical user is not likely to run into them.
 245   </para>
 246
 247   <para>
 248    Most of the input and output functions corresponding to the
 249    base types (e.g., integers and floating point numbers) do some
 250    error-checking.
 251    Some of the operators and functions (e.g.,
 252    addition and multiplication) do not perform run-time error-checking in the
 253    interests of improving execution speed.
 254    On some systems, for example, the numeric operators for some data types may
 255    silently underflow or overflow.
 256   </para>
 257
 258   <para>
 259    Some of the input and output functions are not invertible.  That is,
 260    the result of an output function may lose precision when compared to
 261    the original input.
 262
 263    <note>
 264     <para>
 265      The original <productname>Postgres</productname> v4.2 code received from
 266      Berkeley rounded all double precision floating point results to six digits for
 267      output. Starting with v6.1, floating point numbers are allowed to retain
 268      most of the intrinsic precision of the type (typically 15 digits for doubles,
 269      6 digits for 4-byte floats).
 270      Other types with underlying floating point fields (e.g. geometric
 271      types) carry similar precision.
 272     </para>
 273    </note>
 274   </para>
 275
 276   <sect1>
 277    <title>Numeric Types</title>
 278
 279    <para>
 280     Numeric types consist of two- and four-byte integers and four- and eight-byte
 281     floating point numbers.
 282    </para>
 283
 284    <para>
 285     <table tocentry="1">
 286      <title><productname>Postgres</productname> Numeric Types</title>
 287      <titleabbrev>Numerics</titleabbrev>
 288      <tgroup cols="4">
 289       <thead>
 290        <row>
 291         <entry>Numeric Type</entry>
 292         <entry>Storage</entry>
 293         <entry>Description</entry>
 294         <entry>Range</entry>
 295        </row>
 296       </thead>
 297       <tbody>
 298        <row>
 299         <entry>decimal</entry>
 300         <entry>variable</entry>
 301         <entry>User-specified precision</entry>
 302         <entry>no limit</entry>
 303        </row>
 304        <row>
 305         <entry>float4</entry>
 306         <entry>4 bytes</entry>
 307         <entry>Variable-precision</entry>
 308         <entry>6 decimal places</entry>
 309        </row>
 310        <row>
 311         <entry>float8</entry>
 312         <entry>8 bytes</entry>
 313         <entry>Variable-precision</entry>
 314         <entry>15 decimal places</entry>
 315        </row>
 316        <row>
 317         <entry>int2</entry>
 318         <entry>2 bytes</entry>
 319         <entry>Fixed-precision</entry>
 320         <entry>-32768 to +32767</entry>
 321        </row>
 322        <row>
 323         <entry>int4</entry>
 324         <entry>4 bytes</entry>
 325         <entry>Usual choice for fixed-precision</entry>
 326         <entry>-2147483648 to +2147483647</entry>
 327        </row>
 328        <row>
 329         <entry>int8</entry>
 330         <entry>8 bytes</entry>
 331         <entry>Very large range fixed-precision</entry>
 332         <entry>+/- &gt; 18 decimal places</entry>
 333        </row>
 334        <row>
 335         <entry>numeric</entry>
 336         <entry>variable</entry>
 337         <entry>User-specified precision</entry>
 338         <entry>no limit</entry>
 339        </row>
 340        <row>
 341         <entry>serial</entry>
 342         <entry>4 bytes</entry>
 343         <entry>Identifer or cross-reference</entry>
 344         <entry>0 to +2147483647</entry>
 345        </row>
 346       </tbody>
 347      </tgroup>
 348     </table>
 349    </para>
 350
 351    <para>
 352     The numeric types have a full set of corresponding arithmetic operators and
 353     functions. Refer to <xref endterm="math-opers" linkend="math-opers">
 354     and <xref endterm="math-funcs" linkend="math-funcs"> for more information.
 355    </para>
 356
 357    <para>
 358     The <type>int8</type> type may not be available on all platforms since
 359     it relies on compiler support for this.
 360    </para>
 361
 362    <sect2>
 363     <title>The Serial Type</title>
 364
 365     <para>
 366      The <type>serial</type> type is a special-case type constructed by
 367      <productname>Postgres</productname> from other existing components.
 368      It is typically used to create unique identifiers for table entries.
 369      In the current implementation, specifying
 370
 371      <programlisting>
 372 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 373      </programlisting>
 374
 375      is equivalent to specifying:
 376
 377      <programlisting>
 378 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
 379 CREATE TABLE <replaceable class="parameter">tablename</replaceable>
 380     (<replaceable class="parameter">colname</replaceable> INT4 DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq');
 381 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>);
 382      </programlisting>
 383
 384      <caution>
 385       <para>
 386        The implicit sequence created for the <type>serial</type> type will
 387        <emphasis>not</emphasis> be automatically removed when the
 388        table is dropped.
 389       </para>
 390      </caution>
 391
 392      Implicit sequences supporting the <type>serial</type> are
 393      not automatically dropped when a table containing a serial type
 394      is dropped. So, the following commands executed in order will likely fail:
 395
 396      <programlisting>
 397 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 398 DROP TABLE <replaceable class="parameter">tablename</replaceable>;
 399 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 400      </programlisting>
 401
 402      The sequence will remain in the database until explicitly dropped using
 403      <command>DROP SEQUENCE</command>.
 404     </para>
 405    </sect2>
 406   </sect1>
 407
 408   <sect1>
 409    <title>Monetary Type</title>
 410
 411    <note>
 412     <title>Obsolete Type</title>
 413     <para>
 414      The <type>money</type> is now deprecated. Use <type>numeric</type>
 415      or <type>decimal</type> instead. The money type may become a
 416      locale-aware layer over the numeric type in a future release.
 417     </para>
 418    </note>
 419
 420    <para>
 421     The <type>money</type> type supports US-style currency with
 422     fixed decimal point representation.
 423     If <productname>Postgres</productname> is compiled with USE_LOCALE
 424     then the money type should use the monetary conventions defined for
 425     <citetitle>locale(7)</citetitle>.
 426    </para>
 427
 428    <para>
 429     <table tocentry="1">
 430      <title><productname>Postgres</productname> Monetary Types</title>
 431      <titleabbrev>Money</titleabbrev>
 432      <tgroup cols="4">
 433       <thead>
 434        <row>
 435         <entry>Monetary Type</entry>
 436         <entry>Storage</entry>
 437         <entry>Description</entry>
 438         <entry>Range</entry>
 439        </row>
 440       </thead>
 441       <tbody>
 442        <row>
 443         <entry>money</entry>
 444         <entry>4 bytes</entry>
 445         <entry>Fixed-precision</entry>
 446         <entry>-21474836.48 to +21474836.47</entry>
 447        </row>
 448       </tbody>
 449      </tgroup>
 450     </table>
 451    </para>
 452
 453    <para>
 454     <type>numeric</type>
 455     will replace the money type, and should be preferred.
 456    </para>
 457
 458   </sect1>
 459
 460   <sect1>
 461    <title>Character Types</title>
 462
 463    <para>
 464     <acronym>SQL92</acronym> defines two primary character types:
 465     <type>char</type> and <type>varchar</type>.
 466     <productname>Postgres</productname> supports these types, in
 467     addition to the more general <type>text</type> type,
 468     which unlike <type>varchar</type>
 469     does not require an explicit declared upper
 470     limit on the size of the field.
 471    </para>
 472
 473    <para>
 474     <table tocentry="1">
 475      <title><productname>Postgres</productname> Character Types</title>
 476      <titleabbrev>Characters</titleabbrev>
 477      <tgroup cols="4">
 478       <thead>
 479        <row>
 480         <entry>Character Type</entry>
 481         <entry>Storage</entry>
 482         <entry>Recommendation</entry>
 483         <entry>Description</entry>
 484        </row>
 485       </thead>
 486       <tbody>
 487        <row>
 488         <entry>char</entry>
 489         <entry>1 byte</entry>
 490         <entry><acronym>SQL92</acronym>-compatible</entry>
 491         <entry>Single character</entry>
 492        </row>
 493        <row>
 494         <entry>char(n)</entry>
 495         <entry>(4+n) bytes</entry>
 496         <entry><acronym>SQL92</acronym>-compatible</entry>
 497         <entry>Fixed-length blank padded</entry>
 498        </row>
 499        <row>
 500         <entry>text</entry>
 501         <entry>(4+x) bytes</entry>
 502         <entry>Best choice</entry>
 503         <entry>Variable-length</entry>
 504        </row>
 505        <row>
 506         <entry>varchar(n)</entry>
 507         <entry>(4+n) bytes</entry>
 508         <entry><acronym>SQL92</acronym>-compatible</entry>
 509         <entry>Variable-length with limit</entry>
 510        </row>
 511       </tbody>
 512      </tgroup>
 513     </table>
 514    </para>
 515
 516    <para>
 517     There is one other fixed-length character type in <productname>Postgres</productname>.
 518     The <type>name</type> type
 519     only has one purpose and that is for storage of internal catalog
 520     names.
 521     It is not intended for use by the general user.
 522     Its length is currently defined as 32 bytes (31 characters plus terminator)
 523     but should be reference using NAMEDATALEN.
 524     The length is set at compile time (and is therefore adjustable for
 525     special uses); the default maximum length may change in a future release.
 526    </para>
 527
 528    <para>
 529     <table tocentry="1">
 530      <title><productname>Postgres</productname> Specialty Character Type</title>
 531      <titleabbrev>Specialty Characters</titleabbrev>
 532      <tgroup cols="3">
 533       <thead>
 534        <row>
 535         <entry>Character Type</entry>
 536         <entry>Storage</entry>
 537         <entry>Description</entry>
 538        </row>
 539       </thead>
 540       <tbody>
 541        <row>
 542         <entry>name</entry>
 543         <entry>32 bytes</entry>
 544         <entry>Thirty-one character internal type</entry>
 545        </row>
 546       </tbody>
 547      </tgroup>
 548     </table>
 549    </para>
 550
 551   </sect1>
 552
 553   <sect1>
 554    <title>Date/Time Types</title>
 555
 556    <para>
 557     <productname>PostgreSQL</productname> supports the full set of
 558     <acronym>SQL</acronym> date and time types.
 559    </para>
 560
 561    <para>
 562     <table tocentry="1">
 563      <title><productname>PostgreSQL</productname> Date/Time Types</title>
 564      <titleabbrev>Date/Time</titleabbrev>
 565      <tgroup cols="4">
 566       <thead>
 567        <row>
 568         <entry>Type</entry>
 569         <entry>Description</entry>
 570         <entry>Storage</entry>
 571         <entry>Earliest</entry>
 572         <entry>Latest</entry>
 573         <entry>Resolution</entry>
 574        </row>
 575       </thead>
 576       <tbody>
 577        <row>
 578         <entry><type>timestamp</type></entry>
 579         <entry>for data containing both date and time</entry>
 580         <entry>8 bytes</entry>
 581         <entry>4713 BC</entry>
 582         <entry>AD 1465001</entry>
 583         <entry>1 microsec / 14 digits</entry>
 584        </row>
 585        <row>
 586         <entry><type>timestamp with time zone</type></entry>
 587         <entry>date and time including time zone</entry>
 588         <entry>8 bytes</entry>
 589         <entry>1903 AD</entry>
 590         <entry>2037 AD</entry>
 591         <entry>1 microsec / 14 digits</entry>
 592        </row>
 593        <row>
 594         <entry><type>interval</type></entry>
 595         <entry>for time intervals</entry>
 596         <entry>12 bytes</entry>
 597         <entry>-178000000 years</entry>
 598         <entry>178000000 years</entry>
 599         <entry>1 mircosecond</entry>
 600        </row>
 601        <row>
 602         <entry><type>date</type></entry>
 603         <entry>for data containing only dates</entry>
 604         <entry>4 bytes</entry>
 605         <entry>4713 BC</entry>
 606         <entry>32767 AD</entry>
 607         <entry>1 day</entry>
 608        </row>
 609        <row>
 610         <entry><type>time</type></entry>
 611         <entry>for data containing only times of the day</entry>
 612         <entry>4 bytes</entry>
 613         <entry>00:00:00.00</entry>
 614         <entry>23:59:59.99</entry>
 615         <entry>1 microsecond</entry>
 616        </row>
 617        <row>
 618         <entry><type>time with time zone</type></entry>
 619         <entry>times of the day</entry>
 620         <entry>4 bytes</entry>
 621         <entry>00:00:00.00+12</entry>
 622         <entry>23:59:59.99-12</entry>
 623         <entry>1 microsecond</entry>
 624        </row>
 625       </tbody>
 626      </tgroup>
 627     </table>
 628
 629     <note>
 630      <para>
 631       To ensure compatibility to earlier versions of <productname>PostgreSQL</productname>
 632       we also continue to provide <type>datetime</type> (equivalent to <type>timestamp</type>) and
 633       <type>timespan</type> (equivalent to <type>interval</type>). The types <type>abstime</type>
 634       and <type>reltime</type> are lower precision types which are used internally.
 635       You are discouraged from using any of these types in new
 636       applications and are encouraged to move any old
 637       ones over when appropriate. Any or all of these types might disappear in a future release.
 638      </para>
 639     </note>
 640    </para>
 641
 642
 643    <sect2>
 644     <title>Date/Time Input</title>
 645
 646     <para>
 647      Date and time input is accepted in almost any reasonable format, including
 648      <acronym>ISO-8601</acronym>, <acronym>SQL</acronym>-compatible,
 649      traditional <productname>Postgres</productname>, and others.
 650      The ordering of month and day in date input can be ambiguous, therefore a setting
 651      exists to specify how it should be interpreted. The command
 652      <literal>SET DateStyle TO 'US'</literal> or <literal>SET DateStyle TO 'NonEuropean'</literal>
 653      specifies the variant <quote>month before day</quote>, the command
 654      <literal>SET DateStyle TO 'European'</literal> sets the variant
 655      <quote>day before month</quote>. The <literal>US</literal> style
 656      is the default but this default can be changed at compile time or at run time.
 657     </para>
 658
 659     <para>
 660      See <xref endterm="datetime-appendix-title" linkend="datetime-appendix-title">
 661      for the exact parsing rules of date/time input and for the recognized time zones.
 662     </para>
 663
 664     <para>
 665      Remember that any date or time input needs to be enclosed into single quotes,
 666      like text strings.
 667     </para>
 668
 669     <sect3>
 670     <title>date</title>
 671     <para>
 672      The following are possible inputs for the <type>date</type> type.
 673
 674      <table tocentry="1">
 675       <title><productname>PostgreSQL</productname> Date Input</title>
 676       <titleabbrev>Date Inputs</titleabbrev>
 677       <tgroup cols="2">
 678        <thead>
 679         <row>
 680          <entry>Example</entry>
 681          <entry>Description</entry>
 682         </row>
 683        </thead>
 684        <tbody>
 685         <row>
 686          <entry>January 8, 1999</entry>
 687          <entry>Unambiguous</entry>
 688         </row>
 689         <row>
 690          <entry>1999-01-08</entry>
 691          <entry>ISO-8601 format, preferred</entry>
 692         </row>
 693         <row>
 694          <entry>1/8/1999</entry>
 695          <entry>US; read as August 1 in European mode</entry>
 696         </row>
 697         <row>
 698          <entry>8/1/1999</entry>
 699          <entry>European; read as August 1 in US mode</entry>
 700         </row>
 701         <row>
 702          <entry>1/18/1999</entry>
 703          <entry>US; read as January 18 in any mode</entry>
 704         </row>
 705         <row>
 706          <entry>1999.008</entry>
 707          <entry>Year and day of year</entry>
 708         </row>
 709         <row>
 710          <entry>19990108</entry>
 711          <entry>ISO-8601 year, month, day</entry>
 712         </row>
 713         <row>
 714          <entry>990108</entry>
 715          <entry>ISO-8601 year, month, day</entry>
 716         </row>
 717         <row>
 718          <entry>1999.008</entry>
 719          <entry>Year and day of year</entry>
 720         </row>
 721         <row>
 722          <entry>99008</entry>
 723          <entry>Year and day of year</entry>
 724         </row>
 725         <row>
 726          <entry>January 8, 99 BC</entry>
 727          <entry>Year 99 before the common era</entry>
 728         </row>
 729        </tbody>
 730       </tgroup>
 731      </table>
 732     </para>
 733
 734     <para>
 735      <table tocentry="1">
 736       <title><productname>PostgreSQL</productname> Month Abbreviations</title>
 737       <titleabbrev>Month Abbreviations</titleabbrev>
 738       <tgroup cols="2">
 739        <thead>
 740         <row>
 741          <entry>Month</entry>
 742          <entry>Abbreviations</entry>
 743         </row>
 744        </thead>
 745        <tbody>
 746         <row>
 747          <entry>April</entry>
 748          <entry>Apr</entry>
 749         </row>
 750         <row>
 751          <entry>August</entry>
 752          <entry>Aug</entry>
 753         </row>
 754         <row>
 755          <entry>December</entry>
 756          <entry>Dec</entry>
 757         </row>
 758         <row>
 759          <entry>February</entry>
 760          <entry>Feb</entry>
 761         </row>
 762         <row>
 763          <entry>January</entry>
 764          <entry>Jan</entry>
 765         </row>
 766         <row>
 767          <entry>July</entry>
 768          <entry>Jul</entry>
 769         </row>
 770         <row>
 771          <entry>June</entry>
 772          <entry>Jun</entry>
 773         </row>
 774         <row>
 775          <entry>March</entry>
 776          <entry>Mar</entry>
 777         </row>
 778         <row>
 779          <entry>November</entry>
 780          <entry>Nov</entry>
 781         </row>
 782         <row>
 783          <entry>October</entry>
 784          <entry>Oct</entry>
 785         </row>
 786         <row>
 787          <entry>September</entry>
 788          <entry>Sep, Sept</entry>
 789         </row>
 790        </tbody>
 791       </tgroup>
 792      </table>
 793
 794      <note>
 795       <para>
 796        The month <literal>May</literal> has no explicit abbreviation, for obvious reasons.
 797       </para>
 798      </note>
 799     </para>
 800
 801     <para>
 802      <table tocentry="1">
 803       <title><productname>PostgreSQL</productname> Day of Week Abbreviations</title>
 804       <titleabbrev>Day of Week Abbreviations</titleabbrev>
 805       <tgroup cols="2">
 806        <thead>
 807         <row>
 808          <entry>Day</entry>
 809          <entry>Abbreviation</entry>
 810         </row>
 811        </thead>
 812        <tbody>
 813         <row>
 814          <entry>Sunday</entry>
 815          <entry>Sun</entry>
 816         </row>
 817         <row>
 818          <entry>Monday</entry>
 819          <entry>Mon</entry>
 820         </row>
 821         <row>
 822          <entry>Tuesday</entry>
 823          <entry>Tue, Tues</entry>
 824         </row>
 825         <row>
 826          <entry>Wednesday</entry>
 827          <entry>Wed, Weds</entry>
 828         </row>
 829         <row>
 830          <entry>Thursday</entry>
 831          <entry>Thu, Thur, Thurs</entry>
 832         </row>
 833         <row>
 834          <entry>Friday</entry>
 835          <entry>Fri</entry>
 836         </row>
 837         <row>
 838          <entry>Saturday</entry>
 839          <entry>Sat</entry>
 840         </row>
 841        </tbody>
 842       </tgroup>
 843      </table>
 844     </para>
 845     </sect3>
 846
 847     <sect3>
 848      <title>time</title>
 849      <para>
 850       The following are valid <type>time</type> inputs.
 851
 852       <table tocentry="1">
 853        <title><productname>PostgreSQL</productname> Time Input</title>
 854        <titleabbrev>Time Inputs</titleabbrev>
 855        <tgroup cols="2">
 856         <thead>
 857          <row>
 858           <entry>Example</entry>
 859           <entry>Description</entry>
 860          </row>
 861         </thead>
 862         <tbody>
 863          <row>
 864           <entry>04:05:06.789</entry>
 865           <entry>ISO-8601</entry>
 866          </row>
 867          <row>
 868           <entry>04:05:06</entry>
 869           <entry>ISO-8601</entry>
 870          </row>
 871          <row>
 872           <entry>04:05</entry>
 873           <entry>ISO-8601</entry>
 874          </row>
 875          <row>
 876           <entry>040506</entry>
 877           <entry>ISO-8601</entry>
 878          </row>
 879          <row>
 880           <entry>04:05 AM</entry>
 881           <entry>Same as 04:05; AM does not affect value</entry>
 882          </row>
 883          <row>
 884           <entry>04:05 PM</entry>
 885           <entry>Same as 16:05; input hour must be <= 12</entry>
 886          </row>
 887          <row>
 888           <entry>z</entry>
 889           <entry>Same as 00:00:00</entry>
 890          </row>
 891          <row>
 892           <entry>zulu</entry>
 893           <entry>Same as 00:00:00</entry>
 894          </row>
 895          <row>
 896           <entry>allballs</entry>
 897           <entry>Same as 00:00:00</entry>
 898          </row>
 899         </tbody>
 900        </tgroup>
 901       </table>
 902      </para>
 903     </sect3>
 904
 905     <sect3>
 906      <title>time with time zone</title>
 907      <para>
 908       This type is defined by SQL92, but the definition exhibits
 909       fundamental deficiencies which renders the type near useless. In
 910       most cases, a combination of <type>date</type>,
 911       <type>time</type>, and <type>timestamp with time zone</type>
 912       should provide a complete range of date/time functionality
 913       required by an application.
 914      </para>
 915
 916      <para>
 917       <type>time with time zone</type> accepts all input also legal
 918       for the <type>time</type> type, appended with a legal time zone,
 919       as follows:
 920
 921       <table tocentry="1">
 922        <title><productname>PostgreSQL</productname> Time With Time
 923         Zone Input</title>
 924        <titleabbrev>Time With Time Zone Inputs</titleabbrev>
 925        <tgroup cols="2">
 926        <thead>
 927         <row>
 928          <entry>Example</entry>
 929          <entry>Description</entry>
 930         </row>
 931        </thead>
 932        <tbody>
 933         <row>
 934          <entry>04:05:06.789-8</entry>
 935          <entry>ISO-8601</entry>
 936         </row>
 937         <row>
 938          <entry>04:05:06-08:00</entry>
 939          <entry>ISO-8601</entry>
 940         </row>
 941         <row>
 942          <entry>04:05-08:00</entry>
 943          <entry>ISO-8601</entry>
 944         </row>
 945         <row>
 946          <entry>040506-08</entry>
 947          <entry>ISO-8601</entry>
 948         </row>
 949        </tbody>
 950       </tgroup>
 951      </table>
 952     </para>
 953
 954      <para>
 955       Refer to <xref endterm="timezone-title" linkend="timezone"> for
 956       more examples of time zones.
 957      </para>
 958     </sect3>
 959
 960     <sect3>
 961     <title>timestamp</title>
 962     <para>
 963      Valid input for the <type>timestamp</type> type consists of a concatenation
 964      of a date and a time, followed by an optional <literal>AD</literal> or
 965      <literal>BC</literal>, followed by an optional time zone. (See below.)
 966      Thus
 967 <programlisting>
 968 1999-01-08 04:05:06 -8:00
 969 </programlisting>
 970      is a valid <type>timestamp</type> value, which is <acronym>ISO</acronym>-compliant.
 971      In addition, the wide-spread format
 972 <programlisting>
 973 January 8 04:05:06 1999 PST
 974 </programlisting>
 975      is supported.
 976     </para>
 977
 978     <para>
 979      <table tocentry="1" id="timezone">
 980       <title id="timezone-title"><productname>PostgreSQL</productname> Time Zone Input</title>
 981       <titleabbrev>Time Zone Inputs</titleabbrev>
 982       <tgroup cols="2">
 983        <thead>
 984         <row>
 985          <entry>Time Zone</entry>
 986          <entry>Description</entry>
 987         </row>
 988        </thead>
 989        <tbody>
 990         <row>
 991          <entry>PST</entry>
 992          <entry>Pacific Standard Time</entry>
 993         </row>
 994         <row>
 995          <entry>-8:00</entry>
 996          <entry>ISO-8601 offset for PST</entry>
 997         </row>
 998         <row>
 999          <entry>-800</entry>
1000          <entry>ISO-8601 offset for PST</entry>
1001         </row>
1002         <row>
1003          <entry>-8</entry>
1004          <entry>ISO-8601 offset for PST</entry>
1005         </row>
1006        </tbody>
1007       </tgroup>
1008      </table>
1009     </para>
1010     </sect3>
1011
1012     <sect3>
1013      <title>interval</title>
1014       <para>
1015        <type>interval</type>s can be specified with the following syntax:
1016 <programlisting>
1017   Quantity Unit [Quantity Unit...] [Direction]
1018 @ Quantity Unit [Direction]
1019 </programlisting>
1020        where: <literal>Quantity</literal> is ..., <literal>-1</literal>,
1021        <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...;
1022        <literal>Unit</literal> is <literal>second</literal>,
1023        <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
1024        <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1025        <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>,
1026        or abbreviations or plurals of these units;
1027        <literal>Direction</literal> can be <literal>ago</literal> or
1028        empty.
1029       </para>
1030      </sect3>
1031
1032     <sect3>
1033     <title>Special values</title>
1034     <para>
1035      The following <acronym>SQL</acronym>-compatible functions can be used as date or time
1036      input for the corresponding datatype: <literal>CURRENT_DATE</literal>,
1037      <literal>CURRENT_TIME</literal>, <literal>CURRENT_TIMESTAMP</literal>.
1038     </para>
1039     <para>
1040      <productname>PostgreSQL</productname> also supports several special constants for
1041      convenience.
1042
1043      <table tocentry="1">
1044       <title><productname>PostgresSQL</productname> Special Date/Time Constants</title>
1045       <titleabbrev>Constants</titleabbrev>
1046       <tgroup cols="2">
1047        <thead>
1048         <row>
1049          <entry>Constant</entry>
1050          <entry>Description</entry>
1051         </row>
1052        </thead>
1053        <tbody>
1054         <row>
1055          <entry>current</entry>
1056          <entry>Current transaction time, deferred</entry>
1057         </row>
1058         <row>
1059          <entry>epoch</entry>
1060          <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
1061         </row>
1062         <row>
1063          <entry>infinity</entry>
1064          <entry>Later than other valid times</entry>
1065         </row>
1066         <row>
1067          <entry>-infinity</entry>
1068          <entry>Earlier than other valid times</entry>
1069         </row>
1070         <row>
1071          <entry>invalid</entry>
1072          <entry>Illegal entry</entry>
1073         </row>
1074         <row>
1075          <entry>now</entry>
1076          <entry>Current transaction time</entry>
1077         </row>
1078         <row>
1079          <entry>today</entry>
1080          <entry>Midnight today</entry>
1081         </row>
1082         <row>
1083          <entry>tomorrow</entry>
1084          <entry>Midnight tomorrow</entry>
1085         </row>
1086         <row>
1087          <entry>yesterday</entry>
1088          <entry>Midnight yesterday</entry>
1089         </row>
1090        </tbody>
1091       </tgroup>
1092      </table>
1093      <literal>'now'</literal> is resolved when the value is inserted, <literal>'current'</literal>
1094      is resolved everytime the value is retrieved. So you probably want to use <literal>'now'</literal>
1095      in most applications. (Of course you <emphasis>really</emphasis> want to use
1096      <literal>CURRENT_TIMESTAMP</literal>, which is equivalent to <literal>'now'</literal>.)
1097     </para>
1098     </sect3>
1099
1100    </sect2>
1101
1102
1103    <sect2>
1104     <title>Date/Time Output</title>
1105
1106     <para>
1107      Output formats can be set to one of the four styles
1108      ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
1109      Postgres, and German, using the <command>SET DateStyle</command>.
1110      The default is the <acronym>ISO</acronym> format.
1111
1112      <table tocentry="1">
1113       <title><productname>PostgreSQL</productname> Date/Time Output Styles</title>
1114       <titleabbrev>Styles</titleabbrev>
1115       <tgroup cols="3">
1116        <thead>
1117         <row>
1118          <entry>Style Specification</entry>
1119          <entry>Description</entry>
1120          <entry>Example</entry>
1121         </row>
1122        </thead>
1123        <tbody>
1124         <row>
1125          <entry>'ISO'</entry>
1126          <entry>ISO-8601 standard</entry>
1127          <entry>1997-12-17 07:37:16-08</entry>
1128         </row>
1129         <row>
1130          <entry>'SQL'</entry>
1131          <entry>Traditional style</entry>
1132          <entry>12/17/1997 07:37:16.00 PST</entry>
1133         </row>
1134         <row>
1135          <entry>'Postgres'</entry>
1136          <entry>Original style</entry>
1137          <entry>Wed Dec 17 07:37:16 1997 PST</entry>
1138         </row>
1139         <row>
1140          <entry>'German'</entry>
1141          <entry>Regional style</entry>
1142          <entry>17.12.1997 07:37:16.00 PST</entry>
1143         </row>
1144        </tbody>
1145       </tgroup>
1146      </table>
1147     </para>
1148
1149     <para>
1150      The output of the <type>date</type> and <type>time</type> styles is of course
1151      only the date or time part in accordance with the above examples
1152     </para>
1153
1154     <para>
1155      The <acronym>SQL</acronym> style has European and non-European (US) variants,
1156      which determines whether month follows day or vica versa. (See also above
1157      at Date/Time Input, how this setting affects interpretation of input values.)
1158
1159      <table tocentry="1">
1160       <title><productname>PostgreSQL</productname> Date Order Conventions</title>
1161       <titleabbrev>Order</titleabbrev>
1162       <tgroup cols="3">
1163        <thead>
1164         <row>
1165          <entry>Style Specification</entry>
1166          <entry>Example</entry>
1167         </row>
1168        </thead>
1169        <tbody>
1170         <row>
1171          <entry>European</entry>
1172          <entry>17/12/1997 15:37:16.00 MET</entry>
1173         </row>
1174         <row>
1175          <entry>US</entry>
1176          <entry>12/17/1997 07:37:16.00 PST</entry>
1177         </row>
1178        </tbody>
1179       </tgroup>
1180      </table>
1181     </para>
1182
1183     <para>
1184     <type>interval</type> output looks like the input format, expect that units like
1185     <literal>week</literal> or <literal>century</literal> are converted to years and days.
1186     In ISO mode the output looks like
1187 <programlisting>
1188 [ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
1189 </programlisting>
1190     </para>
1191
1192     <para>
1193      There are several ways to affect the appearance of date/time types:
1194
1195      <itemizedlist spacing="compact" mark="bullet">
1196       <listitem>
1197        <para>
1198         The <envar>PGDATESTYLE</envar> environment variable used by the backend directly
1199         on postmaster startup.
1200        </para>
1201       </listitem>
1202       <listitem>
1203        <para>
1204         The <envar>PGDATESTYLE</envar> environment variable used by the frontend libpq
1205         on session startup.
1206        </para>
1207       </listitem>
1208       <listitem>
1209        <para>
1210         <command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
1211        </para>
1212       </listitem>
1213      </itemizedlist>
1214     </para>
1215
1216    </sect2>
1217
1218    <sect2>
1219     <title>Time Zones</title>
1220
1221     <para>
1222      <productname>PostgreSQL</productname> endeavors to be compatible with
1223      <acronym>SQL92</acronym> definitions for typical usage.
1224      However, the <acronym>SQL92</acronym> standard has an odd mix of date and
1225      time types and capabilities. Two obvious problems are:
1226
1227      <itemizedlist>
1228       <listitem>
1229        <para>
1230         Although the <type>date</type> type
1231         does not have an associated time zone, the
1232         <type>time</type> type can or does.
1233        </para>
1234       </listitem>
1235
1236       <listitem>
1237        <para>
1238         The default time zone is specified as a constant integer offset
1239         from GMT/UTC.
1240        </para>
1241       </listitem>
1242
1243      </itemizedlist>
1244
1245      Time zones in the real world can have no meaning unless
1246      associated with a date as well as a time
1247      since the offset may vary through the year with daylight savings
1248      time boundaries.
1249     </para>
1250
1251     <para>
1252      To address these difficulties, <productname>PostgreSQL</productname>
1253      associates time zones only with date and time
1254      types which contain both date and time,
1255      and assumes local time for any type containing only
1256      date or time. Further, time zone support is derived from
1257      the underlying operating system
1258      time zone capabilities, and hence can handle daylight savings time
1259      and other expected behavior.
1260     </para>
1261
1262     <para>
1263      <productname>PostgreSQL</productname> obtains time zone support
1264      from the underlying operating system for dates between 1902 and
1265      2038 (near the typical date limits for Unix-style
1266      systems). Outside of this range, all dates are assumed to be
1267      specified and used in Universal Coordinated Time (UTC).
1268     </para>
1269
1270     <para>
1271      All dates and times are stored internally in Universal UTC,
1272      alternately known as Greenwich Mean Time (GMT).
1273      Times are converted to local time on the database server before being
1274      sent to the client frontend, hence by default are in the server
1275      time zone.
1276     </para>
1277
1278     <para>
1279      There are several ways to affect the time zone behavior:
1280
1281      <itemizedlist spacing="compact" mark="bullet">
1282       <listitem>
1283        <para>
1284         The TZ environment variable used by the backend directly
1285         on postmaster startup as the default time zone.
1286        </para>
1287       </listitem>
1288       <listitem>
1289        <para>
1290         The PGTZ environment variable set at the client used by libpq
1291         to send time zone information to the backend upon connection.
1292        </para>
1293       </listitem>
1294       <listitem>
1295        <para>
1296         The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
1297         sets the time zone for the session.
1298        </para>
1299       </listitem>
1300      </itemizedlist>
1301     </para>
1302
1303     <para>
1304      If an invalid time zone is specified,
1305      the time zone becomes GMT (on most systems anyway).
1306     </para>
1307
1308     <para>
1309
1310      <note>
1311       <para>
1312        If the compiler option USE_AUSTRALIAN_RULES is set
1313        then <literal>EST</literal> refers to Australia Eastern Std Time,
1314        which has an offset of +10:00 hours from UTC.
1315       </para>
1316      </note>
1317     </para>
1318
1319    </sect2>
1320
1321    <sect2>
1322     <title>Internals</title>
1323
1324     <para>
1325      <productname>PostgreSQL</productname> uses Julian dates
1326      for all date/time calculations. They have the nice property of correctly
1327      predicting/calculating any date more recent than 4713BC
1328      to far into the future, using the assumption that the length of the
1329      year is 365.2425 days.
1330     </para>
1331
1332     <para>
1333      Date conventions before the 19th century make for interesting reading,
1334      but are not consistant enough to warrant coding into a date/time handler.
1335     </para>
1336    </sect2>
1337
1338   </sect1>
1339
1340   <sect1>
1341    <title>Boolean Type</title>
1342
1343    <para>
1344     <productname>Postgres</productname> supports <type>bool</type> as
1345     the <acronym>SQL3</acronym> boolean type.
1346     <type>bool</type> can have one of only two states: 'true' or 'false'.
1347     A third state, 'unknown', is not
1348     implemented and is not suggested in <acronym>SQL3</acronym>;
1349     <acronym>NULL</acronym> is an
1350     effective substitute. <type>bool</type> can be used in any boolean expression,
1351     and boolean expressions
1352     always evaluate to a result compatible with this type.</para>
1353
1354    <para>
1355     <type>bool</type> uses 1 byte of storage.
1356    </para>
1357
1358    <para>
1359     <table tocentry="1">
1360      <title><productname>Postgres</productname> Boolean Type</title>
1361      <titleabbrev>Booleans</titleabbrev>
1362      <tgroup cols="3">
1363       <thead>
1364        <row>
1365         <entry>State</entry>
1366         <entry>Output</entry>
1367         <entry>Input</entry>
1368        </row>
1369       </thead>
1370       <tbody>
1371        <row>
1372         <entry>True</entry>
1373         <entry>'t'</entry>
1374         <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
1375        </row>
1376        <row>
1377         <entry>False</entry>
1378         <entry>'f'</entry>
1379         <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
1380        </row>
1381       </tbody>
1382      </tgroup>
1383     </table>
1384    </para>
1385   </sect1>
1386
1387   <sect1>
1388    <title>Geometric Types</title>
1389
1390    <para>
1391     Geometric types represent two-dimensional spatial objects.
1392     The most fundamental type,
1393     the point, forms the basis for all of the other types.
1394    </para>
1395
1396    <para>
1397     <table tocentry="1">
1398      <title><productname>Postgres</productname> Geometric Types</title>
1399      <titleabbrev>Geometrics</titleabbrev>
1400      <tgroup cols="4">
1401       <thead>
1402        <row>
1403         <entry>Geometric Type</entry>
1404         <entry>Storage</entry>
1405         <entry>Representation</entry>
1406         <entry>Description</entry>
1407        </row>
1408       </thead>
1409       <tbody>
1410        <row>
1411         <entry>point</entry>
1412         <entry>16 bytes</entry>
1413         <entry>(x,y)</entry>
1414         <entry>Point in space</entry>
1415        </row>
1416        <row>
1417         <entry>line</entry>
1418         <entry>32 bytes</entry>
1419         <entry>((x1,y1),(x2,y2))</entry>
1420         <entry>Infinite line</entry>
1421        </row>
1422        <row>
1423         <entry>lseg</entry>
1424         <entry>32 bytes</entry>
1425         <entry>((x1,y1),(x2,y2))</entry>
1426         <entry>Finite line segment</entry>
1427        </row>
1428        <row>
1429         <entry>box</entry>
1430         <entry>32 bytes</entry>
1431         <entry>((x1,y1),(x2,y2))</entry>
1432         <entry>Rectangular box</entry>
1433        </row>
1434        <row>
1435         <entry>path</entry>
1436         <entry>4+32n bytes</entry>
1437         <entry>((x1,y1),...)</entry>
1438         <entry>Closed path (similar to polygon)</entry>
1439        </row>
1440        <row>
1441         <entry>path</entry>
1442         <entry>4+32n bytes</entry>
1443         <entry>[(x1,y1),...]</entry>
1444         <entry>Open path</entry>
1445        </row>
1446        <row>
1447         <entry>polygon</entry>
1448         <entry>4+32n bytes</entry>
1449         <entry>((x1,y1),...)</entry>
1450         <entry>Polygon (similar to closed path)</entry>
1451        </row>
1452        <row>
1453         <entry>circle</entry>
1454         <entry>24 bytes</entry>
1455         <entry><(x,y),r></entry>
1456         <entry>Circle (center and radius)</entry>
1457        </row>
1458       </tbody>
1459      </tgroup>
1460     </table>
1461    </para>
1462
1463    <para>
1464     A rich set of functions and operators is available to perform various geometric
1465     operations such as scaling, translation, rotation, and determining
1466     intersections.
1467    </para>
1468
1469    <sect2>
1470     <title>Point</title>
1471
1472     <para>
1473      Points are the fundamental two-dimensional building block for geometric types.
1474     </para>
1475
1476     <para>
1477      <type>point</type> is specified using the following syntax:
1478
1479      <programlisting>
1480 ( x , y )
1481   x , y
1482 where
1483     x is the x-axis coordinate as a floating point number
1484     y is the y-axis coordinate as a floating point number
1485      </programlisting>
1486     </para>
1487    </sect2>
1488
1489    <sect2>
1490     <title>Line Segment</title>
1491
1492     <para>
1493      Line segments (<type>lseg</type>) are represented by pairs of points.
1494     </para>
1495
1496     <para>
1497      <type>lseg</type> is specified using the following syntax:
1498      <programlisting>
1499 ( ( x1 , y1 ) , ( x2 , y2 ) )
1500   ( x1 , y1 ) , ( x2 , y2 )
1501     x1 , y1   ,   x2 , y2
1502 where
1503     (x1,y1) and (x2,y2) are the endpoints of the segment
1504      </programlisting>
1505     </para>
1506    </sect2>
1507
1508    <sect2>
1509     <title>Box</title>
1510
1511     <para>
1512      Boxes are represented by pairs of points which are opposite
1513      corners of the box.
1514     </para>
1515
1516     <para>
1517      <type>box</type> is specified using the following syntax:
1518
1519      <programlisting>
1520 ( ( x1 , y1 ) , ( x2 , y2 ) )
1521   ( x1 , y1 ) , ( x2 , y2 )
1522     x1 , y1   ,   x2 , y2
1523 where
1524     (x1,y1) and (x2,y2) are opposite corners
1525      </programlisting>
1526
1527      Boxes are output using the first syntax.
1528      The corners are reordered on input to store
1529      the lower left corner first and the upper right corner last.
1530      Other corners of the box can be entered, but the lower
1531      left and upper right corners are determined from the input and stored.
1532     </para>
1533    </sect2>
1534
1535    <sect2>
1536     <title>Path</title>
1537
1538     <para>
1539      Paths are represented by connected sets of points. Paths can be "open", where
1540      the first and last points in the set are not connected, and "closed",
1541      where the first and last point are connected. Functions
1542      <function>popen(p)</function>
1543      and
1544      <function>pclose(p)</function>
1545      are supplied to force a path to be open or closed, and functions
1546      <function>isopen(p)</function>
1547      and
1548      <function>isclosed(p)</function>
1549      are supplied to select either type in a query.
1550     </para>
1551
1552     <para>
1553      <type>path</type> is specified using the following syntax:
1554
1555      <programlisting>
1556 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1557 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
1558   ( x1 , y1 ) , ... , ( xn , yn )
1559   ( x1 , y1   , ... ,   xn , yn )
1560     x1 , y1   , ... ,   xn , yn
1561 where
1562     (x1,y1),...,(xn,yn) are points 1 through n
1563     a leading "[" indicates an open path
1564     a leading "(" indicates a closed path
1565      </programlisting>
1566
1567      Paths are output using the first syntax.
1568      Note that <productname>Postgres</productname> versions prior to
1569      v6.1 used a format for paths which had a single leading parenthesis,
1570      a "closed" flag,
1571      an integer count of the number of points, then the list of points followed by a
1572      closing parenthesis.
1573      The built-in function <function>upgradepath</function> is supplied to convert
1574      paths dumped and reloaded from pre-v6.1 databases.
1575     </para>
1576    </sect2>
1577
1578    <sect2>
1579     <title>Polygon</title>
1580
1581     <para>
1582      Polygons are represented by sets of points. Polygons should probably be
1583      considered equivalent to closed paths, but are stored differently
1584      and have their own set of support routines.
1585     </para>
1586
1587     <para>
1588      <type>polygon</type> is specified using the following syntax:
1589
1590      <programlisting>
1591 ( ( x1 , y1 ) , ... , ( xn , yn ) )
1592   ( x1 , y1 ) , ... , ( xn , yn )
1593   ( x1 , y1   , ... ,   xn , yn )
1594     x1 , y1   , ... ,   xn , yn
1595 where
1596     (x1,y1),...,(xn,yn) are points 1 through n
1597      </programlisting>
1598
1599      Polygons are output using the first syntax.
1600      Note that <productname>Postgres</productname> versions prior to
1601      v6.1 used a format for polygons which had a single leading parenthesis, the list
1602      of x-axis coordinates, the list of y-axis coordinates,
1603      followed by a closing parenthesis.
1604      The built-in function <function>upgradepoly</function> is supplied to convert
1605      polygons dumped and reloaded from pre-v6.1 databases.
1606     </para>
1607    </sect2>
1608
1609    <sect2>
1610     <title>Circle</title>
1611
1612     <para>
1613      Circles are represented by a center point and a radius.
1614     </para>
1615
1616     <para>
1617      <type>circle</type> is specified using the following syntax:
1618
1619      <programlisting>
1620 < ( x , y ) , r >
1621 ( ( x , y ) , r )
1622   ( x , y ) , r
1623     x , y   , r
1624 where
1625     (x,y) is the center of the circle
1626     r is the radius of the circle
1627      </programlisting>
1628
1629      Circles are output using the first syntax.
1630     </para>
1631    </sect2>
1632
1633   </sect1>
1634
1635   <sect1>
1636    <title>IP Version 4 Networks and Host Addresses</title>
1637
1638    <para>
1639     The <type>cidr</type> type stores networks specified
1640     in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
1641     The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
1642     variation in representation to represent simple host TCP/IP addresses.
1643    </para>
1644
1645    <para>
1646     <table tocentry="1">
1647      <title><productname>Postgres</productname>IP Version 4 Types</title>
1648      <titleabbrev>IPV4</titleabbrev>
1649      <tgroup cols="4">
1650       <thead>
1651        <row>
1652         <entry>IPV4 Type</entry>
1653         <entry>Storage</entry>
1654         <entry>Description</entry>
1655         <entry>Range</entry>
1656        </row>
1657       </thead>
1658       <tbody>
1659        <row>
1660         <entry>cidr</entry>
1661         <entry>variable</entry>
1662         <entry>CIDR networks</entry>
1663         <entry>Valid IPV4 CIDR blocks</entry>
1664        </row>
1665        <row>
1666         <entry>inet</entry>
1667         <entry>variable</entry>
1668         <entry>nets and hosts</entry>
1669         <entry>Valid IPV4 CIDR blocks</entry>
1670        </row>
1671       </tbody>
1672      </tgroup>
1673     </table>
1674    </para>
1675
1676    <sect2>
1677     <title>CIDR</title>
1678
1679     <para>
1680      The <type>cidr</type> type holds a CIDR network.
1681      The format for specifying classless networks is
1682      <replaceable class="parameter">x.x.x.x/y</replaceable>
1683      where <replaceable class="parameter">x.x.x.x</replaceable> is the
1684      network and <replaceable class="parameter">/y</replaceable> is
1685      the number of bits in the netmask.
1686      If <replaceable class="parameter">/y</replaceable> omitted, it is
1687      calculated using assumptions from
1688      the older classfull naming system except that it is extended to include at least
1689      all of the octets in the input.
1690     </para>
1691
1692     <para>
1693      Here are some examples:
1694
1695      <table tocentry="1">
1696       <title><productname>Postgres</productname>IP Types Examples</title>
1697       <tgroup cols="2">
1698        <thead>
1699         <row>
1700          <entry>CIDR Input</entry>
1701          <entry>CIDR Displayed</entry>
1702         </row>
1703        </thead>
1704        <tbody>
1705         <row>
1706          <entry>192.168.1</entry>
1707          <entry>192.168.1/24</entry>
1708         </row>
1709         <row>
1710          <entry>192.168</entry>
1711          <entry>192.168.0/24</entry>
1712         </row>
1713         <row>
1714          <entry>128.1</entry>
1715          <entry>128.1/16</entry>
1716         </row>
1717         <row>
1718          <entry>128</entry>
1719          <entry>128.0/16</entry>
1720         </row>
1721         <row>
1722          <entry>128.1.2</entry>
1723          <entry>128.1.2/24</entry>
1724         </row>
1725         <row>
1726          <entry>10.1.2</entry>
1727          <entry>10.1.2/24</entry>
1728         </row>
1729         <row>
1730          <entry>10.1</entry>
1731          <entry>10.1/16</entry>
1732         </row>
1733         <row>
1734          <entry>10</entry>
1735          <entry>10/8</entry>
1736         </row>
1737        </tbody>
1738       </tgroup>
1739      </table>
1740     </para>
1741    </sect2>
1742
1743    <sect2>
1744     <title id="inet-type"><type>inet</type></title>
1745
1746     <para>
1747      The <type>inet</type> type is designed to hold, in one field, all of the information
1748      about a host including the CIDR-style subnet that it is in.
1749      Note that if you want to store proper CIDR networks,
1750      you should use the <type>cidr</type> type.
1751      The <type>inet</type> type is similar to the <type>cidr</type>
1752      type except that the bits in the
1753      host part can be non-zero.
1754      Functions exist to extract the various elements of the field.
1755     </para>
1756
1757     <para>
1758      The input format for this function is
1759      <replaceable class="parameter">x.x.x.x/y</replaceable>
1760      where <replaceable class="parameter">x.x.x.x</replaceable> is
1761      an internet host and <replaceable class="parameter">y</replaceable>
1762      is the number of bits in the netmask.
1763      If the <replaceable class="parameter">/y</replaceable> part is left off,
1764      it is treated as <literal>/32</literal>.
1765      On output, the <replaceable class="parameter">/y</replaceable> part is not printed
1766      if it is <literal>/32</literal>.
1767      This allows the type to be used as a straight host type by just leaving off
1768      the bits part.
1769     </para>
1770    </sect2>
1771   </sect1>
1772
1773  </chapter>
1774
1775 <!-- Keep this comment at the end of the file
1776 Local variables:
1777 mode:sgml
1778 sgml-omittag:nil
1779 sgml-shorttag:t
1780 sgml-minimize-attributes:nil
1781 sgml-always-quote-attributes:t
1782 sgml-indent-step:1
1783 sgml-indent-data:t
1784 sgml-parent-document:nil
1785 sgml-default-dtd-file:"./reference.ced"
1786 sgml-exposed-tags:nil
1787 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1788 sgml-local-ecat-files:nil
1789 End:
1790 -->