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

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.120 2002/09/02 05:53:23 momjian Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6  <chapter id="functions">
   7   <title>Functions and Operators</title>
   8
   9   <indexterm zone="functions">
  10    <primary>functions</primary>
  11   </indexterm>
  12
  13   <indexterm zone="functions">
  14    <primary>operators</primary>
  15   </indexterm>
  16
  17   <para>
  18    <productname>PostgreSQL</productname> provides a large number of
  19    functions and operators for the built-in data types.  Users can also
  20    define their own functions and operators, as described in the
  21    <citetitle>Programmer's Guide</citetitle>.  The
  22    <application>psql</application> commands <command>\df</command> and
  23    <command>\do</command> can be used to show the list of all actually
  24    available functions and operators, respectively.
  25   </para>
  26
  27   <para>
  28    If you are concerned about portability then take note that most of
  29    the functions and operators described in this chapter, with the
  30    exception of the most trivial arithmetic and comparison operators
  31    and some explicitly marked functions, are not specified by the
  32    <acronym>SQL</acronym>
  33    standard. Some of this extended functionality is present in other
  34    <acronym>RDBMS</acronym> products, and in many cases this
  35    functionality is compatible and consistent between various products.
  36   </para>
  37
  38
  39   <sect1 id="functions-logical">
  40    <title>Logical Operators</title>
  41
  42    <indexterm zone="functions-logical">
  43     <primary>operators</primary>
  44     <secondary>logical</secondary>
  45    </indexterm>
  46
  47    <indexterm>
  48     <primary>Boolean</primary>
  49     <secondary>operators</secondary>
  50     <see>operators, logical</see>
  51    </indexterm>
  52
  53    <para>
  54     The usual logical operators are available:
  55
  56     <indexterm>
  57      <primary>and</primary>
  58      <secondary>operator</secondary>
  59     </indexterm>
  60
  61     <indexterm>
  62      <primary>or</primary>
  63      <secondary>operator</secondary>
  64     </indexterm>
  65
  66     <indexterm>
  67      <primary>not</primary>
  68      <secondary>operator</secondary>
  69     </indexterm>
  70
  71     <simplelist>
  72      <member>AND</member>
  73      <member>OR</member>
  74      <member>NOT</member>
  75     </simplelist>
  76
  77     <acronym>SQL</acronym> uses a three-valued Boolean logic where NULL represents
  78     <quote>unknown</quote>.  Observe the following truth tables:
  79
  80     <informaltable>
  81      <tgroup cols="4">
  82       <thead>
  83        <row>
  84         <entry><replaceable>a</replaceable></entry>
  85         <entry><replaceable>b</replaceable></entry>
  86         <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
  87         <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
  88        </row>
  89       </thead>
  90
  91       <tbody>
  92        <row>
  93         <entry>TRUE</entry>
  94         <entry>TRUE</entry>
  95         <entry>TRUE</entry>
  96         <entry>TRUE</entry>
  97        </row>
  98
  99        <row>
 100         <entry>TRUE</entry>
 101         <entry>FALSE</entry>
 102         <entry>FALSE</entry>
 103         <entry>TRUE</entry>
 104        </row>
 105
 106        <row>
 107         <entry>TRUE</entry>
 108         <entry>NULL</entry>
 109         <entry>NULL</entry>
 110         <entry>TRUE</entry>
 111        </row>
 112
 113        <row>
 114         <entry>FALSE</entry>
 115         <entry>FALSE</entry>
 116         <entry>FALSE</entry>
 117         <entry>FALSE</entry>
 118        </row>
 119
 120        <row>
 121         <entry>FALSE</entry>
 122         <entry>NULL</entry>
 123         <entry>FALSE</entry>
 124         <entry>NULL</entry>
 125        </row>
 126
 127        <row>
 128         <entry>NULL</entry>
 129         <entry>NULL</entry>
 130         <entry>NULL</entry>
 131         <entry>NULL</entry>
 132        </row>
 133       </tbody>
 134      </tgroup>
 135     </informaltable>
 136
 137     <informaltable>
 138      <tgroup cols="2">
 139       <thead>
 140        <row>
 141         <entry><replaceable>a</replaceable></entry>
 142         <entry>NOT <replaceable>a</replaceable></entry>
 143        </row>
 144       </thead>
 145
 146       <tbody>
 147        <row>
 148         <entry>TRUE</entry>
 149         <entry>FALSE</entry>
 150        </row>
 151
 152        <row>
 153         <entry>FALSE</entry>
 154         <entry>TRUE</entry>
 155        </row>
 156
 157        <row>
 158         <entry>NULL</entry>
 159         <entry>NULL</entry>
 160        </row>
 161       </tbody>
 162      </tgroup>
 163     </informaltable>
 164    </para>
 165   </sect1>
 166
 167   <sect1 id="functions-comparison">
 168    <title>Comparison Operators</title>
 169
 170    <indexterm zone="functions-comparison">
 171     <primary>comparison</primary>
 172     <secondary>operators</secondary>
 173    </indexterm>
 174
 175    <table>
 176     <title>Comparison Operators</title>
 177     <tgroup cols="2">
 178      <thead>
 179       <row>
 180        <entry>Operator</entry>
 181        <entry>Description</entry>
 182       </row>
 183      </thead>
 184
 185      <tbody>
 186       <row>
 187        <entry> <literal>&lt;</literal> </entry>
 188        <entry>less than</entry>
 189       </row>
 190
 191       <row>
 192        <entry> <literal>&gt;</literal> </entry>
 193        <entry>greater than</entry>
 194       </row>
 195
 196       <row>
 197        <entry> <literal>&lt;=</literal> </entry>
 198        <entry>less than or equal to</entry>
 199       </row>
 200
 201       <row>
 202        <entry> <literal>&gt;=</literal> </entry>
 203        <entry>greater than or equal to</entry>
 204       </row>
 205
 206       <row>
 207        <entry> <literal>=</literal> </entry>
 208        <entry>equal</entry>
 209       </row>
 210
 211       <row>
 212        <entry> <literal>&lt;&gt;</literal> or <literal>!=</literal> </entry>
 213        <entry>not equal</entry>
 214       </row>
 215      </tbody>
 216     </tgroup>
 217    </table>
 218
 219    <note>
 220     <para>
 221      The <literal>!=</literal> operator is converted to
 222      <literal>&lt;&gt;</literal> in the parser stage.  It is not
 223      possible to implement <literal>!=</literal> and
 224      <literal>&lt;&gt;</literal> operators that do different things.
 225     </para>
 226    </note>
 227
 228    <para>
 229     Comparison operators are available for all data types where this
 230     makes sense.  All comparison operators are binary operators that
 231     return values of type <type>boolean</type>; expressions like
 232     <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is
 233     no <literal>&lt;</literal> operator to compare a Boolean value with
 234     <literal>3</literal>).
 235    </para>
 236
 237    <para>
 238     <indexterm>
 239      <primary>between</primary>
 240     </indexterm>
 241     In addition to the comparison operators, the special
 242     <token>BETWEEN</token> construct is available.
 243     <synopsis>
 244 <replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
 245     </synopsis>
 246     is equivalent to
 247     <synopsis>
 248 <replaceable>a</replaceable> &gt;= <replaceable>x</replaceable> AND <replaceable>a</replaceable> &lt;= <replaceable>y</replaceable>
 249     </synopsis>
 250     Similarly,
 251     <synopsis>
 252 <replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
 253     </synopsis>
 254     is equivalent to
 255     <synopsis>
 256 <replaceable>a</replaceable> &lt; <replaceable>x</replaceable> OR <replaceable>a</replaceable> &gt; <replaceable>y</replaceable>
 257     </synopsis>
 258     There is no difference between the two respective forms apart from
 259     the <acronym>CPU</acronym> cycles required to rewrite the first one
 260     into the second one internally.
 261    </para>
 262
 263    <para>
 264     To check whether a value is or is not NULL, use the constructs
 265     <synopsis>
 266 <replaceable>expression</replaceable> IS NULL
 267 <replaceable>expression</replaceable> IS NOT NULL
 268     </synopsis>
 269     or the equivalent, but less standard, constructs
 270     <synopsis>
 271 <replaceable>expression</replaceable> ISNULL
 272 <replaceable>expression</replaceable> NOTNULL
 273     </synopsis>
 274    </para>
 275
 276    <para>
 277     Do <emphasis>not</emphasis> write
 278     <literal><replaceable>expression</replaceable> = NULL</literal>
 279     because NULL is not <quote>equal to</quote> NULL.  (NULL represents
 280     an unknown value, and it is not known whether two unknown values are
 281     equal.)
 282    </para>
 283
 284    <para>
 285     Some applications may (incorrectly) require that
 286     <literal><replaceable>expression</replaceable> = NULL</literal>
 287     returns true if <replaceable>expression</replaceable> evaluates to
 288     the NULL value.  To support these applications, the run-time option
 289     <varname>transform_null_equals</varname> can be turned on (e.g.,
 290     <literal>SET transform_null_equals TO ON;</literal>).
 291     <productname>PostgreSQL</productname> will then convert
 292     <literal>x = NULL</literal> clauses to
 293     <literal>x IS NULL</literal>.  This was
 294     the default behavior in releases 6.5 through 7.1.
 295    </para>
 296
 297    <para>
 298     Boolean values can also be tested using the constructs
 299     <synopsis>
 300 <replaceable>expression</replaceable> IS TRUE
 301 <replaceable>expression</replaceable> IS NOT TRUE
 302 <replaceable>expression</replaceable> IS FALSE
 303 <replaceable>expression</replaceable> IS NOT FALSE
 304 <replaceable>expression</replaceable> IS UNKNOWN
 305 <replaceable>expression</replaceable> IS NOT UNKNOWN
 306     </synopsis>
 307     These are similar to <literal>IS NULL</literal> in that they will
 308     always return TRUE or FALSE, never NULL, even when the operand is NULL.
 309     A NULL input is treated as the logical value UNKNOWN.
 310    </para>
 311   </sect1>
 312
 313   <sect1 id="functions-math">
 314    <title>Mathematical Functions and Operators</title>
 315
 316    <para>
 317     Mathematical operators are provided for many
 318     <productname>PostgreSQL</productname> types. For types without
 319     common mathematical conventions for all possible permutations
 320     (e.g. date/time types) we
 321     describe the actual behavior in subsequent sections.
 322    </para>
 323
 324    <table>
 325     <title>Mathematical Operators</title>
 326
 327     <tgroup cols="4">
 328      <thead>
 329       <row>
 330        <entry>Name</entry>
 331        <entry>Description</entry>
 332        <entry>Example</entry>
 333        <entry>Result</entry>
 334       </row>
 335      </thead>
 336
 337      <tbody>
 338       <row>
 339        <entry> <literal>+</literal> </entry>
 340        <entry>Addition</entry>
 341        <entry>2 + 3</entry>
 342        <entry>5</entry>
 343       </row>
 344
 345       <row>
 346        <entry> <literal>-</literal> </entry>
 347        <entry>Subtraction</entry>
 348        <entry>2 - 3</entry>
 349        <entry>-1</entry>
 350       </row>
 351
 352       <row>
 353        <entry> <literal>*</literal> </entry>
 354        <entry>Multiplication</entry>
 355        <entry>2 * 3</entry>
 356        <entry>6</entry>
 357       </row>
 358
 359       <row>
 360        <entry> <literal>/</literal> </entry>
 361        <entry>Division (integer division truncates results)</entry>
 362        <entry>4 / 2</entry>
 363        <entry>2</entry>
 364       </row>
 365
 366       <row>
 367        <entry> <literal>%</literal> </entry>
 368        <entry>Modulo (remainder)</entry>
 369        <entry>5 % 4</entry>
 370        <entry>1</entry>
 371       </row>
 372
 373       <row>
 374        <entry> <literal>^</literal> </entry>
 375        <entry>Exponentiation</entry>
 376        <entry>2.0 ^ 3.0</entry>
 377        <entry>8</entry>
 378       </row>
 379
 380       <row>
 381        <entry> <literal>|/</literal> </entry>
 382        <entry>Square root</entry>
 383        <entry>|/ 25.0</entry>
 384        <entry>5</entry>
 385       </row>
 386
 387       <row>
 388        <entry> <literal>||/</literal> </entry>
 389        <entry>Cube root</entry>
 390        <entry>||/ 27.0</entry>
 391        <entry>3</entry>
 392       </row>
 393
 394       <row>
 395        <entry> <literal>!</literal> </entry>
 396        <entry>Factorial</entry>
 397        <entry>5 !</entry>
 398        <entry>120</entry>
 399       </row>
 400
 401       <row>
 402        <entry> <literal>!!</literal> </entry>
 403        <entry>Factorial (prefix operator)</entry>
 404        <entry>!! 5</entry>
 405        <entry>120</entry>
 406       </row>
 407
 408       <row>
 409        <entry> <literal>@</literal> </entry>
 410        <entry>Absolute value</entry>
 411        <entry>@ -5.0</entry>
 412        <entry>5</entry>
 413       </row>
 414
 415       <row>
 416        <entry> <literal>&amp;</literal> </entry>
 417        <entry>Binary AND</entry>
 418        <entry>91 & 15</entry>
 419        <entry>11</entry>
 420       </row>
 421
 422       <row>
 423        <entry> <literal>|</literal> </entry>
 424        <entry>Binary OR</entry>
 425        <entry>32 | 3</entry>
 426        <entry>35</entry>
 427       </row>
 428
 429       <row>
 430        <entry> <literal>#</literal> </entry>
 431        <entry>Binary XOR</entry>
 432        <entry>17 # 5</entry>
 433        <entry>20</entry>
 434       </row>
 435
 436       <row>
 437        <entry> <literal>~</literal> </entry>
 438        <entry>Binary NOT</entry>
 439        <entry>~1</entry>
 440        <entry>-2</entry>
 441       </row>
 442
 443       <row>
 444        <entry> &lt;&lt; </entry>
 445        <entry>Binary shift left</entry>
 446        <entry>1 &lt;&lt; 4</entry>
 447        <entry>16</entry>
 448       </row>
 449
 450       <row>
 451        <entry> &gt;&gt; </entry>
 452        <entry>Binary shift right</entry>
 453        <entry>8 &gt;&gt; 2</entry>
 454        <entry>2</entry>
 455       </row>
 456
 457      </tbody>
 458     </tgroup>
 459    </table>
 460
 461    <para>
 462     The <quote>binary</quote> operators are also available for the bit
 463     string types <type>BIT</type> and <type>BIT VARYING</type>.
 464
 465     <table>
 466      <title>Bit String Binary Operators</title>
 467
 468      <tgroup cols="2">
 469       <thead>
 470        <row>
 471         <entry>Example</entry>
 472         <entry>Result</entry>
 473        </row>
 474       </thead>
 475
 476       <tbody>
 477        <row>
 478         <entry>B'10001' & B'01101'</entry>
 479         <entry>00001</entry>
 480        </row>
 481        <row>
 482         <entry>B'10001' | B'01101'</entry>
 483         <entry>11101</entry>
 484        </row>
 485        <row>
 486         <entry>B'10001' # B'01101'</entry>
 487         <entry>11110</entry>
 488        </row>
 489        <row>
 490         <entry>~ B'10001'</entry>
 491         <entry>01110</entry>
 492        </row>
 493        <row>
 494         <entry>B'10001' << 3</entry>
 495         <entry>01000</entry>
 496        </row>
 497        <row>
 498         <entry>B'10001' >> 2</entry>
 499         <entry>00100</entry>
 500        </row>
 501       </tbody>
 502      </tgroup>
 503     </table>
 504
 505     Bit string arguments to <literal>&</literal>, <literal>|</literal>,
 506     and <literal>#</literal> must be of equal length.  When bit
 507     shifting, the original length of the string is preserved, as shown
 508     here.
 509    </para>
 510
 511    <table tocentry="1">
 512     <title>Mathematical Functions</title>
 513     <tgroup cols="5">
 514      <thead>
 515       <row>
 516        <entry>Function</entry>
 517        <entry>Return Type</entry>
 518        <entry>Description</entry>
 519        <entry>Example</entry>
 520        <entry>Result</entry>
 521       </row>
 522      </thead>
 523
 524      <tbody>
 525       <row>
 526        <entry><function>abs</function>(<replaceable>x</replaceable>)</entry>
 527        <entry>(same as x)</entry>
 528        <entry>absolute value</entry>
 529        <entry><literal>abs(-17.4)</literal></entry>
 530        <entry>17.4</entry>
 531       </row>
 532
 533       <row>
 534        <entry><function>cbrt</function>(<type>dp</type>)</entry>
 535        <entry><type>dp</type></entry>
 536        <entry>cube root</entry>
 537        <entry><literal>cbrt(27.0)</literal></entry>
 538        <entry>3</entry>
 539       </row>
 540
 541       <row>
 542        <entry><function>ceil</function>(<type>numeric</type>)</entry>
 543        <entry><type>numeric</type></entry>
 544        <entry>smallest integer not less than argument</entry>
 545        <entry><literal>ceil(-42.8)</literal></entry>
 546        <entry>-42</entry>
 547       </row>
 548
 549       <row>
 550        <entry><function>degrees</function>(<type>dp</type>)</entry>
 551        <entry><type>dp</type></entry>
 552        <entry>radians to degrees</entry>
 553        <entry><literal>degrees(0.5)</literal></entry>
 554        <entry>28.6478897565412</entry>
 555       </row>
 556
 557       <row>
 558        <entry><function>exp</function>(<type>dp</type>)</entry>
 559        <entry><type>dp</type></entry>
 560        <entry>exponential</entry>
 561        <entry><literal>exp(1.0)</literal></entry>
 562        <entry>2.71828182845905</entry>
 563       </row>
 564
 565       <row>
 566        <entry><function>floor</function>(<type>numeric</type>)</entry>
 567        <entry><type>numeric</type></entry>
 568        <entry>largest integer not greater than argument</entry>
 569        <entry><literal>floor(-42.8)</literal></entry>
 570        <entry>-43</entry>
 571       </row>
 572
 573       <row>
 574        <entry><function>ln</function>(<type>dp</type>)</entry>
 575        <entry><type>dp</type></entry>
 576        <entry>natural logarithm</entry>
 577        <entry><literal>ln(2.0)</literal></entry>
 578        <entry>0.693147180559945</entry>
 579       </row>
 580
 581       <row>
 582        <entry><function>log</function>(<type>dp</type>)</entry>
 583        <entry><type>dp</type></entry>
 584        <entry>base 10 logarithm</entry>
 585        <entry><literal>log(100.0)</literal></entry>
 586        <entry>2</entry>
 587       </row>
 588
 589       <row>
 590        <entry><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
 591         <parameter>x</parameter> <type>numeric</type>)</entry>
 592        <entry><type>numeric</type></entry>
 593        <entry>logarithm to base <parameter>b</parameter></entry>
 594        <entry><literal>log(2.0, 64.0)</literal></entry>
 595        <entry>6.0000000000</entry>
 596       </row>
 597
 598       <row>
 599        <entry><function>mod</function>(<parameter>y</parameter>,
 600         <parameter>x</parameter>)</entry>
 601        <entry>(same as argument types)</entry>
 602        <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
 603        <entry><literal>mod(9,4)</literal></entry>
 604        <entry>1</entry>
 605       </row>
 606
 607       <row>
 608        <entry><function>pi</function>()</entry>
 609        <entry><type>dp</type></entry>
 610        <entry><quote>Pi</quote> constant</entry>
 611        <entry><literal>pi()</literal></entry>
 612        <entry>3.14159265358979</entry>
 613       </row>
 614
 615       <row>
 616        <entry><function>pow</function>(<parameter>e</parameter> <type>dp</type>,
 617         <parameter>n</parameter> <type>dp</type>)</entry>
 618        <entry><type>dp</type></entry>
 619        <entry>raise a number to exponent <parameter>e</parameter></entry>
 620        <entry><literal>pow(9.0, 3.0)</literal></entry>
 621        <entry>729</entry>
 622       </row>
 623
 624       <row>
 625        <entry><function>radians</function>(<type>dp</type>)</entry>
 626        <entry><type>dp</type></entry>
 627        <entry>degrees to radians</entry>
 628        <entry><literal>radians(45.0)</literal></entry>
 629        <entry>0.785398163397448</entry>
 630       </row>
 631
 632       <row>
 633        <entry><function>random</function>()</entry>
 634        <entry><type>dp</type></entry>
 635        <entry>value between 0.0 to 1.0</entry>
 636        <entry><literal>random()</literal></entry>
 637        <entry></entry>
 638       </row>
 639
 640       <row>
 641        <entry><function>round</function>(<type>dp</type>)</entry>
 642        <entry><type>dp</type></entry>
 643        <entry>round to nearest integer</entry>
 644        <entry><literal>round(42.4)</literal></entry>
 645        <entry>42</entry>
 646       </row>
 647
 648       <row>
 649        <entry><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
 650        <entry><type>numeric</type></entry>
 651        <entry>round to <parameter>s</parameter> decimal places</entry>
 652        <entry><literal>round(42.4382, 2)</literal></entry>
 653        <entry>42.44</entry>
 654       </row>
 655 <!--
 656      <row>
 657       <entry><function>setseed</function>(<replaceable>new-seed</replaceable>)</entry>
 658       <entry>set seed for subsequent random() calls</entry>
 659       <entry><literal>setseed(0.54823)</literal></entry>
 660       <entry></entry>
 661      </row>
 662 -->
 663       <row>
 664        <entry><function>sign</function>(<type>numeric</type>)</entry>
 665        <entry><type>numeric</type></entry>
 666        <entry>sign of the argument (-1, 0, +1)</entry>
 667        <entry><literal>sign(-8.4)</literal></entry>
 668        <entry>-1</entry>
 669       </row>
 670
 671       <row>
 672        <entry><function>sqrt</function>(<type>dp</type>)</entry>
 673        <entry><type>dp</type></entry>
 674        <entry>square root</entry>
 675        <entry><literal>sqrt(2.0)</literal></entry>
 676        <entry>1.4142135623731</entry>
 677       </row>
 678
 679       <row>
 680        <entry><function>trunc</function>(<type>dp</type>)</entry>
 681        <entry><type>dp</type></entry>
 682        <entry>truncate toward zero</entry>
 683        <entry><literal>trunc(42.8)</literal></entry>
 684        <entry>42</entry>
 685       </row>
 686
 687       <row>
 688        <entry><function>trunc</function>(<type>numeric</type>,
 689         <parameter>r</parameter> <type>integer</type>)</entry>
 690        <entry><type>numeric</type></entry>
 691        <entry>truncate to <parameter>s</parameter> decimal places</entry>
 692        <entry><literal>trunc(42.4382, 2)</literal></entry>
 693        <entry>42.43</entry>
 694       </row>
 695
 696      </tbody>
 697     </tgroup>
 698    </table>
 699
 700    <para>
 701     In the table above, <literal>dp</literal> indicates <type>double precision</type>.
 702     The functions <function>exp</function>, <function>ln</function>,
 703     <function>log</function>, <function>pow</function>,
 704     <function>round</function> (1 argument), <function>sqrt</function>,
 705     and <function>trunc</function> (1 argument) are also available for
 706     the type <type>numeric</type> in place of
 707     <type>double precision</type>.
 708     Functions returning a <type>numeric</type> result take
 709     <type>numeric</type> input arguments, unless otherwise specified.
 710     Many of these functions are implemented on top
 711     of the host system's C library; accuracy and behavior in boundary cases
 712     could therefore vary depending on the host system.
 713    </para>
 714
 715    <table>
 716     <title>Trigonometric Functions</title>
 717
 718     <tgroup cols="2">
 719      <thead>
 720       <row>
 721        <entry>Function</entry>
 722        <entry>Description</entry>
 723       </row>
 724      </thead>
 725
 726      <tbody>
 727       <row>
 728        <entry><function>acos</function>(<replaceable>x</replaceable>)</entry>
 729        <entry>inverse cosine</entry>
 730       </row>
 731
 732       <row>
 733        <entry><function>asin</function>(<replaceable>x</replaceable>)</entry>
 734        <entry>inverse sine</entry>
 735       </row>
 736
 737       <row>
 738        <entry><function>atan</function>(<replaceable>x</replaceable>)</entry>
 739        <entry>inverse tangent</entry>
 740       </row>
 741
 742       <row>
 743        <entry><function>atan2</function>(<replaceable>x</replaceable>,
 744         <replaceable>y</replaceable>)</entry>
 745        <entry>inverse tangent of
 746         <replaceable>a</replaceable>/<replaceable>x</replaceable></entry>
 747       </row>
 748
 749       <row>
 750        <entry><function>cos</function>(<replaceable>x</replaceable>)</entry>
 751        <entry>cosine</entry>
 752       </row>
 753
 754       <row>
 755        <entry><function>cot</function>(<replaceable>x</replaceable>)</entry>
 756        <entry>cotangent</entry>
 757       </row>
 758
 759       <row>
 760        <entry><function>sin</function>(<replaceable>x</replaceable>)</entry>
 761        <entry>sine</entry>
 762       </row>
 763
 764       <row>
 765        <entry><function>tan</function>(<replaceable>x</replaceable>)</entry>
 766        <entry>tangent</entry>
 767       </row>
 768      </tbody>
 769     </tgroup>
 770    </table>
 771
 772    <para>
 773     All trigonometric functions have arguments and return values of
 774     type <type>double precision</type>.
 775    </para>
 776
 777   </sect1>
 778
 779
 780   <sect1 id="functions-string">
 781    <title>String Functions and Operators</title>
 782
 783    <para>
 784     This section describes functions and operators for examining and
 785     manipulating string values.  Strings in this context include values
 786     of all the types <type>CHARACTER</type>, <type>CHARACTER
 787      VARYING</type>, and <type>TEXT</type>.  Unless otherwise noted, all
 788     of the functions listed below work on all of these types, but be
 789     wary of potential effects of the automatic padding when using the
 790     <type>CHARACTER</type> type.  Generally, the functions described
 791     here also work on data of non-string types by converting that data
 792     to a string representation first.  Some functions also exist
 793     natively for bit-string types.
 794    </para>
 795
 796    <para>
 797     <acronym>SQL</acronym> defines some string functions with a special syntax where
 798     certain keywords rather than commas are used to separate the
 799     arguments.  Details are in <xref linkend="functions-string-sql">.
 800     These functions are also implemented using the regular syntax for
 801     function invocation.  (See <xref linkend="functions-string-other">.)
 802    </para>
 803
 804    <table id="functions-string-sql">
 805     <title><acronym>SQL</acronym> String Functions and Operators</title>
 806     <tgroup cols="5">
 807      <thead>
 808       <row>
 809        <entry>Function</entry>
 810        <entry>Return Type</entry>
 811        <entry>Description</entry>
 812        <entry>Example</entry>
 813        <entry>Result</entry>
 814       </row>
 815      </thead>
 816
 817      <tbody>
 818       <row>
 819        <entry> <parameter>string</parameter> <literal>||</literal>
 820         <parameter>string</parameter> </entry>
 821        <entry> <type>text</type> </entry>
 822        <entry>
 823         string concatenation
 824         <indexterm>
 825          <primary>character strings</primary>
 826          <secondary>concatenation</secondary>
 827         </indexterm>
 828        </entry>
 829        <entry><literal>'Postgre' || 'SQL'</literal></entry>
 830        <entry><literal>PostgreSQL</literal></entry>
 831       </row>
 832
 833       <row>
 834        <entry><function>bit_length</function>(<parameter>string</parameter>)</entry>
 835        <entry><type>integer</type></entry>
 836        <entry>number of bits in string</entry>
 837        <entry><literal>bit_length('jose')</literal></entry>
 838        <entry><literal>32</literal></entry>
 839       </row>
 840
 841       <row>
 842        <entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>
 843        <entry><type>integer</type></entry>
 844        <entry>
 845         number of characters in string
 846         <indexterm>
 847          <primary>character strings</primary>
 848          <secondary>length</secondary>
 849         </indexterm>
 850         <indexterm>
 851          <primary>length</primary>
 852          <secondary>character strings</secondary>
 853          <see>character strings, length</see>
 854         </indexterm>
 855        </entry>
 856        <entry><literal>char_length('jose')</literal></entry>
 857        <entry><literal>4</literal></entry>
 858       </row>
 859
 860       <row>
 861        <entry><function>convert</function>(<parameter>string</parameter>
 862        using <parameter>conversion_name</parameter>)</entry>
 863        <entry><type>text</type></entry>
 864        <entry>Change encoding using specified conversion name.
 865        Conversions can be defined by <command>CREATE CONVERSION</command>.
 866        Also there are some pre-defined conversion names.
 867        See <xref linkend="conversion-names"> for available
 868        conversion names. </entry>
 869        <entry><literal>convert('PostgreSQL' using iso8859_1_to_utf8)</literal></entry>
 870        <entry><literal>'PostgreSQL' in UNICODE(UTF-8) encoding</literal></entry>
 871       </row>
 872
 873       <row>
 874        <entry><function>lower</function>(<parameter>string</parameter>)</entry>
 875        <entry><type>text</type></entry>
 876        <entry>Convert string to lower case.</entry>
 877        <entry><literal>lower('TOM')</literal></entry>
 878        <entry><literal>tom</literal></entry>
 879       </row>
 880
 881       <row>
 882        <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
 883        <entry><type>integer</type></entry>
 884        <entry>number of bytes in string</entry>
 885        <entry><literal>octet_length('jose')</literal></entry>
 886        <entry><literal>4</literal></entry>
 887       </row>
 888
 889       <row>
 890        <entry><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</entry>
 891        <entry><type>text</type></entry>
 892        <entry>
 893         insert substring
 894         <indexterm>
 895          <primary>overlay</primary>
 896         </indexterm>
 897        </entry>
 898        <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
 899        <entry><literal>Thomas</literal></entry>
 900       </row>
 901
 902       <row>
 903        <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
 904        <entry><type>integer</type></entry>
 905        <entry>location of specified substring</entry>
 906        <entry><literal>position('om' in 'Thomas')</literal></entry>
 907        <entry><literal>3</literal></entry>
 908       </row>
 909
 910       <row>
 911        <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
 912        <entry><type>text</type></entry>
 913        <entry>
 914         extract substring
 915         <indexterm>
 916          <primary>substring</primary>
 917         </indexterm>
 918        </entry>
 919        <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
 920        <entry><literal>hom</literal></entry>
 921       </row>
 922
 923       <row>
 924        <entry><function>substring</function>(<parameter>string</parameter> <optional>from <replaceable>pattern</replaceable></optional> <optional>for <replaceable>escape</replaceable></optional>)</entry>
 925        <entry><type>text</type></entry>
 926        <entry>
 927         extract regular expression
 928         <indexterm>
 929          <primary>substring</primary>
 930         </indexterm>
 931        </entry>
 932        <entry><literal>substring('Thomas' from 'mas$' for <optional>escape '\\'</optional>)</literal></entry>
 933        <entry><literal>mas</literal></entry>
 934       </row>
 935
 936       <row>
 937        <entry>
 938         <function>trim</function>(<optional>leading | trailing | both</optional>
 939         <optional><parameter>characters</parameter></optional> from
 940         <parameter>string</parameter>)
 941        </entry>
 942        <entry><type>text</type></entry>
 943        <entry>
 944         Removes the longest string containing only the
 945         <parameter>characters</parameter> (a space by default) from the
 946         beginning/end/both ends of the <parameter>string</parameter>.
 947        </entry>
 948        <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
 949        <entry><literal>Tom</literal></entry>
 950       </row>
 951
 952       <row>
 953        <entry><function>upper</function>(<parameter>string</parameter>)</entry>
 954        <entry><type>text</type></entry>
 955        <entry>Convert string to upper case.</entry>
 956        <entry><literal>upper('tom')</literal></entry>
 957        <entry><literal>TOM</literal></entry>
 958       </row>
 959      </tbody>
 960     </tgroup>
 961    </table>
 962
 963    <table id="conversion-names">
 964     <title>Available conversion names</title>
 965     <tgroup cols="3">
 966      <thead>
 967       <row>
 968        <entry>conversion</entry>
 969        <entry>source encoding</entry>
 970        <entry>destination encoding</entry>
 971       </row>
 972      </thead>
 973
 974      <tbody>
 975       <row>
 976        <entry>ascii_to_mic</entry>
 977        <entry>SQL_ASCII</entry>
 978        <entry>MULE_INTERNAL</entry>
 979       </row>
 980
 981       <row>
 982        <entry>ascii_to_utf_8</entry>
 983        <entry>SQL_ASCII</entry>
 984        <entry>UNICODE</entry>
 985       </row>
 986
 987       <row>
 988        <entry>big5_to_euc_tw</entry>
 989        <entry>BIG5</entry>
 990        <entry>EUC_TW</entry>
 991       </row>
 992
 993       <row>
 994        <entry>big5_to_mic</entry>
 995        <entry>BIG5</entry>
 996        <entry>MULE_INTERNAL</entry>
 997       </row>
 998
 999       <row>
