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

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