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

   1 <chapter id="datatype">
   2 <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>
  16 command described elsewhere.
  17 </para>
  18
  19 <para>
  20 In the context of data types, the following sections will discuss
  21 <acronym>SQL</acronym> standards compliance, porting issues, and usage.
  22
  23 Some <productname>Postgres</productname> types correspond directly to
  24 <acronym>SQL92</acronym>-compatible types. In other
  25 cases, data types defined by <acronym>SQL92</acronym> syntax are mapped directly
  26 into native <productname>Postgres</productname> types.
  27
  28 Many of the built-in types have obvious external formats. However, several
  29 types are either unique to <productname>Postgres</productname>,
  30 such as open and closed paths, or have
  31 several possibilities for formats, such as the date and time types.
  32 </para>
  33
  34 <para>
  35 <table tocentry="1">
  36 <title><productname>Postgres</productname> Data Types</title>
  37 <titleabbrev>Data Types</titleabbrev>
  38 <tgroup cols="3">
  39 <thead>
  40   <row>
  41     <entry><productname>Postgres</productname> Type</entry>
  42     <entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>
  43     <entry>Description</entry>
  44   </row>
  45 </thead>
  46 <tbody>
  47   <row>
  48     <entry>bool</entry>
  49     <entry>boolean</entry>
  50     <entry>logical boolean (true/false)</entry>
  51   </row>
  52   <row>
  53     <entry>box</entry>
  54     <entry></entry>
  55     <entry>rectangular box in 2D plane</entry>
  56   </row>
  57   <row>
  58     <entry>char(n)</entry>
  59     <entry>character(n)</entry>
  60     <entry>fixed-length character string</entry>
  61   </row>
  62   <row>
  63     <entry>cidr</entry>
  64     <entry></entry>
  65     <entry>IP version 4 network or host address</entry>
  66   </row>
  67   <row>
  68     <entry>circle</entry>
  69     <entry></entry>
  70     <entry>circle in 2D plane</entry>
  71   </row>
  72   <row>
  73     <entry>date</entry>
  74     <entry>date</entry>
  75     <entry>calendar date without time of day</entry>
  76   </row>
  77   <row>
  78     <entry>float4/8</entry>
  79     <entry>float(p)</entry>
  80     <entry>floating-point number with precision p</entry>
  81   </row>
  82   <row>
  83     <entry>float8</entry>
  84     <entry>real, double precision</entry>
  85     <entry>double-precision floating-point number</entry>
  86   </row>
  87   <row>
  88     <entry>inet</entry>
  89     <entry></entry>
  90     <entry>IP version 4 network or host address</entry>
  91   </row>
  92   <row>
  93     <entry>int2</entry>
  94     <entry>smallint</entry>
  95     <entry>signed two-byte integer</entry>
  96   </row>
  97   <row>
  98     <entry>int4</entry>
  99     <entry>int, integer</entry>
 100     <entry>signed 4-byte integer</entry>
 101   </row>
 102   <row>
 103     <entry>int4</entry>
 104     <entry>decimal(p,s)</entry>
 105     <entry>exact numeric for p <= 9, s = 0</entry>
 106   </row>
 107   <row>
 108     <entry>int4</entry>
 109     <entry>numeric(p,s)</entry>
 110     <entry>exact numeric for p == 9, s = 0</entry>
 111   </row>
 112   <row>
 113     <entry>int8</entry>
 114     <entry></entry>
 115     <entry>signed 8-byte integer</entry>
 116   </row>
 117   <row>
 118     <entry>line</entry>
 119     <entry></entry>
 120     <entry>infinite line in 2D plane</entry>
 121   </row>
 122   <row>
 123     <entry>lseg</entry>
 124     <entry></entry>
 125     <entry>line segment in 2D plane</entry>
 126   </row>
 127   <row>
 128     <entry>money</entry>
 129     <entry>decimal(9,2)</entry>
 130     <entry>US-style currency</entry>
 131   </row>
 132   <row>
 133     <entry>path</entry>
 134     <entry></entry>
 135     <entry>open and closed geometric path in 2D plane</entry>
 136   </row>
 137   <row>
 138     <entry>point</entry>
 139     <entry></entry>
 140     <entry>geometric point in 2D plane</entry>
 141   </row>
 142   <row>
 143     <entry>polygon</entry>
 144     <entry></entry>
 145     <entry>closed geometric path in 2D plane</entry>
 146   </row>
 147   <row>
 148     <entry>serial</entry>
 149     <entry></entry>
 150     <entry>unique id for indexing and cross-reference</entry>
 151   </row>
 152   <row>
 153     <entry>time</entry>
 154     <entry>time</entry>
 155     <entry>time of day</entry>
 156   </row>
 157   <row>
 158     <entry>timespan</entry>
 159     <entry>interval</entry>
 160     <entry>general-use time span</entry>
 161   </row>
 162   <row>
 163     <entry>timestamp</entry>
 164     <entry>timestamp with time zone</entry>
 165     <entry>date/time</entry>
 166   </row>
 167   <row>
 168     <entry>varchar(n)</entry>
 169     <entry>character varying(n)</entry>
 170     <entry>variable-length character string</entry>
 171   </row>
 172 </tbody>
 173 </tgroup>
 174 </table>
 175 </para>
 176
 177 <para>
 178 <note>
 179 <para>
 180 The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type
 181 but only ipv4 is handled in the current implementation.
 182 Everything here that talks about ipv4 will apply to ipv6 in a future release.</para>
 183 </note></para>
 184
 185 <para>
 186 <table tocentry="1">
 187 <title><productname>Postgres</productname> Function Constants</title>
 188 <titleabbrev>Constants</titleabbrev>
 189 <tgroup cols="3">
 190 <thead>
 191   <row>
 192     <entry><productname>Postgres</productname> Function</entry>
 193     <entry><acronym>SQL92</acronym> Constant</entry>
 194     <entry>Description</entry>
 195   </row>
 196 </thead>
 197 <tbody>
 198   <row>
 199     <entry>getpgusername()</entry>
 200     <entry>current_user</entry>
 201     <entry>user name in current session</entry>
 202   </row>
 203   <row>
 204     <entry>date('now')</entry>
 205     <entry>current_date</entry>
 206     <entry>date of current transaction</entry>
 207   </row>
 208   <row>
 209     <entry>time('now')</entry>
 210     <entry>current_time</entry>
 211     <entry>time of current transaction</entry>
 212   </row>
 213   <row>
 214     <entry>timestamp('now')</entry>
 215     <entry>current_timestamp</entry>
 216     <entry>date and time of current transaction</entry>
 217   </row>
 218 </tbody>
 219 </tgroup>
 220 </table>
 221 </para>
 222
 223 <para>
 224 <productname>Postgres</productname> has features at the forefront of
 225 <acronym>ORDBMS</acronym> development. In addition to
 226 <acronym>SQL3</acronym> conformance, substantial portions
 227 of <acronym>SQL92</acronym> are also supported.
 228 Although we strive for <acronym>SQL92</acronym> compliance,
 229 there are some aspects of the standard
 230 which are ill considered and which should not live through subsequent standards.
 231 <productname>Postgres</productname> will not make great efforts to
 232 conform to these features; however, these tend to apply in little-used
 233 or obsure cases, and a typical user is not likely to run into them.</para>
 234
 235 <para>
 236 Most of the input and output functions corresponding to the
 237 base types (e.g., integers and floating point numbers) do some
 238 error-checking.
 239 Some of the operators and functions (e.g.,
 240 addition and multiplication) do not perform run-time error-checking in the
 241 interests of improving execution speed.
 242 On some systems, for example, the numeric operators for some data types may
 243 silently underflow or overflow.
 244 </para>
 245
 246 <para>
 247 Note that some of the input and output functions are not invertible.  That is,
 248 the result of an output function may lose precision when compared to
 249 the original input.
 250
 251 <note>
 252 <para>
 253 The original <productname>Postgres</productname> v4.2 code received from
 254 Berkeley rounded all double precision floating point results to six digits for
 255 output. Starting with v6.1, floating point numbers are allowed to retain
 256 most of the intrinsic precision of the type (typically 15 digits for doubles,
 257 6 digits for 4-byte floats).
 258 Other types with underlying floating point fields (e.g. geometric
 259 types) carry similar precision.</para>
 260 </note>
 261 </para>
 262
 263 <sect1>
 264 <title>Numeric Types</title>
 265
 266 <para>
 267 Numeric types consist of two- and four-byte integers and four- and eight-byte
 268 floating point numbers.</para>
 269
 270 <para>
 271 <table tocentry="1">
 272 <title><productname>Postgres</productname> Numeric Types</title>
 273 <titleabbrev>Numerics</titleabbrev>
 274 <tgroup cols="4">
 275 <thead>
 276   <row>
 277     <entry>Numeric Type</entry>
 278     <entry>Storage</entry>
 279     <entry>Description</entry>
 280     <entry>Range</entry>
 281   </row>
 282 </thead>
 283 <tbody>
 284   <row>
 285     <entry>float4</entry>
 286     <entry>4 bytes</entry>
 287     <entry>Variable-precision</entry>
 288     <entry>6 decimal places</entry>
 289   </row>
 290   <row>
 291     <entry>float8</entry>
 292     <entry>8 bytes</entry>
 293     <entry>Variable-precision</entry>
 294     <entry>15 decimal places</entry>
 295   </row>
 296   <row>
 297     <entry>int2</entry>
 298     <entry>2 bytes</entry>
 299     <entry>Fixed-precision</entry>
 300     <entry>-32768 to +32767</entry>
 301   </row>
 302   <row>
 303     <entry>int4</entry>
 304     <entry>4 bytes</entry>
 305     <entry>Usual choice for fixed-precision</entry>
 306     <entry>-2147483648 to +2147483647</entry>
 307   </row>
 308   <row>
 309     <entry>int8</entry>
 310     <entry>8 bytes</entry>
 311     <entry>Very large range fixed-precision</entry>
 312     <entry>+/- &gt; 18 decimal places</entry>
 313   </row>
 314   <row>
 315     <entry>serial</entry>
 316     <entry>4 bytes</entry>
 317     <entry>Identifer or cross-reference</entry>
 318     <entry>0 to +2147483647</entry>
 319   </row>
 320 </tbody>
 321 </tgroup>
 322 </table>
 323 </para>
 324
 325 <para>
 326 The numeric types have a full set of corresponding arithmetic operators and
 327 functions. Refer to <xref endterm="math-opers" linkend="math-opers">
 328 and <xref endterm="math-funcs" linkend="math-funcs"> for more information.
 329    </para>
 330
 331 <para>
 332 The <type>serial</type> type is a special-case type constructed by
 333 <productname>Postgres</productname> from other existing components.
 334 It is typically used to create unique identifiers for table entries.
 335 In the current implementation, specifying
 336
 337 <programlisting>
 338 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 339 </programlisting>
 340
 341 is equivalent to specifying:
 342
 343 <programlisting>
 344 CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq;
 345 CREATE TABLE <replaceable class="parameter">tablename</replaceable>
 346     (<replaceable class="parameter">colname</replaceable> INT4 DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq');
 347 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>);
 348 </programlisting>
 349
 350 <caution>
 351 <para>
 352 The implicit sequence created for the <type>serial</type> type will
 353 <emphasis>not</emphasis> be automatically removed when the table is dropped.
 354 So, the following commands executed in order will likely fail:
 355
 356 <programlisting>
 357 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 358 DROP TABLE <replaceable class="parameter">tablename</replaceable>;
 359 CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceable class="parameter">colname</replaceable> SERIAL);
 360 </programlisting>
 361
 362 The sequence will remain in the database until explicitly dropped using
 363 <command>DROP SEQUENCE</command>.</para>
 364 </caution>
 365    </para>
 366
 367 <para>
 368 The <firstterm>exact numerics</firstterm> <type>decimal</type> and
 369 <type>numeric</type>
 370 have fully implemented syntax but currently
 371 (<productname>Postgres</productname> v6.4)
 372  support only a small range of precision and/or range values.
 373 The <type>int8</type> type may not be available on all platforms since
 374 it relies on compiler support for this.
 375 </para>
 376
 377 </sect1>
 378
 379 <sect1>
 380 <title>Monetary Type</title>
 381
 382 <para>
 383 The <type>money</type> type supports US-style currency with
 384 fixed decimal point representation.
 385 If <productname>Postgres</productname> is compiled with USE_LOCALE
 386 then the money type should use the monetary conventions defined for
 387 <citetitle>locale(7)</citetitle>.
 388    </para>
 389
 390 <para>
 391 <table tocentry="1">
 392 <title><productname>Postgres</productname> Monetary Types</title>
 393 <titleabbrev>Money</titleabbrev>
 394 <tgroup cols="4">
 395 <thead>
 396   <row>
 397     <entry>Monetary Type</entry>
 398     <entry>Storage</entry>
 399     <entry>Description</entry>
 400     <entry>Range</entry>
 401   </row>
 402 </thead>
 403 <tbody>
 404   <row>
 405     <entry>money</entry>
 406     <entry>4 bytes</entry>
 407     <entry>Fixed-precision</entry>
 408     <entry>-21474836.48 to +21474836.47</entry>
 409   </row>
 410 </tbody>
 411 </tgroup>
 412 </table>
 413 </para>
 414
 415 <para>
 416 <type>numeric</type>
 417 should eventually replace the money type. It has a
 418 fully implemented syntax but currently
 419 (<productname>Postgres</productname> v6.4)
 420  support only a small range of precision and/or range values
 421 and cannot adequately substitute for the money type.
 422 </para>
 423
 424 </sect1>
 425
 426 <sect1>
 427 <title>Character Types</title>
 428
 429 <para>
 430 <acronym>SQL92</acronym> defines two primary character types:
 431  <type>char</type> and <type>varchar</type>.
 432 <productname>Postgres</productname> supports these types, in
 433 addition to the more general <type>text</type> type,
 434 which unlike <type>varchar</type>
 435 does not require an upper
 436 limit to be declared on the size of the field.
 437 </para>
 438
 439 <para>
 440 <table tocentry="1">
 441 <title><productname>Postgres</productname> Character Types</title>
 442 <titleabbrev>Characters</titleabbrev>
 443 <tgroup cols="4">
 444 <thead>
 445   <row>
 446     <entry>Character Type</entry>
 447     <entry>Storage</entry>
 448     <entry>Recommendation</entry>
 449     <entry>Description</entry>
 450   </row>
 451 </thead>
 452 <tbody>
 453   <row>
 454     <entry>char</entry>
 455     <entry>1 byte</entry>
 456     <entry><acronym>SQL92</acronym>-compatible</entry>
 457     <entry>Single character</entry>
 458   </row>
 459   <row>
 460     <entry>char(n)</entry>
 461     <entry>(4+n) bytes</entry>
 462     <entry><acronym>SQL92</acronym>-compatible</entry>
 463     <entry>Fixed-length blank padded</entry>
 464   </row>
 465   <row>
 466     <entry>text</entry>
 467     <entry>(4+x) bytes</entry>
 468     <entry>Best choice</entry>
 469     <entry>Variable-length</entry>
 470   </row>
 471   <row>
 472     <entry>varchar(n)</entry>
 473     <entry>(4+n) bytes</entry>
 474     <entry><acronym>SQL92</acronym>-compatible</entry>
 475     <entry>Variable-length with limit</entry>
 476   </row>
 477 </tbody>
 478 </tgroup>
 479 </table>
 480 </para>
 481
 482 <para>
 483 There is one other fixed-length character type.
 484 The <type>name</type> type
 485 only has one purpose and that is to provide
 486 <productname>Postgres</productname> with a
 487 special type to use for internal names.
 488 It is not intended for use by the general user.
 489 It's length is currently defined as 32 chars
 490 but should be reference using NAMEDATALEN.
 491 This is set at compile time and may change in a future release.
 492 </para>
 493
 494 <para>
 495 <table tocentry="1">
 496 <title><productname>Postgres</productname> Specialty Character Type</title>
 497 <titleabbrev>Specialty Characters</titleabbrev>
 498 <tgroup cols="3">
 499 <thead>
 500   <row>
 501     <entry>Character Type</entry>
 502     <entry>Storage</entry>
 503     <entry>Description</entry>
 504   </row>
 505 </thead>
 506 <tbody>
 507   <row>
 508     <entry>name</entry>
 509     <entry>32 bytes</entry>
 510     <entry>Thirty-two character internal type</entry>
 511   </row>
 512 </tbody>
 513 </tgroup>
 514 </table>
 515 </para>
 516
 517 </sect1>
 518
 519 <sect1>
 520 <title>Date/Time Types</title>
 521
 522 <para>
 523 There are two fundamental kinds of date and time measurements:
 524  absolute clock times and relative time intervals.
 525 Both kinds of quantities should have behaviors demonstrating both
 526 continuity and smoothness.
 527 <productname>Postgres</productname> supplies two primary user-oriented
 528 date and time types,
 529 <type>datetime</type> and <type>timespan</type>, as well as
 530 the related <acronym>SQL92</acronym> types <type>timestamp</type>,
 531 <type>interval</type>,
 532 <type>date</type> and <type>time</type>.
 533 </para>
 534
 535 <para>
 536 In a future release, <type>datetime</type> and <type>timespan</type> are likely
 537 to merge with the <acronym>SQL92</acronym> types <type>timestamp</type>,
 538 <type>interval</type>.
 539 Other date and time types are also available, mostly
 540 for historical reasons.
 541 </para>
 542
 543 <para>
 544 <table tocentry="1">
 545 <title><productname>Postgres</productname> Date/Time Types</title>
 546 <titleabbrev>Date/Time</titleabbrev>
 547 <tgroup cols="4">
 548 <thead>
 549   <row>
 550     <entry>Date/Time Type</entry>
 551     <entry>Storage</entry>
 552     <entry>Recommendation</entry>
 553     <entry>Description</entry>
 554   </row>
 555 </thead>
 556 <tbody>
 557   <row>
 558     <entry>abstime</entry>
 559     <entry>4 bytes</entry>
 560     <entry>original date and time</entry>
 561     <entry>limited range</entry>
 562   </row>
 563   <row>
 564     <entry>date</entry>
 565     <entry>4 bytes</entry>
 566     <entry><acronym>SQL92</acronym> type</entry>
 567     <entry>wide range</entry>
 568   </row>
 569   <row>
 570     <entry>datetime</entry>
 571     <entry>8 bytes</entry>
 572     <entry>best general date and time</entry>
 573     <entry>wide range, high precision</entry>
 574   </row>
 575   <row>
 576     <entry>interval</entry>
 577     <entry>12 bytes</entry>
 578     <entry><acronym>SQL92</acronym> type</entry>
 579     <entry>equivalent to timespan</entry>
 580   </row>
 581   <row>
 582     <entry>reltime</entry>
 583     <entry>4 bytes</entry>
 584     <entry>original time interval</entry>
 585     <entry>limited range, low precision</entry>
 586   </row>
 587   <row>
 588     <entry>time</entry>
 589     <entry>4 bytes</entry>
 590     <entry><acronym>SQL92</acronym> type</entry>
 591     <entry>wide range</entry>
 592   </row>
 593   <row>
 594     <entry>timespan</entry>
 595     <entry>12 bytes</entry>
 596     <entry>best general time interval</entry>
 597     <entry>wide range, high precision</entry>
 598   </row>
 599   <row>
 600     <entry>timestamp</entry>
 601     <entry>4 bytes</entry>
 602     <entry><acronym>SQL92</acronym> type</entry>
 603     <entry>limited range</entry>
 604   </row>
 605 </tbody>
 606 </tgroup>
 607 </table>
 608
 609 <type>timestamp</type> is currently implemented separately from
 610 <type>datetime</type>, although they share input and output routines.
 611 </para>
 612
 613 <para>
 614 <table tocentry="1">
 615 <title><productname>Postgres</productname> Date/Time Ranges</title>
 616 <titleabbrev>Ranges</titleabbrev>
 617 <tgroup cols="4">
 618 <thead>
 619   <row>
 620     <entry>Date/Time Type</entry>
 621     <entry>Earliest</entry>
 622     <entry>Latest</entry>
 623     <entry>Resolution</entry>
 624   </row>
 625 </thead>
 626 <tbody>
 627   <row>
 628     <entry>abstime</entry>
 629     <entry>1901-12-14</entry>
 630     <entry>2038-01-19</entry>
 631     <entry>1 sec</entry>
 632   </row>
 633   <row>
 634     <entry>date</entry>
 635     <entry>4713 BC</entry>
 636     <entry>32767 AD</entry>
 637     <entry>1 day</entry>
 638   </row>
 639   <row>
 640     <entry>datetime</entry>
 641     <entry>4713 BC</entry>
 642     <entry>1465001 AD</entry>
 643     <entry>1 microsec to 14 digits</entry>
 644   </row>
 645   <row>
 646     <entry>interval</entry>
 647     <entry>-178000000 years</entry>
 648     <entry>178000000 years</entry>
 649     <entry>1 microsec</entry>
 650   </row>
 651   <row>
 652     <entry>reltime</entry>
 653     <entry>-68 years</entry>
 654     <entry>+68 years</entry>
 655     <entry>1 sec</entry>
 656   </row>
 657   <row>
 658     <entry>time</entry>
 659     <entry>00:00:00.00</entry>
 660     <entry>23:59:59.99</entry>
 661     <entry>1 microsec</entry>
 662   </row>
 663   <row>
 664     <entry>timespan</entry>
 665     <entry>-178000000 years</entry>
 666     <entry>178000000 years</entry>
 667     <entry>1 microsec (14 digits)</entry>
 668   </row>
 669   <row>
 670     <entry>timestamp</entry>
 671     <entry>1901-12-14</entry>
 672     <entry>2038-01-19</entry>
 673     <entry>1 sec</entry>
 674   </row>
 675 </tbody>
 676 </tgroup>
 677 </table>
 678 </para>
 679
 680 <para>
 681 <productname>Postgres</productname> endeavors to be compatible with
 682 <acronym>SQL92</acronym> definitions for typical usage.
 683 The <acronym>SQL92</acronym> standard has an odd mix of date and
 684 time types and capabilities. Two obvious problems are:
 685
 686 <itemizedlist>
 687 <listitem>
 688 <para>
 689 Although the <type>date</type> type
 690 does not have an associated time zone, the
 691 <type>time</type> type can or does.</para></listitem>
 692
 693 <listitem>
 694 <para>
 695 The default time zone is specified as a constant integer offset
 696 from GMT/UTC.</para></listitem>
 697
 698 </itemizedlist>
 699
 700 However, time zones in the real world can have no meaning unless
 701 associated with a date as well as a time
 702 since the offset may vary through the year with daylight savings
 703 time boundaries.
 704 </para>
 705
 706 <para>
 707 To address these difficulties, <productname>Postgres</productname>
 708 associates time zones only with date and time
 709 types which contain both date and time,
 710  and assumes local time for any type containing only
 711 date or time. Further, time zone support is derived from
 712 the underlying operating system
 713 time zone capabilities, and hence can handle daylight savings time
 714 and other expected behavior.
 715 </para>
 716
 717 <para>
 718 In future releases, the number of date/time types will decrease,
 719 with the current implementation of
 720 <type>datetime</type> becoming <type>timestamp</type>,
 721 <type>timespan</type> becoming <type>interval</type>,
 722 and (possibly) <type>abstime</type> and <type>reltime</type>
 723 being deprecated in favor of <type>timestamp</type> and <type>interval</type>.
 724 The more arcane features of the date/time definitions from
 725 the <acronym>SQL92</acronym> standard are not likely to be pursued.
 726 </para>
 727
 728 <sect2>
 729 <title>Date/Time Styles</title>
 730
 731 <para>
 732 Output formats can be set to one of four styles:
 733 ISO-8601, <acronym>SQL</acronym> (Ingres), traditional
 734 Postgres, and German.
 735
 736 <table tocentry="1">
 737 <title><productname>Postgres</productname> Date Styles</title>
 738 <titleabbrev>Styles</titleabbrev>
 739 <tgroup cols="3">
 740 <thead>
 741   <row>
 742     <entry>Style Specification</entry>
 743     <entry>Description</entry>
 744     <entry>Example</entry>
 745   </row>
 746 </thead>
 747 <tbody>
 748   <row>
 749     <entry>ISO</entry>
 750     <entry>ISO-8601 standard</entry>
 751     <entry>1997-12-17 07:37:16-08</entry>
 752   </row>
 753   <row>
 754     <entry><acronym>SQL</acronym></entry>
 755     <entry>Traditional style</entry>
 756     <entry>12/17/1997 07:37:16.00 PST</entry>
 757   </row>
 758   <row>
 759     <entry>Postgres</entry>
 760     <entry>Original style</entry>
 761     <entry>Wed Dec 17 07:37:16 1997 PST</entry>
 762   </row>
 763   <row>
 764     <entry>German</entry>
 765     <entry>Regional style</entry>
 766     <entry>17.12.1997 07:37:16.00 PST</entry>
 767   </row>
 768 </tbody>
 769 </tgroup>
 770 </table>
 771 </para>
 772
 773 <para>
 774 The <acronym>SQL</acronym> style has European and non-European (US) variants,
 775 which determines whether month follows day or vica versa.
 776
 777 <table tocentry="1">
 778 <title><productname>Postgres</productname> Date Order Conventions</title>
 779 <titleabbrev>Order</titleabbrev>
 780 <tgroup cols="3">
 781 <thead>
 782   <row>
 783     <entry>Style Specification</entry>
 784     <entry>Description</entry>
 785     <entry>Example</entry>
 786   </row>
 787 </thead>
 788 <tbody>
 789   <row>
 790     <entry>European</entry>
 791     <entry>Regional convention</entry>
 792     <entry>17/12/1997 15:37:16.00 MET</entry>
 793   </row>
 794   <row>
 795     <entry>NonEuropean</entry>
 796     <entry>Regional convention</entry>
 797     <entry>12/17/1997 07:37:16.00 PST</entry>
 798   </row>
 799   <row>
 800     <entry>US</entry>
 801     <entry>Regional convention</entry>
 802     <entry>12/17/1997 07:37:16.00 PST</entry>
 803   </row>
 804 </tbody>
 805 </tgroup>
 806 </table>
 807 </para>
 808
 809 <para>
 810 There are several ways to affect the appearance of date/time types:
 811
 812 <itemizedlist spacing="compact" mark="bullet">
 813 <listitem>
 814 <para>
 815 The PGDATESTYLE environment variable used by the backend directly
 816 on postmaster startup.
 817 </para>
 818 </listitem>
 819 <listitem>
 820 <para>
 821 The PGDATESTYLE environment variable used by the frontend libpq
 822 on session startup.
 823 </para>
 824 </listitem>
 825 <listitem>
 826 <para>
 827 <command>SET DATESTYLE</command> <acronym>SQL</acronym> command.
 828 </para>
 829 </listitem>
 830 </itemizedlist>
 831 </para>
 832
 833 <para>
 834 For <productname>Postgres</productname> v6.4 (and earlier)
 835 the default date/time style is
 836 "non-European traditional Postgres".
 837 In future releases, the default may become "ISO" (compatible with ISO-8601),
 838 which alleviates date specification ambiguities and Y2K collation problems.
 839 </para>
 840
 841 </sect2>
 842
 843 <sect2>
 844 <title>Time Zones</title>
 845
 846 <para>
 847 <productname>Postgres</productname> obtains time zone support
 848 from the underlying operating system.
 849 All dates and times are stored internally in Universal Coordinated Time (UTC),
 850  alternately known as Greenwich Mean Time (GMT).
 851 Times are converted to local time on the database server before being
 852 sent to the client frontend, hence by default are in the server time zone.</para>
 853
 854 <para>
 855 There are several ways to affect the time zone behavior:
 856
 857 <itemizedlist spacing="compact" mark="bullet">
 858 <listitem>
 859 <para>
 860 The TZ environment variable used by the backend directly
 861  on postmaster startup as the default time zone.
 862 </para>
 863 </listitem>
 864 <listitem>
 865 <para>
 866 The PGTZ environment variable set at the client used by libpq
 867 to send time zone information to the backend upon connection.
 868 </para>
 869 </listitem>
 870 <listitem>
 871 <para>
 872 The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>
 873 sets the time zone for the session.
 874 </para>
 875 </listitem>
 876 </itemizedlist></para>
 877
 878 <para>
 879  If an invalid time zone is specified,
 880 the time zone becomes GMT (on most systems anyway).
 881 </para>
 882 </sect2>
 883
 884    <sect2>
 885     <title>Date/Time Input</title>
 886
 887     <para>
 888      General-use date and time is input using a wide range of
 889      styles, including ISO-compatible, <acronym>SQL</acronym>-compatible,
 890      traditional <productname>Postgres</productname>
 891      and other permutations of date and time. In cases where interpretation
 892      can be ambiguous (quite possible with many traditional styles of date
 893      specification) <productname>Postgres</productname> uses a style setting
 894      to resolve the ambiguity.
 895     </para>
 896
 897     <para>
 898      Most date and time types share code for data input. For those types
 899      the input can have any of a wide variety of styles. For numeric date
 900      representations,
 901      European and US conventions can differ, and the proper interpretation
 902      is obtained by using the <command>SET DATESTYLE</command>
 903      command before entering data.
 904      Note that the style setting does not preclude use of various styles for input;
 905      it is used primarily to determine the output style and to resolve ambiguities.
 906     </para>
 907
 908     <para>
 909      The special values <literal>current</literal>,
 910      <literal>infinity</literal> and <literal>-infinity</literal> are provided.
 911      <literal>infinity</literal> specifies a time later than any other valid time, and
 912      <literal>-infinity</literal> specifies a time earlier than any other valid time.
 913      <literal>current</literal> indicates that the current time should be
 914      substituted whenever this value appears in a computation.
 915     </para>
 916
 917     <para>
 918      The strings
 919      <literal>now</literal>,
 920      <literal>today</literal>,
 921      <literal>yesterday</literal>,
 922      <literal>tomorrow</literal>,
 923      and <literal>epoch</literal>
 924      can be used to specify time values.
 925      <literal>now</literal>
 926      means the current transaction time, and differs from
 927      <literal>current</literal>
 928      in that the current time is immediately substituted for it.
 929      <literal>epoch</literal> means <literal>Jan 1 00:00:00 1970 GMT</literal>.
 930     </para>
 931
 932     <para>
 933      <table tocentry="1">
 934       <title><productname>Postgres</productname> Date/Time Special Constants</title>
 935       <titleabbrev>Constants</titleabbrev>
 936       <tgroup cols="2">
 937        <thead>
 938         <row>
 939          <entry>Constant</entry>
 940          <entry>Description</entry>
 941         </row>
 942        </thead>
 943        <tbody>
 944         <row>
 945          <entry>current</entry>
 946          <entry>Current transaction time, deferred</entry>
 947         </row>
 948         <row>
 949          <entry>epoch</entry>
 950          <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
 951         </row>
 952         <row>
 953          <entry>infinity</entry>
 954          <entry>Later than other valid times</entry>
 955         </row>
 956         <row>
 957          <entry>-infinity</entry>
 958          <entry>Earlier than other valid times</entry>
 959         </row>
 960         <row>
 961          <entry>invalid</entry>
 962          <entry>Illegal entry</entry>
 963         </row>
 964         <row>
 965          <entry>now</entry>
 966          <entry>Current transaction time</entry>
 967         </row>
 968         <row>
 969          <entry>today</entry>
 970          <entry>Midnight today</entry>
 971         </row>
 972         <row>
 973          <entry>tomorrow</entry>
 974          <entry>Midnight tomorrow</entry>
 975         </row>
 976         <row>
 977          <entry>yesterday</entry>
 978          <entry>Midnight yesterday</entry>
 979         </row>
 980        </tbody>
 981       </tgroup>
 982      </table>
 983     </para>
 984
 985     <para>
 986      <table tocentry="1">
 987       <title><productname>Postgres</productname> Date Input</title>
 988       <titleabbrev>Date Inputs</titleabbrev>
 989       <tgroup cols="2">
 990        <thead>
 991         <row>
 992          <entry>Example</entry>
 993          <entry>Description</entry>
 994         </row>
 995        </thead>
 996        <tbody>
 997         <row>
 998          <entry>January 8, 1999</entry>
 999          <entry>Unambiguous text month</entry>