1000        <entry>big5_to_utf_8</entry>
1001        <entry>BIG5</entry>
1002        <entry>UNICODE</entry>
1003       </row>
1004
1005       <row>
1006        <entry>euc_cn_to_mic</entry>
1007        <entry>EUC_CN</entry>
1008        <entry>MULE_INTERNAL</entry>
1009       </row>
1010
1011       <row>
1012        <entry>euc_cn_to_utf_8</entry>
1013        <entry>EUC_CN</entry>
1014        <entry>UNICODE</entry>
1015       </row>
1016
1017       <row>
1018        <entry>euc_jp_to_mic</entry>
1019        <entry>EUC_JP</entry>
1020        <entry>MULE_INTERNAL</entry>
1021       </row>
1022
1023       <row>
1024        <entry>euc_jp_to_sjis</entry>
1025        <entry>EUC_JP</entry>
1026        <entry>SJIS</entry>
1027       </row>
1028
1029       <row>
1030        <entry>euc_jp_to_utf_8</entry>
1031        <entry>EUC_JP</entry>
1032        <entry>UNICODE</entry>
1033       </row>
1034
1035       <row>
1036        <entry>euc_kr_to_mic</entry>
1037        <entry>EUC_KR</entry>
1038        <entry>MULE_INTERNAL</entry>
1039       </row>
1040
1041       <row>
1042        <entry>euc_kr_to_utf_8</entry>
1043        <entry>EUC_KR</entry>
1044        <entry>UNICODE</entry>
1045       </row>
1046
1047       <row>
1048        <entry>euc_tw_to_big5</entry>
1049        <entry>EUC_TW</entry>
1050        <entry>BIG5</entry>
1051       </row>
1052
1053       <row>
1054        <entry>euc_tw_to_mic</entry>
1055        <entry>EUC_TW</entry>
1056        <entry>MULE_INTERNAL</entry>
1057       </row>
1058
1059       <row>
1060        <entry>euc_tw_to_utf_8</entry>
1061        <entry>EUC_TW</entry>
1062        <entry>UNICODE</entry>
1063       </row>
1064
1065       <row>
1066        <entry>gb18030_to_utf_8</entry>
1067        <entry>GB18030</entry>
1068        <entry>UNICODE</entry>
1069       </row>
1070
1071       <row>
1072        <entry>gbk_to_utf_8</entry>
1073        <entry>GBK</entry>
1074        <entry>UNICODE</entry>
1075       </row>
1076
1077       <row>
1078        <entry>iso_8859_10_to_utf_8</entry>
1079        <entry>LATIN6</entry>
1080        <entry>UNICODE</entry>
1081       </row>
1082
1083       <row>
1084        <entry>iso_8859_13_to_utf_8</entry>
1085        <entry>LATIN7</entry>
1086        <entry>UNICODE</entry>
1087       </row>
1088
1089       <row>
1090        <entry>iso_8859_14_to_utf_8</entry>
1091        <entry>LATIN8</entry>
1092        <entry>UNICODE</entry>
1093       </row>
1094
1095       <row>
1096        <entry>iso_8859_15_to_utf_8</entry>
1097        <entry>LATIN9</entry>
1098        <entry>UNICODE</entry>
1099       </row>
1100
1101       <row>
1102        <entry>iso_8859_16_to_utf_8</entry>
1103        <entry>LATIN10</entry>
1104        <entry>UNICODE</entry>
1105       </row>
1106
1107       <row>
1108        <entry>iso_8859_1_to_mic</entry>
1109        <entry>LATIN1</entry>
1110        <entry>MULE_INTERNAL</entry>
1111       </row>
1112
1113       <row>
1114        <entry>iso_8859_1_to_utf_8</entry>
1115        <entry>LATIN1</entry>
1116        <entry>UNICODE</entry>
1117       </row>
1118
1119       <row>
1120        <entry>iso_8859_2_to_mic</entry>
1121        <entry>LATIN2</entry>
1122        <entry>MULE_INTERNAL</entry>
1123       </row>
1124
1125       <row>
1126        <entry>iso_8859_2_to_utf_8</entry>
1127        <entry>LATIN2</entry>
1128        <entry>UNICODE</entry>
1129       </row>
1130
1131       <row>
1132        <entry>iso_8859_2_to_win1250</entry>
1133        <entry>LATIN2</entry>
1134        <entry>WIN1250</entry>
1135       </row>
1136
1137       <row>
1138        <entry>iso_8859_3_to_mic</entry>
1139        <entry>LATIN3</entry>
1140        <entry>MULE_INTERNAL</entry>
1141       </row>
1142
1143       <row>
1144        <entry>iso_8859_3_to_utf_8</entry>
1145        <entry>LATIN3</entry>
1146        <entry>UNICODE</entry>
1147       </row>
1148
1149       <row>
1150        <entry>iso_8859_4_to_mic</entry>
1151        <entry>LATIN4</entry>
1152        <entry>MULE_INTERNAL</entry>
1153       </row>
1154
1155       <row>
1156        <entry>iso_8859_4_to_utf_8</entry>
1157        <entry>LATIN4</entry>
1158        <entry>UNICODE</entry>
1159       </row>
1160
1161       <row>
1162        <entry>iso_8859_5_to_koi8r</entry>
1163        <entry>ISO_8859_5</entry>
1164        <entry>KOI8</entry>
1165       </row>
1166
1167       <row>
1168        <entry>iso_8859_5_to_mic</entry>
1169        <entry>ISO_8859_5</entry>
1170        <entry>MULE_INTERNAL</entry>
1171       </row>
1172
1173       <row>
1174        <entry>iso_8859_5_to_utf_8</entry>
1175        <entry>ISO_8859_5</entry>
1176        <entry>UNICODE</entry>
1177       </row>
1178
1179       <row>
1180        <entry>iso_8859_5_to_win1251</entry>
1181        <entry>ISO_8859_5</entry>
1182        <entry>WIN</entry>
1183       </row>
1184
1185       <row>
1186        <entry>iso_8859_5_to_win866</entry>
1187        <entry>ISO_8859_5</entry>
1188        <entry>ALT</entry>
1189       </row>
1190
1191       <row>
1192        <entry>iso_8859_6_to_utf_8</entry>
1193        <entry>ISO_8859_6</entry>
1194        <entry>UNICODE</entry>
1195       </row>
1196
1197       <row>
1198        <entry>iso_8859_7_to_utf_8</entry>
1199        <entry>ISO_8859_7</entry>
1200        <entry>UNICODE</entry>
1201       </row>
1202
1203       <row>
1204        <entry>iso_8859_8_to_utf_8</entry>
1205        <entry>ISO_8859_8</entry>
1206        <entry>UNICODE</entry>
1207       </row>
1208
1209       <row>
1210        <entry>iso_8859_9_to_utf_8</entry>
1211        <entry>LATIN5</entry>
1212        <entry>UNICODE</entry>
1213       </row>
1214
1215       <row>
1216        <entry>johab_to_utf_8</entry>
1217        <entry>JOHAB</entry>
1218        <entry>UNICODE</entry>
1219       </row>
1220
1221       <row>
1222        <entry>koi8r_to_iso_8859_5</entry>
1223        <entry>KOI8</entry>
1224        <entry>ISO_8859_5</entry>
1225       </row>
1226
1227       <row>
1228        <entry>koi8r_to_mic</entry>
1229        <entry>KOI8</entry>
1230        <entry>MULE_INTERNAL</entry>
1231       </row>
1232
1233       <row>
1234        <entry>koi8r_to_utf_8</entry>
1235        <entry>KOI8</entry>
1236        <entry>UNICODE</entry>
1237       </row>
1238
1239       <row>
1240        <entry>koi8r_to_win1251</entry>
1241        <entry>KOI8</entry>
1242        <entry>WIN</entry>
1243       </row>
1244
1245       <row>
1246        <entry>koi8r_to_win866</entry>
1247        <entry>KOI8</entry>
1248        <entry>ALT</entry>
1249       </row>
1250
1251       <row>
1252        <entry>mic_to_ascii</entry>
1253        <entry>MULE_INTERNAL</entry>
1254        <entry>SQL_ASCII</entry>
1255       </row>
1256
1257       <row>
1258        <entry>mic_to_big5</entry>
1259        <entry>MULE_INTERNAL</entry>
1260        <entry>BIG5</entry>
1261       </row>
1262
1263       <row>
1264        <entry>mic_to_euc_cn</entry>
1265        <entry>MULE_INTERNAL</entry>
1266        <entry>EUC_CN</entry>
1267       </row>
1268
1269       <row>
1270        <entry>mic_to_euc_jp</entry>
1271        <entry>MULE_INTERNAL</entry>
1272        <entry>EUC_JP</entry>
1273       </row>
1274
1275       <row>
1276        <entry>mic_to_euc_kr</entry>
1277        <entry>MULE_INTERNAL</entry>
1278        <entry>EUC_KR</entry>
1279       </row>
1280
1281       <row>
1282        <entry>mic_to_euc_tw</entry>
1283        <entry>MULE_INTERNAL</entry>
1284        <entry>EUC_TW</entry>
1285       </row>
1286
1287       <row>
1288        <entry>mic_to_iso_8859_1</entry>
1289        <entry>MULE_INTERNAL</entry>
1290        <entry>LATIN1</entry>
1291       </row>
1292
1293       <row>
1294        <entry>mic_to_iso_8859_2</entry>
1295        <entry>MULE_INTERNAL</entry>
1296        <entry>LATIN2</entry>
1297       </row>
1298
1299       <row>
1300        <entry>mic_to_iso_8859_3</entry>
1301        <entry>MULE_INTERNAL</entry>
1302        <entry>LATIN3</entry>
1303       </row>
1304
1305       <row>
1306        <entry>mic_to_iso_8859_4</entry>
1307        <entry>MULE_INTERNAL</entry>
1308        <entry>LATIN4</entry>
1309       </row>
1310
1311       <row>
1312        <entry>mic_to_iso_8859_5</entry>
1313        <entry>MULE_INTERNAL</entry>
1314        <entry>ISO_8859_5</entry>
1315       </row>
1316
1317       <row>
1318        <entry>mic_to_koi8r</entry>
1319        <entry>MULE_INTERNAL</entry>
1320        <entry>KOI8</entry>
1321       </row>
1322
1323       <row>
1324        <entry>mic_to_sjis</entry>
1325        <entry>MULE_INTERNAL</entry>
1326        <entry>SJIS</entry>
1327       </row>
1328
1329       <row>
1330        <entry>mic_to_win1250</entry>
1331        <entry>MULE_INTERNAL</entry>
1332        <entry>WIN1250</entry>
1333       </row>
1334
1335       <row>
1336        <entry>mic_to_win1251</entry>
1337        <entry>MULE_INTERNAL</entry>
1338        <entry>WIN</entry>
1339       </row>
1340
1341       <row>
1342        <entry>mic_to_win866</entry>
1343        <entry>MULE_INTERNAL</entry>
1344        <entry>ALT</entry>
1345       </row>
1346
1347       <row>
1348        <entry>sjis_to_euc_jp</entry>
1349        <entry>SJIS</entry>
1350        <entry>EUC_JP</entry>
1351       </row>
1352
1353       <row>
1354        <entry>sjis_to_mic</entry>
1355        <entry>SJIS</entry>
1356        <entry>MULE_INTERNAL</entry>
1357       </row>
1358
1359       <row>
1360        <entry>sjis_to_utf_8</entry>
1361        <entry>SJIS</entry>
1362        <entry>UNICODE</entry>
1363       </row>
1364
1365       <row>
1366        <entry>tcvn_to_utf_8</entry>
1367        <entry>TCVN</entry>
1368        <entry>UNICODE</entry>
1369       </row>
1370
1371       <row>
1372        <entry>uhc_to_utf_8</entry>
1373        <entry>UHC</entry>
1374        <entry>UNICODE</entry>
1375       </row>
1376
1377       <row>
1378        <entry>utf_8_to_ascii</entry>
1379        <entry>UNICODE</entry>
1380        <entry>SQL_ASCII</entry>
1381       </row>
1382
1383       <row>
1384        <entry>utf_8_to_big5</entry>
1385        <entry>UNICODE</entry>
1386        <entry>BIG5</entry>
1387       </row>
1388
1389       <row>
1390        <entry>utf_8_to_euc_cn</entry>
1391        <entry>UNICODE</entry>
1392        <entry>EUC_CN</entry>
1393       </row>
1394
1395       <row>
1396        <entry>utf_8_to_euc_jp</entry>
1397        <entry>UNICODE</entry>
1398        <entry>EUC_JP</entry>
1399       </row>
1400
1401       <row>
1402        <entry>utf_8_to_euc_kr</entry>
1403        <entry>UNICODE</entry>
1404        <entry>EUC_KR</entry>
1405       </row>
1406
1407       <row>
1408        <entry>utf_8_to_euc_tw</entry>
1409        <entry>UNICODE</entry>
1410        <entry>EUC_TW</entry>
1411       </row>
1412
1413       <row>
1414        <entry>utf_8_to_gb18030</entry>
1415        <entry>UNICODE</entry>
1416        <entry>GB18030</entry>
1417       </row>
1418
1419       <row>
1420        <entry>utf_8_to_gbk</entry>
1421        <entry>UNICODE</entry>
1422        <entry>GBK</entry>
1423       </row>
1424
1425       <row>
1426        <entry>utf_8_to_iso_8859_1</entry>
1427        <entry>UNICODE</entry>
1428        <entry>LATIN1</entry>
1429       </row>
1430
1431       <row>
1432        <entry>utf_8_to_iso_8859_10</entry>
1433        <entry>UNICODE</entry>
1434        <entry>LATIN6</entry>
1435       </row>
1436
1437       <row>
1438        <entry>utf_8_to_iso_8859_13</entry>
1439        <entry>UNICODE</entry>
1440        <entry>LATIN7</entry>
1441       </row>
1442
1443       <row>
1444        <entry>utf_8_to_iso_8859_14</entry>
1445        <entry>UNICODE</entry>
1446        <entry>LATIN8</entry>
1447       </row>
1448
1449       <row>
1450        <entry>utf_8_to_iso_8859_15</entry>
1451        <entry>UNICODE</entry>
1452        <entry>LATIN9</entry>
1453       </row>
1454
1455       <row>
1456        <entry>utf_8_to_iso_8859_16</entry>
1457        <entry>UNICODE</entry>
1458        <entry>LATIN10</entry>
1459       </row>
1460
1461       <row>
1462        <entry>utf_8_to_iso_8859_2</entry>
1463        <entry>UNICODE</entry>
1464        <entry>LATIN2</entry>
1465       </row>
1466
1467       <row>
1468        <entry>utf_8_to_iso_8859_3</entry>
1469        <entry>UNICODE</entry>
1470        <entry>LATIN3</entry>
1471       </row>
1472
1473       <row>
1474        <entry>utf_8_to_iso_8859_4</entry>
1475        <entry>UNICODE</entry>
1476        <entry>LATIN4</entry>
1477       </row>
1478
1479       <row>
1480        <entry>utf_8_to_iso_8859_5</entry>
1481        <entry>UNICODE</entry>
1482        <entry>ISO_8859_5</entry>
1483       </row>
1484
1485       <row>
1486        <entry>utf_8_to_iso_8859_6</entry>
1487        <entry>UNICODE</entry>
1488        <entry>ISO_8859_6</entry>
1489       </row>
1490
1491       <row>
1492        <entry>utf_8_to_iso_8859_7</entry>
1493        <entry>UNICODE</entry>
1494        <entry>ISO_8859_7</entry>
1495       </row>
1496
1497       <row>
1498        <entry>utf_8_to_iso_8859_8</entry>
1499        <entry>UNICODE</entry>
1500        <entry>ISO_8859_8</entry>
1501       </row>
1502
1503       <row>
1504        <entry>utf_8_to_iso_8859_9</entry>
1505        <entry>UNICODE</entry>
1506        <entry>LATIN5</entry>
1507       </row>
1508
1509       <row>
1510        <entry>utf_8_to_johab</entry>
1511        <entry>UNICODE</entry>
1512        <entry>JOHAB</entry>
1513       </row>
1514
1515       <row>
1516        <entry>utf_8_to_koi8r</entry>
1517        <entry>UNICODE</entry>
1518        <entry>KOI8</entry>
1519       </row>
1520
1521       <row>
1522        <entry>utf_8_to_sjis</entry>
1523        <entry>UNICODE</entry>
1524        <entry>SJIS</entry>
1525       </row>
1526
1527       <row>
1528        <entry>utf_8_to_tcvn</entry>
1529        <entry>UNICODE</entry>
1530        <entry>TCVN</entry>
1531       </row>
1532
1533       <row>
1534        <entry>utf_8_to_uhc</entry>
1535        <entry>UNICODE</entry>
1536        <entry>UHC</entry>
1537       </row>
1538
1539       <row>
1540        <entry>utf_8_to_win1250</entry>
1541        <entry>UNICODE</entry>
1542        <entry>WIN1250</entry>
1543       </row>
1544
1545       <row>
1546        <entry>utf_8_to_win1251</entry>
1547        <entry>UNICODE</entry>
1548        <entry>WIN</entry>
1549       </row>
1550
1551       <row>
1552        <entry>utf_8_to_win1256</entry>
1553        <entry>UNICODE</entry>
1554        <entry>WIN1256</entry>
1555       </row>
1556
1557       <row>
1558        <entry>utf_8_to_win866</entry>
1559        <entry>UNICODE</entry>
1560        <entry>ALT</entry>
1561       </row>
1562
1563       <row>
1564        <entry>utf_8_to_win874</entry>
1565        <entry>UNICODE</entry>
1566        <entry>WIN874</entry>
1567       </row>
1568
1569       <row>
1570        <entry>win1250_to_iso_8859_2</entry>
1571        <entry>WIN1250</entry>
1572        <entry>LATIN2</entry>
1573       </row>
1574
1575       <row>
1576        <entry>win1250_to_mic</entry>
1577        <entry>WIN1250</entry>
1578        <entry>MULE_INTERNAL</entry>
1579       </row>
1580
1581       <row>
1582        <entry>win1250_to_utf_8</entry>
1583        <entry>WIN1250</entry>
1584        <entry>UNICODE</entry>
1585       </row>
1586
1587       <row>
1588        <entry>win1251_to_iso_8859_5</entry>
1589        <entry>WIN</entry>
1590        <entry>ISO_8859_5</entry>
1591       </row>
1592
1593       <row>
1594        <entry>win1251_to_koi8r</entry>
1595        <entry>WIN</entry>
1596        <entry>KOI8</entry>
1597       </row>
1598
1599       <row>
1600        <entry>win1251_to_mic</entry>
1601        <entry>WIN</entry>
1602        <entry>MULE_INTERNAL</entry>
1603       </row>
1604
1605       <row>
1606        <entry>win1251_to_utf_8</entry>
1607        <entry>WIN</entry>
1608        <entry>UNICODE</entry>
1609       </row>
1610
1611       <row>
1612        <entry>win1251_to_win866</entry>
1613        <entry>WIN</entry>
1614        <entry>ALT</entry>
1615       </row>
1616
1617       <row>
1618        <entry>win1256_to_utf_8</entry>
1619        <entry>WIN1256</entry>
1620        <entry>UNICODE</entry>
1621       </row>
1622
1623       <row>
1624        <entry>win866_to_iso_8859_5</entry>
1625        <entry>ALT</entry>
1626        <entry>ISO_8859_5</entry>
1627       </row>
1628
1629       <row>
1630        <entry>win866_to_koi8r</entry>
1631        <entry>ALT</entry>
1632        <entry>KOI8</entry>
1633       </row>
1634
1635       <row>
1636        <entry>win866_to_mic</entry>
1637        <entry>ALT</entry>
1638        <entry>MULE_INTERNAL</entry>
1639       </row>
1640
1641       <row>
1642        <entry>win866_to_utf_8</entry>
1643        <entry>ALT</entry>
1644        <entry>UNICODE</entry>
1645       </row>
1646
1647       <row>
1648        <entry>win866_to_win1251</entry>
1649        <entry>ALT</entry>
1650        <entry>WIN</entry>
1651       </row>
1652
1653       <row>
1654        <entry>win874_to_utf_8</entry>
1655        <entry>WIN874</entry>
1656        <entry>UNICODE</entry>
1657       </row>
1658
1659      </tbody>
1660     </tgroup>
1661    </table>
1662
1663    <para>
1664     Additional string manipulation functions are available and are
1665     listed below.  Some of them are used internally to implement the
1666     <acronym>SQL</acronym>-standard string functions listed above.
1667    </para>
1668
1669    <table id="functions-string-other">
1670     <title>Other String Functions</title>
1671     <tgroup cols="5">
1672      <thead>
1673       <row>
1674        <entry>Function</entry>
1675        <entry>Return Type</entry>
1676        <entry>Description</entry>
1677        <entry>Example</entry>
1678        <entry>Result</entry>
1679       </row>
1680      </thead>
1681
1682      <tbody>
1683       <row>
1684        <entry><function>ascii</function>(<type>text</type>)</entry>
1685        <entry>integer</entry>
1686        <entry>Returns the <acronym>ASCII</acronym> code of the first character of the argument.</entry>
1687        <entry><literal>ascii('x')</literal></entry>
1688        <entry><literal>120</literal></entry>
1689       </row>
1690
1691       <row>
1692        <entry><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
1693        <entry><type>text</type></entry>
1694        <entry>
1695         Remove (trim) the longest string consisting only of characters
1696         in <parameter>trim</parameter> from the start and end of
1697         <parameter>string</parameter>.
1698        </entry>
1699        <entry><literal>btrim('xyxtrimyyx','xy')</literal></entry>
1700        <entry><literal>trim</literal></entry>
1701       </row>
1702
1703       <row>
1704        <entry><function>chr</function>(<type>integer</type>)</entry>
1705        <entry><type>text</type></entry>
1706        <entry>Returns the character with the given <acronym>ASCII</acronym> code.</entry>
1707        <entry><literal>chr(65)</literal></entry>
1708        <entry><literal>A</literal></entry>
1709       </row>
1710
1711       <row>
1712        <entry>
1713         <function>convert</function>(<parameter>string</parameter>
1714         <type>text</type>,
1715         <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
1716         <parameter>dest_encoding</parameter> <type>name</type>)
1717        </entry>
1718        <entry><type>text</type></entry>
1719        <entry>
1720         Converts string using <parameter>dest_encoding</parameter>.
1721         The original encoding is specified by
1722         <parameter>src_encoding</parameter>.  If
1723         <parameter>src_encoding</parameter> is omitted, database
1724         encoding is assumed.
1725        </entry>
1726        <entry><literal>convert('text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
1727        <entry><literal>text_in_unicode</literal> represented in ISO 8859-1</entry>
1728       </row>
1729
1730       <row>
1731        <entry>
1732         <function>decode</function>(<parameter>string</parameter> <type>text</type>,
1733         <parameter>type</parameter> <type>text</type>)
1734        </entry>
1735        <entry><type>bytea</type></entry>
1736        <entry>
1737         Decodes binary data from <parameter>string</parameter> previously
1738         encoded with encode().  Parameter type is same as in encode().
1739        </entry>
1740        <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
1741        <entry><literal>123\000\001</literal></entry>
1742       </row>
1743
1744       <row>
1745        <entry>
1746         <function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
1747         <parameter>type</parameter> <type>text</type>)
1748        </entry>
1749        <entry><type>text</type></entry>
1750        <entry>
1751         Encodes binary data to <acronym>ASCII</acronym>-only representation.  Supported
1752         types are: 'base64', 'hex', 'escape'.
1753        </entry>
1754        <entry><literal>encode('123\\000\\001', 'base64')</literal></entry>
1755        <entry><literal>MTIzAAE=</literal></entry>
1756       </row>
1757
1758       <row>
1759        <entry><function>initcap</function>(<type>text</type>)</entry>
1760        <entry><type>text</type></entry>
1761        <entry>Converts first letter of each word (whitespace separated) to upper case.</entry>
1762        <entry><literal>initcap('hi thomas')</literal></entry>
1763        <entry><literal>Hi Thomas</literal></entry>
1764       </row>
1765
1766       <row>
1767        <entry><function>length</function>(<parameter>string</parameter>)</entry>
1768        <entry><type>integer</type></entry>
1769        <entry>
1770         length of string
1771         <indexterm>
1772          <primary>character strings</primary>
1773          <secondary>length</secondary>
1774         </indexterm>
1775         <indexterm>
1776          <primary>length</primary>
1777          <secondary>character strings</secondary>
1778          <see>character strings, length</see>
1779         </indexterm>
1780        </entry>
1781        <entry><literal>length('jose')</literal></entry>
1782        <entry><literal>4</literal></entry>
1783       </row>
1784
1785       <row>
1786        <entry>
1787         <function>lpad</function>(<parameter>string</parameter> <type>text</type>,
1788         <parameter>length</parameter> <type>integer</type>
1789         <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1790        </entry>
1791        <entry>text</entry>
1792        <entry>
1793         Fills up the <parameter>string</parameter> to length
1794         <parameter>length</parameter> by prepending the characters
1795         <parameter>fill</parameter> (a space by default).  If the
1796         <parameter>string</parameter> is already longer than
1797         <parameter>length</parameter> then it is truncated (on the
1798         right).
1799        </entry>
1800        <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
1801        <entry><literal>xyxhi</literal></entry>
1802       </row>
1803
1804       <row>
1805        <entry><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>text</parameter> <type>text</type>)</entry>
1806        <entry><type>text</type></entry>
1807        <entry>
1808         Removes the longest string containing only characters from
1809         <parameter>trim</parameter> from the start of the string.
1810        </entry>
1811        <entry><literal>ltrim('zzzytrim','xyz')</literal></entry>
1812        <entry><literal>trim</literal></entry>
1813       </row>
1814
1815       <row>
1816        <entry><function>pg_client_encoding</function>()</entry>
1817        <entry><type>name</type></entry>
1818        <entry>
1819         Returns current client encoding name.
1820        </entry>
1821        <entry><literal>pg_client_encoding()</literal></entry>
1822        <entry><literal>SQL_ASCII</literal></entry>
1823       </row>
1824
1825       <row>
1826        <entry><function>quote_ident</function>(<parameter>string</parameter> text)</entry>
1827        <entry><type>text</type></entry>
1828        <entry>
1829         Returns the given string suitably quoted to be used as an identifier
1830         in an SQL query string.
1831         Quotes are added only if necessary (i.e., if the string contains
1832         non-identifier characters or would be case-folded).
1833         Embedded quotes are properly doubled.
1834        </entry>
1835        <entry><literal>quote_ident('Foo')</literal></entry>
1836        <entry><literal>"Foo"</literal></entry>
1837       </row>
1838
1839       <row>
1840        <entry><function>quote_literal</function>(<parameter>string</parameter> text)</entry>
1841        <entry><type>text</type></entry>
1842        <entry>
1843         Returns the given string suitably quoted to be used as a literal
1844         in an SQL query string.
1845         Embedded quotes and backslashes are properly doubled.
1846        </entry>
1847        <entry><literal>quote_literal('O\'Reilly')</literal></entry>
1848        <entry><literal>'O''Reilly'</literal></entry>
1849       </row>
1850
1851       <row>
1852        <entry><function>repeat</function>(<type>text</type>, <type>integer</type>)</entry>
1853        <entry><type>text</type></entry>
1854        <entry>Repeat text a number of times.</entry>
1855        <entry><literal>repeat('Pg', 4)</literal></entry>
1856        <entry><literal>PgPgPgPg</literal></entry>
1857       </row>
1858
1859       <row>
1860        <entry><function>replace</function>(<parameter>string</parameter> <type>text</type>,
1861        <parameter>from</parameter> <type>text</type>,
1862        <parameter>to</parameter> <type>text</type>)</entry>
1863        <entry><type>text</type></entry>
1864        <entry>Replace all occurrences in <parameter>string</parameter> of substring
1865         <parameter>from</parameter> with substring <parameter>to</parameter>
1866        </entry>
1867        <entry><literal>replace('abcdefabcdef', 'cd', 'XX')</literal></entry>
1868        <entry><literal>abXXefabXXef</literal></entry>
1869       </row>
1870
1871       <row>
1872        <entry>
1873         <function>rpad</function>(<parameter>string</parameter> <type>text</type>,
1874         <parameter>length</parameter> <type>integer</type>
1875         <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1876        </entry>
1877        <entry><type>text</type></entry>
1878        <entry>
1879         Fills up the <parameter>string</parameter> to length
1880         <parameter>length</parameter> by appending the characters
1881         <parameter>fill</parameter> (a space by default).  If the
1882         <parameter>string</parameter> is already longer than
1883         <parameter>length</parameter> then it is truncated.
1884        </entry>
1885        <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
1886        <entry><literal>hixyx</literal></entry>
1887       </row>
1888
1889       <row>
1890        <entry><function>rtrim</function>(<parameter>string</parameter>
1891         text, <parameter>trim</parameter> text)</entry>
1892        <entry><type>text</type></entry>
1893        <entry>
1894         Removes the longest string containing only characters from
1895         <parameter>trim</parameter> from the end of the string.
1896        </entry>
1897        <entry><literal>rtrim('trimxxxx','x')</literal></entry>
1898        <entry><literal>trim</literal></entry>
1899       </row>
1900
1901       <row>
1902        <entry><function>split</function>(<parameter>string</parameter> <type>text</type>,
1903        <parameter>delimiter</parameter> <type>text</type>,
1904        <parameter>column</parameter> <type>integer</type>)</entry>
1905        <entry><type>text</type></entry>
1906        <entry>Split <parameter>string</parameter> on <parameter>delimiter</parameter>
1907         returning the resulting (one based) <parameter>column</parameter> number.
1908        </entry>
1909        <entry><literal>split('abc~@~def~@~ghi','~@~',2)</literal></entry>
1910        <entry><literal>def</literal></entry>
1911       </row>
1912
1913       <row>
1914        <entry><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</entry>
1915        <entry><type>text</type></entry>
1916        <entry>
1917         Locates specified substring. (same as
1918         <literal>position(<parameter>substring</parameter> in
1919          <parameter>string</parameter>)</literal>, but note the reversed
1920         argument order)
1921        </entry>
1922        <entry><literal>strpos('high','ig')</literal></entry>
1923        <entry><literal>2</literal></entry>
1924       </row>
1925
1926       <row>
1927        <entry><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</entry>
1928        <entry><type>text</type></entry>
1929        <entry>
1930         Extracts specified substring. (same as
1931         <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
1932        </entry>
1933        <entry><literal>substr('alphabet', 3, 2)</literal></entry>
1934        <entry><literal>ph</literal></entry>
1935       </row>
1936
1937       <row>
1938        <entry><function>to_ascii</function>(<type>text</type>
1939         <optional>, <parameter>encoding</parameter></optional>)</entry>
1940        <entry><type>text</type></entry>
1941        <entry>Converts text from multibyte encoding to <acronym>ASCII</acronym>.</entry>
1942        <entry><literal>to_ascii('Karel')</literal></entry>
1943        <entry><literal>Karel</literal></entry>
1944       </row>
1945
1946       <row>
1947        <entry><function>to_hex</function>(<parameter>number</parameter> <type>integer</type>
1948        or <type>bigint</type>)</entry>
1949        <entry><type>text</type></entry>
1950        <entry>Convert <parameter>number</parameter> to its equivalent hexadecimal
1951         representation.
1952        </entry>
1953        <entry><literal>to_hex(9223372036854775807::bigint)</literal></entry>
1954        <entry><literal>7fffffffffffffff</literal></entry>
1955       </row>
1956
1957       <row>
1958        <entry>
1959         <function>translate</function>(<parameter>string</parameter>
1960         <type>text</type>,
1961         <parameter>from</parameter> <type>text</type>,
1962         <parameter>to</parameter> <type>text</type>)
1963        </entry>
1964        <entry><type>text</type></entry>
1965        <entry>
1966         Any character in <parameter>string</parameter> that matches a
1967         character in the <parameter>from</parameter> set is replaced by
1968         the corresponding character in the <parameter>to</parameter>
1969         set.
1970        </entry>
1971        <entry><literal>translate('12345', '14', 'ax')</literal></entry>
1972        <entry><literal>a23x5</literal></entry>
1973       </row>
1974
1975      </tbody>
1976     </tgroup>
1977    </table>
1978
1979    <para>
1980     The <function>to_ascii</function> function supports conversion from
1981     LATIN1, LATIN2, WIN1250 (CP1250) only.
1982    </para>
1983   </sect1>
1984
1985
1986   <sect1 id="functions-binarystring">
1987    <title>Binary String Functions and Operators</title>
1988
1989    <para>
1990     This section describes functions and operators for examining and
1991     manipulating binary string values.  Strings in this context include
1992     values of the type <type>BYTEA</type>.
1993    </para>
1994
1995    <para>
1996     <acronym>SQL</acronym> defines some string functions with a
1997     special syntax where
1998     certain keywords rather than commas are used to separate the
1999     arguments.  Details are in
2000     <xref linkend="functions-binarystring-sql">.
2001     Some functions are also implemented using the regular syntax for
2002     function invocation.
2003     (See <xref linkend="functions-binarystring-other">.)
2004    </para>
2005
2006    <table id="functions-binarystring-sql">
2007     <title><acronym>SQL</acronym> Binary String Functions and Operators</title>
2008     <tgroup cols="5">
2009      <thead>
2010       <row>
2011        <entry>Function</entry>
2012        <entry>Return Type</entry>
2013        <entry>Description</entry>
2014        <entry>Example</entry>
2015        <entry>Result</entry>
2016       </row>
2017      </thead>
2018
2019      <tbody>
2020       <row>
2021        <entry> <parameter>string</parameter> <literal>||</literal>
2022         <parameter>string</parameter> </entry>
2023        <entry> <type>bytea</type> </entry>
2024        <entry>
2025         string concatenation
2026         <indexterm>
2027          <primary>binary strings</primary>
2028          <secondary>concatenation</secondary>
2029         </indexterm>
2030        </entry>
2031        <entry><literal>'\\\\Postgre'::bytea || '\\047SQL\\000'::bytea</literal></entry>
2032        <entry><literal>\\Postgre'SQL\000</literal></entry>
2033       </row>
2034
2035       <row>
2036        <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
2037        <entry><type>integer</type></entry>
2038        <entry>number of bytes in binary string</entry>
2039        <entry><literal>octet_length('jo\\000se'::bytea)</literal></entry>
2040        <entry><literal>5</literal></entry>
2041       </row>
2042
2043       <row>
2044        <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
2045        <entry><type>integer</type></entry>
2046        <entry>location of specified substring</entry>
2047       <entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>
2048        <entry><literal>3</literal></entry>
2049       </row>
2050
2051       <row>
2052        <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
2053        <entry><type>bytea</type></entry>
2054        <entry>
2055         extract substring
2056         <indexterm>
2057          <primary>substring</primary>
2058         </indexterm>
2059        </entry>
2060        <entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>
2061        <entry><literal>h\000o</literal></entry>
2062       </row>
2063
2064       <row>
2065        <entry>
2066         <function>trim</function>(<optional>both</optional>
2067         <parameter>characters</parameter> from
2068         <parameter>string</parameter>)
2069        </entry>
2070        <entry><type>bytea</type></entry>
2071        <entry>
2072         Removes the longest string containing only the
2073         <parameter>characters</parameter> from the
2074         beginning/end/both ends of the <parameter>string</parameter>.
2075        </entry>
2076        <entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>
2077        <entry><literal>Tom</literal></entry>
2078       </row>
2079      </tbody>
2080     </tgroup>
2081    </table>
2082
2083    <para>
2084     Additional binary string manipulation functions are available and are
2085     listed below.  Some of them are used internally to implement the
2086     <acronym>SQL</acronym>-standard string functions listed above.
2087    </para>
2088
2089    <table id="functions-binarystring-other">
2090     <title>Other Binary String Functions</title>
2091     <tgroup cols="5">
2092      <thead>
2093       <row>
2094        <entry>Function</entry>
2095        <entry>Return Type</entry>
2096        <entry>Description</entry>
2097        <entry>Example</entry>
2098        <entry>Result</entry>
2099       </row>
2100      </thead>
2101
2102      <tbody>
2103       <row>
2104        <entry><function>btrim</function>(<parameter>string</parameter>
2105         <type>bytea</type> <parameter>trim</parameter> <type>bytea</type>)</entry>
2106        <entry><type>bytea</type></entry>
2107        <entry>
2108         Remove (trim) the longest string consisting only of characters
2109         in <parameter>trim</parameter> from the start and end of
2110         <parameter>string</parameter>.
2111       </entry>
2112       <entry><literal>btrim('\\000trim\\000'::bytea,'\\000'::bytea)</literal></entry>
2113       <entry><literal>trim</literal></entry>
2114      </row>
2115
2116      <row>
2117       <entry><function>length</function>(<parameter>string</parameter>)</entry>
2118       <entry><type>integer</type></entry>
2119       <entry>
2120        length of binary string
2121        <indexterm>
2122         <primary>binary strings</primary>
2123         <secondary>length</secondary>
2124        </indexterm>
2125        <indexterm>
2126         <primary>length</primary>
2127         <secondary>binary strings</secondary>
2128         <see>binary strings, length</see>
2129        </indexterm>
2130       </entry>
2131       <entry><literal>length('jo\\000se'::bytea)</literal></entry>
2132       <entry><literal>5</literal></entry>
2133      </row>
2134
2135      <row>
2136       <entry>
2137        <function>encode</function>(<parameter>string</parameter> <type>bytea</type>,
2138               <parameter>type</parameter> <type>text</type>)
2139       </entry>
2140       <entry><type>text</type></entry>
2141       <entry>
2142        Encodes binary string to <acronym>ASCII</acronym>-only representation.  Supported
2143        types are: 'base64', 'hex', 'escape'.
2144       </entry>
2145       <entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>
2146       <entry><literal>123\000456</literal></entry>
2147      </row>
2148
2149      <row>
2150       <entry>
2151        <function>decode</function>(<parameter>string</parameter> <type>text</type>,
2152               <parameter>type</parameter> <type>text</type>)
2153       </entry>
2154       <entry><type>bytea</type></entry>
2155       <entry>
2156        Decodes binary string from <parameter>string</parameter> previously
2157        encoded with encode().  Parameter type is same as in encode().
2158       </entry>
2159       <entry><literal>decode('123\\000456', 'escape')</literal></entry>
2160       <entry><literal>123\000456</literal></entry>
2161      </row>
2162
2163     </tbody>
2164    </tgroup>
2165   </table>
2166
2167  </sect1>
2168
2169
2170  <sect1 id="functions-matching">
2171   <title>Pattern Matching</title>
2172
2173    <para>
2174     There are two separate approaches to pattern matching provided by
2175     <productname>PostgreSQL</productname>:  the traditional
2176     <acronym>SQL</acronym>
2177     <function>LIKE</function> operator and the more recent
2178     <acronym>SQL99</acronym>
2179     <function>SIMILAR TO</function> operator implementing
2180     <acronym>POSIX</acronym>-style regular expressions.
2181     Additionally, a pattern matching function,
2182     <function>SUBSTRING</function>, is available, as defined in
2183     <acronym>SQL99</acronym>.
2184    </para>
2185
2186    <tip>
2187     <para>
2188      If you have pattern matching needs that go beyond this,
2189      consider writing a user-defined function in Perl or Tcl.
2190     </para>
2191    </tip>
2192
2193    <para>
2194     Both <function>LIKE</function> and <function>SIMILAR TO</function>
2195     are SQL-standard operators which are also available in alternate
2196     forms as <productname>PostgreSQL</productname> operators; look at
2197     <literal>~</literal> and <literal>~~</literal> for examples.
2198    </para>
2199
2200   <sect2 id="functions-like">
2201    <title><function>LIKE</function></title>
2202
2203    <indexterm>
2204     <primary>like</primary>
2205    </indexterm>
2206
2207     <synopsis>
2208 <replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
2209 <replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
2210     </synopsis>
2211
2212     <para>
2213      Every <replaceable>pattern</replaceable> defines a set of strings.
2214      The <function>LIKE</function> expression returns true if the
2215      <replaceable>string</replaceable> is contained in the set of
2216      strings represented by <replaceable>pattern</replaceable>.  (As
2217      expected, the <function>NOT LIKE</function> expression returns
2218      false if <function>LIKE</function> returns true, and vice versa.
2219      An equivalent expression is
2220      <literal>NOT (<replaceable>string</replaceable> LIKE
2221       <replaceable>pattern</replaceable>)</literal>.)
2222     </para>
2223
2224     <para>
2225      If <replaceable>pattern</replaceable> does not contain percent
2226      signs or underscore, then the pattern only represents the string
2227      itself; in that case <function>LIKE</function> acts like the
2228      equals operator.  An underscore (<literal>_</literal>) in
2229      <replaceable>pattern</replaceable> stands for (matches) any single
2230      character; a percent sign (<literal>%</literal>) matches any string
2231      of zero or more characters.
2232     </para>
2233
2234     <informalexample>
2235      <para>
2236       Some examples:
2237       <programlisting>
2238 'abc' LIKE 'abc'    <lineannotation>true</lineannotation>
2239 'abc' LIKE 'a%'     <lineannotation>true</lineannotation>
2240 'abc' LIKE '_b_'    <lineannotation>true</lineannotation>
2241 'abc' LIKE 'c'      <lineannotation>false</lineannotation>
2242       </programlisting>
2243      </para>
2244     </informalexample>
2245
2246    <para>
2247     <function>LIKE</function> pattern matches always cover the entire
2248     string.  To match a pattern anywhere within a string, the
2249     pattern must therefore start and end with a percent sign.
2250    </para>
2251
2252    <para>
2253     To match a literal underscore or percent sign without matching
2254     other characters, the respective character in
2255     <replaceable>pattern</replaceable> must be
2256     preceded by the escape character.  The default escape
2257     character is the backslash but a different one may be selected by
2258     using the <literal>ESCAPE</literal> clause.  To match the escape
2259     character itself, write two escape characters.
2260    </para>
2261
2262    <para>
2263     Note that the backslash already has a special meaning in string
2264     literals, so to write a pattern constant that contains a backslash
2265     you must write two backslashes in the query.  Thus, writing a pattern
2266     that actually matches a literal backslash means writing four backslashes
2267     in the query.  You can avoid this by selecting a different escape
2268     character with <literal>ESCAPE</literal>; then backslash is not special
2269     to <function>LIKE</function> anymore. (But it is still special to the string
2270     literal parser, so you still need two of them.)
2271    </para>
2272
2273    <para>
2274     It's also possible to select no escape character by writing
2275     <literal>ESCAPE ''</literal>.  This effectively disables the
2276     escape mechanism, which makes it impossible to turn off the
2277     special meaning of underscore and percent signs in the pattern.
2278    </para>
2279
2280    <para>
2281     The keyword <token>ILIKE</token> can be used instead of
2282     <token>LIKE</token> to make the match case insensitive according
2283     to the active locale.  This is not in the <acronym>SQL</acronym> standard but is a
2284     <productname>PostgreSQL</productname> extension.
2285    </para>
2286
2287    <para>
2288     The operator <literal>~~</literal> is equivalent to
2289     <function>LIKE</function>, and <literal>~~*</literal> corresponds to
2290     <function>ILIKE</function>.  There are also
2291     <literal>!~~</literal> and <literal>!~~*</literal> operators that
2292     represent <function>NOT LIKE</function> and <function>NOT
2293     ILIKE</function>.  All of these operators are
2294     <productname>PostgreSQL</productname>-specific.
2295    </para>
2296   </sect2>
2297
2298
2299   <sect2 id="functions-regexp">
2300    <title><function>SIMILAR TO</function> and <acronym>POSIX</acronym>
2301      Regular Expressions</title>
2302
2303    <indexterm zone="functions-regexp">
2304     <primary>regular expressions</primary>
2305     <seealso>pattern matching</seealso>
2306    </indexterm>
2307
2308    <table>
2309     <title>Regular Expression Match Operators</title>
2310
2311     <tgroup cols="3">
2312      <thead>
2313       <row>
2314        <entry>Operator</entry>
2315        <entry>Description</entry>
2316        <entry>Example</entry>
2317       </row>
2318      </thead>
2319
2320       <tbody>
2321        <row>
2322         <entry> <literal>~</literal> </entry>
2323         <entry>Matches regular expression, case sensitive</entry>
2324         <entry><literal>'thomas' ~ '.*thomas.*'</literal></entry>
2325        </row>
2326
2327        <row>
2328         <entry> <literal>~*</literal> </entry>
2329         <entry>Matches regular expression, case insensitive</entry>
2330         <entry><literal>'thomas' ~* '.*Thomas.*'</literal></entry>
2331        </row>
2332
2333        <row>
2334         <entry> <literal>!~</literal> </entry>
2335         <entry>Does not match regular expression, case sensitive</entry>
2336         <entry><literal>'thomas' !~ '.*Thomas.*'</literal></entry>
2337        </row>
2338
2339        <row>
2340         <entry> <literal>!~*</literal> </entry>
2341         <entry>Does not match regular expression, case insensitive</entry>
2342         <entry><literal>'thomas' !~* '.*vadim.*'</literal></entry>
2343        </row>
2344
2345        <row>
2346         <entry> <literal>SIMILAR TO</literal> </entry>
2347         <entry>Matches regular expression, case sensitive</entry>
2348         <entry><literal>'thomas' SIMILAR TO '.*thomas.*'</literal></entry>
2349        </row>
2350       </tbody>
2351      </tgroup>
2352     </table>
2353
2354     <para>
2355      <acronym>POSIX</acronym> regular expressions provide a more
2356      powerful means for
2357      pattern matching than the <function>LIKE</function> function.
2358      Many Unix tools such as <command>egrep</command>,
2359      <command>sed</command>, or <command>awk</command> use a pattern
2360      matching language that is similar to the one described here.
2361     </para>
2362
2363     <para>
2364      A regular expression is a character sequence that is an
2365      abbreviated definition of a set of strings (a <firstterm>regular
2366       set</firstterm>).  A string is said to match a regular expression
2367      if it is a member of the regular set described by the regular
2368      expression.  As with <function>LIKE</function>, pattern characters
2369      match string characters exactly unless they are special characters
2370      in the regular expression language --- but regular expressions use
2371      different special characters than <function>LIKE</function> does.
2372      Unlike <function>LIKE</function> patterns, a
2373      regular expression is allowed to match anywhere within a string, unless
2374      the regular expression is explicitly anchored to the beginning or
2375      end of the string.
2376     </para>
2377
2378     <informalexample>
2379      <para>
2380       Some examples:
2381       <programlisting>
2382 'abc' SIMILAR TO 'abc'    <lineannotation>true</lineannotation>
2383 'abc' SIMILAR TO '^a'     <lineannotation>true</lineannotation>
2384 'abc' SIMILAR TO '(b|d)'  <lineannotation>true</lineannotation>
2385 'abc' SIMILAR TO '^(b|c)' <lineannotation>false</lineannotation>
2386       </programlisting>
2387      </para>
2388     </informalexample>
2389
2390 <!-- derived from the re_format.7 man page -->
2391    <para>
2392     Regular expressions (<quote>RE</quote>s), as defined in
2393      <acronym>POSIX</acronym>
2394     1003.2, come in two forms: modern REs (roughly those of
2395     <command>egrep</command>; 1003.2 calls these
2396     <quote>extended</quote> REs) and obsolete REs (roughly those of
2397     <command>ed</command>; 1003.2 <quote>basic</quote> REs).
2398     <productname>PostgreSQL</productname> implements the modern form.
2399    </para>
2400
2401    <para>
2402     A (modern) RE is one or more non-empty
2403     <firstterm>branches</firstterm>, separated by
2404     <literal>|</literal>.  It matches anything that matches one of the
2405     branches.
2406    </para>
2407
2408    <para>
2409     A branch is one or more <firstterm>pieces</firstterm>,
2410     concatenated.  It matches a match for the first, followed by a
2411     match for the second, etc.
2412    </para>
2413
2414    <para>
2415     A piece is an <firstterm>atom</firstterm> possibly followed by a
2416     single <literal>*</literal>, <literal>+</literal>,
2417     <literal>?</literal>, or <firstterm>bound</firstterm>.  An atom
2418     followed by <literal>*</literal> matches a sequence of 0 or more
2419     matches of the atom.  An atom followed by <literal>+</literal>
2420     matches a sequence of 1 or more matches of the atom.  An atom
2421     followed by <literal>?</literal> matches a sequence of 0 or 1
2422     matches of the atom.
2423    </para>
2424
2425    <para>
2426     A <firstterm>bound</firstterm> is <literal>{</literal> followed by
2427     an unsigned decimal integer, possibly followed by
2428     <literal>,</literal> possibly followed by another unsigned decimal
2429     integer, always followed by <literal>}</literal>.  The integers
2430     must lie between 0 and <symbol>RE_DUP_MAX</symbol> (255)
2431     inclusive, and if there are two of them, the first may not exceed
2432     the second.  An atom followed by a bound containing one integer
2433     <replaceable>i</replaceable> and no comma matches a sequence of
2434     exactly <replaceable>i</replaceable> matches of the atom.  An atom
2435     followed by a bound containing one integer
2436     <replaceable>i</replaceable> and a comma matches a sequence of
2437     <replaceable>i</replaceable> or more matches of the atom.  An atom
2438     followed by a bound containing two integers
2439     <replaceable>i</replaceable> and <replaceable>j</replaceable>
2440     matches a sequence of <replaceable>i</replaceable> through
2441     <replaceable>j</replaceable> (inclusive) matches of the atom.
2442    </para>
2443
2444    <note>
2445     <para>
2446      A repetition operator (<literal>?</literal>,
2447      <literal>*</literal>, <literal>+</literal>, or bounds) cannot
2448      follow another repetition operator.  A repetition operator cannot
2449      begin an expression or subexpression or follow
2450      <literal>^</literal> or <literal>|</literal>.
2451     </para>
2452    </note>
2453
2454    <para>
2455     An <firstterm>atom</firstterm> is a regular expression enclosed in
2456     <literal>()</literal> (matching a match for the regular
2457     expression), an empty set of <literal>()</literal> (matching the
2458     null string), a <firstterm>bracket expression</firstterm> (see
2459     below), <literal>.</literal> (matching any single character),
2460     <literal>^</literal> (matching the null string at the beginning of the
2461     input string), <literal>$</literal> (matching the null string at the end
2462     of the input string), a <literal>\</literal> followed by one of the
2463     characters <literal>^.[$()|*+?{\</literal> (matching that
2464     character taken as an ordinary character), a <literal>\</literal>
2465     followed by any other character (matching that character taken as
2466     an ordinary character, as if the <literal>\</literal> had not been
2467     present), or a single character with no other significance
2468     (matching that character).  A <literal>{</literal> followed by a
2469     character other than a digit is an ordinary character, not the
2470     beginning of a bound.  It is illegal to end an RE with
2471     <literal>\</literal>.
2472    </para>
2473
2474    <para>
2475     Note that the backslash (<literal>\</literal>) already has a special
2476     meaning in string
2477     literals, so to write a pattern constant that contains a backslash
2478     you must write two backslashes in the query.
2479    </para>
2480
2481    <para>
2482     A <firstterm>bracket expression</firstterm> is a list of
2483     characters enclosed in <literal>[]</literal>.  It normally matches
2484     any single character from the list (but see below).  If the list
2485     begins with <literal>^</literal>, it matches any single character
2486     (but see below) not from the rest of the list.  If two characters
2487     in the list are separated by <literal>-</literal>, this is
2488     shorthand for the full range of characters between those two
2489     (inclusive) in the collating sequence,
2490     e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches
2491     any decimal digit.  It is illegal for two ranges to share an
2492     endpoint, e.g.  <literal>a-c-e</literal>.  Ranges are very
2493     collating-sequence-dependent, and portable programs should avoid
2494     relying on them.
2495    </para>
2496
2497    <para>
2498     To include a literal <literal>]</literal> in the list, make it the
2499     first character (following a possible <literal>^</literal>).  To
2500     include a literal <literal>-</literal>, make it the first or last
2501     character, or the second endpoint of a range.  To use a literal
2502     <literal>-</literal> as the first endpoint of a range, enclose it
2503     in <literal>[.</literal> and <literal>.]</literal> to make it a
2504     collating element (see below).  With the exception of these and
2505     some combinations using <literal>[</literal> (see next
2506     paragraphs), all other special characters, including
2507     <literal>\</literal>, lose their special significance within a
2508     bracket expression.
2509    </para>
2510
2511    <para>
2512     Within a bracket expression, a collating element (a character, a
2513     multiple-character sequence that collates as if it were a single
2514     character, or a collating-sequence name for either) enclosed in
2515     <literal>[.</literal> and <literal>.]</literal> stands for the
2516     sequence of characters of that collating element.  The sequence is
2517     a single element of the bracket expression's list.  A bracket
2518     expression containing a multiple-character collating element can thus
2519     match more than one character, e.g. if the collating sequence
2520     includes a <literal>ch</literal> collating element, then the RE
2521     <literal>[[.ch.]]*c</literal> matches the first five characters of
2522     <literal>chchcc</literal>.
2523    </para>
2524
2525    <para>
2526     Within a bracket expression, a collating element enclosed in
2527     <literal>[=</literal> and <literal>=]</literal> is an equivalence
2528     class, standing for the sequences of characters of all collating
2529     elements equivalent to that one, including itself.  (If there are
2530     no other equivalent collating elements, the treatment is as if the
2531     enclosing delimiters were <literal>[.</literal> and
2532     <literal>.]</literal>.)  For example, if <literal>o</literal> and
2533     <literal>^</literal> are the members of an equivalence class, then
2534     <literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and
2535     <literal>[o^]</literal> are all synonymous.  An equivalence class
2536     may not be an endpoint of a range.
2537    </para>
2538
2539    <para>
2540     Within a bracket expression, the name of a character class
2541     enclosed in <literal>[:</literal> and <literal>:]</literal> stands
2542     for the list of all characters belonging to that class.  Standard
2543     character class names are: <literal>alnum</literal>,
2544     <literal>alpha</literal>, <literal>blank</literal>,
2545     <literal>cntrl</literal>, <literal>digit</literal>,
2546     <literal>graph</literal>, <literal>lower</literal>,
2547     <literal>print</literal>, <literal>punct</literal>,
2548     <literal>space</literal>, <literal>upper</literal>,
2549     <literal>xdigit</literal>.  These stand for the character classes
2550     defined in
2551     <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.
2552     A locale may provide others.  A character class may not be used as
2553     an endpoint of a range.
2554    </para>
2555
2556    <para>
2557     There are two special cases of bracket expressions:  the bracket
2558     expressions <literal>[[:<:]]</literal> and
2559     <literal>[[:>:]]</literal> match the null string at the beginning
2560     and end of a word respectively.  A word is defined as a sequence
2561     of word characters which is neither preceded nor followed by word
2562     characters.  A word character is an alnum character (as defined by
2563     <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)
2564     or an underscore.  This is an extension, compatible with but not
2565     specified by <acronym>POSIX</acronym> 1003.2, and should be used with caution in
2566     software intended to be portable to other systems.
2567    </para>
2568
2569    <para>
2570     In the event that an RE could match more than one substring of a
2571     given string, the RE matches the one starting earliest in the
2572     string.  If the RE could match more than one substring starting at
2573     that point, it matches the longest.  Subexpressions also match the
2574     longest possible substrings, subject to the constraint that the
2575     whole match be as long as possible, with subexpressions starting
2576     earlier in the RE taking priority over ones starting later.  Note
2577     that higher-level subexpressions thus take priority over their
2578     lower-level component subexpressions.
2579    </para>
2580
2581    <para>
2582     Match lengths are measured in characters, not collating
2583     elements.  A null string is considered longer than no match at
2584     all.  For example, <literal>bb*</literal> matches the three middle
2585     characters of <literal>abbbc</literal>,
2586     <literal>(wee|week)(knights|nights)</literal> matches all ten
2587     characters of <literal>weeknights</literal>, when
2588     <literal>(.*).*</literal> is matched against
2589     <literal>abc</literal> the parenthesized subexpression matches all
2590     three characters, and when <literal>(a*)*</literal> is matched
2591     against <literal>bc</literal> both the whole RE and the
2592     parenthesized subexpression match the null string.
2593    </para>
2594
2595    <para>
2596     If case-independent matching is specified, the effect is much as
2597     if all case distinctions had vanished from the alphabet.  When an
2598     alphabetic that exists in multiple cases appears as an ordinary
2599     character outside a bracket expression, it is effectively
2600     transformed into a bracket expression containing both cases,
2601     e.g. <literal>x</literal> becomes <literal>[xX]</literal>.  When
2602     it appears inside a bracket expression, all case counterparts of
2603     it are added to the bracket expression, so that (e.g.)
2604     <literal>[x]</literal> becomes <literal>[xX]</literal> and
2605     <literal>[^x]</literal> becomes <literal>[^xX]</literal>.
2606    </para>
2607
2608    <para>
2609     There is no particular limit on the length of REs, except insofar
2610     as memory is limited.  Memory usage is approximately linear in RE
2611     size, and largely insensitive to RE complexity, except for bounded
2612     repetitions.  Bounded repetitions are implemented by macro
2613     expansion, which is costly in time and space if counts are large
2614     or bounded repetitions are nested.  An RE like, say,
2615     <literal>((((a{1,100}){1,100}){1,100}){1,100}){1,100}</literal>
2616     will (eventually) run almost any existing machine out of swap
2617     space.
2618     <footnote>
2619      <para>
2620       This was written in 1994, mind you.  The
2621       numbers have probably changed, but the problem
2622       persists.
2623      </para>
2624     </footnote>
2625    </para>
2626 <!-- end re_format.7 man page -->
2627   </sect2>
2628
2629  </sect1>
2630
2631
2632   <sect1 id="functions-formatting">
2633    <title>Data Type Formatting Functions</title>
2634
2635    <indexterm zone="functions-formatting">
2636     <primary>formatting</primary>
2637    </indexterm>
2638
2639    <note>
2640     <title>Author</title>
2641     <para>
2642      Written by Karel Zak (<email>zakkr@zf.jcu.cz</email>) on 2000-01-24
2643     </para>
2644    </note>
2645
2646    <para>
2647     The <productname>PostgreSQL</productname> formatting functions
2648     provide a powerful set of tools for converting various data types
2649     (date/time, integer, floating point, numeric) to formatted strings
2650     and for converting from formatted strings to specific data types.
2651     These functions all follow a common calling convention: the first
2652     argument is the value to be formatted and the second argument is a
2653     template that defines the output or input format.
2654    </para>
2655
2656     <table tocentry="1">
2657      <title>Formatting Functions</title>
2658      <tgroup cols="4">
2659       <thead>
2660        <row>
2661         <entry>Function</entry>
2662         <entry>Returns</entry>
2663         <entry>Description</entry>
2664         <entry>Example</entry>
2665        </row>
2666       </thead>
2667       <tbody>
2668        <row>
2669         <entry><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</entry>
2670         <entry><type>text</type></entry>
2671         <entry>convert time stamp to string</entry>
2672         <entry><literal>to_char(timestamp 'now','HH12:MI:SS')</literal></entry>
2673        </row>
2674        <row>
2675         <entry><function>to_char</function>(<type>interval</type>, <type>text</type>)</entry>
2676         <entry><type>text</type></entry>
2677         <entry>convert interval to string</entry>
2678         <entry><literal>to_char(interval '15h 2m 12s','HH24:MI:SS')</literal></entry>
2679        </row>
2680        <row>
2681         <entry><function>to_char</function>(<type>int</type>, <type>text</type>)</entry>
2682         <entry><type>text</type></entry>
2683         <entry>convert int4/int8 to string</entry>
2684         <entry><literal>to_char(125, '999')</literal></entry>
2685        </row>
2686        <row>
2687         <entry><function>to_char</function>(<type>double precision</type>,
2688         <type>text</type>)</entry>
2689         <entry><type>text</type></entry>
2690         <entry>convert real/double precision to string</entry>
2691         <entry><literal>to_char(125.8, '999D9')</literal></entry>
2692        </row>
2693        <row>
2694         <entry><function>to_char</function>(<type>numeric</type>, <type>text</type>)</entry>
2695         <entry><type>text</type></entry>
2696         <entry>convert numeric to string</entry>
2697         <entry><literal>to_char(numeric '-125.8', '999D99S')</literal></entry>
2698        </row>
2699        <row>
2700         <entry><function>to_date</function>(<type>text</type>, <type>text</type>)</entry>
2701         <entry><type>date</type></entry>
2702         <entry>convert string to date</entry>
2703         <entry><literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
2704        </row>
2705        <row>
2706         <entry><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</entry>
2707         <entry><type>timestamp</type></entry>
2708         <entry>convert string to time stamp</entry>
2709         <entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
2710        </row>
2711        <row>
2712         <entry><function>to_number</function>(<type>text</type>, <type>text</type>)</entry>
2713         <entry><type>numeric</type></entry>
2714         <entry>convert string to numeric</entry>
2715         <entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>
2716        </row>
2717       </tbody>
2718      </tgroup>
2719     </table>
2720
2721    <para>
2722     In an output template string, there are certain patterns that are
2723     recognized and replaced with appropriately-formatted data from the value
2724     to be formatted.  Any text that is not a template pattern is simply
2725     copied verbatim.  Similarly, in an input template string, template patterns
2726     identify the parts of the input data string to be looked at and the
2727     values to be found there.
2728    </para>
2729
2730     <table tocentry="1">
2731      <title>Template patterns for date/time conversions</title>
2732      <tgroup cols="2">
2733       <thead>
2734        <row>
2735         <entry>Pattern</entry>
2736         <entry>Description</entry>
2737        </row>
2738       </thead>
2739       <tbody>
2740        <row>
2741         <entry><literal>HH</literal></entry>
2742         <entry>hour of day (01-12)</entry>
2743        </row>
2744        <row>
2745         <entry><literal>HH12</literal></entry>
2746         <entry>hour of day (01-12)</entry>
2747        </row>
2748        <row>
2749         <entry><literal>HH24</literal></entry>
2750         <entry>hour of day (00-23)</entry>
2751        </row>
2752        <row>
2753         <entry><literal>MI</literal></entry>
2754         <entry>minute (00-59)</entry>
2755        </row>
2756        <row>
2757         <entry><literal>SS</literal></entry>
2758         <entry>second (00-59)</entry>
2759        </row>
2760        <row>
2761         <entry><literal>MS</literal></entry>
2762         <entry>millisecond (000-999)</entry>
2763        </row>
2764        <row>
2765         <entry><literal>US</literal></entry>
2766         <entry>microsecond (000000-999999)</entry>
2767        </row>
2768        <row>
2769         <entry><literal>SSSS</literal></entry>
2770         <entry>seconds past midnight (0-86399)</entry>
2771        </row>
2772        <row>
2773         <entry><literal>AM</literal> or <literal>A.M.</literal> or
2774         <literal>PM</literal> or <literal>P.M.</literal></entry>
2775         <entry>meridian indicator (upper case)</entry>
2776        </row>
2777        <row>
2778         <entry><literal>am</literal> or <literal>a.m.</literal> or
2779         <literal>pm</literal> or <literal>p.m.</literal></entry>
2780         <entry>meridian indicator (lower case)</entry>
2781        </row>
2782        <row>
2783         <entry><literal>Y,YYY</literal></entry>
2784         <entry>year (4 and more digits) with comma</entry>
2785        </row>
2786        <row>
2787         <entry><literal>YYYY</literal></entry>
2788         <entry>year (4 and more digits)</entry>
2789        </row>
2790        <row>
2791         <entry><literal>YYY</literal></entry>
2792         <entry>last 3 digits of year</entry>
2793        </row>
2794        <row>
2795         <entry><literal>YY</literal></entry>
2796         <entry>last 2 digits of year</entry>
2797        </row>
2798        <row>
2799         <entry><literal>Y</literal></entry>
2800         <entry>last digit of year</entry>
2801        </row>
2802        <row>
2803         <entry><literal>BC</literal> or <literal>B.C.</literal> or
2804         <literal>AD</literal> or <literal>A.D.</literal></entry>
2805         <entry>era indicator (upper case)</entry>
2806        </row>
2807        <row>
2808         <entry><literal>bc</literal> or <literal>b.c.</literal> or
2809         <literal>ad</literal> or <literal>a.d.</literal></entry>
2810         <entry>era indicator (lower case)</entry>
2811        </row>
2812        <row>
2813         <entry><literal>MONTH</literal></entry>
2814         <entry>full upper case month name (blank-padded to 9 chars)</entry>
2815        </row>
2816        <row>
2817         <entry><literal>Month</literal></entry>
2818         <entry>full mixed case month name (blank-padded to 9 chars)</entry>
2819        </row>
2820        <row>
2821         <entry><literal>month</literal></entry>
2822         <entry>full lower case month name (blank-padded to 9 chars)</entry>
2823        </row>
2824        <row>
2825         <entry><literal>MON</literal></entry>
2826         <entry>abbreviated upper case month name (3 chars)</entry>
2827        </row>
2828        <row>
2829         <entry><literal>Mon</literal></entry>
2830         <entry>abbreviated mixed case month name (3 chars)</entry>
2831        </row>
2832        <row>
2833         <entry><literal>mon</literal></entry>
2834         <entry>abbreviated lower case month name (3 chars)</entry>
2835        </row>
2836        <row>
2837         <entry><literal>MM</literal></entry>
2838         <entry>month number (01-12)</entry>
2839        </row>
2840        <row>
2841         <entry><literal>DAY</literal></entry>
2842         <entry>full upper case day name (blank-padded to 9 chars)</entry>
2843        </row>
2844        <row>
2845         <entry><literal>Day</literal></entry>
2846         <entry>full mixed case day name (blank-padded to 9 chars)</entry>
2847        </row>
2848        <row>
2849         <entry><literal>day</literal></entry>
2850         <entry>full lower case day name (blank-padded to 9 chars)</entry>
2851        </row>
2852        <row>
2853         <entry><literal>DY</literal></entry>
2854         <entry>abbreviated upper case day name (3 chars)</entry>
2855        </row>
2856        <row>
2857         <entry><literal>Dy</literal></entry>
2858         <entry>abbreviated mixed case day name (3 chars)</entry>
2859        </row>
2860        <row>
2861         <entry><literal>dy</literal></entry>
2862         <entry>abbreviated lower case day name (3 chars)</entry>
2863        </row>
2864        <row>
2865         <entry><literal>DDD</literal></entry>
2866         <entry>day of year (001-366)</entry>
2867        </row>
2868        <row>
2869         <entry><literal>DD</literal></entry>
2870         <entry>day of month (01-31)</entry>
2871        </row>
2872        <row>
2873         <entry><literal>D</literal></entry>
2874         <entry>day of week (1-7; SUN=1)</entry>
2875        </row>
2876        <row>
2877         <entry><literal>W</literal></entry>
2878         <entry>week of month (1-5) where first week start on the first day of the month</entry>
2879        </row>
2880        <row>
2881         <entry><literal>WW</literal></entry>
2882         <entry>week number of year (1-53) where first week start on the first day of the year</entry>
2883        </row>
2884        <row>
2885         <entry><literal>IW</literal></entry>
2886         <entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
2887        </row>
2888        <row>
2889         <entry><literal>CC</literal></entry>
2890         <entry>century (2 digits)</entry>
2891        </row>
2892        <row>
2893         <entry><literal>J</literal></entry>
2894         <entry>Julian Day (days since January 1, 4712 BC)</entry>
2895        </row>
2896        <row>
2897         <entry><literal>Q</literal></entry>
2898         <entry>quarter</entry>
2899        </row>
2900        <row>
2901         <entry><literal>RM</literal></entry>
2902         <entry>month in Roman Numerals (I-XII; I=January) - upper case</entry>
2903        </row>
2904        <row>
2905         <entry><literal>rm</literal></entry>
2906         <entry>month in Roman Numerals (I-XII; I=January) - lower case</entry>
2907        </row>
2908        <row>
2909         <entry><literal>TZ</literal></entry>
2910         <entry>timezone name - upper case</entry>
2911        </row>
2912        <row>
2913         <entry><literal>tz</literal></entry>
2914         <entry>timezone name - lower case</entry>
2915        </row>
2916       </tbody>
2917      </tgroup>
2918     </table>
2919
2920    <para>
2921     Certain modifiers may be applied to any template pattern to alter its
2922     behavior.  For example, <quote><literal>FMMonth</literal></quote>
2923     is the <quote><literal>Month</literal></quote> pattern with the
2924     <quote><literal>FM</literal></quote> prefix.
2925    </para>
2926
2927     <table tocentry="1">
2928      <title>Template pattern modifiers for date/time conversions</title>
2929      <tgroup cols="3">
2930       <thead>
2931        <row>
2932         <entry>Modifier</entry>
2933         <entry>Description</entry>
2934         <entry>Example</entry>
2935        </row>
2936       </thead>
2937       <tbody>
2938        <row>
2939         <entry><literal>FM</literal> prefix</entry>
2940         <entry>fill mode (suppress padding blanks and zeroes)</entry>
2941         <entry><literal>FMMonth</literal></entry>
2942        </row>
2943        <row>
2944         <entry><literal>TH</literal> suffix</entry>
2945         <entry>add upper-case ordinal number suffix</entry>
2946         <entry><literal>DDTH</literal></entry>
2947        </row>
2948        <row>
2949         <entry><literal>th</literal> suffix</entry>
2950         <entry>add lower-case ordinal number suffix</entry>
2951         <entry><literal>DDth</literal></entry>
2952        </row>
2953        <row>
2954         <entry><literal>FX</literal> prefix</entry>
2955         <entry>Fixed format global option (see below)</entry>
2956         <entry><literal>FX Month DD Day</literal></entry>
2957        </row>
2958        <row>
2959         <entry><literal>SP</literal> suffix</entry>
2960         <entry>spell mode (not yet implemented)</entry>
2961         <entry><literal>DDSP</literal></entry>
2962        </row>
2963       </tbody>
2964      </tgroup>
2965     </table>
2966
2967    <para>
2968     Usage notes:
2969
2970     <itemizedlist>
2971      <listitem>
2972       <para>
2973        <literal>FM</literal> suppresses leading zeroes or trailing blanks
2974        that would otherwise be added to make the output of a pattern be
2975        fixed-width.
2976       </para>
2977      </listitem>
2978
2979      <listitem>
2980       <para>
2981        <function>to_timestamp</function> and <function>to_date</function>
2982        skip multiple blank spaces in the input string if the <literal>FX</literal> option
2983        is not used. <literal>FX</literal> must be specified as the first item
2984        in the template; for example
2985        <literal>to_timestamp('2000    JUN','YYYY MON')</literal> is right, but
2986        <literal>to_timestamp('2000    JUN','FXYYYY MON')</literal> returns an error,
2987        because <function>to_timestamp</function> expects one blank space only.
2988       </para>
2989      </listitem>
2990
2991      <listitem>
2992       <para>
2993        If a backslash (<quote><literal>\</literal></quote>) is desired
2994        in a string constant, a double backslash
2995        (<quote><literal>\\</literal></quote>) must be entered; for
2996        example <literal>'\\HH\\MI\\SS'</literal>.  This is true for
2997        any string constant in <productname>PostgreSQL</productname>.
2998       </para>
2999      </listitem>
3000
3001      <listitem>
3002       <para>
3003        Ordinary text is allowed in <function>to_char</function>
3004        templates and will be output literally.  You can put a substring
3005        in double quotes to force it to be interpreted as literal text
3006        even if it contains pattern keywords.  For example, in
3007        <literal>'"Hello Year: "YYYY'</literal>, the <literal>YYYY</literal>
3008        will be replaced by year data, but the single <literal>Y</literal>
3009        will not be.
3010       </para>
3011      </listitem>
3012
3013      <listitem>
3014       <para>
3015        If you want to have a double quote in the output you must
3016        precede it with a backslash, for example <literal>'\\"YYYY
3017        Month\\"'</literal>. <!-- "" font-lock sanity :-) -->
3018       </para>
3019      </listitem>
3020
3021      <listitem>
3022       <para>
3023        <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
3024        <type>date</type> is restricted if you use a year with more than 4 digits. You must
3025        use some non-digit character or template after <literal>YYYY</literal>,
3026        otherwise the year is always interpreted as 4 digits. For example
3027        (with year 20000):
3028        <literal>to_date('200001131', 'YYYYMMDD')</literal> will be
3029        interpreted as a 4-digit year; better is to use a non-digit
3030        separator after the year, like
3031        <literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or
3032        <literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.
3033       </para>
3034      </listitem>
3035
3036      <listitem>
3037       <para>
3038        Millisecond <literal>MS</literal> and microsecond <literal>US</literal>
3039        values in a conversion from string to time stamp are used as part of the
3040        seconds after the decimal point. For example
3041        <literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,
3042        but 300, because the conversion counts it as 12 + 0.3.
3043        This means for the format <literal>SS:MS</literal>, the input values
3044        <literal>12:3</literal>, <literal>12:30</literal>, and <literal>12:300</literal> specify the
3045        same number of milliseconds. To get three milliseconds, one must use
3046        <literal>12:003</literal>, which the conversion counts as
3047        12 + 0.003 = 12.003 seconds.
3048       </para>
3049
3050       <para>
3051        Here is a more
3052        complex example:
3053        <literal>to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')</literal>
3054        is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
3055        1230 microseconds = 2.021230 seconds.
3056       </para>
3057      </listitem>
3058     </itemizedlist>
3059    </para>
3060
3061     <table tocentry="1">
3062      <title>Template patterns for numeric conversions</title>
3063      <tgroup cols="2">
3064       <thead>
3065        <row>
3066         <entry>Pattern</entry>
3067         <entry>Description</entry>
3068        </row>
3069       </thead>
3070       <tbody>
3071        <row>
3072         <entry><literal>9</literal></entry>
3073         <entry>value with the specified number of digits</entry>
3074        </row>
3075        <row>
3076         <entry><literal>0</literal></entry>
3077         <entry>value with leading zeros</entry>
3078        </row>
3079        <row>
3080         <entry><literal>.</literal> (period)</entry>
3081         <entry>decimal point</entry>
3082        </row>
3083        <row>
3084         <entry><literal>,</literal> (comma)</entry>
3085         <entry>group (thousand) separator</entry>
3086        </row>
3087        <row>
3088         <entry><literal>PR</literal></entry>
3089         <entry>negative value in angle brackets</entry>
3090        </row>
3091        <row>
3092         <entry><literal>S</literal></entry>
3093         <entry>negative value with minus sign (uses locale)</entry>
3094        </row>
3095        <row>
3096         <entry><literal>L</literal></entry>
3097         <entry>currency symbol (uses locale)</entry>
3098        </row>
3099        <row>
3100         <entry><literal>D</literal></entry>
3101         <entry>decimal point (uses locale)</entry>
3102        </row>
3103        <row>
3104         <entry><literal>G</literal></entry>
3105         <entry>group separator (uses locale)</entry>
3106        </row>
3107        <row>
3108         <entry><literal>MI</literal></entry>
3109         <entry>minus sign in specified position (if number < 0)</entry>
3110        </row>
3111        <row>
3112         <entry><literal>PL</literal></entry>
3113         <entry>plus sign in specified position (if number > 0)</entry>
3114        </row>
3115        <row>
3116         <entry><literal>SG</literal></entry>
3117         <entry>plus/minus sign in specified position</entry>
3118        </row>
3119        <row>
3120         <entry><literal>RN</literal></entry>
3121         <entry>roman numeral (input between 1 and 3999)</entry>
3122        </row>
3123        <row>
3124         <entry><literal>TH</literal> or <literal>th</literal></entry>
3125         <entry>convert to ordinal number</entry>
3126        </row>
3127        <row>
3128         <entry><literal>V</literal></entry>
3129         <entry>shift <replaceable>n</replaceable> digits (see
3130          notes)</entry>
3131        </row>
3132        <row>
3133         <entry><literal>EEEE</literal></entry>
3134         <entry>scientific notation (not implemented yet)</entry>
3135        </row>
3136       </tbody>
3137      </tgroup>
3138     </table>
3139
3140    <para>
3141     Usage notes:
3142
3143     <itemizedlist>
3144      <listitem>
3145       <para>
3146        A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
3147        <literal>MI</literal> is not an anchor in
3148        the number; for example,
3149        <literal>to_char(-12, 'S9999')</literal> produces <literal>'  -12'</literal>,
3150        but <literal>to_char(-12, 'MI9999')</literal> produces <literal>'-  12'</literal>.
3151        The Oracle implementation does not allow the use of
3152        <literal>MI</literal> ahead of <literal>9</literal>, but rather
3153        requires that <literal>9</literal> precede
3154        <literal>MI</literal>.
3155       </para>
3156      </listitem>
3157
3158      <listitem>
3159       <para>
3160        <literal>9</literal> specifies a value with the same number of
3161        digits as there are <literal>9</literal>s. If a digit is
3162        not available use blank space.
3163       </para>
3164      </listitem>
3165
3166      <listitem>
3167       <para>
3168        <literal>TH</literal> does not convert values less than zero
3169        and does not convert decimal numbers.
3170       </para>
3171      </listitem>
3172
3173      <listitem>
3174       <para>
3175        <literal>PL</literal>, <literal>SG</literal>, and
3176        <literal>TH</literal> are <productname>PostgreSQL</productname>
3177        extensions.
3178       </para>
3179      </listitem>
3180
3181      <listitem>
3182       <para>
3183        <literal>V</literal> effectively
3184        multiplies the input values by
3185        <literal>10^<replaceable>n</replaceable></literal>, where
3186        <replaceable>n</replaceable> is the number of digits following
3187        <literal>V</literal>.
3188        <function>to_char</function> does not support the use of
3189        <literal>V</literal> combined with a decimal point.
3190        (E.g., <literal>99.9V99</literal> is not allowed.)
3191       </para>
3192      </listitem>
3193     </itemizedlist>
3194    </para>
3195
3196     <table tocentry="1">
3197      <title><function>to_char</function> Examples</title>
3198      <tgroup cols="2">
3199       <thead>
3200        <row>
3201         <entry>Input</entry>
3202         <entry>Output</entry>
3203        </row>
3204       </thead>
3205       <tbody>
3206        <row>
3207         <entry><literal>to_char(now(),'Day, DD  HH12:MI:SS')</literal></entry>
3208         <entry><literal>'Tuesday  , 06  05:39:18'</literal></entry>
3209        </row>
3210        <row>
3211         <entry><literal>to_char(now(),'FMDay, FMDD  HH12:MI:SS')</literal></entry>
3212         <entry><literal>'Tuesday, 6  05:39:18'</literal></entry>
3213        </row>
3214        <row>
3215         <entry><literal>to_char(-0.1,'99.99')</literal></entry>
3216         <entry><literal>' -.10'</literal></entry>
3217        </row>
3218        <row>
3219         <entry><literal>to_char(-0.1,'FM9.99')</literal></entry>
3220         <entry><literal>'-.1'</literal></entry>
3221        </row>
3222        <row>
3223         <entry><literal>to_char(0.1,'0.9')</literal></entry>
3224         <entry><literal>' 0.1'</literal></entry>
3225        </row>
3226        <row>
3227         <entry><literal>to_char(12,'9990999.9')</literal></entry>
3228         <entry><literal>'    0012.0'</literal></entry>
3229        </row>
3230        <row>
3231         <entry><literal>to_char(12,'FM9990999.9')</literal></entry>
3232         <entry><literal>'0012'</literal></entry>
3233        </row>
3234        <row>
3235         <entry><literal>to_char(485,'999')</literal></entry>
3236         <entry><literal>' 485'</literal></entry>
3237        </row>
3238        <row>
3239         <entry><literal>to_char(-485,'999')</literal></entry>
3240         <entry><literal>'-485'</literal></entry>
3241        </row>
3242        <row>
3243         <entry><literal>to_char(485,'9 9 9')</literal></entry>
3244         <entry><literal>' 4 8 5'</literal></entry>
3245        </row>
3246        <row>
3247         <entry><literal>to_char(1485,'9,999')</literal></entry>
3248         <entry><literal>' 1,485'</literal></entry>
3249        </row>
3250        <row>
3251         <entry><literal>to_char(1485,'9G999')</literal></entry>
3252         <entry><literal>' 1 485'</literal></entry>
3253        </row>
3254        <row>
3255         <entry><literal>to_char(148.5,'999.999')</literal></entry>
3256         <entry><literal>' 148.500'</literal></entry>
3257        </row>
3258        <row>
3259         <entry><literal>to_char(148.5,'999D999')</literal></entry>
3260         <entry><literal>' 148,500'</literal></entry>
3261        </row>
3262        <row>
3263         <entry><literal>to_char(3148.5,'9G999D999')</literal></entry>
3264         <entry><literal>' 3 148,500'</literal></entry>
3265        </row>
3266        <row>
3267         <entry><literal>to_char(-485,'999S')</literal></entry>
3268         <entry><literal>'485-'</literal></entry>
3269        </row>
3270        <row>
3271         <entry><literal>to_char(-485,'999MI')</literal></entry>
3272         <entry><literal>'485-'</literal></entry>
3273        </row>
3274        <row>
3275         <entry><literal>to_char(485,'999MI')</literal></entry>
3276         <entry><literal>'485'</literal></entry>
3277        </row>
3278        <row>
3279         <entry><literal>to_char(485,'PL999')</literal></entry>
3280         <entry><literal>'+485'</literal></entry>
3281        </row>
3282        <row>
3283         <entry><literal>to_char(485,'SG999')</literal></entry>
3284         <entry><literal>'+485'</literal></entry>
3285        </row>
3286        <row>
3287         <entry><literal>to_char(-485,'SG999')</literal></entry>
3288         <entry><literal>'-485'</literal></entry>
3289        </row>
3290        <row>
3291         <entry><literal>to_char(-485,'9SG99')</literal></entry>
3292         <entry><literal>'4-85'</literal></entry>
3293        </row>
3294        <row>
3295         <entry><literal>to_char(-485,'999PR')</literal></entry>
3296         <entry><literal>'&lt;485&gt;'</literal></entry>
3297        </row>
3298        <row>
3299         <entry><literal>to_char(485,'L999')</literal></entry>
3300         <entry><literal>'DM 485</literal></entry>
3301        </row>
3302        <row>
3303         <entry><literal>to_char(485,'RN')</literal></entry>
3304         <entry><literal>'        CDLXXXV'</literal></entry>
3305        </row>
3306        <row>
3307         <entry><literal>to_char(485,'FMRN')</literal></entry>
3308         <entry><literal>'CDLXXXV'</literal></entry>
3309        </row>
3310        <row>
3311         <entry><literal>to_char(5.2,'FMRN')</literal></entry>
3312         <entry><literal>V</literal></entry>
3313        </row>
3314        <row>
3315         <entry><literal>to_char(482,'999th')</literal></entry>
3316         <entry><literal>' 482nd'</literal></entry>
3317        </row>
3318        <row>
3319         <entry><literal>to_char(485, '"Good number:"999')</literal></entry>
3320         <entry><literal>'Good number: 485'</literal></entry>
3321        </row>
3322        <row>
3323         <entry><literal>to_char(485.8,'"Pre:"999" Post:" .999')</literal></entry>
3324         <entry><literal>'Pre: 485 Post: .800'</literal></entry>
3325        </row>
3326        <row>
3327         <entry><literal>to_char(12,'99V999')</literal></entry>
3328         <entry><literal>' 12000'</literal></entry>
3329        </row>
3330        <row>
3331         <entry><literal>to_char(12.4,'99V999')</literal></entry>
3332         <entry><literal>' 12400'</literal></entry>
3333        </row>
3334        <row>
3335         <entry><literal>to_char(12.45, '99V9')</literal></entry>
3336         <entry><literal>' 125'</literal></entry>
3337        </row>
3338       </tbody>
3339      </tgroup>
3340     </table>
3341
3342   </sect1>
3343
3344
3345   <sect1 id="functions-datetime">
3346    <title>Date/Time Functions and Operators</title>
3347
3348    <para>
3349     <xref linkend="functions-datetime-table"> shows the available
3350     functions for date/time value processing.
3351     <xref linkend="operators-datetime-table"> illustrates the
3352     behaviors of the basic arithmetic
3353     operators (<literal>+</literal>, <literal>*</literal>, etc.).
3354     For formatting functions, refer to <xref
3355                                              linkend="functions-formatting">.  You should be familiar with the
3356     background information on date/time data types (see <xref
3357                                                               linkend="datatype-datetime">).
3358    </para>
3359
3360    <para>
3361     The date/time operators described below behave similarly for types
3362     involving time zones as well as those without.
3363
3364     <table id="operators-datetime-table">
3365      <title>Date/Time Operators</title>
3366
3367      <tgroup cols="3">
3368       <thead>
3369        <row>
3370         <entry>Name</entry>
3371         <entry>Example</entry>
3372         <entry>Result</entry>
3373        </row>
3374       </thead>
3375
3376       <tbody>
3377        <row>
3378         <entry> <literal>+</literal> </entry>
3379         <entry><type>timestamp</type> '2001-09-28 01:00' + <type>interval</type> '23 hours'</entry>
3380         <entry><type>timestamp</type> '2001-09-29 00:00'</entry>
3381        </row>
3382
3383        <row>
3384         <entry> <literal>+</literal> </entry>
3385         <entry><type>date</type> '2001-09-28' + <type>interval</type> '1 hour'</entry>
3386         <entry><type>timestamp</type> '2001-09-28 01:00'</entry>
3387        </row>
3388
3389        <row>
3390         <entry> <literal>+</literal> </entry>
3391         <entry><type>time</type> '01:00' + <type>interval</type> '3 hours'</entry>
3392         <entry><type>time</type> '04:00'</entry>
3393        </row>
3394
3395        <row>
3396         <entry> <literal>-</literal> </entry>
3397         <entry><type>timestamp</type> '2001-09-28 23:00' - <type>interval</type> '23 hours'</entry>
3398         <entry><type>timestamp</type> '2001-09-28'</entry>
3399        </row>
3400
3401        <row>
3402         <entry> <literal>-</literal> </entry>
3403         <entry><type>date</type> '2001-09-28' - <type>interval</type> '1 hour'</entry>
3404         <entry><type>timestamp</type> '2001-09-27 23:00'</entry>
3405        </row>
3406
3407        <row>
3408         <entry> <literal>-</literal> </entry>
3409         <entry><type>time</type> '05:00' - <type>interval</type> '2 hours'</entry>
3410         <entry><type>time</type> '03:00'</entry>
3411        </row>
3412
3413        <row>
3414         <entry> <literal>-</literal> </entry>
3415         <entry><type>interval</type> '2 hours' - <type>time</type> '05:00'</entry>
3416         <entry><type>time</type> '03:00:00'</entry>
3417        </row>
3418
3419        <row>
3420         <entry> <literal>*</literal> </entry>
3421         <entry><type>interval</type> '1 hour' * <type>int</type> '3'</entry>
3422         <entry><type>interval</type> '03:00'</entry>
3423        </row>
3424
3425        <row>
3426         <entry> <literal>/</literal> </entry>
3427         <entry><type>interval</type> '1 hour' / <type>int</type> '3'</entry>
3428         <entry><type>interval</type> '00:20'</entry>
3429        </row>
3430       </tbody>
3431      </tgroup>
3432     </table>
3433    </para>
3434
3435    <para>
3436     The date/time functions are summarized below, with additional
3437     details in subsequent sections.
3438
3439     <table id="functions-datetime-table">
3440      <title>Date/Time Functions</title>
3441      <tgroup cols="5">
3442       <thead>
3443        <row>
3444         <entry>Name</entry>
3445         <entry>Return Type</entry>
3446         <entry>Description</entry>
3447         <entry>Example</entry>
3448         <entry>Result</entry>
3449        </row>
3450       </thead>
3451
3452       <tbody>
3453        <row>
3454         <entry><function>age</function>(<type>timestamp</type>)</entry>
3455         <entry><type>interval</type></entry>
3456         <entry>Subtract from today</entry>
3457         <entry><literal>age(timestamp '1957-06-13')</literal></entry>
3458         <entry><literal>43 years 8 mons 3 days</literal></entry>
3459        </row>
3460
3461        <row>
3462         <entry><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</entry>
3463         <entry><type>interval</type></entry>
3464         <entry>Subtract arguments</entry>
3465         <entry><literal>age('2001-04-10', timestamp '1957-06-13')</literal></entry>
3466         <entry><literal>43 years 9 mons 27 days</literal></entry>
3467        </row>
3468
3469        <row>
3470         <entry><function>current_date</function></entry>
3471         <entry><type>date</type></entry>
3472         <entry>Today's date; see <link linkend="functions-datetime-current">below</link>
3473         </entry>
3474         <entry></entry>
3475         <entry></entry>
3476        </row>
3477
3478        <row>
3479         <entry><function>current_time</function></entry>
3480         <entry><type>time with time zone</type></entry>
3481         <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
3482         </entry>
3483         <entry></entry>
3484         <entry></entry>
3485        </row>
3486
3487        <row>
3488         <entry><function>current_timestamp</function></entry>
3489         <entry><type>timestamp with time zone</type></entry>
3490         <entry>Date and time; see <link linkend="functions-datetime-current">below</link>
3491         </entry>
3492         <entry></entry>
3493         <entry></entry>
3494        </row>
3495
3496        <row>
3497         <entry><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</entry>
3498         <entry><type>double precision</type></entry>
3499         <entry>Get subfield (equivalent to
3500          <function>extract</function>); see also <link
3501                                                 linkend="functions-datetime-datepart">below</link>
3502         </entry>
3503         <entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
3504         <entry><literal>20</literal></entry>
3505        </row>
3506
3507        <row>
3508         <entry><function>date_part</function>(<type>text</type>, <type>interval</type>)</entry>
3509         <entry><type>double precision</type></entry>
3510         <entry>Get subfield (equivalent to
3511          <function>extract</function>); see also <link
3512                                                 linkend="functions-datetime-datepart">below</link>
3513         </entry>
3514         <entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>
3515         <entry><literal>3</literal></entry>
3516        </row>
3517
3518        <row>
3519         <entry><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</entry>
3520         <entry><type>timestamp</type></entry>
3521         <entry>Truncate to specified precision; see also <link
3522                                                         linkend="functions-datetime-trunc">below</link>
3523         </entry>
3524         <entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
3525         <entry><literal>2001-02-16 20:00:00+00</literal></entry>
3526        </row>
3527
3528        <row>
3529         <entry><function>extract</function>(<parameter>field</parameter> from
3530          <type>timestamp</type>)</entry>
3531         <entry><type>double precision</type></entry>
3532         <entry>Get subfield; see also <link
3533                                      linkend="functions-datetime-extract">below</link>
3534         </entry>
3535         <entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>
3536         <entry><literal>20</literal></entry>
3537        </row>
3538
3539        <row>
3540         <entry><function>extract</function>(<parameter>field</parameter> from
3541          <type>interval</type>)</entry>
3542         <entry><type>double precision</type></entry>
3543         <entry>Get subfield; see also <link
3544                                      linkend="functions-datetime-extract">below</link>
3545         </entry>
3546         <entry><literal>extract(month from interval '2 years 3 months')</literal></entry>
3547         <entry><literal>3</literal></entry>
3548        </row>
3549
3550        <row>
3551         <entry><function>isfinite</function>(<type>timestamp</type>)</entry>
3552         <entry><type>boolean</type></entry>
3553         <entry>Test for finite time stamp (neither invalid nor infinity)</entry>
3554         <entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>
3555         <entry><literal>true</literal></entry>
3556        </row>
3557
3558        <row>
3559         <entry><function>isfinite</function>(<type>interval</type>)</entry>
3560         <entry><type>boolean</type></entry>
3561         <entry>Test for finite interval</entry>
3562         <entry><literal>isfinite(interval '4 hours')</literal></entry>
3563         <entry><literal>true</literal></entry>
3564        </row>
3565
3566        <row>
3567         <entry><function>localtime</function></entry>
3568         <entry><type>time</type></entry>
3569         <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
3570         </entry>
3571         <entry></entry>
3572         <entry></entry>
3573        </row>
3574
3575        <row>
3576         <entry><function>localtimestamp</function></entry>
3577         <entry><type>timestamp</type></entry>
3578         <entry>Date and time; see <link linkend="functions-datetime-current">below</link>
3579         </entry>
3580         <entry></entry>
3581         <entry></entry>
3582        </row>
3583
3584        <row>
3585         <entry><function>now</function>()</entry>
3586         <entry><type>timestamp</type></entry>
3587         <entry>Current date and time (equivalent to
3588          <function>current_timestamp</function>); see <link
3589                                                      linkend="functions-datetime-current">below</link>
3590         </entry>
3591         <entry></entry>
3592         <entry></entry>
3593        </row>
3594
3595        <row>
3596         <entry><function>timeofday()</function></entry>
3597         <entry><type>text</type></entry>
3598         <entry>Current date and time; see <link
3599                                          linkend="functions-datetime-current">below</link>
3600         </entry>
3601         <entry><literal>timeofday()</literal></entry>
3602         <entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
3603        </row>
3604
3605       </tbody>
3606      </tgroup>
3607     </table>
3608    </para>
3609
3610   <sect2 id="functions-datetime-extract">
3611    <title><function>EXTRACT</function>, <function>date_part</function></title>
3612
3613 <synopsis>
3614 EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
3615 </synopsis>
3616
3617    <para>
3618     The <function>extract</function> function retrieves sub-fields
3619     from date/time values, such as year or hour.
3620     <replaceable>source</replaceable> is a value expression that
3621     evaluates to type <type>timestamp</type> or <type>interval</type>.
3622     (Expressions of type <type>date</type> or <type>time</type> will
3623     be cast to <type>timestamp</type> and can therefore be used as
3624     well.)  <replaceable>field</replaceable> is an identifier or
3625     string that selects what field to extract from the source value.
3626     The <function>extract</function> function returns values of type
3627     <type>double precision</type>.
3628     The following are valid values:
3629
3630     <!-- alphabetical -->
3631     <variablelist>
3632      <varlistentry>
3633       <term><literal>century</literal></term>
3634       <listitem>
3635        <para>
3636         The year field divided by 100
3637        </para>
3638
3639        <informalexample>
3640 <screen>
3641 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
3642 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
3643 </screen>
3644        </informalexample>
3645
3646        <para>
3647         Note that the result for the century field is simply the year field
3648         divided by 100, and not the conventional definition which puts most
3649         years in the 1900's in the twentieth century.
3650        </para>
3651       </listitem>
3652      </varlistentry>
3653
3654      <varlistentry>
3655       <term><literal>day</literal></term>
3656       <listitem>
3657        <para>
3658         The day (of the month) field (1 - 31)
3659        </para>
3660
3661        <informalexample>
3662 <screen>
3663 SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
3664 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
3665 </screen>
3666        </informalexample>
3667       </listitem>
3668      </varlistentry>
3669
3670      <varlistentry>
3671       <term><literal>decade</literal></term>
3672       <listitem>
3673        <para>
3674         The year field divided by 10
3675        </para>
3676
3677        <informalexample>
3678 <screen>
3679 SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
3680 <lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
3681 </screen>
3682        </informalexample>
3683       </listitem>
3684      </varlistentry>
3685
3686      <varlistentry>
3687       <term><literal>dow</literal></term>
3688       <listitem>
3689        <para>
3690         The day of the week (0 - 6; Sunday is 0) (for
3691         <type>timestamp</type> values only)
3692        </para>
3693
3694        <informalexample>
3695 <screen>
3696 SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
3697 <lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
3698 </screen>
3699        </informalexample>
3700       </listitem>
3701      </varlistentry>
3702
3703      <varlistentry>
3704       <term><literal>doy</literal></term>
3705       <listitem>
3706        <para>
3707         The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
3708        </para>
3709        <informalexample>
3710 <screen>
3711 SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
3712 <lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
3713 </screen>
3714        </informalexample>
3715       </listitem>
3716      </varlistentry>
3717
3718      <varlistentry>
3719       <term><literal>epoch</literal></term>
3720       <listitem>
3721        <para>
3722         For <type>date</type> and <type>timestamp</type> values, the
3723         number of seconds since 1970-01-01 00:00:00-00 (Result may be
3724         negative.); for <type>interval</type> values, the total number
3725         of seconds in the interval
3726        </para>
3727
3728        <informalexample>
3729 <screen>
3730 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
3731 <lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>
3732
3733 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
3734 <lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
3735 </screen>
3736        </informalexample>
3737       </listitem>
3738      </varlistentry>
3739
3740      <varlistentry>
3741       <term><literal>hour</literal></term>
3742       <listitem>
3743        <para>
3744         The hour field (0 - 23)
3745        </para>
3746
3747        <informalexample>
3748 <screen>
3749 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
3750 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
3751 </screen>
3752        </informalexample>
3753       </listitem>
3754      </varlistentry>
3755
3756      <varlistentry>
3757       <term><literal>microseconds</literal></term>
3758       <listitem>
3759        <para>
3760         The seconds field, including fractional parts, multiplied by 1
3761         000 000.  Note that this includes full seconds.
3762        </para>
3763
3764        <informalexample>
3765 <screen>
3766 SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
3767 <lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
3768 </screen>
3769        </informalexample>
3770       </listitem>
3771      </varlistentry>
3772
3773      <varlistentry>
3774       <term><literal>millennium</literal></term>
3775       <listitem>
3776        <para>
3777         The year field divided by 1000
3778        </para>
3779
3780        <informalexample>
3781 <screen>
3782 SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
3783 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
3784 </screen>
3785        </informalexample>
3786
3787        <para>
3788         Note that the result for the millennium field is simply the year field
3789         divided by 1000, and not the conventional definition which puts
3790         years in the 1900's in the second millennium.
3791        </para>
3792       </listitem>
3793      </varlistentry>
3794
3795      <varlistentry>
3796       <term><literal>milliseconds</literal></term>
3797       <listitem>
3798        <para>
3799         The seconds field, including fractional parts, multiplied by
3800         1000.  Note that this includes full seconds.
3801        </para>
3802
3803        <informalexample>
3804 <screen>
3805 SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
3806 <lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
3807 </screen>
3808        </informalexample>
3809       </listitem>
3810      </varlistentry>
3811
3812      <varlistentry>
3813       <term><literal>minute</literal></term>
3814       <listitem>
3815        <para>
3816         The minutes field (0 - 59)
3817        </para>
3818
3819        <informalexample>
3820 <screen>
3821 SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
3822 <lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
3823 </screen>
3824        </informalexample>
3825       </listitem>
3826      </varlistentry>
3827
3828      <varlistentry>
3829       <term><literal>month</literal></term>
3830       <listitem>
3831        <para>
3832         For <type>timestamp</type> values, the number of the month
3833         within the year (1 - 12) ; for <type>interval</type> values
3834         the number of months, modulo 12 (0 - 11)
3835        </para>
3836
3837        <informalexample>
3838 <screen>
3839 SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
3840 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
3841
3842 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
3843 <lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
3844
3845 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
3846 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3847 </screen>
3848        </informalexample>
3849       </listitem>
3850      </varlistentry>
3851
3852      <varlistentry>
3853       <term><literal>quarter</literal></term>
3854       <listitem>
3855        <para>
3856         The quarter of the year (1 - 4) that the day is in (for
3857         <type>timestamp</type> values only)
3858        </para>
3859
3860        <informalexample>
3861 <screen>
3862 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
3863 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3864 </screen>
3865        </informalexample>
3866       </listitem>
3867      </varlistentry>
3868
3869      <varlistentry>
3870       <term><literal>second</literal></term>
3871       <listitem>
3872        <para>
3873         The seconds field, including fractional parts (0 -
3874         59<footnote><simpara>60 if leap seconds are
3875         implemented by the operating system</simpara></footnote>)
3876        </para>
3877
3878        <informalexample>
3879 <screen>
3880 SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
3881 <lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
3882
3883 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
3884 <lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
3885 </screen>
3886        </informalexample>
3887       </listitem>
3888      </varlistentry>
3889 <!--
3890      <varlistentry>
3891       <term><literal>timezone</literal></term>
3892       <listitem>
3893        <para>
3894         The time zone offset. XXX But in what units?
3895        </para>
3896       </listitem>
3897      </varlistentry>
3898 -->
3899
3900      <varlistentry>
3901       <term><literal>timezone_hour</literal></term>
3902       <listitem>
3903        <para>
3904         The hour component of the time zone offset.
3905        </para>
3906       </listitem>
3907      </varlistentry>
3908
3909      <varlistentry>
3910       <term><literal>timezone_minute</literal></term>
3911       <listitem>
3912        <para>
3913         The minute component of the time zone offset.
3914        </para>
3915       </listitem>
3916      </varlistentry>
3917
3918      <varlistentry>
3919       <term><literal>week</literal></term>
3920       <listitem>
3921        <para>
3922         From a <type>timestamp</type> value, calculate the number of
3923         the week of the year that the day is in.  By definition
3924         (<acronym>ISO</acronym> 8601), the first week of a year
3925         contains January 4 of that year.  (The <acronym>ISO</acronym>
3926         week starts on Monday.)  In other words, the first Thursday of
3927         a year is in week 1 of that year.
3928        </para>
3929
3930        <informalexample>
3931 <screen>
3932 SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
3933 <lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
3934 </screen>
3935        </informalexample>
3936       </listitem>
3937      </varlistentry>
3938
3939      <varlistentry>
3940       <term><literal>year</literal></term>
3941       <listitem>
3942        <para>
3943         The year field
3944        </para>
3945
3946        <informalexample>
3947 <screen>
3948 SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
3949 <lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
3950 </screen>
3951        </informalexample>
3952       </listitem>
3953      </varlistentry>
3954
3955     </variablelist>
3956
3957    </para>
3958
3959    <para>
3960     The <function>extract</function> function is primarily intended
3961     for computational processing.  For formatting date/time values for
3962     display, see <xref linkend="functions-formatting">.
3963    </para>
3964
3965    <anchor id="functions-datetime-datepart">
3966    <para>
3967     The <function>date_part</function> function is modeled on the traditional
3968     <productname>Ingres</productname> equivalent to the
3969     <acronym>SQL</acronym>-function <function>extract</function>:
3970 <synopsis>
3971 date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3972 </synopsis>
3973     Note that here the <replaceable>field</replaceable> value needs to
3974     be a string.  The valid field values for
3975     <function>date_part</function> are the same as for
3976     <function>extract</function>.
3977    </para>
3978
3979    <informalexample>
3980 <screen>
3981 SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
3982 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
3983
3984 SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
3985 <lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
3986 </screen>
3987    </informalexample>
3988
3989   </sect2>
3990
3991   <sect2 id="functions-datetime-trunc">
3992    <title><function>date_trunc</function></title>
3993
3994    <para>
3995     The function <function>date_trunc</function> is conceptually
3996     similar to the <function>trunc</function> function for numbers.
3997    </para>
3998
3999    <para>
4000 <synopsis>
4001 date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
4002 </synopsis>
4003     <replaceable>source</replaceable> is a value expression of type
4004     <type>timestamp</type> (values of type <type>date</type> and
4005     <type>time</type> are cast automatically).
4006     <replaceable>field</replaceable> selects to which precision to
4007     truncate the time stamp value.  The return value is of type
4008     <type>timestamp</type> with all fields that are less than the
4009     selected one set to zero (or one, for day and month).
4010    </para>
4011
4012    <para>
4013     Valid values for <replaceable>field</replaceable> are:
4014     <simplelist>
4015      <member>microseconds</member>
4016      <member>milliseconds</member>
4017      <member>second</member>
4018      <member>minute</member>
4019      <member>hour</member>
4020      <member>day</member>
4021      <member>month</member>
4022      <member>year</member>
4023      <member>decade</member>
4024      <member>century</member>
4025      <member>millennium</member>
4026     </simplelist>
4027    </para>
4028
4029    <informalexample>
4030     <para>
4031 <screen>
4032 SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
4033 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>
4034
4035 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
4036 <lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
4037 </screen>
4038     </para>
4039    </informalexample>
4040   </sect2>
4041
4042   <sect2 id="functions-datetime-current">
4043    <title>Current Date/Time</title>
4044
4045    <indexterm>
4046     <primary>date</primary>
4047     <secondary>current</secondary>
4048    </indexterm>
4049
4050    <indexterm>
4051     <primary>time</primary>
4052     <secondary>current</secondary>
4053    </indexterm>
4054
4055    <para>
4056     The following functions are available to obtain the current date and/or
4057     time:
4058      <synopsis>
4059 CURRENT_DATE
4060 CURRENT_TIME
4061 CURRENT_TIMESTAMP
4062 CURRENT_TIME ( <replaceable>precision</replaceable> )
4063 CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )
4064 LOCALTIME
4065 LOCALTIMESTAMP
4066 LOCALTIME ( <replaceable>precision</replaceable> )
4067 LOCALTIMESTAMP ( <replaceable>precision</replaceable> )
4068      </synopsis>
4069      <function>CURRENT_TIME</function>,
4070      <function>CURRENT_TIMESTAMP</function>,
4071      <function>LOCALTIME</function>, and
4072      <function>LOCALTIMESTAMP</function>
4073      can optionally be given
4074      a precision parameter, which causes the result to be rounded
4075      to that many fractional digits.  Without a precision parameter,
4076      the result is given to the full available precision.
4077     </para>
4078
4079     <note>
4080      <para>
4081       Prior to <productname>PostgreSQL</productname> 7.2, the precision
4082       parameters were unimplemented, and the result was always given
4083       in integer seconds.
4084      </para>
4085     </note>
4086
4087     <informalexample>
4088      <screen>
4089 SELECT CURRENT_TIME;
4090 <computeroutput>14:39:53.662522-05</computeroutput>
4091
4092 SELECT CURRENT_DATE;
4093 <computeroutput>2001-12-23</computeroutput>
4094
4095 SELECT CURRENT_TIMESTAMP;
4096 <computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>
4097
4098 SELECT CURRENT_TIMESTAMP(2);
4099 <computeroutput>2001-12-23 14:39:53.66-05</computeroutput>
4100
4101 SELECT LOCALTIMESTAMP;
4102 <computeroutput>2001-12-23 14:39:53.662522</computeroutput>
4103 </screen>
4104    </informalexample>
4105
4106    <para>
4107     The function <function>now()</function> is the traditional
4108     <productname>PostgreSQL</productname> equivalent to
4109     <function>CURRENT_TIMESTAMP</function>.
4110    </para>
4111
4112    <para>
4113     There is also <function>timeofday()</function>, which for historical
4114     reasons returns a text string rather than a <type>timestamp</type> value:
4115    </para>
4116
4117    <informalexample>
4118 <screen>
4119 SELECT timeofday();
4120  Sat Feb 17 19:07:32.000126 2001 EST
4121 </screen>
4122    </informalexample>
4123
4124    <para>
4125     It is quite important to realize that
4126     <function>CURRENT_TIMESTAMP</function> and related functions all return
4127     the time as of the start of the current transaction; their values do not
4128     increment while a transaction is running.  But
4129     <function>timeofday()</function> returns the actual current time.
4130    </para>
4131
4132    <para>
4133     All the date/time data types also accept the special literal value
4134     <literal>now</literal> to specify the current date and time.  Thus,
4135     the following three all return the same result:
4136 <programlisting>
4137 SELECT CURRENT_TIMESTAMP;
4138 SELECT now();
4139 SELECT TIMESTAMP 'now';
4140 </programlisting>
4141     <note>
4142      <para>
4143       You do not want to use the third form when specifying a DEFAULT
4144       value while creating a table.  The system will convert <literal>now</literal>
4145       to a <type>timestamp</type> as soon as the constant is parsed, so that when
4146       the default value is needed,
4147       the time of the table creation would be used!  The first two
4148       forms will not be evaluated until the default value is used,
4149       because they are function calls.  Thus they will give the desired
4150       behavior of defaulting to the time of row insertion.
4151      </para>
4152     </note>
4153    </para>
4154   </sect2>
4155  </sect1>
4156
4157
4158  <sect1 id="functions-geometry">
4159    <title>Geometric Functions and Operators</title>
4160
4161    <para>
4162     The geometric types <type>point</type>, <type>box</type>,
4163     <type>lseg</type>, <type>line</type>, <type>path</type>,
4164     <type>polygon</type>, and <type>circle</type> have a large set of
4165     native support functions and operators.
4166    </para>
4167
4168    <table>
4169      <title>Geometric Operators</title>
4170      <tgroup cols="3">
4171       <thead>
4172        <row>
4173         <entry>Operator</entry>
4174         <entry>Description</entry>
4175         <entry>Usage</entry>
4176        </row>
4177       </thead>
4178       <tbody>
4179        <row>
4180         <entry> + </entry>
4181         <entry>Translation</entry>
4182         <entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>
4183        </row>
4184        <row>
4185         <entry> - </entry>
4186         <entry>Translation</entry>
4187         <entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>
4188        </row>
4189        <row>
4190         <entry> * </entry>
4191         <entry>Scaling/rotation</entry>
4192         <entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>
4193        </row>
4194        <row>
4195         <entry> / </entry>
4196         <entry>Scaling/rotation</entry>
4197         <entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>
4198        </row>
4199        <row>
4200         <entry> # </entry>
4201         <entry>Intersection</entry>
4202         <entry><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></entry>
4203        </row>
4204        <row>
4205         <entry> # </entry>
4206         <entry>Number of points in path or polygon</entry>
4207         <entry><literal># '((1,0),(0,1),(-1,0))'</literal></entry>
4208        </row>
4209        <row>
4210         <entry> ## </entry>
4211         <entry>Point of closest proximity</entry>
4212         <entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>
4213        </row>
4214        <row>
4215         <entry> &amp;&amp; </entry>
4216         <entry>Overlaps?</entry>
4217         <entry><literal>box '((0,0),(1,1))' &amp;&amp; box '((0,0),(2,2))'</literal></entry>
4218        </row>
4219        <row>
4220         <entry> &amp;&lt; </entry>
4221         <entry>Overlaps to left?</entry>
4222         <entry><literal>box '((0,0),(1,1))' &amp;&lt; box '((0,0),(2,2))'</literal></entry>
4223        </row>
4224        <row>
4225         <entry> &amp;&gt; </entry>
4226         <entry>Overlaps to right?</entry>
4227         <entry><literal>box '((0,0),(3,3))' &amp;&gt; box '((0,0),(2,2))'</literal></entry>
4228        </row>
4229        <row>
4230         <entry> &lt;-&gt; </entry>
4231         <entry>Distance between</entry>
4232         <entry><literal>circle '((0,0),1)' &lt;-&gt; circle '((5,0),1)'</literal></entry>
4233        </row>
4234        <row>
4235         <entry> &lt;&lt; </entry>
4236         <entry>Left of?</entry>
4237         <entry><literal>circle '((0,0),1)' &lt;&lt; circle '((5,0),1)'</literal></entry>
4238        </row>
4239        <row>
4240         <entry> &lt;^ </entry>
4241         <entry>Is below?</entry>
4242         <entry><literal>circle '((0,0),1)' &lt;^ circle '((0,5),1)'</literal></entry>
4243        </row>
4244        <row>
4245         <entry> &gt;&gt; </entry>
4246         <entry>Is right of?</entry>
4247         <entry><literal>circle '((5,0),1)' &gt;&gt; circle '((0,0),1)'</literal></entry>
4248        </row>
4249        <row>
4250         <entry> &gt;^ </entry>
4251         <entry>Is above?</entry>
4252         <entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>
4253        </row>
4254        <row>
4255         <entry> ?# </entry>
4256         <entry>Intersects or overlaps</entry>
4257         <entry><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></entry>
4258        </row>
4259        <row>
4260         <entry> ?- </entry>
4261         <entry>Is horizontal?</entry>
4262         <entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>
4263        </row>
4264        <row>
4265         <entry> ?-| </entry>
4266         <entry>Is perpendicular?</entry>
4267         <entry><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></entry>
4268        </row>
4269        <row>
4270         <entry> @-@  </entry>
4271         <entry>Length or circumference</entry>
4272         <entry><literal>@-@ path '((0,0),(1,0))'</literal></entry>
4273        </row>
4274        <row>
4275         <entry> ?| </entry>
4276         <entry>Is vertical?</entry>
4277         <entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>
4278        </row>
4279        <row>
4280         <entry> ?|| </entry>
4281         <entry>Is parallel?</entry>
4282         <entry><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></entry>
4283        </row>
4284        <row>
4285         <entry> @ </entry>
4286         <entry>Contained or on</entry>
4287         <entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>
4288        </row>
4289        <row>
4290         <entry> @@ </entry>
4291         <entry>Center of</entry>
4292         <entry><literal>@@ circle '((0,0),10)'</literal></entry>
4293        </row>
4294        <row>
4295         <entry> ~= </entry>
4296         <entry>Same as</entry>
4297         <entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>
4298        </row>
4299       </tbody>
4300      </tgroup>
4301    </table>
4302
4303    <table>
4304      <title>Geometric Functions</title>
4305      <tgroup cols="4">
4306       <thead>
4307        <row>
4308         <entry>Function</entry>
4309         <entry>Returns</entry>
4310         <entry>Description</entry>
4311         <entry>Example</entry>
4312        </row>
4313       </thead>
4314       <tbody>
4315        <row>
4316         <entry><function>area</function>(object)</entry>
4317         <entry><type>double precision</type></entry>
4318         <entry>area of item</entry>
4319         <entry><literal>area(box '((0,0),(1,1))')</literal></entry>
4320        </row>
4321        <row>
4322         <entry><function>box</function>(box, box)</entry>
4323         <entry><type>box</type></entry>
4324         <entry>intersection box</entry>
4325         <entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
4326        </row>
4327        <row>
4328         <entry><function>center</function>(object)</entry>
4329         <entry><type>point</type></entry>
4330         <entry>center of item</entry>
4331         <entry><literal>center(box '((0,0),(1,2))')</literal></entry>
4332        </row>
4333        <row>
4334         <entry><function>diameter</function>(circle)</entry>
4335         <entry><type>double precision</type></entry>
4336         <entry>diameter of circle</entry>
4337         <entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
4338        </row>
4339        <row>
4340         <entry><function>height</function>(box)</entry>
4341         <entry><type>double precision</type></entry>
4342         <entry>vertical size of box</entry>
4343         <entry><literal>height(box '((0,0),(1,1))')</literal></entry>
4344        </row>
4345        <row>
4346         <entry><function>isclosed</function>(path)</entry>
4347         <entry><type>boolean</type></entry>
4348         <entry>a closed path?</entry>
4349         <entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
4350        </row>
4351        <row>
4352         <entry><function>isopen</function>(path)</entry>
4353         <entry><type>boolean</type></entry>
4354         <entry>an open path?</entry>
4355         <entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
4356        </row>
4357        <row>
4358         <entry><function>length</function>(object)</entry>
4359         <entry><type>double precision</type></entry>
4360         <entry>length of item</entry>
4361         <entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
4362        </row>
4363        <row>
4364         <entry><function>npoints</function>(path)</entry>
4365         <entry><type>integer</type></entry>
4366         <entry>number of points</entry>
4367         <entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
4368        </row>
4369        <row>
4370         <entry><function>npoints</function>(polygon)</entry>
4371         <entry><type>integer</type></entry>
4372         <entry>number of points</entry>
4373         <entry><literal>npoints(polygon '((1,1),(0,0))')</literal></entry>
4374        </row>
4375        <row>
4376         <entry><function>pclose</function>(path)</entry>
4377         <entry><type>path</type></entry>
4378         <entry>convert path to closed</entry>
4379         <entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
4380        </row>
4381 <![IGNORE[
4382 <!-- Not defined by this name. Implements the intersection operator '#' -->
4383        <row>
4384         <entry><function>point</function>(lseg,lseg)</entry>
4385         <entry><type>point</type></entry>
4386         <entry>intersection</entry>
4387         <entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
4388        </row>
4389 ]]>
4390        <row>
4391         <entry><function>popen</function>(path)</entry>
4392         <entry><type>path</type></entry>
4393         <entry>convert path to open path</entry>
4394         <entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
4395        </row>
4396        <row>
4397         <entry><function>radius</function>(circle)</entry>
4398         <entry><type>double precision</type></entry>
4399         <entry>radius of circle</entry>
4400         <entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
4401        </row>
4402        <row>
4403         <entry><function>width</function>(box)</entry>
4404         <entry><type>double precision</type></entry>
4405         <entry>horizontal size</entry>
4406         <entry><literal>width(box '((0,0),(1,1))')</literal></entry>
4407        </row>
4408       </tbody>
4409      </tgroup>
4410    </table>
4411
4412
4413    <table>
4414      <title>Geometric Type Conversion Functions</title>
4415      <tgroup cols="4">
4416       <thead>
4417        <row>
4418         <entry>Function</entry>
4419         <entry>Returns</entry>
4420         <entry>Description</entry>
4421         <entry>Example</entry>
4422        </row>
4423       </thead>
4424       <tbody>
4425        <row>
4426         <entry><function>box</function>(<type>circle</type>)</entry>
4427         <entry><type>box</type></entry>
4428         <entry>circle to box</entry>
4429         <entry><literal>box(circle '((0,0),2.0)')</literal></entry>
4430        </row>
4431        <row>
4432         <entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
4433         <entry><type>box</type></entry>
4434         <entry>points to box</entry>
4435         <entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
4436        </row>
4437        <row>
4438         <entry><function>box</function>(<type>polygon</type>)</entry>
4439         <entry><type>box</type></entry>
4440         <entry>polygon to box</entry>
4441         <entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4442        </row>
4443        <row>
4444         <entry><function>circle</function>(<type>box</type>)</entry>
4445         <entry><type>circle</type></entry>
4446         <entry>to circle</entry>
4447         <entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
4448        </row>
4449        <row>
4450         <entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
4451         <entry><type>circle</type></entry>
4452         <entry>point to circle</entry>
4453         <entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
4454        </row>
4455        <row>
4456         <entry><function>lseg</function>(<type>box</type>)</entry>
4457         <entry><type>lseg</type></entry>
4458         <entry>box diagonal to <type>lseg</type></entry>
4459         <entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
4460        </row>
4461        <row>
4462         <entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
4463         <entry><type>lseg</type></entry>
4464         <entry>points to <type>lseg</type></entry>
4465         <entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
4466        </row>
4467        <row>
4468         <entry><function>path</function>(<type>polygon</type>)</entry>
4469         <entry><type>point</type></entry>
4470         <entry>polygon to path</entry>
4471         <entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4472        </row>
4473        <row>
4474         <entry><function>point</function>(<type>circle</type>)</entry>
4475         <entry><type>point</type></entry>
4476         <entry>center</entry>
4477         <entry><literal>point(circle '((0,0),2.0)')</literal></entry>
4478        </row>
4479        <row>
4480         <entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
4481         <entry><type>point</type></entry>
4482         <entry>intersection</entry>
4483         <entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
4484        </row>
4485        <row>
4486         <entry><function>point</function>(<type>polygon</type>)</entry>
4487         <entry><type>point</type></entry>
4488         <entry>center</entry>
4489         <entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4490        </row>
4491        <row>
4492         <entry><function>polygon</function>(<type>box</type>)</entry>
4493         <entry><type>polygon</type></entry>
4494         <entry>4-point polygon</entry>
4495         <entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
4496        </row>
4497        <row>
4498         <entry><function>polygon</function>(<type>circle</type>)</entry>
4499         <entry><type>polygon</type></entry>
4500         <entry>12-point polygon</entry>
4501         <entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
4502        </row>
4503        <row>
4504         <entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
4505         <entry><type>polygon</type></entry>
4506         <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
4507         <entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
4508        </row>
4509        <row>
4510         <entry><function>polygon</function>(<type>path</type>)</entry>
4511         <entry><type>polygon</type></entry>
4512         <entry>path to polygon</entry>
4513         <entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
4514        </row>
4515       </tbody>
4516      </tgroup>
4517    </table>
4518
4519     <para>
4520      It is possible to access the two component numbers of a <type>point</>
4521      as though it were an array with subscripts 0,1.  For example, if
4522      <literal>t.p</> is a <type>point</> column then
4523      <literal>SELECT p[0] FROM t</> retrieves the X coordinate;
4524      <literal>UPDATE t SET p[1] = ...</> changes the Y coordinate.
4525      In the same way, a <type>box</> or an <type>lseg</> may be treated
4526      as an array of two <type>point</>s.
4527     </para>
4528
4529   </sect1>
4530
4531
4532   <sect1 id="functions-net">
4533    <title>Network Address Type Functions</title>
4534
4535
4536     <table tocentry="1" id="cidr-inet-operators-table">
4537      <title><type>cidr</type> and <type>inet</type> Operators</title>
4538      <tgroup cols="3">
4539       <thead>
4540        <row>
4541         <entry>Operator</entry>
4542         <entry>Description</entry>
4543         <entry>Usage</entry>
4544        </row>
4545       </thead>
4546       <tbody>
4547        <row>
4548         <entry> &lt; </entry>
4549         <entry>Less than</entry>
4550         <entry><literal>inet '192.168.1.5' &lt; inet '192.168.1.6'</literal></entry>
4551        </row>
4552        <row>
4553         <entry> &lt;= </entry>
4554         <entry>Less than or equal</entry>
4555         <entry><literal>inet '192.168.1.5' &lt;= inet '192.168.1.5'</literal></entry>
4556        </row>
4557        <row>
4558         <entry> = </entry>
4559         <entry>Equals</entry>
4560         <entry><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></entry>
4561        </row>
4562        <row>
4563         <entry> &gt;= </entry>
4564         <entry>Greater or equal</entry>
4565         <entry><literal>inet '192.168.1.5' &gt;= inet '192.168.1.5'</literal></entry>
4566        </row>
4567        <row>
4568         <entry> &gt; </entry>
4569         <entry>Greater</entry>
4570         <entry><literal>inet '192.168.1.5' &gt; inet '192.168.1.4'</literal></entry>
4571        </row>
4572        <row>
4573         <entry> &lt;&gt; </entry>
4574         <entry>Not equal</entry>
4575         <entry><literal>inet '192.168.1.5' &lt;&gt; inet '192.168.1.4'</literal></entry>
4576        </row>
4577        <row>
4578         <entry> &lt;&lt; </entry>
4579         <entry>is contained within</entry>
4580         <entry><literal>inet '192.168.1.5' &lt;&lt; inet '192.168.1/24'</literal></entry>
4581        </row>
4582        <row>
4583         <entry> &lt;&lt;= </entry>
4584         <entry>is contained within or equals</entry>
4585         <entry><literal>inet '192.168.1/24' &lt;&lt;= inet '192.168.1/24'</literal></entry>
4586        </row>
4587        <row>
4588         <entry> &gt;&gt; </entry>
4589         <entry>contains</entry>
4590         <entry><literal>inet'192.168.1/24' &gt;&gt; inet '192.168.1.5'</literal></entry>
4591        </row>
4592        <row>
4593         <entry> &gt;&gt;= </entry>
4594         <entry>contains or equals</entry>
4595         <entry><literal>inet '192.168.1/24' &gt;&gt;= inet '192.168.1/24'</literal></entry>
4596        </row>
4597       </tbody>
4598      </tgroup>
4599     </table>
4600
4601     <para>
4602      All of the operators for <type>inet</type> can be applied to
4603      <type>cidr</type> values as well.  The operators
4604      <literal>&lt;&lt;</literal>, <literal>&lt;&lt;=</literal>,
4605      <literal>&gt;&gt;</literal>, <literal>&gt;&gt;=</literal>
4606      test for subnet inclusion: they consider only the network parts
4607      of the two addresses, ignoring any host part, and determine whether
4608      one network part is identical to or a subnet of the other.
4609     </para>
4610
4611
4612     <table tocentry="1" id="cidr-inet-functions">
4613      <title><type>cidr</type> and <type>inet</type> Functions</title>
4614      <tgroup cols="5">
4615       <thead>
4616        <row>
4617         <entry>Function</entry>
4618         <entry>Returns</entry>
4619         <entry>Description</entry>
4620         <entry>Example</entry>
4621         <entry>Result</entry>
4622        </row>
4623       </thead>
4624       <tbody>
4625        <row>
4626         <entry><function>broadcast</function>(<type>inet</type>)</entry>
4627         <entry><type>inet</type></entry>
4628         <entry>broadcast address for network</entry>
4629         <entry><literal>broadcast('192.168.1.5/24')</literal></entry>
4630         <entry><literal>192.168.1.255/24</literal></entry>
4631        </row>
4632        <row>
4633         <entry><function>host</function>(<type>inet</type>)</entry>
4634         <entry><type>text</type></entry>
4635         <entry>extract IP address as text</entry>
4636         <entry><literal>host('192.168.1.5/24')</literal></entry>
4637         <entry><literal>192.168.1.5</literal></entry>
4638        </row>
4639        <row>
4640         <entry><function>masklen</function>(<type>inet</type>)</entry>
4641         <entry><type>integer</type></entry>
4642         <entry>extract netmask length</entry>
4643         <entry><literal>masklen('192.168.1.5/24')</literal></entry>
4644         <entry><literal>24</literal></entry>
4645        </row>
4646        <row>
4647         <entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
4648         <entry><type>inet</type></entry>
4649         <entry>set netmask length for <type>inet</type> value</entry>
4650         <entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
4651         <entry><literal>192.168.1.5/16</literal></entry>
4652        </row>
4653        <row>
4654         <entry><function>netmask</function>(<type>inet</type>)</entry>
4655         <entry><type>inet</type></entry>
4656         <entry>construct netmask for network</entry>
4657         <entry><literal>netmask('192.168.1.5/24')</literal></entry>
4658         <entry><literal>255.255.255.0</literal></entry>
4659        </row>
4660        <row>
4661         <entry><function>network</function>(<type>inet</type>)</entry>
4662         <entry><type>cidr</type></entry>
4663         <entry>extract network part of address</entry>
4664         <entry><literal>network('192.168.1.5/24')</literal></entry>
4665         <entry><literal>192.168.1.0/24</literal></entry>
4666        </row>
4667        <row>
4668         <entry><function>text</function>(<type>inet</type>)</entry>
4669         <entry><type>text</type></entry>
4670         <entry>extract IP address and masklen as text</entry>
4671         <entry><literal>text(inet '192.168.1.5')</literal></entry>
4672         <entry><literal>192.168.1.5/32</literal></entry>
4673        </row>
4674        <row>
4675         <entry><function>abbrev</function>(<type>inet</type>)</entry>
4676         <entry><type>text</type></entry>
4677         <entry>extract abbreviated display as text</entry>
4678         <entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
4679         <entry><literal>10.1/16</literal></entry>
4680        </row>
4681       </tbody>
4682      </tgroup>
4683     </table>
4684
4685    <para>
4686     All of the functions for <type>inet</type> can be applied to
4687     <type>cidr</type> values as well.  The <function>host</function>(),
4688     <function>text</function>(), and <function>abbrev</function>() functions are primarily
4689     intended to offer alternative display formats. You can cast a text
4690     field to inet using normal casting syntax: <literal>inet(expression)</literal> or
4691     <literal>colname::inet</literal>.
4692    </para>
4693
4694     <table tocentry="1" id="macaddr-functions">
4695      <title><type>macaddr</type> Functions</title>
4696      <tgroup cols="5">
4697       <thead>
4698        <row>
4699         <entry>Function</entry>
4700         <entry>Returns</entry>
4701         <entry>Description</entry>
4702         <entry>Example</entry>
4703         <entry>Result</entry>
4704        </row>
4705       </thead>
4706       <tbody>
4707        <row>
4708         <entry><function>trunc</function>(<type>macaddr</type>)</entry>
4709         <entry><type>macaddr</type></entry>
4710         <entry>set last 3 bytes to zero</entry>
4711         <entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
4712         <entry><literal>12:34:56:00:00:00</literal></entry>
4713        </row>
4714       </tbody>
4715      </tgroup>
4716     </table>
4717
4718    <para>
4719     The function <function>trunc</function>(<type>macaddr</type>) returns a MAC
4720     address with the last 3 bytes set to 0.  This can be used to
4721     associate the remaining prefix with a manufacturer.  The directory
4722     <filename>contrib/mac</filename> in the source distribution contains some
4723     utilities to create and maintain such an association table.
4724    </para>
4725
4726    <para>
4727     The <type>macaddr</type> type also supports the standard relational
4728     operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
4729     lexicographical ordering.
4730    </para>
4731
4732   </sect1>
4733
4734
4735  <sect1 id="functions-sequence">
4736   <title>Sequence-Manipulation Functions</title>
4737
4738   <indexterm>
4739    <primary>sequences</primary>
4740   </indexterm>
4741   <indexterm>
4742    <primary>nextval</primary>
4743   </indexterm>
4744   <indexterm>
4745    <primary>currval</primary>
4746   </indexterm>
4747   <indexterm>
4748    <primary>setval</primary>
4749   </indexterm>
4750
4751    <table>
4752     <title>Sequence Functions</title>
4753     <tgroup cols="3">
4754      <thead>
4755       <row><entry>Function</entry> <entry>Returns</entry> <entry>Description</entry></row>
4756      </thead>
4757
4758      <tbody>
4759       <row>
4760         <entry><function>nextval</function>(<type>text</type>)</entry>
4761         <entry><type>bigint</type></entry>
4762         <entry>Advance sequence and return new value</entry>
4763       </row>
4764       <row>
4765         <entry><function>currval</function>(<type>text</type>)</entry>
4766         <entry><type>bigint</type></entry>
4767         <entry>Return value most recently obtained with <function>nextval</function></entry>
4768       </row>
4769       <row>
4770         <entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
4771         <entry><type>bigint</type></entry>
4772         <entry>Set sequence's current value</entry>
4773       </row>
4774       <row>
4775         <entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</type>)</entry>
4776         <entry><type>bigint</type></entry>
4777         <entry>Set sequence's current value and <literal>is_called</literal> flag</entry>
4778       </row>
4779      </tbody>
4780     </tgroup>
4781    </table>
4782
4783   <para>
4784    This section describes <productname>PostgreSQL</productname>'s functions
4785    for operating on <firstterm>sequence objects</firstterm>.
4786    Sequence objects (also called sequence generators or
4787    just sequences) are special single-row tables created with
4788    <command>CREATE SEQUENCE</command>.  A sequence object is usually used to
4789    generate unique identifiers for rows of a table.  The sequence functions
4790    provide simple, multiuser-safe methods for obtaining successive
4791    sequence values from sequence objects.
4792   </para>
4793
4794   <para>
4795    For largely historical reasons, the sequence to be operated on by
4796    a sequence-function call is specified by a text-string argument.
4797    To achieve some compatibility with the handling of ordinary SQL
4798    names, the sequence functions convert their argument to lower case
4799    unless the string is double-quoted.  Thus
4800 <programlisting>
4801 nextval('foo')      <lineannotation>operates on sequence </><literal>foo</literal>
4802 nextval('FOO')      <lineannotation>operates on sequence </><literal>foo</literal>
4803 nextval('"Foo"')    <lineannotation>operates on sequence </><literal>Foo</literal>
4804 </programlisting>
4805    The sequence name can be schema-qualified if necessary:
4806 <programlisting>
4807 nextval('myschema.foo') <lineannotation>operates on </><literal>myschema.foo</literal>
4808 nextval('"myschema".foo') <lineannotation>same as above</lineannotation>
4809 nextval('foo')      <lineannotation>searches search path for
4810      </><literal>foo</literal>
4811 </programlisting>
4812    Of course, the text argument can be the result of an expression,
4813    not only a simple literal, which is occasionally useful.
4814   </para>
4815
4816   <para>
4817    The available sequence functions are:
4818
4819     <variablelist>
4820      <varlistentry>
4821       <term><function>nextval</function></term>
4822       <listitem>
4823        <para>
4824         Advance the sequence object to its next value and return that
4825         value.  This is done atomically: even if multiple server processes
4826         execute <function>nextval</function> concurrently, each will safely receive
4827         a distinct sequence value.
4828        </para>
4829       </listitem>
4830      </varlistentry>
4831
4832      <varlistentry>
4833       <term><function>currval</function></term>
4834       <listitem>
4835        <para>
4836         Return the value most recently obtained by <function>nextval</function>
4837         for this sequence in the current server process.  (An error is
4838         reported if <function>nextval</function> has never been called for this
4839         sequence in this process.)  Notice that because this is returning
4840         a process-local value, it gives a predictable answer even if other
4841         server processes are executing <function>nextval</function> meanwhile.
4842        </para>
4843       </listitem>
4844      </varlistentry>
4845
4846      <varlistentry>
4847       <term><function>setval</function></term>
4848       <listitem>
4849        <para>
4850         Reset the sequence object's counter value.  The two-parameter
4851         form sets the sequence's <literal>last_value</literal> field to the specified
4852         value and sets its <literal>is_called</literal> field to <literal>true</literal>,
4853         meaning that the next <function>nextval</function> will advance the sequence
4854         before returning a value.  In the three-parameter form,
4855         <literal>is_called</literal> may be set either <literal>true</literal> or
4856         <literal>false</literal>.  If it's set to <literal>false</literal>,
4857         the next <function>nextval</function> will return exactly the specified
4858         value, and sequence advancement commences with the following
4859         <function>nextval</function>.  For example,
4860        </para>
4861
4862        <informalexample>
4863 <screen>
4864 SELECT setval('foo', 42);           <lineannotation>Next nextval() will return 43</lineannotation>
4865 SELECT setval('foo', 42, true);     <lineannotation>Same as above</lineannotation>
4866 SELECT setval('foo', 42, false);    <lineannotation>Next nextval() will return 42</lineannotation>
4867 </screen>
4868        </informalexample>
4869
4870        <para>
4871         The result returned by <function>setval</function> is just the value of its
4872         second argument.
4873        </para>
4874       </listitem>
4875      </varlistentry>
4876     </variablelist>
4877   </para>
4878
4879   <important>
4880    <para>
4881     To avoid blocking of concurrent transactions that obtain numbers from the
4882     same sequence, a <function>nextval</function> operation is never rolled back;
4883     that is, once a value has been fetched it is considered used, even if the
4884     transaction that did the <function>nextval</function> later aborts.  This means
4885     that aborted transactions may leave unused <quote>holes</quote> in the
4886     sequence of assigned values.  <function>setval</function> operations are never
4887     rolled back, either.
4888    </para>
4889   </important>
4890
4891   <para>
4892    If a sequence object has been created with default parameters,
4893    <function>nextval()</function> calls on it will return successive values
4894    beginning with one.  Other behaviors can be obtained by using
4895    special parameters in the <command>CREATE SEQUENCE</command> command;
4896    see its command reference page for more information.
4897   </para>
4898
4899  </sect1>
4900
4901
4902  <sect1 id="functions-conditional">
4903   <title>Conditional Expressions</title>
4904
4905   <indexterm>
4906    <primary>case</primary>
4907   </indexterm>
4908
4909   <indexterm>
4910    <primary>conditionals</primary>
4911   </indexterm>
4912
4913   <para>
4914    This section describes the <acronym>SQL</acronym>-compliant conditional expressions
4915    available in <productname>PostgreSQL</productname>.
4916   </para>
4917
4918   <tip>
4919    <para>
4920     If your needs go beyond the capabilities of these conditional
4921     expressions you might want to consider writing a stored procedure
4922     in a more expressive programming language.
4923    </para>
4924   </tip>
4925
4926   <bridgehead renderas="sect2">CASE</bridgehead>
4927
4928 <synopsis>
4929 CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
4930      <optional>WHEN ...</optional>
4931      <optional>ELSE <replaceable>result</replaceable></optional>
4932 END
4933 </synopsis>
4934
4935   <para>
4936    The <acronym>SQL</acronym> <token>CASE</token> expression is a
4937    generic conditional expression, similar to if/else statements in
4938    other languages.  <token>CASE</token> clauses can be used wherever
4939    an expression is valid.  <replaceable>condition</replaceable> is an
4940    expression that returns a <type>boolean</type> result.  If the result is true
4941    then the value of the <token>CASE</token> expression is
4942    <replaceable>result</replaceable>.  If the result is false any
4943    subsequent <token>WHEN</token> clauses are searched in the same
4944    manner.  If no <token>WHEN</token>
4945    <replaceable>condition</replaceable> is true then the value of the
4946    case expression is the <replaceable>result</replaceable> in the
4947    <token>ELSE</token> clause.  If the <token>ELSE</token> clause is
4948    omitted and no condition matches, the result is NULL.
4949   </para>
4950
4951   <informalexample>
4952    <para>
4953     An example:
4954 <screen>
4955 <prompt>=&gt;</prompt> <userinput>SELECT * FROM test;</userinput>
4956 <computeroutput>
4957  a
4958 ---
4959  1
4960  2
4961  3
4962 </computeroutput>
4963
4964 <prompt>=&gt;</prompt> <userinput>SELECT a,
4965           CASE WHEN a=1 THEN 'one'
4966                WHEN a=2 THEN 'two'
4967                ELSE 'other'
4968           END
4969     FROM test;</userinput>
4970 <computeroutput>
4971  a | case
4972 ---+-------
4973  1 | one
4974  2 | two
4975  3 | other
4976 </computeroutput>
4977 </screen>
4978    </para>
4979   </informalexample>
4980
4981   <para>
4982    The data types of all the <replaceable>result</replaceable>
4983    expressions must be coercible to a single output type.
4984    See <xref linkend="typeconv-union-case"> for more detail.
4985   </para>
4986
4987 <synopsis>
4988 CASE <replaceable>expression</replaceable>
4989     WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
4990     <optional>WHEN ...</optional>
4991     <optional>ELSE <replaceable>result</replaceable></optional>
4992 END
4993 </synopsis>
4994
4995   <para>
4996    This <quote>simple</quote> <token>CASE</token> expression is a
4997    specialized variant of the general form above.  The
4998    <replaceable>expression</replaceable> is computed and compared to
4999    all the <replaceable>value</replaceable>s in the
5000    <token>WHEN</token> clauses until one is found that is equal.  If
5001    no match is found, the <replaceable>result</replaceable> in the
5002    <token>ELSE</token> clause (or NULL) is returned.  This is similar
5003    to the <function>switch</function> statement in C.
5004   </para>
5005
5006   <informalexample>
5007    <para>
5008     The example above can be written using the simple
5009     <token>CASE</token> syntax:
5010 <screen>
5011 <prompt>=&gt;</prompt> <userinput>SELECT a,
5012           CASE a WHEN 1 THEN 'one'
5013                  WHEN 2 THEN 'two'
5014                  ELSE 'other'
5015           END
5016     FROM test;</userinput>
5017 <computeroutput>
5018  a | case
5019 ---+-------
5020  1 | one
5021  2 | two
5022  3 | other
5023 </computeroutput>
5024 </screen>
5025     </para>
5026    </informalexample>
5027
5028    <bridgehead renderas="sect2">COALESCE</bridgehead>
5029
5030 <synopsis>
5031 <function>COALESCE</function>(<replaceable>value</replaceable>
5032 <optional>, ...</optional>)
5033 </synopsis>
5034
5035   <para>
5036    The <function>COALESCE</function> function returns the first of its
5037    arguments that is not NULL.  This is often useful to substitute a
5038    default value for NULL values when data is retrieved for display,
5039    for example:
5040 <programlisting>
5041 SELECT COALESCE(description, short_description, '(none)') ...
5042 </programlisting>
5043   </para>
5044
5045  <bridgehead renderas="sect2">NULLIF</bridgehead>
5046
5047   <indexterm>
5048    <primary>nullif</primary>
5049   </indexterm>
5050
5051 <synopsis>
5052 <function>NULLIF</function>(<replaceable>value1</replaceable>,
5053     <replaceable>value2</replaceable>)
5054 </synopsis>
5055
5056   <para>
5057    The <function>NULLIF</function> function returns NULL if and only
5058    if <replaceable>value1</replaceable> and
5059    <replaceable>value2</replaceable> are equal.  Otherwise it returns
5060    <replaceable>value1</replaceable>.  This can be used to perform the
5061    inverse operation of the <function>COALESCE</function> example
5062    given above:
5063 <programlisting>
5064 SELECT NULLIF(value, '(none)') ...
5065 </programlisting>
5066   </para>
5067
5068   <tip>
5069    <para>
5070     <function>COALESCE</function> and <function>NULLIF</function> are
5071     just shorthand for <token>CASE</token> expressions.  They are actually
5072     converted into <token>CASE</token> expressions at a very early stage
5073     of processing, and subsequent processing thinks it is dealing with
5074     <token>CASE</token>.  Thus an incorrect <function>COALESCE</function> or
5075     <function>NULLIF</function> usage may draw an error message that
5076     refers to <token>CASE</token>.
5077    </para>
5078   </tip>
5079
5080  </sect1>
5081
5082
5083   <sect1 id="functions-misc">
5084    <title>Miscellaneous Functions</title>
5085
5086    <table>
5087     <title>Session Information Functions</title>
5088     <tgroup cols="3">
5089      <thead>
5090       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5091      </thead>
5092
5093      <tbody>
5094       <row>
5095        <entry><function>current_user</function></entry>
5096        <entry><type>name</type></entry>
5097        <entry>user name of current execution context</entry>
5098       </row>
5099       <row>
5100        <entry><function>session_user</function></entry>
5101        <entry><type>name</type></entry>
5102        <entry>session user name</entry>
5103       </row>
5104       <row>
5105        <entry><function>user</function></entry>
5106        <entry><type>name</type></entry>
5107        <entry>equivalent to <function>current_user</function></entry>
5108       </row>
5109       <row>
5110        <entry><function>current_schema()</function></entry>
5111        <entry><type>name</type></entry>
5112        <entry>name of current schema</entry>
5113       </row>
5114       <row>
5115        <entry><function>current_schemas(boolean)</function></entry>
5116        <entry><type>name[]</type></entry>
5117        <entry>names of schemas in search path optionally including implicit schemas</entry>
5118       </row>
5119       <row>
5120        <entry><function>current_database()</function></entry>
5121        <entry><type>name</type></entry>
5122        <entry>name of current database</entry>
5123       </row>
5124      </tbody>
5125     </tgroup>
5126    </table>
5127
5128    <indexterm zone="functions-misc">
5129     <primary>user</primary>
5130     <secondary>current</secondary>
5131    </indexterm>
5132
5133    <indexterm zone="functions-misc">
5134     <primary>schema</primary>
5135     <secondary>current</secondary>
5136    </indexterm>
5137
5138    <indexterm zone="functions-misc">
5139     <primary>search path</primary>
5140     <secondary>current</secondary>
5141    </indexterm>
5142
5143    <para>
5144     The <function>session_user</function> is the user that initiated a
5145     database connection; it is fixed for the duration of that
5146     connection. The <function>current_user</function> is the user identifier
5147     that is applicable for permission checking. Normally, it is equal
5148     to the session user, but it changes during the execution of
5149     functions with the attribute <literal>SECURITY DEFINER</literal>.
5150     In Unix parlance, the session user is the <quote>real user</quote> and
5151     the current user is the <quote>effective user</quote>.
5152    </para>
5153
5154    <note>
5155     <para>
5156      <function>current_user</function>, <function>session_user</function>, and
5157      <function>user</function> have special syntactic status in <acronym>SQL</acronym>:
5158      they must be called without trailing parentheses.
5159     </para>
5160    </note>
5161
5162    <note>
5163     <title>Deprecated</title>
5164     <para>
5165      The function <function>getpgusername()</function> is an obsolete equivalent
5166      of <function>current_user</function>.
5167     </para>
5168    </note>
5169
5170    <para>
5171     <function>current_schema</function> returns the name of the schema that is
5172     at the front of the search path (or NULL if the search path is
5173     empty).  This is the schema that will be used for any tables or
5174     other named objects that are created without specifying a target schema.
5175     <function>current_schemas(boolean)</function> returns an array of the names of all
5176     schemas presently in the search path.  The boolean option determines whether or not
5177     implicitly included system schemas such as pg_catalog are included in the search
5178     path returned.
5179    </para>
5180
5181    <para id=set-search-path xreflabel="SET SEARCH_PATH">
5182     <indexterm>
5183      <primary>search path</primary>
5184      <secondary>changing at runtime</secondary>
5185     </indexterm>
5186     The search path may be altered by a runtime-alterable GUC setting.  The
5187     command to use is <command>
5188      SET SEARCH_PATH '<varname>schema</varname>'[,'<varname>schema</varname>']...
5189     </command>
5190    </para>
5191
5192    <table>
5193     <title>System Information Functions</title>
5194     <tgroup cols="3">
5195      <thead>
5196       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5197      </thead>
5198
5199      <tbody>
5200       <row>
5201        <entry><function>version</function></entry>
5202        <entry><type>text</type></entry>
5203        <entry>PostgreSQL version information</entry>
5204       </row>
5205      </tbody>
5206     </tgroup>
5207    </table>
5208
5209    <indexterm zone="functions-misc">
5210     <primary>version</primary>
5211    </indexterm>
5212
5213    <para>
5214     <function>version()</function> returns a string describing the PostgreSQL
5215     server's version.
5216    </para>
5217
5218    <table>
5219     <title>Configuration Settings Information Functions</title>
5220     <tgroup cols="3">
5221      <thead>
5222       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5223      </thead>
5224
5225      <tbody>
5226       <row>
5227        <entry>
5228         <function>current_setting</function>(<parameter>setting_name</parameter>)
5229        </entry>
5230        <entry><type>text</type></entry>
5231        <entry>value of current setting</entry>
5232       </row>
5233       <row>
5234        <entry>
5235         <function>set_config(<parameter>setting_name</parameter>,
5236                              <parameter>new_value</parameter>,
5237                              <parameter>is_local</parameter>)</function>
5238        </entry>
5239        <entry><type>text</type></entry>
5240        <entry>new value of current setting</entry>
5241       </row>
5242      </tbody>
5243     </tgroup>
5244    </table>
5245
5246    <indexterm zone="functions-misc">
5247     <primary>setting</primary>
5248     <secondary>current</secondary>
5249    </indexterm>
5250
5251    <indexterm zone="functions-misc">
5252     <primary>setting</primary>
5253     <secondary>set</secondary>
5254    </indexterm>
5255
5256    <para>
5257     The <function>current_setting</function> is used to obtain the current
5258     value of the <parameter>setting_name</parameter> setting, as a query
5259     result. It is the equivalent to the SQL <command>SHOW</command> command.
5260     For example:
5261 <programlisting>
5262 select current_setting('DateStyle');
5263             current_setting
5264 ---------------------------------------
5265  ISO with US (NonEuropean) conventions
5266 (1 row)
5267 </programlisting>
5268    </para>
5269
5270    <para>
5271     <function>set_config</function> allows the <parameter>setting_name
5272     </parameter> setting to be changed to <parameter>new_value</parameter>.
5273     If <parameter>is_local</parameter> is set to <literal>true</literal>,
5274     the new value will only apply to the current transaction. If you want
5275     the new value to apply for the current session, use
5276     <literal>false</literal> instead. It is the equivalent to the SQL
5277     <command>SET</command> command. For example:
5278 <programlisting>
5279 select set_config('show_statement_stats','off','f');
5280  set_config
5281 ------------
5282  off
5283 (1 row)
5284 </programlisting>
5285    </para>
5286
5287    <table>
5288     <title>Access Privilege Inquiry Functions</title>
5289     <tgroup cols="3">
5290      <thead>
5291       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5292      </thead>
5293
5294      <tbody>
5295       <row>
5296        <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
5297                                   <parameter>table</parameter>,
5298                                   <parameter>access</parameter>)
5299        </entry>
5300        <entry><type>boolean</type></entry>
5301        <entry>does user have access to table</entry>
5302       </row>
5303       <row>
5304        <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
5305                                   <parameter>access</parameter>)
5306        </entry>
5307        <entry><type>boolean</type></entry>
5308        <entry>does current user have access to table</entry>
5309       </row>
5310       <row>
5311        <entry><function>has_database_privilege</function>(<parameter>user</parameter>,
5312                                   <parameter>database</parameter>,
5313                                   <parameter>access</parameter>)
5314        </entry>
5315        <entry><type>boolean</type></entry>
5316        <entry>does user have access to database</entry>
5317       </row>
5318       <row>
5319        <entry><function>has_database_privilege</function>(<parameter>database</parameter>,
5320                                   <parameter>access</parameter>)
5321        </entry>
5322        <entry><type>boolean</type></entry>
5323        <entry>does current user have access to database</entry>
5324       </row>
5325       <row>
5326        <entry><function>has_function_privilege</function>(<parameter>user</parameter>,
5327                                   <parameter>function</parameter>,
5328                                   <parameter>access</parameter>)
5329        </entry>
5330        <entry><type>boolean</type></entry>
5331        <entry>does user have access to function</entry>
5332       </row>
5333       <row>
5334        <entry><function>has_function_privilege</function>(<parameter>function</parameter>,
5335                                   <parameter>access</parameter>)
5336        </entry>
5337        <entry><type>boolean</type></entry>
5338        <entry>does current user have access to function</entry>
5339       </row>
5340       <row>
5341        <entry><function>has_language_privilege</function>(<parameter>user</parameter>,
5342                                   <parameter>language</parameter>,
5343                                   <parameter>access</parameter>)
5344        </entry>
5345        <entry><type>boolean</type></entry>
5346        <entry>does user have access to language</entry>
5347       </row>
5348       <row>
5349        <entry><function>has_language_privilege</function>(<parameter>language</parameter>,
5350                                   <parameter>access</parameter>)
5351        </entry>
5352        <entry><type>boolean</type></entry>
5353        <entry>does current user have access to language</entry>
5354       </row>
5355       <row>
5356        <entry><function>has_schema_privilege</function>(<parameter>user</parameter>,
5357                                   <parameter>schema</parameter>,
5358                                   <parameter>access</parameter>)
5359        </entry>
5360        <entry><type>boolean</type></entry>
5361        <entry>does user have access to schema</entry>
5362       </row>
5363       <row>
5364        <entry><function>has_schema_privilege</function>(<parameter>schema</parameter>,
5365                                   <parameter>access</parameter>)
5366        </entry>
5367        <entry><type>boolean</type></entry>
5368        <entry>does current user have access to schema</entry>
5369       </row>
5370      </tbody>
5371     </tgroup>
5372    </table>
5373
5374    <indexterm zone="functions-misc">
5375     <primary>has_table_privilege</primary>
5376    </indexterm>
5377    <indexterm zone="functions-misc">
5378     <primary>has_database_privilege</primary>
5379    </indexterm>
5380    <indexterm zone="functions-misc">
5381     <primary>has_function_privilege</primary>
5382    </indexterm>
5383    <indexterm zone="functions-misc">
5384     <primary>has_language_privilege</primary>
5385    </indexterm>
5386    <indexterm zone="functions-misc">
5387     <primary>has_schema_privilege</primary>
5388    </indexterm>
5389
5390    <para>
5391     <function>has_table_privilege</function> checks whether a user
5392     can access a table in a particular way.  The user can be
5393     specified by name or by ID
5394     (<classname>pg_user</classname>.<structfield>usesysid</structfield>), or if the argument is
5395     omitted
5396     <function>current_user</function> is assumed.  The table can be specified
5397     by name or by OID.  (Thus, there are actually six variants of
5398     <function>has_table_privilege</function>, which can be distinguished by
5399     the number and types of their arguments.)  When specifying by name,
5400     the name can be schema-qualified if necessary.
5401     The desired access type
5402     is specified by a text string, which must evaluate to one of the
5403     values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
5404     <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
5405     <literal>TRIGGER</literal>.  (Case of the string is not significant, however.)
5406     An example is:
5407 <programlisting>
5408 SELECT has_table_privilege('myschema.mytable', 'select');
5409 </programlisting>
5410    </para>
5411
5412    <para>
5413     <function>has_database_privilege</function> checks whether a user
5414     can access a database in a particular way.  The possibilities for its
5415     arguments are analogous to <function>has_table_privilege</function>.
5416     The desired access type must evaluate to
5417     <literal>CREATE</literal>,
5418     <literal>TEMPORARY</literal>, or
5419     <literal>TEMP</literal> (which is equivalent to
5420     <literal>TEMPORARY</literal>).
5421    </para>
5422
5423    <para>
5424     <function>has_function_privilege</function> checks whether a user
5425     can access a function in a particular way.  The possibilities for its
5426     arguments are analogous to <function>has_table_privilege</function>.
5427     When specifying a function by a text string rather than by OID,
5428     the allowed input is the same as for the <type>regprocedure</> datatype.
5429     The desired access type must currently evaluate to
5430     <literal>EXECUTE</literal>.
5431    </para>
5432
5433    <para>
5434     <function>has_language_privilege</function> checks whether a user
5435     can access a procedural language in a particular way.  The possibilities
5436     for its arguments are analogous to <function>has_table_privilege</function>.
5437     The desired access type must currently evaluate to
5438     <literal>USAGE</literal>.
5439    </para>
5440
5441    <para>
5442     <function>has_schema_privilege</function> checks whether a user
5443     can access a schema in a particular way.  The possibilities for its
5444     arguments are analogous to <function>has_table_privilege</function>.
5445     The desired access type must evaluate to
5446     <literal>CREATE</literal> or
5447     <literal>USAGE</literal>.
5448    </para>
5449
5450    <table>
5451     <title>Schema Visibility Inquiry Functions</title>
5452     <tgroup cols="3">
5453      <thead>
5454       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5455      </thead>
5456
5457      <tbody>
5458       <row>
5459        <entry><function>pg_table_is_visible</function>(<parameter>tableOID</parameter>)
5460        </entry>
5461        <entry><type>boolean</type></entry>
5462        <entry>is table visible in search path</entry>
5463       </row>
5464       <row>
5465        <entry><function>pg_type_is_visible</function>(<parameter>typeOID</parameter>)
5466        </entry>
5467        <entry><type>boolean</type></entry>
5468        <entry>is type visible in search path</entry>
5469       </row>
5470       <row>
5471        <entry><function>pg_function_is_visible</function>(<parameter>functionOID</parameter>)
5472        </entry>
5473        <entry><type>boolean</type></entry>
5474        <entry>is function visible in search path</entry>
5475       </row>
5476       <row>
5477        <entry><function>pg_operator_is_visible</function>(<parameter>operatorOID</parameter>)
5478        </entry>
5479        <entry><type>boolean</type></entry>
5480        <entry>is operator visible in search path</entry>
5481       </row>
5482       <row>
5483        <entry><function>pg_opclass_is_visible</function>(<parameter>opclassOID</parameter>)
5484        </entry>
5485        <entry><type>boolean</type></entry>
5486        <entry>is operator class visible in search path</entry>
5487       </row>
5488      </tbody>
5489     </tgroup>
5490    </table>
5491
5492    <indexterm zone="functions-misc">
5493     <primary>pg_table_is_visible</primary>
5494    </indexterm>
5495    <indexterm zone="functions-misc">
5496     <primary>pg_type_is_visible</primary>
5497    </indexterm>
5498    <indexterm zone="functions-misc">
5499     <primary>pg_function_is_visible</primary>
5500    </indexterm>
5501    <indexterm zone="functions-misc">
5502     <primary>pg_operator_is_visible</primary>
5503    </indexterm>
5504    <indexterm zone="functions-misc">
5505     <primary>pg_opclass_is_visible</primary>
5506    </indexterm>
5507
5508    <para>
5509     <function>pg_table_is_visible</function> checks whether a table
5510     (or view, or any other kind of <structname>pg_class</> entry) is
5511     <firstterm>visible</> in the current schema search path.  A table
5512     is said to be visible if its containing schema is in the search path
5513     and no table of the same name appears earlier in the search path.
5514     This is equivalent to the statement that the table can be referenced
5515     by name without explicit schema qualification.
5516     For example, to list the names of all visible tables:
5517 <programlisting>
5518 SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
5519 </programlisting>
5520    </para>
5521
5522    <para>
5523     <function>pg_type_is_visible</function>,
5524     <function>pg_function_is_visible</function>,
5525     <function>pg_operator_is_visible</function>, and
5526     <function>pg_opclass_is_visible</function> perform the same sort of
5527     visibility check for types, functions, operators, and operator classes,
5528     respectively.  For functions and operators, an object in the search path
5529     is visible if there is no object of the same name <emphasis>and argument
5530     datatype(s)</> earlier in the path.  For operator classes,
5531     both name and associated index access method are considered.
5532    </para>
5533
5534    <para>
5535     All these functions require object OIDs to identify the object to be
5536     checked.  If you want to test an object by name, it is convenient to use
5537     the OID alias types (<type>regclass</>, <type>regtype</>,
5538     <type>regprocedure</>, or <type>regoperator</>), for example
5539 <programlisting>
5540 SELECT pg_type_is_visible('myschema.widget'::regtype);
5541 </programlisting>
5542     Note that it would not make much sense to test an unqualified name in
5543     this way --- if the name can be recognized at all, it must be visible.
5544    </para>
5545
5546    <table>
5547     <title>Catalog Information Functions</title>
5548     <tgroup cols="3">
5549      <thead>
5550       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5551      </thead>
5552
5553      <tbody>
5554       <row>
5555        <entry><function>pg_get_viewdef</function>(<parameter>viewname</parameter>)</entry>
5556        <entry><type>text</type></entry>
5557        <entry>Get CREATE VIEW command for view</entry>
5558       </row>
5559       <row>
5560        <entry><function>pg_get_viewdef</function>(<parameter>viewOID</parameter>)</entry>
5561        <entry><type>text</type></entry>
5562        <entry>Get CREATE VIEW command for view</entry>
5563       </row>
5564       <row>
5565        <entry><function>pg_get_ruledef</function>(<parameter>ruleOID</parameter>)</entry>
5566        <entry><type>text</type></entry>
5567        <entry>Get CREATE RULE command for rule</entry>
5568       </row>
5569       <row>
5570        <entry><function>pg_get_indexdef</function>(<parameter>indexOID</parameter>)</entry>
5571        <entry><type>text</type></entry>
5572        <entry>Get CREATE INDEX command for index</entry>
5573       </row>
5574       <row>
5575        <entry><function>pg_get_constraintdef</function>(<parameter>constraintOID</parameter>)</entry>
5576        <entry><type>text</type></entry>
5577        <entry>Get definition of a constraint</entry>
5578       </row>
5579       <row>
5580        <entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
5581        <entry><type>name</type></entry>
5582        <entry>Get user name given ID</entry>
5583       </row>
5584      </tbody>
5585     </tgroup>
5586    </table>
5587
5588    <indexterm zone="functions-misc">
5589     <primary>pg_get_viewdef</primary>
5590    </indexterm>
5591
5592    <indexterm zone="functions-misc">
5593     <primary>pg_get_ruledef</primary>
5594    </indexterm>
5595
5596    <indexterm zone="functions-misc">
5597     <primary>pg_get_indexdef</primary>
5598    </indexterm>
5599
5600    <indexterm zone="functions-misc">
5601     <primary>pg_get_constraintdef</primary>
5602    </indexterm>
5603
5604    <indexterm zone="functions-misc">
5605     <primary>pg_get_userbyid</primary>
5606    </indexterm>
5607
5608    <para>
5609     These functions extract information from the system catalogs.
5610     <function>pg_get_viewdef()</function>,
5611     <function>pg_get_ruledef()</function>,
5612     <function>pg_get_indexdef()</function>, and
5613     <function>pg_get_constraintdef()</function> respectively reconstruct the
5614     creating command for a view, rule, index, or constraint.
5615     (Note that this is a decompiled
5616     reconstruction, not the verbatim text of the command.)
5617     At present <function>pg_get_constraintdef()</function> only works for
5618     foreign-key constraints.
5619     <function>pg_get_userbyid()</function> extracts a user's name given a
5620     <structfield>usesysid</structfield> value.
5621    </para>
5622
5623    <table>
5624     <title>Comment Information Functions</title>
5625     <tgroup cols="3">
5626      <thead>
5627       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5628      </thead>
5629
5630      <tbody>
5631       <row>
5632        <entry><function>obj_description</function>(<parameter>objectOID</parameter>, <parameter>tablename</parameter>)</entry>
5633        <entry><type>text</type></entry>
5634        <entry>Get comment for a database object</entry>
5635       </row>
5636       <row>
5637        <entry><function>obj_description</function>(<parameter>objectOID</parameter>)</entry>
5638        <entry><type>text</type></entry>
5639        <entry>Get comment for a database object (<emphasis>deprecated</emphasis>)</entry>
5640       </row>
5641       <row>
5642        <entry><function>col_description</function>(<parameter>tableOID</parameter>, <parameter>columnnumber</parameter>)</entry>
5643        <entry><type>text</type></entry>
5644        <entry>Get comment for a table column</entry>
5645       </row>
5646      </tbody>
5647     </tgroup>
5648    </table>
5649
5650    <indexterm zone="functions-misc">
5651     <primary>obj_description</primary>
5652    </indexterm>
5653
5654    <indexterm zone="functions-misc">
5655     <primary>col_description</primary>
5656    </indexterm>
5657
5658    <para>
5659     These functions extract comments previously stored with the
5660     <command>COMMENT</command> command.  <literal>NULL</literal> is returned if
5661     no comment can be found matching the specified parameters.
5662    </para>
5663
5664    <para>
5665     The two-parameter form of <function>obj_description()</function> returns the
5666     comment for a database object specified by its OID and the name of the
5667     containing system catalog.  For example,
5668     <literal>obj_description(123456,'pg_class')</literal>
5669     would retrieve the comment for a table with OID 123456.
5670     The one-parameter form of <function>obj_description()</function> requires only
5671     the object OID.  It is now deprecated since there is no guarantee that
5672     OIDs are unique across different system catalogs; therefore, the wrong
5673     comment could be returned.
5674    </para>
5675
5676    <para>
5677     <function>col_description()</function> returns the comment for a table column,
5678     which is specified by the OID of its table and its column number.
5679     <function>obj_description()</function> cannot be used for table columns since
5680     columns do not have OIDs of their own.
5681    </para>
5682
5683   </sect1>
5684
5685
5686  <sect1 id="functions-aggregate">
5687   <title>Aggregate Functions</title>
5688
5689   <note>
5690    <title>Author</title>
5691    <para>
5692     Written by Isaac Wilcox <email>isaac@azartmedia.com</email> on 2000-06-16
5693    </para>
5694   </note>
5695
5696   <para>
5697    <firstterm>Aggregate functions</firstterm> compute a single result
5698    value from a set of input values.  The special syntax
5699    considerations for aggregate functions are explained in <xref
5700                                                                  linkend="syntax-aggregates">.  Consult the <citetitle>PostgreSQL
5701    Tutorial</citetitle> for additional introductory information.
5702   </para>
5703
5704   <table tocentry="1">
5705    <title>Aggregate Functions</title>
5706
5707    <tgroup cols="3">
5708     <thead>
5709      <row>
5710       <entry>Function</entry>
5711       <entry>Description</entry>
5712       <entry>Notes</entry>
5713      </row>
5714     </thead>
5715
5716     <tbody>
5717      <row>
5718       <entry>avg(<replaceable class="parameter">expression</replaceable>)</entry>
5719       <entry>the average (arithmetic mean) of all input values</entry>
5720       <entry>
5721        <indexterm>
5722         <primary>average</primary>
5723         <secondary>function</secondary>
5724        </indexterm>
5725        Finding the average value is available on the following data
5726        types: <type>smallint</type>, <type>integer</type>,
5727        <type>bigint</type>, <type>real</type>, <type>double
5728        precision</type>, <type>numeric</type>, <type>interval</type>.
5729        The result is of type <type>numeric</type> for any integer type
5730        input, <type>double precision</type> for floating-point input,
5731        otherwise the same as the input data type.
5732       </entry>
5733      </row>
5734
5735      <row>
5736       <entry><function>count</function>(*)</entry>
5737       <entry>number of input values</entry>
5738       <entry>The return value is of type <type>bigint</type>.</entry>
5739      </row>
5740
5741      <row>
5742       <entry><function>count</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5743       <entry>
5744        Counts the input values for which the value of <replaceable
5745                                                                    class="parameter">expression</replaceable> is not NULL.
5746       </entry>
5747       <entry>The return value is of type <type>bigint</type>.</entry>
5748      </row>
5749
5750      <row>
5751       <entry><function>max</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5752       <entry>the maximum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
5753       <entry>
5754        Available for all numeric, string, and date/time types.  The
5755        result has the same type as the input expression.
5756       </entry>
5757      </row>
5758
5759      <row>
5760       <entry><function>min</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5761       <entry>the minimum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
5762       <entry>
5763        Available for all numeric, string, and date/time types.  The
5764        result has the same type as the input expression.
5765       </entry>
5766      </row>
5767
5768      <row>
5769       <entry><function>stddev</function>(<replaceable
5770                                                       class="parameter">expression</replaceable>)</entry>
5771       <entry>the sample standard deviation of the input values</entry>
5772       <entry>
5773        <indexterm>
5774         <primary>standard deviation</primary>
5775        </indexterm>
5776        Finding the standard deviation is available on the following
5777        data types: <type>smallint</type>, <type>integer</type>,
5778        <type>bigint</type>, <type>real</type>, <type>double
5779        precision</type>, <type>numeric</type>.  The result is of type
5780        <type>double precision</type> for floating-point input,
5781        otherwise <type>numeric</type>.
5782       </entry>
5783      </row>
5784
5785      <row>
5786       <entry><function>sum</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5787       <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
5788       <entry>
5789        Summation is available on the following data types:
5790        <type>smallint</type>, <type>integer</type>,
5791        <type>bigint</type>, <type>real</type>, <type>double
5792        precision</type>, <type>numeric</type>, <type>interval</type>.
5793        The result is of type <type>bigint</type> for <type>smallint</type>
5794        or <type>integer</type> input, <type>numeric</type> for
5795        <type>bigint</type>
5796        input, <type>double precision</type> for floating-point input,
5797        otherwise the same as the input data type.
5798       </entry>
5799      </row>
5800
5801      <row>
5802       <entry><function>variance</function>(<replaceable
5803                                                         class="parameter">expression</replaceable>)</entry>
5804       <entry>the sample variance of the input values</entry>
5805       <entry>
5806        <indexterm>
5807         <primary>variance</primary>
5808        </indexterm>
5809        The variance is the square of the standard deviation.  The
5810        supported data types and result types are the same as for
5811        standard deviation.
5812       </entry>
5813      </row>
5814
5815     </tbody>
5816    </tgroup>
5817   </table>
5818
5819   <para>
5820    It should be noted that except for <function>COUNT</function>,
5821    these functions return NULL when no rows are selected.  In
5822    particular, <function>SUM</function> of no rows returns NULL, not
5823    zero as one might expect.  <function>COALESCE</function> may be
5824    used to substitute zero for NULL when necessary.
5825   </para>
5826
5827  </sect1>
5828
5829
5830  <sect1 id="functions-subquery">
5831   <title>Subquery Expressions</title>
5832
5833   <indexterm>
5834    <primary>exists</primary>
5835   </indexterm>
5836
5837   <indexterm>
5838    <primary>in</primary>
5839   </indexterm>
5840
5841   <indexterm>
5842    <primary>not in</primary>
5843   </indexterm>
5844
5845   <indexterm>
5846    <primary>any</primary>
5847   </indexterm>
5848
5849   <indexterm>
5850    <primary>all</primary>
5851   </indexterm>
5852
5853   <indexterm>
5854    <primary>some</primary>
5855   </indexterm>
5856
5857   <indexterm>
5858    <primary>subqueries</primary>
5859   </indexterm>
5860
5861   <para>
5862    This section describes the <acronym>SQL</acronym>-compliant subquery
5863    expressions available in <productname>PostgreSQL</productname>.
5864    All of the expression forms documented in this section return
5865    Boolean (true/false) results.
5866   </para>
5867
5868   <bridgehead renderas="sect2">EXISTS</bridgehead>
5869
5870 <synopsis>
5871 EXISTS ( <replaceable>subquery</replaceable> )
5872 </synopsis>
5873
5874   <para>
5875    The argument of <token>EXISTS</token> is an arbitrary SELECT statement,
5876    or <firstterm>subquery</firstterm>.  The
5877    subquery is evaluated to determine whether it returns any rows.
5878    If it returns at least one row, the result of <token>EXISTS</token> is
5879    TRUE; if the subquery returns no rows, the result of <token>EXISTS</token>
5880    is FALSE.
5881   </para>
5882
5883   <para>
5884    The subquery can refer to variables from the surrounding query,
5885    which will act as constants during any one evaluation of the subquery.
5886   </para>
5887
5888   <para>
5889    The subquery will generally only be executed far enough to determine
5890    whether at least one row is returned, not all the way to completion.
5891    It is unwise to write a subquery that has any side-effects (such as
5892    calling sequence functions); whether the side-effects occur or not
5893    may be difficult to predict.
5894   </para>
5895
5896   <para>
5897    Since the result depends only on whether any rows are returned,
5898    and not on the contents of those rows, the output list of the
5899    subquery is normally uninteresting.  A common coding convention is
5900    to write all EXISTS tests in the form
5901    <literal>EXISTS(SELECT 1 WHERE ...)</literal>.  There are exceptions to
5902    this rule however, such as subqueries that use <token>INTERSECT</token>.
5903   </para>
5904
5905   <para>
5906    This simple example is like an inner join on col2, but it produces at
5907    most one output row for each tab1 row, even if there are multiple matching
5908    tab2 rows:
5909 <screen>
5910 SELECT col1 FROM tab1
5911     WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
5912 </screen>
5913   </para>
5914
5915    <bridgehead renderas="sect2">IN (scalar form)</bridgehead>
5916
5917 <synopsis>
5918 <replaceable>expression</replaceable> IN
5919  <replaceable>value</replaceable><optional>, ...</optional>)
5920 </synopsis>
5921
5922   <para>
5923    The right-hand side of this form of <token>IN</token> is a parenthesized list
5924    of scalar expressions.  The result is TRUE if the left-hand expression's
5925    result is equal to any of the right-hand expressions.  This is a shorthand
5926    notation for
5927
5928 <synopsis>
5929 <replaceable>expression</replaceable> = <replaceable>value1</replaceable>
5930 OR
5931 <replaceable>expression</replaceable> = <replaceable>value2</replaceable>
5932 OR
5933 ...
5934 </synopsis>
5935
5936    Note that if the left-hand expression yields NULL, or if there are
5937    no equal right-hand values and at least one right-hand expression yields
5938    NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
5939    This is in accordance with SQL's normal rules for Boolean combinations
5940    of NULL values.
5941   </para>
5942
5943   <note>
5944   <para>
5945    This form of <token>IN</token> is not truly a subquery expression, but it
5946    seems best to document it in the same place as subquery <token>IN</token>.
5947   </para>
5948   </note>
5949
5950    <bridgehead renderas="sect2">IN (subquery form)</bridgehead>
5951
5952 <synopsis>
5953 <replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)
5954 </synopsis>
5955
5956   <para>
5957    The right-hand side of this form of <token>IN</token> is a parenthesized
5958    subquery, which must return exactly one column.  The left-hand expression
5959    is evaluated and compared to each row of the subquery result.
5960    The result of <token>IN</token> is TRUE if any equal subquery row is found.
5961    The result is FALSE if no equal row is found (including the special
5962    case where the subquery returns no rows).
5963   </para>
5964
5965   <para>
5966    Note that if the left-hand expression yields NULL, or if there are
5967    no equal right-hand values and at least one right-hand row yields
5968    NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
5969    This is in accordance with SQL's normal rules for Boolean combinations
5970    of NULL values.
5971   </para>
5972
5973   <para>
5974    As with <token>EXISTS</token>, it's unwise to assume that the subquery will
5975    be evaluated completely.
5976   </para>
5977
5978 <synopsis>
5979 (<replaceable>expression</replaceable>,
5980 <replaceable>expres</replaceable><optional>,</optional>)
5981     IN (<replaceable>subquery</replaceable>)
5982 </synopsis>
5983
5984   <para>
5985    The right-hand side of this form of <token>IN</token> is a parenthesized
5986    subquery, which must return exactly as many columns as there are
5987    expressions in the left-hand list.  The left-hand expressions are
5988    evaluated and compared row-wise to each row of the subquery result.
5989    The result of <token>IN</token> is TRUE if any equal subquery row is found.
5990    The result is FALSE if no equal row is found (including the special
5991    case where the subquery returns no rows).
5992   </para>
5993
5994   <para>
5995    As usual, NULLs in the expressions or subquery rows are combined per
5996    the normal rules of SQL Boolean expressions.  Two rows are considered
5997    equal if all their corresponding members are non-null and equal; the rows
5998    are unequal if any corresponding members are non-null and unequal;
5999    otherwise the result of that row comparison is unknown (NULL).
6000    If all the row results are either unequal or NULL, with at least one NULL,
6001    then the result of <token>IN</token> is NULL.
6002   </para>
6003
6004    <bridgehead renderas="sect2">NOT IN (scalar form)</bridgehead>
6005
6006 <synopsis>
6007 <replaceable>expression</replaceable> NOT IN
6008  <replaceable>value</replaceable><optional>, ...</optional>)
6009 </synopsis>
6010
6011   <para>
6012    The right-hand side of this form of <token>NOT IN</token> is a parenthesized list
6013    of scalar expressions.  The result is TRUE if the left-hand expression's
6014    result is unequal to all of the right-hand expressions.  This is a shorthand
6015    notation for
6016
6017 <synopsis>
6018 <replaceable>expression</replaceable> &lt;&gt; <replaceable>value1</replaceable>
6019 AND
6020 <replaceable>expression</replaceable> &lt;&gt; <replaceable>value2</replaceable>
6021 AND
6022 ...
6023 </synopsis>
6024
6025    Note that if the left-hand expression yields NULL, or if there are
6026    no equal right-hand values and at least one right-hand expression yields
6027    NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE
6028    as one might naively expect.
6029    This is in accordance with SQL's normal rules for Boolean combinations
6030    of NULL values.
6031   </para>
6032
6033   <tip>
6034   <para>
6035    <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
6036    cases.  However, NULLs are much more likely to trip up the novice when
6037    working with <token>NOT IN</token> than when working with <token>IN</token>.
6038    It's best to express your condition positively if possible.
6039   </para>
6040   </tip>
6041
6042    <bridgehead renderas="sect2">NOT IN (subquery form)</bridgehead>
6043
6044 <synopsis>
6045 <replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)
6046 </synopsis>
6047
6048   <para>
6049    The right-hand side of this form of <token>NOT IN</token> is a parenthesized
6050    subquery, which must return exactly one column.  The left-hand expression
6051    is evaluated and compared to each row of the subquery result.
6052    The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
6053    are found (including the special case where the subquery returns no rows).
6054    The result is FALSE if any equal row is found.
6055   </para>
6056
6057   <para>
6058    Note that if the left-hand expression yields NULL, or if there are
6059    no equal right-hand values and at least one right-hand row yields
6060    NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE.
6061    This is in accordance with SQL's normal rules for Boolean combinations
6062    of NULL values.
6063   </para>
6064
6065   <para>
6066    As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6067    be evaluated completely.
6068   </para>
6069
6070 <synopsis>
6071 (<replaceable>expression</replaceable>,
6072 <replaceable>expres</replaceable><optional>,</optional>)
6073     NOT IN (<replaceable>subquery</replaceable>)
6074 </synopsis>
6075
6076   <para>
6077    The right-hand side of this form of <token>NOT IN</token> is a parenthesized
6078    subquery, which must return exactly as many columns as there are
6079    expressions in the left-hand list.  The left-hand expressions are
6080    evaluated and compared row-wise to each row of the subquery result.
6081    The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
6082    are found (including the special case where the subquery returns no rows).
6083    The result is FALSE if any equal row is found.
6084   </para>
6085
6086   <para>
6087    As usual, NULLs in the expressions or subquery rows are combined per
6088    the normal rules of SQL Boolean expressions.  Two rows are considered
6089    equal if all their corresponding members are non-null and equal; the rows
6090    are unequal if any corresponding members are non-null and unequal;
6091    otherwise the result of that row comparison is unknown (NULL).
6092    If all the row results are either unequal or NULL, with at least one NULL,
6093    then the result of <token>NOT IN</token> is NULL.
6094   </para>
6095
6096    <bridgehead renderas="sect2">ANY</bridgehead>
6097
6098 <synopsis>
6099 <replaceable>expression</replaceable>
6100 <replaceable>oper</replaceable> ANY (<replaceable>subquery</replaceable>)
6101 <replaceable>expression</replaceable>
6102 <replaceable>oper</replaceable> SOME (<replaceable>subquery</replaceable>)
6103 </synopsis>
6104
6105   <para>
6106    The right-hand side of this form of <token>ANY</token> is a parenthesized
6107    subquery, which must return exactly one column.  The left-hand expression
6108    is evaluated and compared to each row of the subquery result using the
6109    given <replaceable>operator</replaceable>, which must yield a Boolean
6110    result.
6111    The result of <token>ANY</token> is TRUE if any true result is obtained.
6112    The result is FALSE if no true result is found (including the special
6113    case where the subquery returns no rows).
6114   </para>
6115
6116   <para>
6117    <token>SOME</token> is a synonym for <token>ANY</token>.
6118    <token>IN</token> is equivalent to <literal>= ANY</literal>.
6119   </para>
6120
6121   <para>
6122    Note that if there are no successes and at least one right-hand row yields
6123    NULL for the operator's result, the result of the <token>ANY</token> construct
6124    will be NULL, not FALSE.
6125    This is in accordance with SQL's normal rules for Boolean combinations
6126    of NULL values.
6127   </para>
6128
6129   <para>
6130    As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6131    be evaluated completely.
6132   </para>
6133
6134 <synopsis>
6135 (<replaceable>expression</replaceable>,
6136 <replaceable>expres</replaceable><optional>,</optional>optiona<replaceable></replaceable> ANY (<replaceable>subquery</replaceable>)
6137 (<replaceable>expression</replaceable>,
6138 <replaceable>expres</replaceable><optional>,</optional>optiona<replaceable></replaceable> SOME (<replaceable>subquery</replaceable>)
6139 </synopsis>
6140
6141   <para>
6142    The right-hand side of this form of <token>ANY</token> is a parenthesized
6143    subquery, which must return exactly as many columns as there are
6144    expressions in the left-hand list.  The left-hand expressions are
6145    evaluated and compared row-wise to each row of the subquery result,
6146    using the given <replaceable>operator</replaceable>.  Presently,
6147    only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
6148    in row-wise <token>ANY</token> queries.
6149    The result of <token>ANY</token> is TRUE if any equal or unequal row is
6150    found, respectively.
6151    The result is FALSE if no such row is found (including the special
6152    case where the subquery returns no rows).
6153   </para>
6154
6155   <para>
6156    As usual, NULLs in the expressions or subquery rows are combined per
6157    the normal rules of SQL Boolean expressions.  Two rows are considered
6158    equal if all their corresponding members are non-null and equal; the rows
6159    are unequal if any corresponding members are non-null and unequal;
6160    otherwise the result of that row comparison is unknown (NULL).
6161    If there is at least one NULL row result, then the result of <token>ANY</token>
6162    cannot be FALSE; it will be TRUE or NULL.
6163   </para>
6164
6165    <bridgehead renderas="sect2">ALL</bridgehead>
6166
6167 <synopsis>
6168 <replaceable>expression</replaceable>
6169 <replaceable>oper</replaceable> ALL (<replaceable>subquery</replaceable>)
6170 </synopsis>
6171
6172   <para>
6173    The right-hand side of this form of <token>ALL</token> is a parenthesized
6174    subquery, which must return exactly one column.  The left-hand expression
6175    is evaluated and compared to each row of the subquery result using the
6176    given <replaceable>operator</replaceable>, which must yield a Boolean
6177    result.
6178    The result of <token>ALL</token> is TRUE if all rows yield TRUE
6179    (including the special case where the subquery returns no rows).
6180    The result is FALSE if any false result is found.
6181   </para>
6182
6183   <para>
6184    <token>NOT IN</token> is equivalent to <literal>&lt;&gt; ALL</literal>.
6185   </para>
6186
6187   <para>
6188    Note that if there are no failures but at least one right-hand row yields
6189    NULL for the operator's result, the result of the <token>ALL</token> construct
6190    will be NULL, not TRUE.
6191    This is in accordance with SQL's normal rules for Boolean combinations
6192    of NULL values.
6193   </para>
6194
6195   <para>
6196    As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6197    be evaluated completely.
6198   </para>
6199
6200    <synopsis>
6201 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
6202    </synopsis>
6203
6204   <para>
6205    The right-hand side of this form of <token>ALL</token> is a parenthesized
6206    subquery, which must return exactly as many columns as there are
6207    expressions in the left-hand list.  The left-hand expressions are
6208    evaluated and compared row-wise to each row of the subquery result,
6209    using the given <replaceable>operator</replaceable>.  Presently,
6210    only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
6211    in row-wise <token>ALL</token> queries.
6212    The result of <token>ALL</token> is TRUE if all subquery rows are equal
6213    or unequal, respectively (including the special
6214    case where the subquery returns no rows).
6215    The result is FALSE if any row is found to be unequal or equal,
6216    respectively.
6217   </para>
6218
6219   <para>
6220    As usual, NULLs in the expressions or subquery rows are combined per
6221    the normal rules of SQL Boolean expressions.  Two rows are considered
6222    equal if all their corresponding members are non-null and equal; the rows
6223    are unequal if any corresponding members are non-null and unequal;
6224    otherwise the result of that row comparison is unknown (NULL).
6225    If there is at least one NULL row result, then the result of <token>ALL</token>
6226    cannot be TRUE; it will be FALSE or NULL.
6227   </para>
6228
6229    <bridgehead renderas="sect2">Row-wise comparison</bridgehead>
6230
6231    <synopsis>
6232 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
6233 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <replaceable>expression</replaceable><optional>, ...</optional>)
6234    </synopsis>
6235
6236   <para>
6237    The left-hand side is a list of scalar expressions.  The right-hand side
6238    can be either a list of scalar expressions of the same length, or a
6239    parenthesized subquery, which must return exactly as many columns as there
6240    are expressions on the left-hand side.  Furthermore, the subquery cannot
6241    return more than one row.  (If it returns zero rows, the result is taken to
6242    be NULL.)  The left-hand side is evaluated and compared row-wise to the
6243    single subquery result row, or to the right-hand expression list.
6244    Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
6245    in row-wise comparisons.
6246    The result is TRUE if the two rows are equal or unequal, respectively.
6247   </para>
6248
6249   <para>
6250    As usual, NULLs in the expressions or subquery rows are combined per
6251    the normal rules of SQL Boolean expressions.  Two rows are considered
6252    equal if all their corresponding members are non-null and equal; the rows
6253    are unequal if any corresponding members are non-null and unequal;
6254    otherwise the result of the row comparison is unknown (NULL).
6255   </para>
6256
6257  </sect1>
6258
6259 </chapter>
6260
6261 <!-- Keep this comment at the end of the file
6262 Local variables:
6263 mode:sgml
6264 sgml-omittag:nil
6265 sgml-shorttag:t
6266 sgml-minimize-attributes:nil
6267 sgml-always-quote-attributes:t
6268 sgml-indent-step:1
6269 sgml-indent-data:t
6270 sgml-parent-document:nil
6271 sgml-default-dtd-file:"./reference.ced"
6272 sgml-exposed-tags:nil
6273 sgml-local-catalogs:("/usr/lib/sgml/catalog")
6274 sgml-local-ecat-files:nil
6275 End:
6276 -->