1000         </row>
1001         <row>
1002          <entry>1999-01-08</entry>
1003          <entry>ISO-8601</entry>
1004         </row>
1005         <row>
1006          <entry>1/8/1999</entry>
1007          <entry>US; read as August 1 in European mode</entry>
1008         </row>
1009         <row>
1010          <entry>8/1/1999</entry>
1011          <entry>European; read as August 1 in US mode</entry>
1012         </row>
1013         <row>
1014          <entry>1/18/1999</entry>
1015          <entry>US; read as January 18 in any mode</entry>
1016         </row>
1017         <row>
1018          <entry>1999.008</entry>
1019          <entry>Year and day of year</entry>
1020         </row>
1021         <row>
1022          <entry>19990108</entry>
1023          <entry>ISO-8601 year, month, day</entry>
1024         </row>
1025         <row>
1026          <entry>990108</entry>
1027          <entry>ISO-8601 year, month, day</entry>
1028         </row>
1029         <row>
1030          <entry>1999.008</entry>
1031          <entry>Year and day of year</entry>
1032         </row>
1033         <row>
1034          <entry>99008</entry>
1035          <entry>Year and day of year</entry>
1036         </row>
1037         <row>
1038          <entry>January 8, 99 BC</entry>
1039          <entry>Year 99 before the Christian Era</entry>
1040         </row>
1041        </tbody>
1042       </tgroup>
1043      </table>
1044     </para>
1045
1046     <para>
1047      <table tocentry="1">
1048       <title><productname>Postgres</productname> Month Abbreviations</title>
1049       <titleabbrev>Month Abbreviations</titleabbrev>
1050       <tgroup cols="2">
1051        <thead>
1052         <row>
1053          <entry>Month</entry>
1054          <entry>Abbreviations</entry>
1055         </row>
1056        </thead>
1057        <tbody>
1058         <row>
1059          <entry>April</entry>
1060          <entry>Apr</entry>
1061         </row>
1062         <row>
1063          <entry>August</entry>
1064          <entry>Aug</entry>
1065         </row>
1066         <row>
1067          <entry>December</entry>
1068          <entry>Dec</entry>
1069         </row>
1070         <row>
1071          <entry>February</entry>
1072          <entry>Feb</entry>
1073         </row>
1074         <row>
1075          <entry>January</entry>
1076          <entry>Jan</entry>
1077         </row>
1078         <row>
1079          <entry>July</entry>
1080          <entry>Jul</entry>
1081         </row>
1082         <row>
1083          <entry>June</entry>
1084          <entry>Jun</entry>
1085         </row>
1086         <row>
1087          <entry>March</entry>
1088          <entry>Mar</entry>
1089         </row>
1090         <row>
1091          <entry>November</entry>
1092          <entry>Nov</entry>
1093         </row>
1094         <row>
1095          <entry>October</entry>
1096          <entry>Oct</entry>
1097         </row>
1098         <row>
1099          <entry>September</entry>
1100          <entry>Sep, Sept</entry>
1101         </row>
1102        </tbody>
1103       </tgroup>
1104      </table>
1105
1106      <note>
1107       <para>
1108        The month <literal>May</literal> has no explicit abbreviation, for obvious reasons.
1109       </para>
1110      </note>
1111     </para>
1112
1113     <para>
1114      <table tocentry="1">
1115       <title><productname>Postgres</productname> Day of Week Abbreviations</title>
1116       <titleabbrev>Day of Week Abbreviations</titleabbrev>
1117       <tgroup cols="2">
1118        <thead>
1119         <row>
1120          <entry>Day</entry>
1121          <entry>Abbreviation</entry>
1122         </row>
1123        </thead>
1124        <tbody>
1125         <row>
1126          <entry>Sunday</entry>
1127          <entry>Sun</entry>
1128         </row>
1129         <row>
1130          <entry>Monday</entry>
1131          <entry>Mon</entry>
1132         </row>
1133         <row>
1134          <entry>Tuesday</entry>
1135          <entry>Tue, Tues</entry>
1136         </row>
1137         <row>
1138          <entry>Wednesday</entry>
1139          <entry>Wed, Weds</entry>
1140         </row>
1141         <row>
1142          <entry>Thursday</entry>
1143          <entry>Thu, Thur, Thurs</entry>
1144         </row>
1145         <row>
1146          <entry>Friday</entry>
1147          <entry>Fri</entry>
1148         </row>
1149         <row>
1150          <entry>Saturday</entry>
1151          <entry>Sat</entry>
1152         </row>
1153        </tbody>
1154       </tgroup>
1155      </table>
1156     </para>
1157
1158     <para>
1159      <table tocentry="1">
1160       <title><productname>Postgres</productname> Time Input</title>
1161       <titleabbrev>Time Inputs</titleabbrev>
1162       <tgroup cols="2">
1163        <thead>
1164         <row>
1165          <entry>Example</entry>
1166          <entry>Description</entry>
1167         </row>
1168        </thead>
1169        <tbody>
1170         <row>
1171          <entry>04:05:06.789</entry>
1172          <entry>ISO-8601, with all time fields</entry>
1173         </row>
1174         <row>
1175          <entry>04:05:06</entry>
1176          <entry>ISO-8601</entry>
1177         </row>
1178         <row>
1179          <entry>04:05</entry>
1180          <entry>ISO-8601</entry>
1181         </row>
1182         <row>
1183          <entry>040506</entry>
1184          <entry>ISO-8601</entry>
1185         </row>
1186         <row>
1187          <entry>04:05 AM</entry>
1188          <entry>Same as 04:05; AM does not affect value</entry>
1189         </row>
1190         <row>
1191          <entry>04:05 PM</entry>
1192          <entry>Same as 16:05; input hour must be <= 12</entry>
1193         </row>
1194         <row>
1195          <entry>z</entry>
1196          <entry>Same as 00:00:00</entry>
1197         </row>
1198         <row>
1199          <entry>zulu</entry>
1200          <entry>Same as 00:00:00</entry>
1201         </row>
1202         <row>
1203          <entry>allballs</entry>
1204          <entry>Same as 00:00:00</entry>
1205         </row>
1206        </tbody>
1207       </tgroup>
1208      </table>
1209     </para>
1210
1211     <para>
1212      <table tocentry="1">
1213       <title><productname>Postgres</productname> Time Zone Input</title>
1214       <titleabbrev>Time Zone Inputs</titleabbrev>
1215       <tgroup cols="2">
1216        <thead>
1217         <row>
1218          <entry>Time Zone</entry>
1219          <entry>Description</entry>
1220         </row>
1221        </thead>
1222        <tbody>
1223         <row>
1224          <entry>PST</entry>
1225          <entry>Pacific Standard Time</entry>
1226         </row>
1227         <row>
1228          <entry>-8:00</entry>
1229          <entry>ISO-8601 offset for PST</entry>
1230         </row>
1231         <row>
1232          <entry>-800</entry>
1233          <entry>ISO-8601 offset for PST</entry>
1234         </row>
1235         <row>
1236          <entry>-8</entry>
1237          <entry>ISO-8601 offset for PST</entry>
1238         </row>
1239        </tbody>
1240       </tgroup>
1241      </table>
1242     </para>
1243
1244     <para>
1245      <table tocentry="1">
1246       <title><productname>Postgres</productname> Recognized Time Zones</title>
1247       <titleabbrev>Time Zones</titleabbrev>
1248       <tgroup cols="3">
1249        <thead>
1250         <row>
1251          <entry>Time Zone</entry>
1252          <entry>Offset from UTC</entry>
1253          <entry>Description</entry>
1254         </row>
1255        </thead>
1256        <tbody>
1257         <row>
1258          <entry>ACSST</entry>
1259          <entry>+10:30 </entry>
1260          <entry>Central Australia Summer Std Time</entry>
1261         </row>
1262         <row>
1263          <entry>ACST</entry>
1264          <entry>+09:30 </entry>
1265          <entry>Central Australia Std Time</entry>
1266         </row>
1267         <row>
1268          <entry>ADT</entry>
1269          <entry>-02:00 </entry>
1270          <entry>Atlantic Daylight Time</entry>
1271         </row>
1272         <row>
1273          <entry>AESST</entry>
1274          <entry>+11:00 </entry>
1275          <entry>Australia Eastern Summer Std Time</entry>
1276         </row>
1277         <row>
1278          <entry>AEST</entry>
1279          <entry>+10:00 </entry>
1280          <entry>Australia Eastern Std Time</entry>
1281         </row>
1282         <row>
1283          <entry>AHST</entry>
1284          <entry>-10:00 </entry>
1285          <entry>Alaska-Hawaii Std Time</entry>
1286         </row>
1287         <row>
1288          <entry>AST</entry>
1289          <entry>-4:00 </entry>
1290          <entry>Atlantic Std Time (Canada)</entry>
1291         </row>
1292         <row>
1293          <entry>AWSST</entry>
1294          <entry>+9:00 </entry>
1295          <entry>Australia Western Summer Std Time</entry>
1296         </row>
1297         <row>
1298          <entry>AWST</entry>
1299          <entry>+8:00 </entry>
1300          <entry>Australia Western Std Time</entry>
1301         </row>
1302         <row>
1303          <entry>BST</entry>
1304          <entry>+1:00 </entry>
1305          <entry>British Summer Time</entry>
1306         </row>
1307         <row>
1308          <entry>BT</entry>
1309          <entry>+3:00 </entry>
1310          <entry>Baghdad Time</entry>
1311         </row>
1312         <row>
1313          <entry>CADT</entry>
1314          <entry>+10:30 </entry>
1315          <entry>Central Australia Daylight Savings Time</entry>
1316         </row>
1317         <row>
1318          <entry>CAST</entry>
1319          <entry>+09:30 </entry>
1320          <entry>Central Australia Std Time</entry>
1321         </row>
1322         <row>
1323          <entry>CAT</entry>
1324          <entry>-10:00 </entry>
1325          <entry>Central Alaska Time</entry>
1326         </row>
1327         <row>
1328          <entry>CCT</entry>
1329          <entry>+8:00 </entry>
1330          <entry>China Coastal Time</entry>
1331         </row>
1332         <row>
1333          <entry>CDT</entry>
1334          <entry>-5:00 </entry>
1335          <entry>Central Daylight Time</entry>
1336         </row>
1337         <row>
1338          <entry>CET</entry>
1339          <entry>+1:00 </entry>
1340          <entry>Central European Time</entry>
1341         </row>
1342         <row>
1343          <entry>CETDST</entry>
1344          <entry>+2:00 </entry>
1345          <entry>Central European Daylight Savings Time</entry>
1346         </row>
1347         <row>
1348          <entry>CST</entry>
1349          <entry>+6:00 </entry>
1350          <entry>Central Std Time</entry>
1351         </row>
1352         <row>
1353          <entry>DNT</entry>
1354          <entry>+1:00 </entry>
1355          <entry>Dansk Normal Tid</entry>
1356         </row>
1357         <row>
1358          <entry>DST</entry>
1359          <entry>+1:00 </entry>
1360          <entry>Dansk Standard Time (?)</entry>
1361         </row>
1362         <row>
1363          <entry>EAST</entry>
1364          <entry>+10:00 </entry>
1365          <entry>East Australian Std Time</entry>
1366         </row>
1367         <row>
1368          <entry>EDT</entry>
1369          <entry>-4:00 </entry>
1370          <entry>Eastern Daylight Time</entry>
1371         </row>
1372         <row>
1373          <entry>EET</entry>
1374          <entry>+2:00 </entry>
1375          <entry>Eastern Europe, USSR Zone 1</entry>
1376         </row>
1377         <row>
1378          <entry>EETDST</entry>
1379          <entry>+3:00 </entry>
1380          <entry>Eastern Europe Daylight Savings Time</entry>
1381         </row>
1382         <row>
1383          <entry>EST</entry>
1384          <entry>-5:00 </entry>
1385          <entry>Eastern Standard Time</entry>
1386         </row>
1387         <row>
1388          <entry>FST</entry>
1389          <entry>+1:00 </entry>
1390          <entry>French Summer Time</entry>
1391         </row>
1392         <row>
1393          <entry>FWT</entry>
1394          <entry>+2:00</entry>
1395          <entry>French Winter Time</entry>
1396         </row>
1397         <row>
1398          <entry>GMT</entry>
1399          <entry>0:00</entry>
1400          <entry>Greenwish Mean Time</entry>
1401         </row>
1402         <row>
1403          <entry>GST</entry>
1404          <entry>+10:00</entry>
1405          <entry>Guam Std Time, USSR Zone 9</entry>
1406         </row>
1407         <row>
1408          <entry>HDT</entry>
1409          <entry>-9:00</entry>
1410          <entry>Hawaii/Alaska</entry>
1411         </row>
1412         <row>
1413          <entry>IDLE</entry>
1414          <entry>+12:00</entry>
1415          <entry>International Date Line, East</entry>
1416         </row>
1417         <row>
1418          <entry>IDLW</entry>
1419          <entry>-12:00</entry>
1420          <entry>International Date Line, West</entry>
1421         </row>
1422         <row>
1423          <entry>IST</entry>
1424          <entry>+2:00</entry>
1425          <entry>Israel</entry>
1426         </row>
1427         <row>
1428          <entry>IT</entry>
1429          <entry>+3:40</entry>
1430          <entry>Iran Time</entry>
1431         </row>
1432         <row>
1433          <entry>JST</entry>
1434          <entry>+9:00</entry>
1435          <entry>Japan Std Time,USSR Zone 8</entry>
1436         </row>
1437         <row>
1438          <entry>JT</entry>
1439          <entry>+7:30</entry>
1440          <entry>Java Time</entry>
1441         </row>
1442         <row>
1443          <entry>KST</entry>
1444          <entry>+9:00</entry>
1445          <entry>Korea Standard Time</entry>
1446         </row>
1447         <row>
1448          <entry>LIGT</entry>
1449          <entry>+10:00</entry>
1450          <entry>Melbourne, Australia</entry>
1451         </row>
1452         <row>
1453          <entry>MDT</entry>
1454          <entry>-6:00</entry>
1455          <entry>Mountain Daylight Time</entry>
1456         </row>
1457         <row>
1458          <entry>MEST</entry>
1459          <entry>+2:00</entry>
1460          <entry>Middle Europe Summer Time</entry>
1461         </row>
1462         <row>
1463          <entry>MET</entry>
1464          <entry>+1:00</entry>
1465          <entry>Middle Europe Time</entry>
1466         </row>
1467         <row>
1468          <entry>METDST</entry>
1469          <entry>+2:00</entry>
1470          <entry>Middle Europe Daylight Time</entry>
1471         </row>
1472         <row>
1473          <entry>MEWT</entry>
1474          <entry>+1:00</entry>
1475          <entry>Middle Europe Winter Time</entry>
1476         </row>
1477         <row>
1478          <entry>MEZ</entry>
1479          <entry>+1:00</entry>
1480          <entry>Middle Europe Zone</entry>
1481         </row>
1482         <row>
1483          <entry>MST</entry>
1484          <entry>-7:00</entry>
1485          <entry>Mountain Standard Time</entry>
1486         </row>
1487         <row>
1488          <entry>MT</entry>
1489          <entry>+8:30</entry>
1490          <entry>Moluccas Time</entry>
1491         </row>
1492         <row>
1493          <entry>NDT</entry>
1494          <entry>-2:30</entry>
1495          <entry>Newfoundland Daylight Time</entry>
1496         </row>
1497         <row>
1498          <entry>NFT</entry>
1499          <entry>-3:30</entry>
1500          <entry>Newfoundland Standard Time</entry>
1501         </row>
1502         <row>
1503          <entry>NOR</entry>
1504          <entry>+1:00</entry>
1505          <entry>Norway Standard Time</entry>
1506         </row>
1507         <row>
1508          <entry>NST</entry>
1509          <entry>-3:30</entry>
1510          <entry>Newfoundland Standard Time</entry>
1511         </row>
1512         <row>
1513          <entry>NT</entry>
1514          <entry>-11:00</entry>
1515          <entry>Nome Time</entry>
1516         </row>
1517         <row>
1518          <entry>NZDT</entry>
1519          <entry>+13:00</entry>
1520          <entry>New Zealand Daylight Time</entry>
1521         </row>
1522         <row>
1523          <entry>NZST</entry>
1524          <entry>+12:00</entry>
1525          <entry>New Zealand Std Time</entry>
1526         </row>
1527         <row>
1528          <entry>NZT</entry>
1529          <entry>+12:00</entry>
1530          <entry>New Zealand Time</entry>
1531         </row>
1532         <row>
1533          <entry>PDT</entry>
1534          <entry>-7:00</entry>
1535          <entry>Pacific Daylight Time</entry>
1536         </row>
1537         <row>
1538          <entry>PST</entry>
1539          <entry>-8:00</entry>
1540          <entry>Pacific Std Time</entry>
1541         </row>
1542         <row>
1543          <entry>SADT</entry>
1544          <entry>+10:30</entry>
1545          <entry>South Australian Daylight Time</entry>
1546         </row>
1547         <row>
1548          <entry>SAT</entry>
1549          <entry>+9:30</entry>
1550          <entry>South Australian Std Time</entry>
1551         </row>
1552         <row>
1553          <entry>SET</entry>
1554          <entry>+1:00</entry>
1555          <entry>Seychelles Time</entry>
1556         </row>
1557         <row>
1558          <entry>SST</entry>
1559          <entry>+2:00</entry>
1560          <entry>Swedish Summer Time</entry>
1561         </row>
1562         <row>
1563          <entry>SWT</entry>
1564          <entry>+1:00</entry>
1565          <entry>Swedish Winter Time</entry>
1566         </row>
1567         <row>
1568          <entry>WADT</entry>
1569          <entry>+8:00</entry>
1570          <entry>West Australian Daylight Time</entry>
1571         </row>
1572         <row>
1573          <entry>WAST</entry>
1574          <entry>+7:00</entry>
1575          <entry>West Australian Std Time</entry>
1576         </row>
1577         <row>
1578          <entry>WAT</entry>
1579          <entry>-1:00</entry>
1580          <entry>West Africa Time</entry>
1581         </row>
1582         <row>
1583          <entry>WDT</entry>
1584          <entry>+9:00</entry>
1585          <entry>West Australian Daylight Time</entry>
1586         </row>
1587         <row>
1588          <entry>WET</entry>
1589          <entry>0:00</entry>
1590          <entry>Western Europe</entry>
1591         </row>
1592         <row>
1593          <entry>WETDST</entry>
1594          <entry>+1:00</entry>
1595          <entry>Western Europe Daylight Savings Time</entry>
1596         </row>
1597         <row>
1598          <entry>WST</entry>
1599          <entry>+8:00</entry>
1600          <entry>West Australian Std Time</entry>
1601         </row>
1602         <row>
1603          <entry>YDT</entry>
1604          <entry>-8:00</entry>
1605          <entry>Yukon Daylight Time</entry>
1606         </row>
1607         <row>
1608          <entry>YST</entry>
1609          <entry>-9:00</entry>
1610          <entry>Yukon Standard Time</entry>
1611         </row>
1612         <row>
1613          <entry>ZP4</entry>
1614          <entry>-4:00</entry>
1615          <entry>GMT +4 hours</entry>
1616         </row>
1617         <row>
1618          <entry>ZP5</entry>
1619          <entry>-5:00</entry>
1620          <entry>GMT +5  hours</entry>
1621         </row>
1622         <row>
1623          <entry>ZP6</entry>
1624          <entry>-6:00</entry>
1625          <entry>GMT +6  hours</entry>
1626         </row>
1627        </tbody>
1628       </tgroup>
1629      </table>
1630      <note>
1631       <para>
1632        If the compiler option USE_AUSTRALIAN_RULES is set
1633        then <literal>EST</literal> refers to Australia Eastern Std Time,
1634        which has an offset of +10:00 hours from UTC.
1635       </para>
1636
1637       <para>
1638        It is interesting that Australian time zones and their naming variants
1639        account for fully one quarter of all time zones in the
1640        <productname>Postgres</productname> time zone lookup table.
1641       </para>
1642      </note>
1643     </para>
1644
1645     <procedure>
1646      <title>Date/Time Input Interpretation</title>
1647      <step>
1648       <para>
1649        Break the input string into tokens and categorize each token as
1650        a string, time, time zone, or number.
1651       </para>
1652
1653       <substeps>
1654        <step>
1655         <para>
1656          If the token contains a colon (":"), this is a time string.
1657         </para>
1658        </step>
1659
1660        <step>
1661         <para>
1662          If the token contains a dash ("-"), slash ("/"), or dot ("."),
1663          this is a date string which may have a text month.
1664         </para>
1665        </step>
1666
1667        <step>
1668         <para>
1669          If the token is numeric only, then it is either a single field
1670          or an ISO-8601 concatenated date (e.g. "19990113" for January 13, 1999)
1671          or time (e.g. 141516 for 14:15:16).
1672         </para>
1673        </step>
1674        <step>
1675         <para>
1676          If the token starts with a plus ("+") or minus ("-"),
1677          then it is either a time zone or a special field.
1678         </para>
1679        </step>
1680       </substeps>
1681      </step>
1682
1683      <step>
1684       <para>
1685        If the token is a text string, match up with possible strings.
1686       </para>
1687
1688       <substeps>
1689        <step>
1690         <para>
1691          Do a binary-search table lookup for the token
1692          as either a special string (e.g. <literal>today</literal>),
1693          day (e.g. <literal>Thursday</literal>),
1694          month (e.g. <literal>January</literal>), or noise word (e.g. <literal>on</literal>).
1695         </para>
1696         <para>
1697          Set field values and bit mask for fields.
1698          For example, set year, month, day for <literal>today</literal>, and additionally
1699          hour, minute, second for <literal>now</literal>.
1700         </para>
1701        </step>
1702
1703        <step>
1704         <para>
1705          If not found, do a similar binary-search table lookup to match
1706          the token with a time zone.
1707         </para>
1708        </step>
1709
1710        <step>
1711         <para>
1712          If not found, throw an error.
1713         </para>
1714        </step>
1715       </substeps>
1716      </step>
1717
1718      <step>
1719       <para>
1720        The token is a number or number field. If there are more than 4 digits,
1721        and if no other date fields have been previously read, then interpret
1722        as a "concatenated date".
1723       </para>
1724
1725       <substeps>
1726        <step>
1727         <para>If there </para>
1728        </step>
1729       </substeps>
1730      </step>
1731
1732      <step>
1733       <para>
1734       </para>
1735      </step>
1736
1737      <step>
1738       <para>
1739       </para>
1740      </step>
1741
1742      <step>
1743       <para>
1744       </para>
1745      </step>
1746
1747      <step>
1748       <para>
1749       </para>
1750      </step>
1751
1752      <step>
1753       <para>
1754       </para>
1755      </step>
1756
1757     </procedure>
1758 </sect2>
1759
1760 <sect2>
1761 <title>datetime</title>
1762
1763 <para>
1764 General-use date and time is input using a wide range of
1765 styles, including ISO-compatible, <acronym>SQL</acronym>-compatible, traditional
1766 <productname>Postgres</productname> (see section on "absolute time")
1767 and other permutations of date and time. Output styles can be ISO-compatible,
1768 <acronym>SQL</acronym>-compatible, or traditional
1769 <productname>Postgres</productname>, with the default set to be compatible
1770 with <productname>Postgres</productname> v6.0.
1771 </para>
1772
1773 <para>
1774 <type>datetime</type> is specified using the following syntax:
1775
1776 <programlisting>
1777 Year-Month-Day [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
1778   YearMonthDay [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
1779      Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
1780 where
1781     Year is 4013 BC, ..., very large
1782     Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
1783     Day is 1, 2, ..., 31
1784     Hour is 00, 02, ..., 23
1785     Minute is 00, 01, ..., 59
1786     Second is 00, 01, ..., 59 (60 for leap second)
1787     Timezone is 3 characters or ISO offset to GMT
1788 </programlisting>
1789     </para>
1790
1791 <para>
1792 Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
1793 Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
1794 offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
1795 Dates are stored internally in Greenwich Mean Time. Input and output routines
1796 translate time to the local time zone of the server.
1797 </para></sect2>
1798
1799 <sect2>
1800 <title><type>timespan</type></title>
1801
1802 <para>
1803 General-use time span is input using a wide range of
1804 syntaxes, including ISO-compatible, <acronym>SQL</acronym>-compatible,
1805 traditional
1806 <productname>Postgres</productname> (see section on "relative time")
1807  and other permutations of time span. Output formats can be ISO-compatible,
1808 <acronym>SQL</acronym>-compatible, or traditional
1809 <productname>Postgres</productname>,
1810 with the default set to be <productname>Postgres</productname>-compatible.
1811 Months and years are a "qualitative" time interval, and are stored separately
1812 from the other "quantitative" time intervals such as day or hour.
1813 For date arithmetic,
1814 the qualitative time units are instantiated in the context of the
1815 relevant date or time.
1816     </para>
1817
1818 <para>
1819 Time span is specified with the following syntax:
1820
1821 <programlisting>
1822   Quantity Unit [Quantity Unit...] [Direction]
1823 @ Quantity Unit [Direction]
1824 where
1825     Quantity is ..., <literal>-1</literal>, <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...
1826     Unit is <literal>second</literal>, <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>, <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
1827       <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>, or abbreviations or plurals of these units.
1828     Direction is <literal>ago</literal>.
1829 </programlisting>
1830 </para>
1831 </sect2>
1832
1833 <sect2>
1834 <title>abstime</title>
1835
1836     <para>
1837      Absolute time (<type>abstime</type>) is a limited-range (+/- 68 years) and
1838      limited-precision (1 sec)
1839      date data type. <type>datetime</type> may be preferred, since it
1840      covers a larger range with greater precision.
1841     </para>
1842
1843 <para>
1844 Absolute time is specified using the following syntax:
1845
1846 <programlisting>
1847 Month  Day [ Hour : Minute : Second ]  Year [ Timezone ]
1848 where
1849     Month is Jan, Feb, ..., Dec
1850     Day is 1, 2, ..., 31
1851     Hour is 01, 02, ..., 24
1852     Minute is 00, 01, ..., 59
1853     Second is 00, 01, ..., 59
1854     Year is 1901, 1902, ..., 2038
1855 </programlisting>
1856 </para>
1857
1858 <para>
1859      Valid dates are from <literal>Dec 13 20:45:53 1901 GMT</literal>
1860      to <literal>Jan 19 03:14:04 2038 GMT</literal>.
1861
1862 <note>
1863 <title>Historical Note</title>
1864 <para>
1865 As of Version 3.0, times are no longer read and written
1866 using Greenwich Mean Time; the input and output routines default to
1867 the local time zone.</para>
1868 </note>
1869
1870 All special values allowed for <type>datetime</type> are also
1871 allowed for "absolute time".
1872 </para>
1873
1874 </sect2>
1875
1876 <sect2>
1877 <title>reltime</title>
1878
1879 <para>
1880 Relative time <type>reltime</type> is a limited-range (+/- 68 years)
1881  and limited-precision (1 sec) time span data type.
1882 <type>timespan</type> should be preferred, since it
1883 covers a larger range with greater precision and, more importantly,
1884 can distinguish between
1885 relative units (months and years) and quantitative units (days, hours, etc).
1886 Instead, reltime
1887 must force months to be exactly 30 days, so time arithmetic does not
1888 always work as expected.
1889      For example, adding one reltime <literal>year</literal> to abstime <literal>today</literal> does not
1890 produce today's date one year from
1891 now, but rather a date 360 days from today.
1892 </para>
1893
1894 <para>
1895 <type>reltime</type> shares input and output routines with the other
1896 time span types.
1897 The section on <type>timespan</type> covers this in more detail.
1898 </para>
1899
1900 </sect2>
1901
1902 <sect2>
1903 <title><type>timestamp</type></title>
1904
1905 <para>
1906 This is currently a limited-range absolute time which closely resembles the
1907 abstime
1908 data type. It shares the general input parser with the other date/time types.
1909 In future releases this type will absorb the capabilities of the
1910 <type>datetime</type> type
1911 and will move toward <acronym>SQL92</acronym> compliance.
1912 </para>
1913
1914 <para>
1915 <type>timestamp</type> is specified using the same syntax as for
1916 <type>datetime</type>.
1917 </para>
1918 </sect2>
1919
1920 <sect2>
1921 <title><type>interval</type></title>
1922
1923 <para>
1924 <type>interval</type> is an <acronym>SQL92</acronym> data type which is
1925 currently mapped to the <type>timespan</type>
1926 <productname>Postgres</productname> data type.
1927 </para>
1928 </sect2>
1929
1930 <sect2>
1931 <title>tinterval</title>
1932
1933 <para>
1934 Time ranges are specified as:
1935
1936 <programlisting>
1937 [ 'abstime' 'abstime']
1938 where
1939     abstime is a time in the absolute time format.
1940 </programlisting>
1941
1942 Special abstime values such as
1943 <literal>current', <literal>infinity' and <literal>-infinity' can be used.</literal></literal></literal>
1944 </para></sect2>
1945
1946 </sect1>
1947
1948 <sect1>
1949 <title>Boolean Type</title>
1950
1951 <para>
1952 <productname>Postgres</productname> supports <type>bool</type> as
1953 the <acronym>SQL3</acronym> boolean type.
1954 <type>bool</type> can have one of only two states: 'true' or 'false'.
1955 A third state, 'unknown', is not
1956 implemented and is not suggested in <acronym>SQL3</acronym>;
1957 <acronym>NULL</acronym> is an
1958 effective substitute. <type>bool</type> can be used in any boolean expression,
1959 and boolean expressions
1960 always evaluate to a result compatible with this type.</para>
1961
1962 <para>
1963 <type>bool</type> uses 4 bytes of storage.
1964 </para>
1965
1966 <para>
1967 <table tocentry="1">
1968 <title><productname>Postgres</productname> Boolean Type</title>
1969 <titleabbrev>Booleans</titleabbrev>
1970 <tgroup cols="3">
1971 <thead>
1972   <row>
1973     <entry>State</entry>
1974     <entry>Output</entry>
1975     <entry>Input</entry>
1976   </row>
1977 </thead>
1978 <tbody>
1979   <row>
1980     <entry>True</entry>
1981     <entry>'t'</entry>
1982     <entry>TRUE, 't', 'true', 'y', 'yes', '1'</entry>
1983   </row>
1984   <row>
1985     <entry>False</entry>
1986     <entry>'f'</entry>
1987     <entry>FALSE, 'f', 'false', 'n', 'no', '0'</entry>
1988   </row>
1989 </tbody>
1990 </tgroup>
1991 </table>
1992 </para>
1993 </sect1>
1994
1995 <sect1>
1996 <title>Geometric Types</title>
1997
1998 <para>
1999 Geometric types represent two-dimensional spatial objects.
2000 The most fundamental type,
2001 the point, forms the basis for all of the other types.
2002 </para>
2003
2004 <para>
2005 <table tocentry="1">
2006 <title><productname>Postgres</productname> Geometric Types</title>
2007 <titleabbrev>Geometrics</titleabbrev>
2008 <tgroup cols="4">
2009 <thead>
2010   <row>
2011     <entry>Geometric Type</entry>
2012     <entry>Storage</entry>
2013     <entry>Representation</entry>
2014     <entry>Description</entry>
2015   </row>
2016 </thead>
2017 <tbody>
2018   <row>
2019     <entry>point</entry>
2020     <entry>16 bytes</entry>
2021     <entry>(x,y)</entry>
2022     <entry>Point in space</entry>
2023   </row>
2024   <row>
2025     <entry>line</entry>
2026     <entry>32 bytes</entry>
2027     <entry>((x1,y1),(x2,y2))</entry>
2028     <entry>Infinite line</entry>
2029   </row>
2030   <row>
2031     <entry>lseg</entry>
2032     <entry>32 bytes</entry>
2033     <entry>((x1,y1),(x2,y2))</entry>
2034     <entry>Finite line segment</entry>
2035   </row>
2036   <row>
2037     <entry>box</entry>
2038     <entry>32 bytes</entry>
2039     <entry>((x1,y1),(x2,y2))</entry>
2040     <entry>Rectangular box</entry>
2041   </row>
2042   <row>
2043     <entry>path</entry>
2044     <entry>4+32n bytes</entry>
2045     <entry>((x1,y1),...)</entry>
2046     <entry>Closed path (similar to polygon)</entry>
2047   </row>
2048   <row>
2049     <entry>path</entry>
2050     <entry>4+32n bytes</entry>
2051     <entry>[(x1,y1),...]</entry>
2052     <entry>Open path</entry>
2053   </row>
2054   <row>
2055     <entry>polygon</entry>
2056     <entry>4+32n bytes</entry>
2057     <entry>((x1,y1),...)</entry>
2058     <entry>Polygon (similar to closed path)</entry>
2059   </row>
2060   <row>
2061     <entry>circle</entry>
2062     <entry>24 bytes</entry>
2063     <entry><(x,y),r></entry>
2064     <entry>Circle (center and radius)</entry>
2065   </row>
2066 </tbody>
2067 </tgroup>
2068 </table>
2069 </para>
2070
2071 <para>
2072 A rich set of functions and operators is available to perform various geometric
2073 operations such as scaling, translation, rotation, and determining
2074 intersections.
2075 </para>
2076
2077 <sect2>
2078 <title>Point</title>
2079
2080     <para>
2081      Points are the fundamental two-dimensional building block for geometric types.
2082     </para>
2083
2084 <para>
2085 <type>point</type> is specified using the following syntax:
2086
2087 <programlisting>
2088 ( x , y )
2089   x , y
2090 where
2091     x is the x-axis coordinate as a floating point number
2092     y is the y-axis coordinate as a floating point number
2093 </programlisting>
2094 </para>
2095 </sect2>
2096
2097 <sect2>
2098 <title>Line Segment</title>
2099
2100 <para>
2101 Line segments (lseg) are represented by pairs of points.
2102 </para>
2103
2104 <para>
2105 <type>lseg</type> is specified using the following syntax:
2106 <programlisting>
2107 ( ( x1 , y1 ) , ( x2 , y2 ) )
2108   ( x1 , y1 ) , ( x2 , y2 )
2109     x1 , y1   ,   x2 , y2
2110 where
2111     (x1,y1) and (x2,y2) are the endpoints of the segment
2112 </programlisting>
2113 </para>
2114 </sect2>
2115
2116 <sect2>
2117 <title>Box</title>
2118
2119 <para>
2120 Boxes are represented by pairs of points which are opposite
2121 corners of the box.
2122 </para>
2123
2124 <para>
2125      <type>box</type> is specified using the following syntax:
2126
2127 <programlisting>
2128 ( ( x1 , y1 ) , ( x2 , y2 ) )
2129   ( x1 , y1 ) , ( x2 , y2 )
2130     x1 , y1   ,   x2 , y2
2131 where
2132     (x1,y1) and (x2,y2) are opposite corners
2133 </programlisting>
2134
2135 Boxes are output using the first syntax.
2136 The corners are reordered on input to store
2137 the lower left corner first and the upper right corner last.
2138 Other corners of the box can be entered, but the lower
2139 left and upper right corners are determined from the input and stored.
2140 </para>
2141 </sect2>
2142
2143 <sect2>
2144 <title>Path</title>
2145
2146 <para>
2147 Paths are represented by connected sets of points. Paths can be "open", where
2148 the first and last points in the set are not connected, and "closed",
2149 where the first and last point are connected. Functions
2150 <function>popen(p)</function>
2151 and
2152 <function>pclose(p)</function>
2153 are supplied to force a path to be open or closed, and functions
2154 <function>isopen(p)</function>
2155 and
2156 <function>isclosed(p)</function>
2157 are supplied to select either type in a query.
2158 </para>
2159
2160 <para>
2161 path is specified using the following syntax:
2162
2163 <programlisting>
2164 ( ( x1 , y1 ) , ... , ( xn , yn ) )
2165 [ ( x1 , y1 ) , ... , ( xn , yn ) ]
2166   ( x1 , y1 ) , ... , ( xn , yn )
2167   ( x1 , y1   , ... ,   xn , yn )
2168     x1 , y1   , ... ,   xn , yn
2169 where
2170     (x1,y1),...,(xn,yn) are points 1 through n
2171     a leading "[" indicates an open path
2172     a leading "(" indicates a closed path
2173 </programlisting>
2174 Paths are output using the first syntax.
2175 Note that <productname>Postgres</productname> versions prior to
2176 v6.1 used a format for paths which had a single leading parenthesis,
2177 a "closed" flag,
2178 an integer count of the number of points, then the list of points followed by a
2179 closing parenthesis.
2180 The built-in function <function>upgradepath</function> is supplied to convert
2181 paths dumped and reloaded from pre-v6.1 databases.
2182 </para>
2183 </sect2>
2184
2185 <sect2>
2186 <title>Polygon</title>
2187
2188 <para>
2189 Polygons are represented by sets of points. Polygons should probably be
2190 considered equivalent to closed paths, but are stored differently
2191 and have their own set of support routines.
2192 </para>
2193
2194 <para>
2195 <type>polygon</type> is specified using the following syntax:
2196
2197 <programlisting>
2198 ( ( x1 , y1 ) , ... , ( xn , yn ) )
2199   ( x1 , y1 ) , ... , ( xn , yn )
2200   ( x1 , y1   , ... ,   xn , yn )
2201     x1 , y1   , ... ,   xn , yn
2202 where
2203     (x1,y1),...,(xn,yn) are points 1 through n
2204 </programlisting>
2205
2206 Polygons are output using the first syntax.
2207 Note that <productname>Postgres</productname> versions prior to
2208 v6.1 used a format for polygons which had a single leading parenthesis, the list
2209 of x-axis coordinates, the list of y-axis coordinates,
2210 followed by a closing parenthesis.
2211 The built-in function <function>upgradepoly</function> is supplied to convert
2212 polygons dumped and reloaded from pre-v6.1 databases.
2213 </para>
2214 </sect2>
2215
2216 <sect2>
2217 <title>Circle</title>
2218
2219 <para>
2220 Circles are represented by a center point and a radius.
2221 </para>
2222
2223 <para>
2224 circle is specified using the following syntax:
2225
2226 <programlisting>
2227 < ( x , y ) , r >
2228 ( ( x , y ) , r )
2229   ( x , y ) , r
2230     x , y   , r
2231 where
2232     (x,y) is the center of the circle
2233     r is the radius of the circle
2234 </programlisting>
2235
2236 Circles are output using the first syntax.
2237 </para>
2238 </sect2>
2239
2240 </sect1>
2241
2242 <sect1>
2243 <title>IP Version 4 Networks and Host Addresses</title>
2244
2245 <para>
2246 The <type>cidr</type> type stores networks specified
2247 in <acronym>CIDR</acronym> (Classless Inter-Domain Routing) notation.
2248 The <type>inet</type> type stores hosts and networks in CIDR notation using a simple
2249 variation in representation to represent simple host TCP/IP addresses.
2250 </para>
2251
2252 <para>
2253 <table tocentry="1">
2254 <title><productname>Postgres</productname>IP Version 4 Types</title>
2255 <titleabbrev>IPV4</titleabbrev>
2256 <tgroup cols="4">
2257 <thead>
2258   <row>
2259     <entry>IPV4 Type</entry>
2260     <entry>Storage</entry>
2261     <entry>Description</entry>
2262     <entry>Range</entry>
2263   </row>
2264 </thead>
2265 <tbody>
2266   <row>
2267     <entry>cidr</entry>
2268     <entry>variable</entry>
2269     <entry>CIDR networks</entry>
2270     <entry>Valid IPV4 CIDR blocks</entry>
2271   </row>
2272   <row>
2273     <entry>inet</entry>
2274     <entry>variable</entry>
2275     <entry>nets and hosts</entry>
2276     <entry>Valid IPV4 CIDR blocks</entry>
2277   </row>
2278 </tbody>
2279 </tgroup>
2280 </table>
2281 </para>
2282
2283 <sect2>
2284 <title>CIDR</title>
2285
2286 <para>
2287 The <type>cidr</type> type holds a CIDR network.
2288 The format for specifying classless networks is <replaceable class="parameter">x.x.x.x/y</replaceable>
2289  where <replaceable class="parameter">x.x.x.x</replaceable> is the
2290 network and <replaceable class="parameter">/y</replaceable> is the number of bits in the netmask.
2291 If <replaceable class="parameter">/y</replaceable> omitted, it is calculated using assumptions from
2292 the older classfull naming system except that it is extended to include at least
2293 all of the octets in the input.
2294 </para>
2295
2296 <para>
2297 Here are some examples:
2298
2299      <table tocentry="1">
2300       <title><productname>Postgres</productname>IP Types Examples</title>
2301       <tgroup cols="2">
2302        <thead>
2303         <row>
2304          <entry>CIDR Input</entry>
2305          <entry>CIDR Displayed</entry>
2306         </row>
2307        </thead>
2308        <tbody>
2309         <row>
2310          <entry>192.168.1</entry>
2311          <entry>192.168.1/24</entry>
2312         </row>
2313         <row>
2314          <entry>192.168</entry>
2315          <entry>192.168.0/24</entry>
2316         </row>
2317         <row>
2318          <entry>128.1</entry>
2319          <entry>128.1/16</entry>
2320         </row>
2321         <row>
2322          <entry>128</entry>
2323          <entry>128.0/16</entry>
2324         </row>
2325         <row>
2326          <entry>128.1.2</entry>
2327          <entry>128.1.2/24</entry>
2328         </row>
2329         <row>
2330          <entry>10.1.2</entry>
2331          <entry>10.1.2/24</entry>
2332         </row>
2333         <row>
2334          <entry>10.1</entry>
2335          <entry>10.1/16</entry>
2336         </row>
2337         <row>
2338          <entry>10</entry>
2339          <entry>10/8</entry>
2340         </row>
2341        </tbody>
2342       </tgroup>
2343      </table>
2344     </para>
2345    </sect2>
2346
2347    <sect2>
2348     <title id="inet-type"><type>inet</type></title>
2349
2350 <para>
2351 The <type>inet</type> type is designed to hold, in one field, all of the information
2352 about a host including the CIDR-style subnet that it is in.
2353 Note that if you want to store proper CIDR networks,
2354 you should use the <type>cidr</type> type.
2355 The <type>inet</type> type is similar to the <type>cidr</type> type except that the bits in the
2356 host part can be non-zero.
2357 Functions exist to extract the various elements of the field.
2358 </para>
2359
2360 <para>
2361      The input format for this function is
2362  <replaceable class="parameter">x.x.x.x/y</replaceable>
2363 where <replaceable class="parameter">x.x.x.x</replaceable> is
2364 an internet host and <replaceable class="parameter">y</replaceable>
2365 is the number of bits in the netmask.
2366 If the <replaceable class="parameter">/y</replaceable> part is left off,
2367 it is treated as <literal>/32</literal>.
2368 On output, the <replaceable class="parameter">/y</replaceable> part is not printed
2369 if it is <literal>/32</literal>.
2370 This allows the type to be used as a straight host type by just leaving off
2371 the bits part.
2372 </para></sect2>
2373 </sect1>
2374
2375 </chapter>
2376
2377 <!-- Keep this comment at the end of the file
2378 Local variables:
2379 mode: sgml
2380 sgml-omittag:t
2381 sgml-shorttag:t
2382 sgml-minimize-attributes:nil
2383 sgml-always-quote-attributes:t
2384 sgml-indent-step:1
2385 sgml-indent-data:t
2386 sgml-parent-document:nil
2387 sgml-default-dtd-file:"./reference.ced"
2388 sgml-exposed-tags:nil
2389 sgml-local-catalogs:"/usr/lib/sgml/catalog"
2390 sgml-local-ecat-files:nil
2391 End:
2392 -->