func.sgml 216 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.126 2002/09/24 20:14:58 petere Exp $
3
PostgreSQL documentation
4
-->
5

6 7 8 9 10
 <chapter id="functions">
  <title>Functions and Operators</title>

  <indexterm zone="functions">
   <primary>functions</primary>
11 12
  </indexterm>

13 14
  <indexterm zone="functions">
   <primary>operators</primary>
15 16
  </indexterm>

17
  <para>
18 19 20 21 22 23 24 25
   <productname>PostgreSQL</productname> provides a large number of
   functions and operators for the built-in data types.  Users can also
   define their own functions and operators, as described in the
   <citetitle>Programmer's Guide</citetitle>.  The
   <application>psql</application> commands <command>\df</command> and
   <command>\do</command> can be used to show the list of all actually
   available functions and operators, respectively.
  </para>
26

27 28 29 30 31 32 33 34 35 36
  <para>
   If you are concerned about portability then take note that most of
   the functions and operators described in this chapter, with the
   exception of the most trivial arithmetic and comparison operators
   and some explicitly marked functions, are not specified by the
   <acronym>SQL</acronym>
   standard. Some of this extended functionality is present in other
   <acronym>RDBMS</acronym> products, and in many cases this
   functionality is compatible and consistent between various products.
  </para>
37

38 39 40 41 42 43 44

  <sect1 id="functions-logical">
   <title>Logical Operators</title>

   <indexterm zone="functions-logical">
    <primary>operators</primary>
    <secondary>logical</secondary>
45 46 47
   </indexterm>

   <indexterm>
48 49 50
    <primary>Boolean</primary>
    <secondary>operators</secondary>
    <see>operators, logical</see>
51 52
   </indexterm>

53 54
   <para>
    The usual logical operators are available:
55

56 57 58 59
    <indexterm>
     <primary>and</primary>
     <secondary>operator</secondary>
    </indexterm>
60

61 62 63 64
    <indexterm>
     <primary>or</primary>
     <secondary>operator</secondary>
    </indexterm>
65

66 67 68 69
    <indexterm>
     <primary>not</primary>
     <secondary>operator</secondary>
    </indexterm>
70

71 72 73 74 75
    <simplelist>
     <member>AND</member>
     <member>OR</member>
     <member>NOT</member>
    </simplelist>
76

77
    <acronym>SQL</acronym> uses a three-valued Boolean logic where the null value represents
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150
    <quote>unknown</quote>.  Observe the following truth tables:

    <informaltable>
     <tgroup cols="4">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry><replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>NULL</entry>
        <entry>FALSE</entry>
        <entry>NULL</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>

    <informaltable>
     <tgroup cols="2">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry>NOT <replaceable>a</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
       </row>
151

152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178
       <row>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>
   </para>
  </sect1>

  <sect1 id="functions-comparison">
   <title>Comparison Operators</title>

   <indexterm zone="functions-comparison">
    <primary>comparison</primary>
    <secondary>operators</secondary>
   </indexterm>

   <table>
    <title>Comparison Operators</title>
    <tgroup cols="2">
     <thead>
179
      <row>
180 181
       <entry>Operator</entry>
       <entry>Description</entry>
182
      </row>
183
     </thead>
184

185
     <tbody>
186
      <row>
187 188
       <entry> <literal>&lt;</literal> </entry>
       <entry>less than</entry>
189
      </row>
190 191

      <row>
192 193
       <entry> <literal>&gt;</literal> </entry>
       <entry>greater than</entry>
194
      </row>
195 196

      <row>
197 198
       <entry> <literal>&lt;=</literal> </entry>
       <entry>less than or equal to</entry>
199 200 201
      </row>

      <row>
202 203
       <entry> <literal>&gt;=</literal> </entry>
       <entry>greater than or equal to</entry>
204 205 206
      </row>

      <row>
207 208
       <entry> <literal>=</literal> </entry>
       <entry>equal</entry>
209 210 211
      </row>

      <row>
212 213
       <entry> <literal>&lt;&gt;</literal> or <literal>!=</literal> </entry>
       <entry>not equal</entry>
214 215 216
      </row>
     </tbody>
    </tgroup>
217
   </table>
218

219 220 221 222 223 224 225 226
   <note>
    <para>
     The <literal>!=</literal> operator is converted to
     <literal>&lt;&gt;</literal> in the parser stage.  It is not
     possible to implement <literal>!=</literal> and
     <literal>&lt;&gt;</literal> operators that do different things.
    </para>
   </note>
227

228
   <para>
229 230 231 232 233 234
    Comparison operators are available for all data types where this
    makes sense.  All comparison operators are binary operators that
    return values of type <type>boolean</type>; expressions like
    <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is
    no <literal>&lt;</literal> operator to compare a Boolean value with
    <literal>3</literal>).
235
   </para>
236

237 238 239 240 241 242 243
   <para>
    <indexterm>
     <primary>between</primary>
    </indexterm>
    In addition to the comparison operators, the special
    <token>BETWEEN</token> construct is available.
    <synopsis>
Peter Eisentraut's avatar
Peter Eisentraut committed
244
<replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
245 246 247
    </synopsis>
    is equivalent to
    <synopsis>
Bruce Momjian's avatar
Bruce Momjian committed
248
<replaceable>a</replaceable> &gt;= <replaceable>x</replaceable> AND <replaceable>a</replaceable> &lt;= <replaceable>y</replaceable>
249 250 251
    </synopsis>
    Similarly,
    <synopsis>
Peter Eisentraut's avatar
Peter Eisentraut committed
252
<replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
253 254 255
    </synopsis>
    is equivalent to
    <synopsis>
Bruce Momjian's avatar
Bruce Momjian committed
256
<replaceable>a</replaceable> &lt; <replaceable>x</replaceable> OR <replaceable>a</replaceable> &gt; <replaceable>y</replaceable>
257 258 259 260 261
    </synopsis>
    There is no difference between the two respective forms apart from
    the <acronym>CPU</acronym> cycles required to rewrite the first one
    into the second one internally.
   </para>
Peter Eisentraut's avatar
Peter Eisentraut committed
262

263
   <para>
264
    To check whether a value is or is not null, use the constructs
265
    <synopsis>
266 267
<replaceable>expression</replaceable> IS NULL
<replaceable>expression</replaceable> IS NOT NULL
268 269 270
    </synopsis>
    or the equivalent, but less standard, constructs
    <synopsis>
271 272
<replaceable>expression</replaceable> ISNULL
<replaceable>expression</replaceable> NOTNULL
273 274
    </synopsis>
   </para>
275

276 277 278
   <para>
    Do <emphasis>not</emphasis> write
    <literal><replaceable>expression</replaceable> = NULL</literal>
279 280 281
    because <literal>NULL</> is not <quote>equal to</quote>
    <literal>NULL</>.  (The null value represents an unknown value,
    and it is not known whether two unknown values are equal.)
282
   </para>
283

284 285 286 287
   <para>
    Some applications may (incorrectly) require that
    <literal><replaceable>expression</replaceable> = NULL</literal>
    returns true if <replaceable>expression</replaceable> evaluates to
288
    the null value.  To support these applications, the run-time option
289 290 291 292 293 294 295
    <varname>transform_null_equals</varname> can be turned on (e.g.,
    <literal>SET transform_null_equals TO ON;</literal>).
    <productname>PostgreSQL</productname> will then convert
    <literal>x = NULL</literal> clauses to
    <literal>x IS NULL</literal>.  This was 
    the default behavior in releases 6.5 through 7.1.
   </para>
296

297 298 299
   <para>
    Boolean values can also be tested using the constructs
    <synopsis>
300 301 302 303 304 305
<replaceable>expression</replaceable> IS TRUE
<replaceable>expression</replaceable> IS NOT TRUE
<replaceable>expression</replaceable> IS FALSE
<replaceable>expression</replaceable> IS NOT FALSE
<replaceable>expression</replaceable> IS UNKNOWN
<replaceable>expression</replaceable> IS NOT UNKNOWN
306 307
    </synopsis>
    These are similar to <literal>IS NULL</literal> in that they will
308 309
    always return true or false, never a null value, even when the operand is null.
    A null input is treated as the logical value <quote>unknown</>.
310 311
   </para>
  </sect1>
312

313 314
  <sect1 id="functions-math">
   <title>Mathematical Functions and Operators</title>
315

316 317 318 319 320 321 322
   <para>
    Mathematical operators are provided for many
    <productname>PostgreSQL</productname> types. For types without
    common mathematical conventions for all possible permutations 
    (e.g. date/time types) we
    describe the actual behavior in subsequent sections.
   </para>
323 324

   <table>
325
    <title>Mathematical Operators</title>
326

327
    <tgroup cols="4">
328 329
     <thead>
      <row>
330 331
       <entry>Name</entry>
       <entry>Description</entry>
332 333 334 335 336 337 338
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
339 340 341 342
       <entry> <literal>+</literal> </entry>
       <entry>Addition</entry>
       <entry>2 + 3</entry>
       <entry>5</entry>
343
      </row>
344

345
      <row>
346 347 348 349
       <entry> <literal>-</literal> </entry>
       <entry>Subtraction</entry>
       <entry>2 - 3</entry>
       <entry>-1</entry>
350
      </row>
351

352
      <row>
353 354 355 356
       <entry> <literal>*</literal> </entry>
       <entry>Multiplication</entry>
       <entry>2 * 3</entry>
       <entry>6</entry>
357
      </row>
358

359
      <row>
360 361 362 363
       <entry> <literal>/</literal> </entry>
       <entry>Division (integer division truncates results)</entry>
       <entry>4 / 2</entry>
       <entry>2</entry>
364
      </row>
365

366
      <row>
367 368 369 370
       <entry> <literal>%</literal> </entry>
       <entry>Modulo (remainder)</entry>
       <entry>5 % 4</entry>
       <entry>1</entry>
371
      </row>
372

373
      <row>
374 375 376 377
       <entry> <literal>^</literal> </entry>
       <entry>Exponentiation</entry>
       <entry>2.0 ^ 3.0</entry>
       <entry>8</entry>
378 379
      </row>

380 381 382 383 384 385
      <row>
       <entry> <literal>|/</literal> </entry>
       <entry>Square root</entry>
       <entry>|/ 25.0</entry>
       <entry>5</entry>
      </row>
386

387 388 389 390 391 392
      <row>
       <entry> <literal>||/</literal> </entry>
       <entry>Cube root</entry>
       <entry>||/ 27.0</entry>
       <entry>3</entry>
      </row>
393

394 395 396 397 398 399
      <row>
       <entry> <literal>!</literal> </entry>
       <entry>Factorial</entry>
       <entry>5 !</entry>
       <entry>120</entry>
      </row>
400

401 402 403 404 405 406
      <row>
       <entry> <literal>!!</literal> </entry>
       <entry>Factorial (prefix operator)</entry>
       <entry>!! 5</entry>
       <entry>120</entry>
      </row>
407

408 409 410 411 412 413
      <row>
       <entry> <literal>@</literal> </entry>
       <entry>Absolute value</entry>
       <entry>@ -5.0</entry>
       <entry>5</entry>
      </row>
414

415 416 417 418 419 420
      <row>
       <entry> <literal>&amp;</literal> </entry>
       <entry>Binary AND</entry>
       <entry>91 & 15</entry>
       <entry>11</entry>
      </row>
421

422 423 424 425 426 427
      <row>
       <entry> <literal>|</literal> </entry>
       <entry>Binary OR</entry>
       <entry>32 | 3</entry>
       <entry>35</entry>
      </row>
428

429 430 431 432 433 434
      <row>
       <entry> <literal>#</literal> </entry>
       <entry>Binary XOR</entry>
       <entry>17 # 5</entry>
       <entry>20</entry>
      </row>
435

436 437 438 439 440 441
      <row>
       <entry> <literal>~</literal> </entry>
       <entry>Binary NOT</entry>
       <entry>~1</entry>
       <entry>-2</entry>
      </row>
442

443 444 445 446 447 448
      <row>
       <entry> &lt;&lt; </entry>
       <entry>Binary shift left</entry>
       <entry>1 &lt;&lt; 4</entry>
       <entry>16</entry>
      </row>
449

450 451 452 453 454 455
      <row>
       <entry> &gt;&gt; </entry>
       <entry>Binary shift right</entry>
       <entry>8 &gt;&gt; 2</entry>
       <entry>2</entry>
      </row>
456

457 458 459
     </tbody>
    </tgroup>
   </table>
460

461 462 463
   <para>
    The <quote>binary</quote> operators are also available for the bit
    string types <type>BIT</type> and <type>BIT VARYING</type>.
464

465 466
    <table>
     <title>Bit String Binary Operators</title>
467

468 469 470 471 472 473 474
     <tgroup cols="2">
      <thead>
       <row>
        <entry>Example</entry>
        <entry>Result</entry>
       </row>
      </thead>
475

476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503
      <tbody>
       <row>
        <entry>B'10001' & B'01101'</entry>
        <entry>00001</entry>
       </row>
       <row>
        <entry>B'10001' | B'01101'</entry>
        <entry>11101</entry>
       </row>
       <row>
        <entry>B'10001' # B'01101'</entry>
        <entry>11110</entry>
       </row>
       <row>
        <entry>~ B'10001'</entry>
        <entry>01110</entry>
       </row>
       <row>
        <entry>B'10001' << 3</entry>
        <entry>01000</entry>
       </row>
       <row>
        <entry>B'10001' >> 2</entry>
        <entry>00100</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
504

505 506 507 508 509
    Bit string arguments to <literal>&</literal>, <literal>|</literal>,
    and <literal>#</literal> must be of equal length.  When bit
    shifting, the original length of the string is preserved, as shown
    here.
   </para>
510

511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654
   <table tocentry="1">
    <title>Mathematical Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>abs</function>(<replaceable>x</replaceable>)</entry>
       <entry>(same as x)</entry>
       <entry>absolute value</entry>
       <entry><literal>abs(-17.4)</literal></entry>
       <entry>17.4</entry>
      </row>

      <row>
       <entry><function>cbrt</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>cube root</entry>
       <entry><literal>cbrt(27.0)</literal></entry>
       <entry>3</entry>
      </row>

      <row>
       <entry><function>ceil</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>smallest integer not less than argument</entry>
       <entry><literal>ceil(-42.8)</literal></entry>
       <entry>-42</entry>
      </row>

      <row>
       <entry><function>degrees</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>radians to degrees</entry>
       <entry><literal>degrees(0.5)</literal></entry>
       <entry>28.6478897565412</entry>
      </row>

      <row>
       <entry><function>exp</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>exponential</entry>
       <entry><literal>exp(1.0)</literal></entry>
       <entry>2.71828182845905</entry>
      </row>

      <row>
       <entry><function>floor</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>largest integer not greater than argument</entry>
       <entry><literal>floor(-42.8)</literal></entry>
       <entry>-43</entry>
      </row>

      <row>
       <entry><function>ln</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>natural logarithm</entry>
       <entry><literal>ln(2.0)</literal></entry>
       <entry>0.693147180559945</entry>
      </row>

      <row>
       <entry><function>log</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>base 10 logarithm</entry>
       <entry><literal>log(100.0)</literal></entry>
       <entry>2</entry>
      </row>

      <row>
       <entry><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
        <parameter>x</parameter> <type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>logarithm to base <parameter>b</parameter></entry>
       <entry><literal>log(2.0, 64.0)</literal></entry>
       <entry>6.0000000000</entry>
      </row>

      <row>
       <entry><function>mod</function>(<parameter>y</parameter>,
        <parameter>x</parameter>)</entry>
       <entry>(same as argument types)</entry>
       <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
       <entry><literal>mod(9,4)</literal></entry>
       <entry>1</entry>
      </row>

      <row>
       <entry><function>pi</function>()</entry>
       <entry><type>dp</type></entry>
       <entry><quote>Pi</quote> constant</entry>
       <entry><literal>pi()</literal></entry>
       <entry>3.14159265358979</entry>
      </row>

      <row>
       <entry><function>pow</function>(<parameter>e</parameter> <type>dp</type>,
        <parameter>n</parameter> <type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>raise a number to exponent <parameter>e</parameter></entry>
       <entry><literal>pow(9.0, 3.0)</literal></entry>
       <entry>729</entry>
      </row>

      <row>
       <entry><function>radians</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>degrees to radians</entry>
       <entry><literal>radians(45.0)</literal></entry>
       <entry>0.785398163397448</entry>
      </row>

      <row>
       <entry><function>random</function>()</entry>
       <entry><type>dp</type></entry>
       <entry>value between 0.0 to 1.0</entry>
       <entry><literal>random()</literal></entry>
       <entry></entry>
      </row>

      <row>
       <entry><function>round</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>round to nearest integer</entry>
       <entry><literal>round(42.4)</literal></entry>
       <entry>42</entry>
      </row>

      <row>
       <entry><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>round to <parameter>s</parameter> decimal places</entry>
       <entry><literal>round(42.4382, 2)</literal></entry>
       <entry>42.44</entry>
      </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 661 662
      <entry></entry>
     </row>
-->
663 664 665 666 667 668 669
      <row>
       <entry><function>sign</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>sign of the argument (-1, 0, +1)</entry>
       <entry><literal>sign(-8.4)</literal></entry>
       <entry>-1</entry>
      </row>
670

671 672 673 674 675 676 677
      <row>
       <entry><function>sqrt</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>square root</entry>
       <entry><literal>sqrt(2.0)</literal></entry>
       <entry>1.4142135623731</entry>
      </row>
678

679 680 681 682 683 684 685
      <row>
       <entry><function>trunc</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>truncate toward zero</entry>
       <entry><literal>trunc(42.8)</literal></entry>
       <entry>42</entry>
      </row>
686

687 688
      <row>
       <entry><function>trunc</function>(<type>numeric</type>,
689
        <parameter>r</parameter> <type>integer</type>)</entry>
690 691 692 693 694
       <entry><type>numeric</type></entry>
       <entry>truncate to <parameter>s</parameter> decimal places</entry>
       <entry><literal>trunc(42.4382, 2)</literal></entry>
       <entry>42.43</entry>
      </row>
695

696 697 698
     </tbody>
    </tgroup>
   </table>
699

700 701 702 703 704 705 706 707 708 709 710 711 712 713
   <para>
    In the table above, <literal>dp</literal> indicates <type>double precision</type>.
    The functions <function>exp</function>, <function>ln</function>,
    <function>log</function>, <function>pow</function>,
    <function>round</function> (1 argument), <function>sqrt</function>,
    and <function>trunc</function> (1 argument) are also available for
    the type <type>numeric</type> in place of
    <type>double precision</type>.
    Functions returning a <type>numeric</type> result take
    <type>numeric</type> input arguments, unless otherwise specified.
    Many of these functions are implemented on top
    of the host system's C library; accuracy and behavior in boundary cases
    could therefore vary depending on the host system.
   </para>
714

715 716
   <table>
    <title>Trigonometric Functions</title>
717

718 719 720 721 722 723 724
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Description</entry>
      </row>
     </thead>
725

726 727 728 729 730
     <tbody>
      <row>
       <entry><function>acos</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse cosine</entry>
      </row>
731

732 733 734 735
      <row>
       <entry><function>asin</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse sine</entry>
      </row>
736

737 738 739 740
      <row>
       <entry><function>atan</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse tangent</entry>
      </row>
741

742 743 744 745
      <row>
       <entry><function>atan2</function>(<replaceable>x</replaceable>,
        <replaceable>y</replaceable>)</entry>
       <entry>inverse tangent of
Bruce Momjian's avatar
Bruce Momjian committed
746
        <replaceable>x</replaceable>/<replaceable>y</replaceable></entry>
747
      </row>
748

749 750 751 752
      <row>
       <entry><function>cos</function>(<replaceable>x</replaceable>)</entry>
       <entry>cosine</entry>
      </row>
753

754 755 756 757
      <row>
       <entry><function>cot</function>(<replaceable>x</replaceable>)</entry>
       <entry>cotangent</entry>
      </row>
758

759 760 761 762
      <row>
       <entry><function>sin</function>(<replaceable>x</replaceable>)</entry>
       <entry>sine</entry>
      </row>
763

764 765 766 767 768 769 770
      <row>
       <entry><function>tan</function>(<replaceable>x</replaceable>)</entry>
       <entry>tangent</entry>
      </row>
     </tbody>
    </tgroup>
   </table>
771

772 773 774 775
   <para>
    All trigonometric functions have arguments and return values of
    type <type>double precision</type>.
   </para>
776

777
  </sect1>
778 779


780 781
  <sect1 id="functions-string">
   <title>String Functions and Operators</title>
782

783 784 785 786 787 788 789 790 791 792 793 794
   <para>
    This section describes functions and operators for examining and
    manipulating string values.  Strings in this context include values
    of all the types <type>CHARACTER</type>, <type>CHARACTER
     VARYING</type>, and <type>TEXT</type>.  Unless otherwise noted, all
    of the functions listed below work on all of these types, but be
    wary of potential effects of the automatic padding when using the
    <type>CHARACTER</type> type.  Generally, the functions described
    here also work on data of non-string types by converting that data
    to a string representation first.  Some functions also exist
    natively for bit-string types.
   </para>
795

796 797 798 799 800 801 802
   <para>
    <acronym>SQL</acronym> defines some string functions with a special syntax where
    certain keywords rather than commas are used to separate the
    arguments.  Details are in <xref linkend="functions-string-sql">.
    These functions are also implemented using the regular syntax for
    function invocation.  (See <xref linkend="functions-string-other">.)
   </para>
803

804 805 806 807 808 809 810 811 812 813 814 815
   <table id="functions-string-sql">
    <title><acronym>SQL</acronym> String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>
816

817 818 819 820 821 822 823 824 825 826 827 828 829 830 831
     <tbody>
      <row>
       <entry> <parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter> </entry>
       <entry> <type>text</type> </entry>
       <entry>
        string concatenation
        <indexterm>
         <primary>character strings</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'Postgre' || 'SQL'</literal></entry>
       <entry><literal>PostgreSQL</literal></entry>
      </row>
832

833 834 835 836 837 838 839
      <row>
       <entry><function>bit_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>number of bits in string</entry>
       <entry><literal>bit_length('jose')</literal></entry>
       <entry><literal>32</literal></entry>
      </row>
Peter Eisentraut's avatar
Peter Eisentraut committed
840

841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858
      <row>
       <entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>
        number of characters in string
        <indexterm>
         <primary>character strings</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary>character strings</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>char_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>
859

Tatsuo Ishii's avatar
Tatsuo Ishii committed
860 861 862 863
      <row>
       <entry><function>convert</function>(<parameter>string</parameter>
       using <parameter>conversion_name</parameter>)</entry>
       <entry><type>text</type></entry>
864 865 866 867 868 869 870 871 872
       <entry>
        Change encoding using specified conversion name.  Conversions
        can be defined by <command>CREATE CONVERSION</command>.  Also
        there are some pre-defined conversion names. See <xref
        linkend="conversion-names"> for available conversion
        names.
       </entry>
       <entry><literal>convert('PostgreSQL' using iso_8859_1_to_utf_8)</literal></entry>
       <entry><literal>'PostgreSQL'</literal> in UNICODE (UTF-8) encoding</entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
873 874
      </row>

875 876 877 878 879 880 881
      <row>
       <entry><function>lower</function>(<parameter>string</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert string to lower case.</entry>
       <entry><literal>lower('TOM')</literal></entry>
       <entry><literal>tom</literal></entry>
      </row>
882

883 884 885 886 887 888 889
      <row>
       <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>number of bytes in string</entry>
       <entry><literal>octet_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>
890

891 892 893 894 895 896 897 898 899 900 901 902
      <row>
       <entry><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        insert substring
        <indexterm>
         <primary>overlay</primary>
        </indexterm>
       </entry>
       <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
       <entry><literal>Thomas</literal></entry>
      </row>
903

904 905 906 907 908 909 910
      <row>
       <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>location of specified substring</entry>
       <entry><literal>position('om' in 'Thomas')</literal></entry>
       <entry><literal>3</literal></entry>
      </row>
911

912 913 914 915 916 917 918 919 920 921 922 923
      <row>
       <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        extract substring
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
       <entry><literal>hom</literal></entry>
      </row>
924

925
      <row>
926
       <entry><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</entry>
927 928
       <entry><type>text</type></entry>
       <entry>
929
        extract substring matching POSIX regular expression
930 931 932 933
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
934
       <entry><literal>substring('Thomas' from '...$')</literal></entry>
935 936
       <entry><literal>mas</literal></entry>
      </row>
937

938 939 940 941 942 943 944 945 946 947 948 949 950
      <row>
       <entry><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</entry>
       <entry><type>text</type></entry>
       <entry>
        extract substring matching SQL99 regular expression
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>
       <entry><literal>oma</literal></entry>
      </row>

951 952 953 954 955 956 957 958 959 960 961 962 963 964 965
      <row>
       <entry>
        <function>trim</function>(<optional>leading | trailing | both</optional>
        <optional><parameter>characters</parameter></optional> from
        <parameter>string</parameter>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Removes the longest string containing only the
        <parameter>characters</parameter> (a space by default) from the
        beginning/end/both ends of the <parameter>string</parameter>.
       </entry>
       <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>
966

967 968 969 970 971 972 973 974 975 976
      <row>
       <entry><function>upper</function>(<parameter>string</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert string to upper case.</entry>
       <entry><literal>upper('tom')</literal></entry>
       <entry><literal>TOM</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>
977

978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295
   <para>
    Additional string manipulation functions are available and are
    listed below.  Some of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed above.
   </para>

   <table id="functions-string-other">
    <title>Other String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>ascii</function>(<type>text</type>)</entry>
       <entry>integer</entry>
       <entry>Returns the <acronym>ASCII</acronym> code of the first character of the argument.</entry>
       <entry><literal>ascii('x')</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Remove (trim) the longest string consisting only of characters
        in <parameter>trim</parameter> from the start and end of
        <parameter>string</parameter>.
       </entry>
       <entry><literal>btrim('xyxtrimyyx','xy')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>chr</function>(<type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Returns the character with the given <acronym>ASCII</acronym> code.</entry>
       <entry><literal>chr(65)</literal></entry>
       <entry><literal>A</literal></entry>
      </row>

      <row>
       <entry>
        <function>convert</function>(<parameter>string</parameter>
        <type>text</type>,
        <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
        <parameter>dest_encoding</parameter> <type>name</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Converts string using <parameter>dest_encoding</parameter>.
        The original encoding is specified by
        <parameter>src_encoding</parameter>.  If
        <parameter>src_encoding</parameter> is omitted, database
        encoding is assumed.
       </entry>
       <entry><literal>convert('text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
       <entry><literal>text_in_unicode</literal> represented in ISO 8859-1</entry>
      </row>

      <row>
       <entry>
        <function>decode</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>type</parameter> <type>text</type>)
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Decodes binary data from <parameter>string</parameter> previously 
        encoded with encode().  Parameter type is same as in encode().
       </entry>
       <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
       <entry><literal>123\000\001</literal></entry>
      </row>       

      <row>
       <entry>
        <function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
        <parameter>type</parameter> <type>text</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Encodes binary data to <acronym>ASCII</acronym>-only representation.  Supported
        types are: 'base64', 'hex', 'escape'.
       </entry>
       <entry><literal>encode('123\\000\\001', 'base64')</literal></entry>
       <entry><literal>MTIzAAE=</literal></entry>
      </row>       

      <row>
       <entry><function>initcap</function>(<type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Converts first letter of each word (whitespace separated) to upper case.</entry>
       <entry><literal>initcap('hi thomas')</literal></entry>
       <entry><literal>Hi Thomas</literal></entry>
      </row>

      <row>
       <entry><function>length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>
        length of string
        <indexterm>
         <primary>character strings</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary>character strings</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry>
        <function>lpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)
       </entry>
       <entry>text</entry>
       <entry>
        Fills up the <parameter>string</parameter> to length
        <parameter>length</parameter> by prepending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated (on the
        right).
       </entry>
       <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>xyxhi</literal></entry>
      </row>

      <row>
       <entry><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>text</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Removes the longest string containing only characters from
        <parameter>trim</parameter> from the start of the string.
       </entry>
       <entry><literal>ltrim('zzzytrim','xyz')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>pg_client_encoding</function>()</entry>
       <entry><type>name</type></entry>
       <entry>
        Returns current client encoding name.
       </entry>
       <entry><literal>pg_client_encoding()</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
      </row>

      <row>
       <entry><function>quote_ident</function>(<parameter>string</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Returns the given string suitably quoted to be used as an identifier
	in an SQL query string.
	Quotes are added only if necessary (i.e., if the string contains
	non-identifier characters or would be case-folded).
	Embedded quotes are properly doubled.
       </entry>
       <entry><literal>quote_ident('Foo')</literal></entry>
       <entry><literal>"Foo"</literal></entry>
      </row>

      <row>
       <entry><function>quote_literal</function>(<parameter>string</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Returns the given string suitably quoted to be used as a literal
	in an SQL query string.
	Embedded quotes and backslashes are properly doubled.
       </entry>
       <entry><literal>quote_literal('O\'Reilly')</literal></entry>
       <entry><literal>'O''Reilly'</literal></entry>
      </row>

      <row>
       <entry><function>repeat</function>(<type>text</type>, <type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Repeat text a number of times.</entry>
       <entry><literal>repeat('Pg', 4)</literal></entry>
       <entry><literal>PgPgPgPg</literal></entry>
      </row>

      <row>
       <entry><function>replace</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>from</parameter> <type>text</type>,
       <parameter>to</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Replace all occurrences in <parameter>string</parameter> of substring
        <parameter>from</parameter> with substring <parameter>to</parameter>
       </entry>
       <entry><literal>replace('abcdefabcdef', 'cd', 'XX')</literal></entry>
       <entry><literal>abXXefabXXef</literal></entry>
      </row>

      <row>
       <entry>
        <function>rpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Fills up the <parameter>string</parameter> to length
        <parameter>length</parameter> by appending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated.
       </entry>
       <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>hixyx</literal></entry>
      </row>

      <row>
       <entry><function>rtrim</function>(<parameter>string</parameter>
        text, <parameter>trim</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Removes the longest string containing only characters from
        <parameter>trim</parameter> from the end of the string.
       </entry>
       <entry><literal>rtrim('trimxxxx','x')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>split_part</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>delimiter</parameter> <type>text</type>,
       <parameter>column</parameter> <type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Split <parameter>string</parameter> on <parameter>delimiter</parameter>
        returning the resulting (one based) <parameter>column</parameter> number.
       </entry>
       <entry><literal>split_part('abc~@~def~@~ghi','~@~',2)</literal></entry>
       <entry><literal>def</literal></entry>
      </row>

      <row>
       <entry><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Locates specified substring. (same as
        <literal>position(<parameter>substring</parameter> in
         <parameter>string</parameter>)</literal>, but note the reversed
        argument order)
       </entry>
       <entry><literal>strpos('high','ig')</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

      <row>
       <entry><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Extracts specified substring. (same as
        <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
       </entry>
       <entry><literal>substr('alphabet', 3, 2)</literal></entry>
       <entry><literal>ph</literal></entry>
      </row>

      <row>
       <entry><function>to_ascii</function>(<type>text</type>
        <optional>, <parameter>encoding</parameter></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>Converts text from multibyte encoding to <acronym>ASCII</acronym>.</entry>
       <entry><literal>to_ascii('Karel')</literal></entry>
       <entry><literal>Karel</literal></entry>
      </row>

      <row>
       <entry><function>to_hex</function>(<parameter>number</parameter> <type>integer</type>
       or <type>bigint</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert <parameter>number</parameter> to its equivalent hexadecimal
        representation.
       </entry>
       <entry><literal>to_hex(9223372036854775807::bigint)</literal></entry>
       <entry><literal>7fffffffffffffff</literal></entry>
      </row>

      <row>
       <entry>
        <function>translate</function>(<parameter>string</parameter>
        <type>text</type>,
        <parameter>from</parameter> <type>text</type>,
        <parameter>to</parameter> <type>text</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Any character in <parameter>string</parameter> that matches a
        character in the <parameter>from</parameter> set is replaced by
        the corresponding character in the <parameter>to</parameter>
        set.
       </entry>
       <entry><literal>translate('12345', '14', 'ax')</literal></entry>
       <entry><literal>a23x5</literal></entry>
      </row>       
      
     </tbody>
    </tgroup>
   </table>

   <para>
    The <function>to_ascii</function> function supports conversion from
1296
    <literal>LATIN1</>, <literal>LATIN2</>, and <literal>WIN1250</> only.
1297 1298 1299
   </para>


Tatsuo Ishii's avatar
Tatsuo Ishii committed
1300
   <table id="conversion-names">
1301
    <title>Built-in Conversions</title>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1302 1303 1304
    <tgroup cols="3">
     <thead>
      <row>
1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318
       <entry>Conversion Name
        <footnote>
         <para>
          The conversion names follow a standard naming scheme: The
          official name of the source encoding with all
          non-alphanumeric characters replaced by underscores followed
          by <literal>_to_</literal> followed by the equally processed
          destination encoding name. Therefore the names might deviate
          from the customary encoding names.
         </para>
        </footnote>
       </entry>
       <entry>Source Encoding</entry>
       <entry>Destination Encoding</entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1319 1320
      </row>
     </thead>
1321

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1322 1323
     <tbody>
      <row>
1324 1325 1326
       <entry><literal>ascii_to_mic</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1327 1328 1329
      </row>

      <row>
1330 1331 1332
       <entry><literal>ascii_to_utf_8</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1333 1334 1335
      </row>

      <row>
1336 1337 1338
       <entry><literal>big5_to_euc_tw</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1339 1340 1341
      </row>

      <row>
1342 1343 1344
       <entry><literal>big5_to_mic</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1345 1346 1347
      </row>

      <row>
1348 1349 1350
       <entry><literal>big5_to_utf_8</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1351 1352 1353
      </row>

      <row>
1354 1355 1356
       <entry><literal>euc_cn_to_mic</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1357 1358 1359
      </row>

      <row>
1360 1361 1362
       <entry><literal>euc_cn_to_utf_8</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1363 1364 1365
      </row>

      <row>
1366 1367 1368
       <entry><literal>euc_jp_to_mic</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1369 1370 1371
      </row>

      <row>
1372 1373 1374
       <entry><literal>euc_jp_to_sjis</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>SJIS</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1375 1376 1377
      </row>

      <row>
1378 1379 1380
       <entry><literal>euc_jp_to_utf_8</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1381 1382 1383
      </row>

      <row>
1384 1385 1386
       <entry><literal>euc_kr_to_mic</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1387 1388 1389
      </row>

      <row>
1390 1391 1392
       <entry><literal>euc_kr_to_utf_8</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1393 1394 1395
      </row>

      <row>
1396 1397 1398
       <entry><literal>euc_tw_to_big5</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>BIG5</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1399 1400 1401
      </row>

      <row>
1402 1403 1404
       <entry><literal>euc_tw_to_mic</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1405 1406 1407
      </row>

      <row>
1408 1409 1410
       <entry><literal>euc_tw_to_utf_8</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1411 1412 1413
      </row>

      <row>
1414 1415 1416
       <entry><literal>gb18030_to_utf_8</literal></entry>
       <entry><literal>GB18030</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1417 1418 1419
      </row>

      <row>
1420 1421 1422
       <entry><literal>gbk_to_utf_8</literal></entry>
       <entry><literal>GBK</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1423 1424 1425
      </row>

      <row>
1426 1427 1428
       <entry><literal>iso_8859_10_to_utf_8</literal></entry>
       <entry><literal>LATIN6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1429 1430 1431
      </row>

      <row>
1432 1433 1434
       <entry><literal>iso_8859_13_to_utf_8</literal></entry>
       <entry><literal>LATIN7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1435 1436 1437
      </row>

      <row>
1438 1439 1440
       <entry><literal>iso_8859_14_to_utf_8</literal></entry>
       <entry><literal>LATIN8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1441 1442 1443
      </row>

      <row>
1444 1445 1446
       <entry><literal>iso_8859_15_to_utf_8</literal></entry>
       <entry><literal>LATIN9</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1447 1448 1449
      </row>

      <row>
1450 1451 1452
       <entry><literal>iso_8859_16_to_utf_8</literal></entry>
       <entry><literal>LATIN10</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1453 1454 1455
      </row>

      <row>
1456 1457 1458
       <entry><literal>iso_8859_1_to_mic</literal></entry>
       <entry><literal>LATIN1</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1459 1460 1461
      </row>

      <row>
1462 1463 1464
       <entry><literal>iso_8859_1_to_utf_8</literal></entry>
       <entry><literal>LATIN1</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1465 1466 1467
      </row>

      <row>
1468 1469 1470
       <entry><literal>iso_8859_2_to_mic</literal></entry>
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1471 1472 1473
      </row>

      <row>
1474 1475 1476
       <entry><literal>iso_8859_2_to_utf_8</literal></entry>
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1477 1478 1479
      </row>

      <row>
1480
       <entry><literal>iso_8859_2_to_windows_1250</literal></entry>
1481 1482
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>WIN1250</literal></entry>
1483 1484 1485
      </row>

      <row>
1486 1487 1488
       <entry><literal>iso_8859_3_to_mic</literal></entry>
       <entry><literal>LATIN3</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1489 1490 1491
      </row>

      <row>
1492 1493 1494
       <entry><literal>iso_8859_3_to_utf_8</literal></entry>
       <entry><literal>LATIN3</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1495 1496 1497
      </row>

      <row>
1498 1499 1500
       <entry><literal>iso_8859_4_to_mic</literal></entry>
       <entry><literal>LATIN4</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1501 1502 1503
      </row>

      <row>
1504 1505 1506
       <entry><literal>iso_8859_4_to_utf_8</literal></entry>
       <entry><literal>LATIN4</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1507 1508 1509
      </row>

      <row>
1510
       <entry><literal>iso_8859_5_to_koi8_r</literal></entry>
1511 1512
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>KOI8</literal></entry>
1513 1514 1515
      </row>

      <row>
1516 1517 1518
       <entry><literal>iso_8859_5_to_mic</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1519 1520 1521
      </row>

      <row>
1522 1523 1524
       <entry><literal>iso_8859_5_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1525 1526 1527
      </row>

      <row>
1528
       <entry><literal>iso_8859_5_to_windows_1251</literal></entry>
1529 1530
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>WIN</literal></entry>
1531 1532 1533
      </row>

      <row>
1534
       <entry><literal>iso_8859_5_to_windows_866</literal></entry>
1535 1536
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>ALT</literal></entry>
1537 1538 1539
      </row>

      <row>
1540 1541 1542
       <entry><literal>iso_8859_6_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1543 1544 1545
      </row>

      <row>
1546 1547 1548
       <entry><literal>iso_8859_7_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1549 1550 1551
      </row>

      <row>
1552 1553 1554
       <entry><literal>iso_8859_8_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1555 1556 1557
      </row>

      <row>
1558 1559 1560
       <entry><literal>iso_8859_9_to_utf_8</literal></entry>
       <entry><literal>LATIN5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1561 1562 1563
      </row>

      <row>
1564 1565 1566
       <entry><literal>johab_to_utf_8</literal></entry>
       <entry><literal>JOHAB</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1567 1568
      </row>

1569
      <row>
1570
       <entry><literal>koi8_r_to_iso_8859_5</literal></entry>
1571 1572
       <entry><literal>KOI8</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
1573 1574 1575
      </row>

      <row>
1576
       <entry><literal>koi8_r_to_mic</literal></entry>
1577 1578
       <entry><literal>KOI8</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1579 1580 1581
      </row>

      <row>
1582
       <entry><literal>koi8_r_to_utf_8</literal></entry>
1583 1584
       <entry><literal>KOI8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
1585 1586 1587
      </row>

      <row>
1588
       <entry><literal>koi8_r_to_windows_1251</literal></entry>
1589 1590
       <entry><literal>KOI8</literal></entry>
       <entry><literal>WIN</literal></entry>
1591 1592 1593
      </row>

      <row>
1594
       <entry><literal>koi8_r_to_windows_866</literal></entry>
1595 1596
       <entry><literal>KOI8</literal></entry>
       <entry><literal>ALT</literal></entry>
1597 1598 1599
      </row>

      <row>
1600 1601 1602
       <entry><literal>mic_to_ascii</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
1603 1604
      </row>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1605
      <row>
1606 1607 1608
       <entry><literal>mic_to_big5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>BIG5</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1609 1610
      </row>

1611
      <row>
1612 1613 1614
       <entry><literal>mic_to_euc_cn</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
1615 1616
      </row>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1617
      <row>
1618 1619 1620
       <entry><literal>mic_to_euc_jp</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1621 1622
      </row>

1623
      <row>
1624 1625 1626
       <entry><literal>mic_to_euc_kr</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
1627 1628
      </row>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1629
      <row>
1630 1631 1632
       <entry><literal>mic_to_euc_tw</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1633 1634
      </row>

1635
      <row>
1636 1637 1638
       <entry><literal>mic_to_iso_8859_1</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN1</literal></entry>
1639 1640 1641
      </row>

      <row>
1642 1643 1644
       <entry><literal>mic_to_iso_8859_2</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN2</literal></entry>
1645 1646 1647
      </row>

      <row>
1648 1649 1650
       <entry><literal>mic_to_iso_8859_3</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN3</literal></entry>
1651 1652 1653
      </row>

      <row>
1654 1655 1656
       <entry><literal>mic_to_iso_8859_4</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN4</literal></entry>
1657 1658 1659
      </row>

      <row>
1660 1661 1662
       <entry><literal>mic_to_iso_8859_5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
1663 1664 1665
      </row>

      <row>
1666
       <entry><literal>mic_to_koi8_r</literal></entry>
1667 1668
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>KOI8</literal></entry>
1669 1670
      </row>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1671
      <row>
1672 1673 1674
       <entry><literal>mic_to_sjis</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>SJIS</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1675 1676
      </row>

1677
      <row>
1678
       <entry><literal>mic_to_windows_1250</literal></entry>
1679 1680
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>WIN1250</literal></entry>
1681 1682 1683
      </row>

      <row>
1684
       <entry><literal>mic_to_windows_1251</literal></entry>
1685 1686
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>WIN</literal></entry>
1687 1688 1689
      </row>

      <row>
1690
       <entry><literal>mic_to_windows_866</literal></entry>
1691 1692
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>ALT</literal></entry>
1693 1694
      </row>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
1695
      <row>
1696 1697 1698
       <entry><literal>sjis_to_euc_jp</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1699 1700 1701
      </row>

      <row>
1702 1703 1704
       <entry><literal>sjis_to_mic</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1705 1706 1707
      </row>

      <row>
1708 1709 1710
       <entry><literal>sjis_to_utf_8</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1711 1712 1713
      </row>

      <row>
1714 1715 1716
       <entry><literal>tcvn_to_utf_8</literal></entry>
       <entry><literal>TCVN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1717 1718 1719
      </row>

      <row>
1720 1721 1722
       <entry><literal>uhc_to_utf_8</literal></entry>
       <entry><literal>UHC</literal></entry>
       <entry><literal>UNICODE</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1723 1724 1725
      </row>

      <row>
1726 1727 1728
       <entry><literal>utf_8_to_ascii</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1729 1730 1731
      </row>

      <row>
1732 1733 1734
       <entry><literal>utf_8_to_big5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>BIG5</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1735 1736 1737
      </row>

      <row>
1738 1739 1740
       <entry><literal>utf_8_to_euc_cn</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1741 1742 1743
      </row>

      <row>
1744 1745 1746
       <entry><literal>utf_8_to_euc_jp</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1747 1748 1749
      </row>

      <row>
1750 1751 1752
       <entry><literal>utf_8_to_euc_kr</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1753 1754 1755
      </row>

      <row>
1756 1757 1758
       <entry><literal>utf_8_to_euc_tw</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1759 1760 1761
      </row>

      <row>
1762 1763 1764
       <entry><literal>utf_8_to_gb18030</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>GB18030</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1765 1766 1767
      </row>

      <row>
1768 1769 1770
       <entry><literal>utf_8_to_gbk</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>GBK</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1771 1772 1773
      </row>

      <row>
1774 1775 1776
       <entry><literal>utf_8_to_iso_8859_1</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN1</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1777 1778 1779
      </row>

      <row>
1780 1781 1782
       <entry><literal>utf_8_to_iso_8859_10</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN6</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1783 1784 1785
      </row>

      <row>
1786 1787 1788
       <entry><literal>utf_8_to_iso_8859_13</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN7</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1789 1790 1791
      </row>

      <row>
1792 1793 1794
       <entry><literal>utf_8_to_iso_8859_14</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN8</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1795 1796 1797
      </row>

      <row>
1798 1799 1800
       <entry><literal>utf_8_to_iso_8859_15</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN9</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1801 1802 1803
      </row>

      <row>
1804 1805 1806
       <entry><literal>utf_8_to_iso_8859_16</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN10</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1807 1808 1809
      </row>

      <row>
1810 1811 1812
       <entry><literal>utf_8_to_iso_8859_2</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN2</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1813 1814 1815
      </row>

      <row>
1816 1817 1818
       <entry><literal>utf_8_to_iso_8859_3</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN3</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1819 1820 1821
      </row>

      <row>
1822 1823 1824
       <entry><literal>utf_8_to_iso_8859_4</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN4</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1825 1826 1827
      </row>

      <row>
1828 1829 1830
       <entry><literal>utf_8_to_iso_8859_5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1831 1832 1833
      </row>

      <row>
1834 1835 1836
       <entry><literal>utf_8_to_iso_8859_6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_6</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1837 1838 1839
      </row>

      <row>
1840 1841 1842
       <entry><literal>utf_8_to_iso_8859_7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_7</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1843 1844 1845
      </row>

      <row>
1846 1847 1848
       <entry><literal>utf_8_to_iso_8859_8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_8</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1849 1850 1851
      </row>

      <row>
1852 1853 1854
       <entry><literal>utf_8_to_iso_8859_9</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN5</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1855 1856 1857
      </row>

      <row>
1858 1859 1860
       <entry><literal>utf_8_to_johab</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>JOHAB</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1861 1862 1863
      </row>

      <row>
1864
       <entry><literal>utf_8_to_koi8_r</literal></entry>
1865 1866
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>KOI8</literal></entry>
1867 1868 1869
      </row>

      <row>
1870 1871 1872
       <entry><literal>utf_8_to_sjis</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>SJIS</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1873 1874 1875
      </row>

      <row>
1876 1877 1878
       <entry><literal>utf_8_to_tcvn</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>TCVN</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1879 1880 1881
      </row>

      <row>
1882 1883 1884
       <entry><literal>utf_8_to_uhc</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>UHC</literal></entry>
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1885 1886
      </row>

1887
      <row>
1888
       <entry><literal>utf_8_to_windows_1250</literal></entry>
1889 1890
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN1250</literal></entry>
1891 1892 1893
      </row>

      <row>
1894
       <entry><literal>utf_8_to_windows_1251</literal></entry>
1895 1896
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN</literal></entry>
1897 1898 1899
      </row>

      <row>
1900
       <entry><literal>utf_8_to_windows_1256</literal></entry>
1901 1902
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN1256</literal></entry>
1903 1904 1905
      </row>

      <row>
1906
       <entry><literal>utf_8_to_windows_866</literal></entry>
1907 1908
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ALT</literal></entry>
1909 1910 1911
      </row>

      <row>
1912
       <entry><literal>utf_8_to_windows_874</literal></entry>
1913 1914
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN874</literal></entry>
1915
      </row>
1916

1917
      <row>
1918
       <entry><literal>windows_1250_to_iso_8859_2</literal></entry>
1919 1920
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>LATIN2</literal></entry>
1921
      </row>
1922

1923
      <row>
1924
       <entry><literal>windows_1250_to_mic</literal></entry>
1925 1926
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1927 1928 1929
      </row>

      <row>
1930
       <entry><literal>windows_1250_to_utf_8</literal></entry>
1931 1932
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>UNICODE</literal></entry>
1933 1934
      </row>

1935
      <row>
1936
       <entry><literal>windows_1251_to_iso_8859_5</literal></entry>
1937 1938
       <entry><literal>WIN</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
1939
      </row>
1940

1941
      <row>
1942
       <entry><literal>windows_1251_to_koi8_r</literal></entry>
1943 1944
       <entry><literal>WIN</literal></entry>
       <entry><literal>KOI8</literal></entry>
1945 1946
      </row>

1947
      <row>
1948
       <entry><literal>windows_1251_to_mic</literal></entry>
1949 1950
       <entry><literal>WIN</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1951
      </row>
1952

1953
      <row>
1954
       <entry><literal>windows_1251_to_utf_8</literal></entry>
1955 1956
       <entry><literal>WIN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
1957
      </row>
1958

1959
      <row>
1960
       <entry><literal>windows_1251_to_windows_866</literal></entry>
1961 1962
       <entry><literal>WIN</literal></entry>
       <entry><literal>ALT</literal></entry>
1963 1964
      </row>

1965
      <row>
1966
       <entry><literal>windows_1256_to_utf_8</literal></entry>
1967 1968
       <entry><literal>WIN1256</literal></entry>
       <entry><literal>UNICODE</literal></entry>
1969
      </row>
1970

1971
      <row>
1972
       <entry><literal>windows_866_to_iso_8859_5</literal></entry>
1973 1974
       <entry><literal>ALT</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
1975
      </row>
1976

1977
      <row>
1978
       <entry><literal>windows_866_to_koi8_r</literal></entry>
1979 1980
       <entry><literal>ALT</literal></entry>
       <entry><literal>KOI8</literal></entry>
1981
      </row>
1982

1983
      <row>
1984
       <entry><literal>windows_866_to_mic</literal></entry>
1985 1986
       <entry><literal>ALT</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
1987 1988
      </row>

1989
      <row>
1990
       <entry><literal>windows_866_to_utf_8</literal></entry>
1991 1992 1993 1994 1995
       <entry><literal>ALT</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
1996
       <entry><literal>windows_866_to_windows_1251</literal></entry>
1997 1998 1999 2000 2001
       <entry><literal>ALT</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
2002
       <entry><literal>windows_874_to_utf_8</literal></entry>
2003 2004 2005 2006
       <entry><literal>WIN874</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

2007 2008 2009
     </tbody>
    </tgroup>
   </table>
2010

2011
  </sect1>
2012

2013

2014 2015
  <sect1 id="functions-binarystring">
   <title>Binary String Functions and Operators</title>
2016

2017 2018 2019 2020 2021
   <para>
    This section describes functions and operators for examining and
    manipulating binary string values.  Strings in this context include
    values of the type <type>BYTEA</type>.
   </para>
2022

2023 2024 2025 2026 2027 2028 2029 2030 2031 2032
   <para>
    <acronym>SQL</acronym> defines some string functions with a
    special syntax where 
    certain keywords rather than commas are used to separate the
    arguments.  Details are in
    <xref linkend="functions-binarystring-sql">.
    Some functions are also implemented using the regular syntax for
    function invocation.
    (See <xref linkend="functions-binarystring-other">.)
   </para>
2033

2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045
   <table id="functions-binarystring-sql">
    <title><acronym>SQL</acronym> Binary String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>
2046

2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061
     <tbody>
      <row>
       <entry> <parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter> </entry>
       <entry> <type>bytea</type> </entry>
       <entry>
        string concatenation
        <indexterm>
         <primary>binary strings</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'\\\\Postgre'::bytea || '\\047SQL\\000'::bytea</literal></entry>
       <entry><literal>\\Postgre'SQL\000</literal></entry>
      </row>
2062

2063 2064 2065 2066 2067 2068 2069
      <row>
       <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>number of bytes in binary string</entry>
       <entry><literal>octet_length('jo\\000se'::bytea)</literal></entry>
       <entry><literal>5</literal></entry>
      </row>
2070

2071 2072 2073 2074 2075 2076 2077
      <row>
       <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>location of specified substring</entry>
      <entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>
       <entry><literal>3</literal></entry>
      </row>
2078

2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090
      <row>
       <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>bytea</type></entry>
       <entry>
        extract substring
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>
       <entry><literal>h\000o</literal></entry>
      </row>
2091

2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109
      <row>
       <entry>
        <function>trim</function>(<optional>both</optional>
        <parameter>characters</parameter> from
        <parameter>string</parameter>)
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Removes the longest string containing only the
        <parameter>characters</parameter> from the
        beginning/end/both ends of the <parameter>string</parameter>.
       </entry>
       <entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>
2110

2111 2112 2113 2114 2115
   <para>
    Additional binary string manipulation functions are available and are
    listed below.  Some of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed above.
   </para>
2116

2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128
   <table id="functions-binarystring-other">
    <title>Other Binary String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>
2129

2130 2131 2132 2133 2134 2135 2136 2137 2138
     <tbody>
      <row>
       <entry><function>btrim</function>(<parameter>string</parameter>
        <type>bytea</type> <parameter>trim</parameter> <type>bytea</type>)</entry>
       <entry><type>bytea</type></entry>
       <entry>
        Remove (trim) the longest string consisting only of characters
        in <parameter>trim</parameter> from the start and end of
        <parameter>string</parameter>.
2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158
      </entry>
      <entry><literal>btrim('\\000trim\\000'::bytea,'\\000'::bytea)</literal></entry>
      <entry><literal>trim</literal></entry>
     </row>

     <row>
      <entry><function>length</function>(<parameter>string</parameter>)</entry>
      <entry><type>integer</type></entry>
      <entry>
       length of binary string
       <indexterm>
        <primary>binary strings</primary>
	<secondary>length</secondary>
       </indexterm>
       <indexterm>
        <primary>length</primary>
	<secondary>binary strings</secondary>
	<see>binary strings, length</see>
       </indexterm>
      </entry>
2159 2160
      <entry><literal>length('jo\\000se'::bytea)</literal></entry>
      <entry><literal>5</literal></entry>
2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197
     </row>

     <row>
      <entry>
       <function>encode</function>(<parameter>string</parameter> <type>bytea</type>,
              <parameter>type</parameter> <type>text</type>)
      </entry>
      <entry><type>text</type></entry>
      <entry>
       Encodes binary string to <acronym>ASCII</acronym>-only representation.  Supported
       types are: 'base64', 'hex', 'escape'.
      </entry>
      <entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>
      <entry><literal>123\000456</literal></entry>
     </row>       

     <row>
      <entry>
       <function>decode</function>(<parameter>string</parameter> <type>text</type>,
              <parameter>type</parameter> <type>text</type>)
      </entry>
      <entry><type>bytea</type></entry>
      <entry>
       Decodes binary string from <parameter>string</parameter> previously 
       encoded with encode().  Parameter type is same as in encode().
      </entry>
      <entry><literal>decode('123\\000456', 'escape')</literal></entry>
      <entry><literal>123\000456</literal></entry>
     </row>       

    </tbody>
   </tgroup>
  </table>

 </sect1>


2198 2199 2200
 <sect1 id="functions-matching">
  <title>Pattern Matching</title>

2201
   <para>
2202
    There are three separate approaches to pattern matching provided by
2203 2204
    <productname>PostgreSQL</productname>:  the traditional
    <acronym>SQL</acronym> 
2205
    <function>LIKE</function> operator, the more recent
2206
    <acronym>SQL99</acronym> 
2207
    <function>SIMILAR TO</function> operator, and
2208 2209
    <acronym>POSIX</acronym>-style regular expressions.
    Additionally, a pattern matching function,
2210 2211
    <function>SUBSTRING</function>, is available, using either
    <acronym>SQL99</acronym>-style or POSIX-style regular expressions.
2212 2213 2214 2215 2216 2217 2218 2219
   </para>

   <tip>
    <para>
     If you have pattern matching needs that go beyond this,
     consider writing a user-defined function in Perl or Tcl.
    </para>
   </tip>
2220

2221
  <sect2 id="functions-like">
2222
   <title><function>LIKE</function></title>
2223

2224 2225 2226 2227
   <indexterm>
    <primary>like</primary>
   </indexterm>

2228 2229 2230 2231
    <synopsis>
<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
    </synopsis>
2232

2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243
    <para>
     Every <replaceable>pattern</replaceable> defines a set of strings.
     The <function>LIKE</function> expression returns true if the
     <replaceable>string</replaceable> is contained in the set of
     strings represented by <replaceable>pattern</replaceable>.  (As
     expected, the <function>NOT LIKE</function> expression returns
     false if <function>LIKE</function> returns true, and vice versa.
     An equivalent expression is
     <literal>NOT (<replaceable>string</replaceable> LIKE
      <replaceable>pattern</replaceable>)</literal>.)
    </para>
2244 2245

    <para>
2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258
     If <replaceable>pattern</replaceable> does not contain percent
     signs or underscore, then the pattern only represents the string
     itself; in that case <function>LIKE</function> acts like the
     equals operator.  An underscore (<literal>_</literal>) in
     <replaceable>pattern</replaceable> stands for (matches) any single
     character; a percent sign (<literal>%</literal>) matches any string
     of zero or more characters.
    </para>

    <informalexample>
     <para>
      Some examples:
      <programlisting>
2259 2260 2261 2262
'abc' LIKE 'abc'    <lineannotation>true</lineannotation>
'abc' LIKE 'a%'     <lineannotation>true</lineannotation>
'abc' LIKE '_b_'    <lineannotation>true</lineannotation>
'abc' LIKE 'c'      <lineannotation>false</lineannotation>
2263 2264 2265
      </programlisting>
     </para>
    </informalexample>
2266

2267
   <para>
2268
    <function>LIKE</function> pattern matches always cover the entire
2269
    string.  To match a pattern anywhere within a string, the
2270
    pattern must therefore start and end with a percent sign.
2271
   </para>
2272 2273

   <para>
2274 2275 2276 2277
    To match a literal underscore or percent sign without matching
    other characters, the respective character in
    <replaceable>pattern</replaceable> must be 
    preceded by the escape character.  The default escape
2278
    character is the backslash but a different one may be selected by
2279 2280 2281 2282 2283 2284 2285
    using the <literal>ESCAPE</literal> clause.  To match the escape
    character itself, write two escape characters.
   </para>

   <para>
    Note that the backslash already has a special meaning in string
    literals, so to write a pattern constant that contains a backslash
2286 2287 2288
    you must write two backslashes in the query.  Thus, writing a pattern
    that actually matches a literal backslash means writing four backslashes
    in the query.  You can avoid this by selecting a different escape
Peter Eisentraut's avatar
Peter Eisentraut committed
2289
    character with <literal>ESCAPE</literal>; then backslash is not special
2290
    to <function>LIKE</function> anymore. (But it is still special to the string
2291 2292 2293 2294 2295
    literal parser, so you still need two of them.)
   </para>

   <para>
    It's also possible to select no escape character by writing
2296
    <literal>ESCAPE ''</literal>.  This effectively disables the
Bruce Momjian's avatar
Bruce Momjian committed
2297 2298
    escape mechanism, which makes it impossible to turn off the
    special meaning of underscore and percent signs in the pattern.
2299
   </para>
2300 2301

   <para>
2302 2303
    The keyword <token>ILIKE</token> can be used instead of
    <token>LIKE</token> to make the match case insensitive according
2304
    to the active locale.  This is not in the <acronym>SQL</acronym> standard but is a
2305
    <productname>PostgreSQL</productname> extension.
2306
   </para>
2307

2308 2309
   <para>
    The operator <literal>~~</literal> is equivalent to
2310 2311 2312
    <function>LIKE</function>, and <literal>~~*</literal> corresponds to
    <function>ILIKE</function>.  There are also
    <literal>!~~</literal> and <literal>!~~*</literal> operators that
2313
    represent <function>NOT LIKE</function> and <function>NOT
2314
    ILIKE</function>.  All of these operators are
2315
    <productname>PostgreSQL</productname>-specific.
2316 2317
   </para>
  </sect2>
2318

2319

2320 2321
  <sect2 id="functions-sql99-regexp">
   <title><function>SIMILAR TO</function> and <acronym>SQL99</acronym>
2322
     Regular Expressions</title>
2323

2324 2325
   <indexterm zone="functions-sql99-regexp">
    <primary>regular expressions</primary>
2326
    <!-- <seealso>pattern matching</seealso> breaks index build -->
2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455
   </indexterm>

   <indexterm>
    <primary>similar to</primary>
   </indexterm>

   <indexterm>
    <primary>substring</primary>
   </indexterm>

    <synopsis>
<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
    </synopsis>

    <para>
     The <function>SIMILAR TO</function> operator returns true or false
     depending on whether its pattern matches the given string.  It is
     much like <function>LIKE</function>, except that it interprets the
     pattern using <acronym>SQL99</acronym>'s definition of a regular
     expression.
     <acronym>SQL99</acronym>'s regular expressions are a curious cross
     between <function>LIKE</function> notation and common regular expression
     notation.
    </para>

    <para>
     Like <function>LIKE</function>, the  <function>SIMILAR TO</function>
     operator succeeds only if its pattern matches the entire string;
     this is unlike common regular expression practice, wherein the pattern
     may match any part of the string.
     Also like
     <function>LIKE</function>, <function>SIMILAR TO</function> uses
     <literal>%</> and <literal>_</> as wildcard characters denoting
     any string and any single character, respectively (these are
     comparable to <literal>.*</> and <literal>.</> in POSIX regular
     expressions).
    </para>

    <para>
     In addition to these facilities borrowed from <function>LIKE</function>,
     <function>SIMILAR TO</function> supports these pattern-matching
     metacharacters borrowed from POSIX regular expressions:

    <itemizedlist>
     <listitem>
      <para>
       <literal>|</literal> denotes alternation (either of two alternatives).
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>*</literal> denotes repetition of the previous item zero
       or more times.
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>+</literal> denotes repetition of the previous item one
       or more times.
      </para>
     </listitem>
     <listitem>
      <para>
       Parentheses <literal>()</literal> may be used to group items into
       a single logical item.
      </para>
     </listitem>
     <listitem>
      <para>
       A bracket expression <literal>[...]</literal> specifies a character
       class, just as in POSIX regular expressions.
      </para>
     </listitem>
    </itemizedlist>

     Notice that bounded repetition (<literal>?</> and <literal>{...}</>)
     are not provided, though they exist in POSIX.  Also, dot (<literal>.</>)
     is not a metacharacter.
    </para>

    <para>
     As with <function>LIKE</>, a backslash disables the special meaning
     of any of these metacharacters; or a different escape character can
     be specified with <literal>ESCAPE</>.
    </para>

    <informalexample>
     <para>
      Some examples:
      <programlisting>
'abc' SIMILAR TO 'abc'      <lineannotation>true</lineannotation>
'abc' SIMILAR TO 'a'        <lineannotation>false</lineannotation>
'abc' SIMILAR TO '%(b|d)%'  <lineannotation>true</lineannotation>
'abc' SIMILAR TO '(b|c)%'   <lineannotation>false</lineannotation>
      </programlisting>
     </para>
    </informalexample>

    <para>
     The SUBSTRING function with three parameters,
     <function>SUBSTRING</function>(<parameter>string</parameter> FROM
     <replaceable>pattern</replaceable> FOR
     <replaceable>escape</replaceable>), provides extraction of a substring
     that matches a SQL99 regular expression pattern.  As with SIMILAR TO,
     the specified pattern must match to the entire data string, else the
     function fails and returns NULL.  To indicate the part of the pattern
     that should be returned on success, SQL99 specifies that the pattern
     must contain two occurrences of the escape character followed by
     double quote (<literal>"</>).  The text matching the portion of the
     pattern between these markers is returned.
    </para>

    <informalexample>
     <para>
      Some examples:
      <programlisting>
SUBSTRING('foobar' FROM '%#"o_b#"%' FOR '#')   <lineannotation>oob</lineannotation>
SUBSTRING('foobar' FROM '#"o_b#"%' FOR '#')    <lineannotation>NULL</lineannotation>
      </programlisting>
     </para>
    </informalexample>

  </sect2>

  <sect2 id="functions-posix-regexp">
   <title><acronym>POSIX</acronym> Regular Expressions</title>

   <indexterm zone="functions-posix-regexp">
2456 2457 2458 2459
    <primary>regular expressions</primary>
    <seealso>pattern matching</seealso>
   </indexterm>

2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471
   <table>
    <title>Regular Expression Match Operators</title>

    <tgroup cols="3">
     <thead>
      <row>
       <entry>Operator</entry>
       <entry>Description</entry>
       <entry>Example</entry>
      </row>
     </thead>

2472 2473 2474 2475 2476 2477
      <tbody>
       <row>
        <entry> <literal>~</literal> </entry>
        <entry>Matches regular expression, case sensitive</entry>
        <entry><literal>'thomas' ~ '.*thomas.*'</literal></entry>
       </row>
2478

2479 2480 2481 2482 2483
       <row>
        <entry> <literal>~*</literal> </entry>
        <entry>Matches regular expression, case insensitive</entry>
        <entry><literal>'thomas' ~* '.*Thomas.*'</literal></entry>
       </row>
2484

2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502
       <row>
        <entry> <literal>!~</literal> </entry>
        <entry>Does not match regular expression, case sensitive</entry>
        <entry><literal>'thomas' !~ '.*Thomas.*'</literal></entry>
       </row>

       <row>
        <entry> <literal>!~*</literal> </entry>
        <entry>Does not match regular expression, case insensitive</entry>
        <entry><literal>'thomas' !~* '.*vadim.*'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

    <para>
     <acronym>POSIX</acronym> regular expressions provide a more
     powerful means for 
2503 2504
     pattern matching than the <function>LIKE</function> and
     <function>SIMILAR TO</> operators.
2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523
     Many Unix tools such as <command>egrep</command>,
     <command>sed</command>, or <command>awk</command> use a pattern
     matching language that is similar to the one described here.
    </para>

    <para>
     A regular expression is a character sequence that is an
     abbreviated definition of a set of strings (a <firstterm>regular
      set</firstterm>).  A string is said to match a regular expression
     if it is a member of the regular set described by the regular
     expression.  As with <function>LIKE</function>, pattern characters
     match string characters exactly unless they are special characters
     in the regular expression language --- but regular expressions use
     different special characters than <function>LIKE</function> does.
     Unlike <function>LIKE</function> patterns, a
     regular expression is allowed to match anywhere within a string, unless
     the regular expression is explicitly anchored to the beginning or
     end of the string.
    </para>
2524

2525 2526 2527 2528
    <informalexample>
     <para>
      Some examples:
      <programlisting>
2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556
'abc' ~ 'abc'    <lineannotation>true</lineannotation>
'abc' ~ '^a'     <lineannotation>true</lineannotation>
'abc' ~ '(b|d)'  <lineannotation>true</lineannotation>
'abc' ~ '^(b|c)' <lineannotation>false</lineannotation>
      </programlisting>
     </para>
    </informalexample>

    <para>
     The SUBSTRING function with two parameters,
     <function>SUBSTRING</function>(<parameter>string</parameter> FROM
     <replaceable>pattern</replaceable>), provides extraction of a substring
     that matches a POSIX regular expression pattern.  It returns NULL if
     there is no match, otherwise the portion of the text that matched the
     pattern.  But if the pattern contains any parentheses, the portion
     of the text that matched the first parenthesized subexpression (the
     one whose left parenthesis comes first) is
     returned.  You can always put parentheses around the whole expression
     if you want to use parentheses within it without triggering this
     exception.
    </para>

    <informalexample>
     <para>
      Some examples:
      <programlisting>
SUBSTRING('foobar' FROM 'o.b')     <lineannotation>oob</lineannotation>
SUBSTRING('foobar' FROM 'o(.)b')   <lineannotation>o</lineannotation>
2557 2558 2559
      </programlisting>
     </para>
    </informalexample>
2560 2561

<!-- derived from the re_format.7 man page -->
2562
   <para>
2563
    Regular expressions (<acronym>RE</acronym>s), as defined in
2564
     <acronym>POSIX</acronym> 
2565
    1003.2, come in two forms: modern <acronym>RE</acronym>s (roughly those of
2566
    <command>egrep</command>; 1003.2 calls these
2567 2568
    <quote>extended</quote> <acronym>RE</acronym>s) and obsolete <acronym>RE</acronym>s (roughly those of
    <command>ed</command>; 1003.2 <quote>basic</quote> <acronym>RE</acronym>s).
2569
    <productname>PostgreSQL</productname> implements the modern form.
2570
   </para>
2571

2572
   <para>
2573 2574 2575 2576
    A (modern) RE is one or more non-empty
    <firstterm>branches</firstterm>, separated by
    <literal>|</literal>.  It matches anything that matches one of the
    branches.
2577
   </para>
2578

2579
   <para>
2580 2581 2582
    A branch is one or more <firstterm>pieces</firstterm>,
    concatenated.  It matches a match for the first, followed by a
    match for the second, etc.
2583
   </para>
2584

Bruce Momjian's avatar
Bruce Momjian committed
2585
   <para>
2586 2587 2588 2589 2590 2591 2592 2593
    A piece is an <firstterm>atom</firstterm> possibly followed by a
    single <literal>*</literal>, <literal>+</literal>,
    <literal>?</literal>, or <firstterm>bound</firstterm>.  An atom
    followed by <literal>*</literal> matches a sequence of 0 or more
    matches of the atom.  An atom followed by <literal>+</literal>
    matches a sequence of 1 or more matches of the atom.  An atom
    followed by <literal>?</literal> matches a sequence of 0 or 1
    matches of the atom.
Bruce Momjian's avatar
Bruce Momjian committed
2594
   </para>
2595

2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623
   <para>
    A <firstterm>bound</firstterm> is <literal>{</literal> followed by
    an unsigned decimal integer, possibly followed by
    <literal>,</literal> possibly followed by another unsigned decimal
    integer, always followed by <literal>}</literal>.  The integers
    must lie between 0 and <symbol>RE_DUP_MAX</symbol> (255)
    inclusive, and if there are two of them, the first may not exceed
    the second.  An atom followed by a bound containing one integer
    <replaceable>i</replaceable> and no comma matches a sequence of
    exactly <replaceable>i</replaceable> matches of the atom.  An atom
    followed by a bound containing one integer
    <replaceable>i</replaceable> and a comma matches a sequence of
    <replaceable>i</replaceable> or more matches of the atom.  An atom
    followed by a bound containing two integers
    <replaceable>i</replaceable> and <replaceable>j</replaceable>
    matches a sequence of <replaceable>i</replaceable> through
    <replaceable>j</replaceable> (inclusive) matches of the atom.
   </para>

   <note>
    <para>
     A repetition operator (<literal>?</literal>,
     <literal>*</literal>, <literal>+</literal>, or bounds) cannot
     follow another repetition operator.  A repetition operator cannot
     begin an expression or subexpression or follow
     <literal>^</literal> or <literal>|</literal>.
    </para>
   </note>
2624

2625
   <para>
2626 2627 2628 2629 2630
    An <firstterm>atom</firstterm> is a regular expression enclosed in
    <literal>()</literal> (matching a match for the regular
    expression), an empty set of <literal>()</literal> (matching the
    null string), a <firstterm>bracket expression</firstterm> (see
    below), <literal>.</literal> (matching any single character),
2631 2632 2633
    <literal>^</literal> (matching the null string at the beginning of the
    input string), <literal>$</literal> (matching the null string at the end
    of the input string), a <literal>\</literal> followed by one of the
2634 2635 2636 2637 2638 2639 2640 2641 2642
    characters <literal>^.[$()|*+?{\</literal> (matching that
    character taken as an ordinary character), a <literal>\</literal>
    followed by any other character (matching that character taken as
    an ordinary character, as if the <literal>\</literal> had not been
    present), or a single character with no other significance
    (matching that character).  A <literal>{</literal> followed by a
    character other than a digit is an ordinary character, not the
    beginning of a bound.  It is illegal to end an RE with
    <literal>\</literal>.
2643
   </para>
2644

2645 2646 2647 2648 2649 2650 2651
   <para>
    Note that the backslash (<literal>\</literal>) already has a special
    meaning in string
    literals, so to write a pattern constant that contains a backslash
    you must write two backslashes in the query.
   </para>

2652
   <para>
2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665
    A <firstterm>bracket expression</firstterm> is a list of
    characters enclosed in <literal>[]</literal>.  It normally matches
    any single character from the list (but see below).  If the list
    begins with <literal>^</literal>, it matches any single character
    (but see below) not from the rest of the list.  If two characters
    in the list are separated by <literal>-</literal>, this is
    shorthand for the full range of characters between those two
    (inclusive) in the collating sequence,
    e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches
    any decimal digit.  It is illegal for two ranges to share an
    endpoint, e.g.  <literal>a-c-e</literal>.  Ranges are very
    collating-sequence-dependent, and portable programs should avoid
    relying on them.
2666
   </para>
2667

2668
   <para>
2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683
    To include a literal <literal>]</literal> in the list, make it the
    first character (following a possible <literal>^</literal>).  To
    include a literal <literal>-</literal>, make it the first or last
    character, or the second endpoint of a range.  To use a literal
    <literal>-</literal> as the first endpoint of a range, enclose it
    in <literal>[.</literal> and <literal>.]</literal> to make it a
    collating element (see below).  With the exception of these and
    some combinations using <literal>[</literal> (see next
    paragraphs), all other special characters, including
    <literal>\</literal>, lose their special significance within a
    bracket expression.
   </para>

   <para>
    Within a bracket expression, a collating element (a character, a
2684
    multiple-character sequence that collates as if it were a single
2685 2686 2687 2688
    character, or a collating-sequence name for either) enclosed in
    <literal>[.</literal> and <literal>.]</literal> stands for the
    sequence of characters of that collating element.  The sequence is
    a single element of the bracket expression's list.  A bracket
2689
    expression containing a multiple-character collating element can thus
2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735
    match more than one character, e.g. if the collating sequence
    includes a <literal>ch</literal> collating element, then the RE
    <literal>[[.ch.]]*c</literal> matches the first five characters of
    <literal>chchcc</literal>.
   </para>

   <para>
    Within a bracket expression, a collating element enclosed in
    <literal>[=</literal> and <literal>=]</literal> is an equivalence
    class, standing for the sequences of characters of all collating
    elements equivalent to that one, including itself.  (If there are
    no other equivalent collating elements, the treatment is as if the
    enclosing delimiters were <literal>[.</literal> and
    <literal>.]</literal>.)  For example, if <literal>o</literal> and
    <literal>^</literal> are the members of an equivalence class, then
    <literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and
    <literal>[o^]</literal> are all synonymous.  An equivalence class
    may not be an endpoint of a range.
   </para>

   <para>
    Within a bracket expression, the name of a character class
    enclosed in <literal>[:</literal> and <literal>:]</literal> stands
    for the list of all characters belonging to that class.  Standard
    character class names are: <literal>alnum</literal>,
    <literal>alpha</literal>, <literal>blank</literal>,
    <literal>cntrl</literal>, <literal>digit</literal>,
    <literal>graph</literal>, <literal>lower</literal>,
    <literal>print</literal>, <literal>punct</literal>,
    <literal>space</literal>, <literal>upper</literal>,
    <literal>xdigit</literal>.  These stand for the character classes
    defined in
    <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.
    A locale may provide others.  A character class may not be used as
    an endpoint of a range.
   </para>

   <para>
    There are two special cases of bracket expressions:  the bracket
    expressions <literal>[[:<:]]</literal> and
    <literal>[[:>:]]</literal> match the null string at the beginning
    and end of a word respectively.  A word is defined as a sequence
    of word characters which is neither preceded nor followed by word
    characters.  A word character is an alnum character (as defined by
    <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)
    or an underscore.  This is an extension, compatible with but not
2736
    specified by <acronym>POSIX</acronym> 1003.2, and should be used with caution in
2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779
    software intended to be portable to other systems.
   </para>

   <para>
    In the event that an RE could match more than one substring of a
    given string, the RE matches the one starting earliest in the
    string.  If the RE could match more than one substring starting at
    that point, it matches the longest.  Subexpressions also match the
    longest possible substrings, subject to the constraint that the
    whole match be as long as possible, with subexpressions starting
    earlier in the RE taking priority over ones starting later.  Note
    that higher-level subexpressions thus take priority over their
    lower-level component subexpressions.
   </para>

   <para>
    Match lengths are measured in characters, not collating
    elements.  A null string is considered longer than no match at
    all.  For example, <literal>bb*</literal> matches the three middle
    characters of <literal>abbbc</literal>,
    <literal>(wee|week)(knights|nights)</literal> matches all ten
    characters of <literal>weeknights</literal>, when
    <literal>(.*).*</literal> is matched against
    <literal>abc</literal> the parenthesized subexpression matches all
    three characters, and when <literal>(a*)*</literal> is matched
    against <literal>bc</literal> both the whole RE and the
    parenthesized subexpression match the null string.
   </para>

   <para>
    If case-independent matching is specified, the effect is much as
    if all case distinctions had vanished from the alphabet.  When an
    alphabetic that exists in multiple cases appears as an ordinary
    character outside a bracket expression, it is effectively
    transformed into a bracket expression containing both cases,
    e.g. <literal>x</literal> becomes <literal>[xX]</literal>.  When
    it appears inside a bracket expression, all case counterparts of
    it are added to the bracket expression, so that (e.g.)
    <literal>[x]</literal> becomes <literal>[xX]</literal> and
    <literal>[^x]</literal> becomes <literal>[^xX]</literal>.
   </para>

   <para>
2780
    There is no particular limit on the length of <acronym>RE</acronym>s, except insofar
2781 2782 2783 2784 2785 2786 2787
    as memory is limited.  Memory usage is approximately linear in RE
    size, and largely insensitive to RE complexity, except for bounded
    repetitions.  Bounded repetitions are implemented by macro
    expansion, which is costly in time and space if counts are large
    or bounded repetitions are nested.  An RE like, say,
    <literal>((((a{1,100}){1,100}){1,100}){1,100}){1,100}</literal>
    will (eventually) run almost any existing machine out of swap
2788 2789 2790 2791 2792 2793 2794 2795
    space.
    <footnote>
     <para>
      This was written in 1994, mind you.  The
      numbers have probably changed, but the problem
      persists.
     </para>
    </footnote>
2796
   </para>
2797 2798
<!-- end re_format.7 man page -->
  </sect2>
2799

2800 2801 2802 2803
 </sect1>


  <sect1 id="functions-formatting">
2804
   <title>Data Type Formatting Functions</title>
2805

2806 2807 2808 2809
   <indexterm zone="functions-formatting">
    <primary>formatting</primary>
   </indexterm>

2810 2811 2812
   <note>
    <title>Author</title>
    <para>
2813
     Written by Karel Zak (<email>zakkr@zf.jcu.cz</email>) on 2000-01-24
2814 2815
    </para>
   </note>
2816

2817
   <para>
2818
    The <productname>PostgreSQL</productname> formatting functions
2819 2820
    provide a powerful set of tools for converting various data types
    (date/time, integer, floating point, numeric) to formatted strings
2821
    and for converting from formatted strings to specific data types.
2822
    These functions all follow a common calling convention: the first
2823
    argument is the value to be formatted and the second argument is a
2824
    template that defines the output or input format.
2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839
   </para>

    <table tocentry="1">
     <title>Formatting Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
2840 2841 2842 2843
	<entry><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert time stamp to string</entry>
	<entry><literal>to_char(timestamp 'now','HH12:MI:SS')</literal></entry>
2844
       </row>
2845
       <row>
2846 2847
	<entry><function>to_char</function>(<type>interval</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
2848
	<entry>convert interval to string</entry>
2849
	<entry><literal>to_char(interval '15h 2m 12s','HH24:MI:SS')</literal></entry>
2850
       </row>
2851
       <row>
2852 2853
	<entry><function>to_char</function>(<type>int</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
2854
	<entry>convert int4/int8 to string</entry>
2855
	<entry><literal>to_char(125, '999')</literal></entry>
2856 2857
       </row>
       <row>
2858 2859
	<entry><function>to_char</function>(<type>double precision</type>,
        <type>text</type>)</entry>
2860
	<entry><type>text</type></entry>
2861
	<entry>convert real/double precision to string</entry>
2862
	<entry><literal>to_char(125.8, '999D9')</literal></entry>
2863 2864
       </row>
       <row>
2865 2866
	<entry><function>to_char</function>(<type>numeric</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
2867
	<entry>convert numeric to string</entry>
2868
	<entry><literal>to_char(numeric '-125.8', '999D99S')</literal></entry>
2869 2870
       </row>
       <row>
2871 2872
	<entry><function>to_date</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>date</type></entry>
2873
	<entry>convert string to date</entry>
2874
	<entry><literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
2875 2876
       </row>
       <row>
2877 2878 2879 2880
	<entry><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>timestamp</type></entry>
	<entry>convert string to time stamp</entry>
	<entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
2881 2882
       </row>
       <row>
2883 2884
	<entry><function>to_number</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>numeric</type></entry>
2885
	<entry>convert string to numeric</entry>
2886
	<entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>
2887 2888 2889 2890 2891
       </row>
      </tbody>
     </tgroup>
    </table>

2892 2893 2894 2895
   <para>
    In an output template string, there are certain patterns that are
    recognized and replaced with appropriately-formatted data from the value
    to be formatted.  Any text that is not a template pattern is simply
Peter Eisentraut's avatar
Peter Eisentraut committed
2896
    copied verbatim.  Similarly, in an input template string, template patterns
2897 2898 2899 2900
    identify the parts of the input data string to be looked at and the
    values to be found there.
   </para>

2901
    <table tocentry="1">
2902
     <title>Template patterns for date/time conversions</title>
2903 2904 2905
     <tgroup cols="2">
      <thead>
       <row>
2906
	<entry>Pattern</entry>
2907 2908 2909 2910 2911
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
2912
	<entry><literal>HH</literal></entry>
2913
	<entry>hour of day (01-12)</entry>
2914 2915
       </row>
       <row>
2916
	<entry><literal>HH12</literal></entry>
2917
	<entry>hour of day (01-12)</entry>
2918
       </row>       
2919
       <row>
2920
	<entry><literal>HH24</literal></entry>
2921 2922
	<entry>hour of day (00-23)</entry>
       </row>       
2923
       <row>
2924
	<entry><literal>MI</literal></entry>
2925
	<entry>minute (00-59)</entry>
2926 2927
       </row>   
       <row>
2928
	<entry><literal>SS</literal></entry>
2929
	<entry>second (00-59)</entry>
2930
       </row>
2931
       <row>
2932
	<entry><literal>MS</literal></entry>
2933 2934 2935
	<entry>millisecond (000-999)</entry>
       </row>
       <row>
2936
	<entry><literal>US</literal></entry>
2937 2938
	<entry>microsecond (000000-999999)</entry>
       </row>
2939
       <row>
2940
	<entry><literal>SSSS</literal></entry>
2941
	<entry>seconds past midnight (0-86399)</entry>
2942
       </row>
2943
       <row>
2944
	<entry><literal>AM</literal> or <literal>A.M.</literal> or
2945
	<literal>PM</literal> or <literal>P.M.</literal></entry>
2946 2947 2948
	<entry>meridian indicator (upper case)</entry>
       </row>
       <row>
2949
	<entry><literal>am</literal> or <literal>a.m.</literal> or
2950
	<literal>pm</literal> or <literal>p.m.</literal></entry>
2951 2952
	<entry>meridian indicator (lower case)</entry>
       </row>
2953
       <row>
2954
	<entry><literal>Y,YYY</literal></entry>
2955
	<entry>year (4 and more digits) with comma</entry>
2956 2957
       </row>
       <row>
2958
	<entry><literal>YYYY</literal></entry>
2959
	<entry>year (4 and more digits)</entry>
2960 2961
       </row>
       <row>
2962
	<entry><literal>YYY</literal></entry>
2963
	<entry>last 3 digits of year</entry>
2964 2965
       </row>
       <row>
2966
	<entry><literal>YY</literal></entry>
2967
	<entry>last 2 digits of year</entry>
2968 2969
       </row>
       <row>
2970
	<entry><literal>Y</literal></entry>
2971
	<entry>last digit of year</entry>
2972
       </row>
2973
       <row>
2974
	<entry><literal>BC</literal> or <literal>B.C.</literal> or
2975
	<literal>AD</literal> or <literal>A.D.</literal></entry>
2976
	<entry>era indicator (upper case)</entry>
2977 2978
       </row>
       <row>
2979
	<entry><literal>bc</literal> or <literal>b.c.</literal> or
2980
	<literal>ad</literal> or <literal>a.d.</literal></entry>
2981
	<entry>era indicator (lower case)</entry>
2982
       </row>
2983
       <row>
2984
	<entry><literal>MONTH</literal></entry>
2985
	<entry>full upper case month name (blank-padded to 9 chars)</entry>
2986 2987
       </row>
       <row>
2988
	<entry><literal>Month</literal></entry>
2989
	<entry>full mixed case month name (blank-padded to 9 chars)</entry>
2990 2991
       </row>
       <row>
2992
	<entry><literal>month</literal></entry>
2993
	<entry>full lower case month name (blank-padded to 9 chars)</entry>
2994 2995
       </row>
       <row>
2996
	<entry><literal>MON</literal></entry>
2997
	<entry>abbreviated upper case month name (3 chars)</entry>
2998 2999
       </row>
       <row>
3000
	<entry><literal>Mon</literal></entry>
3001
	<entry>abbreviated mixed case month name (3 chars)</entry>
3002 3003
       </row>
       <row>
3004
	<entry><literal>mon</literal></entry>
3005
	<entry>abbreviated lower case month name (3 chars)</entry>
3006 3007
       </row>
       <row>
3008
	<entry><literal>MM</literal></entry>
3009
	<entry>month number (01-12)</entry>
3010 3011
       </row>
       <row>
3012
	<entry><literal>DAY</literal></entry>
3013
	<entry>full upper case day name (blank-padded to 9 chars)</entry>
3014 3015
       </row>
       <row>
3016
	<entry><literal>Day</literal></entry>
3017
	<entry>full mixed case day name (blank-padded to 9 chars)</entry>
3018 3019
       </row>
       <row>
3020
	<entry><literal>day</literal></entry>
3021
	<entry>full lower case day name (blank-padded to 9 chars)</entry>
3022 3023
       </row>
       <row>
3024
	<entry><literal>DY</literal></entry>
3025
	<entry>abbreviated upper case day name (3 chars)</entry>
3026 3027
       </row>
       <row>
3028
	<entry><literal>Dy</literal></entry>
3029
	<entry>abbreviated mixed case day name (3 chars)</entry>
3030 3031
       </row>
       <row>
3032
	<entry><literal>dy</literal></entry>
3033
	<entry>abbreviated lower case day name (3 chars)</entry>
3034 3035
       </row>
       <row>
3036
	<entry><literal>DDD</literal></entry>
3037
	<entry>day of year (001-366)</entry>
3038 3039
       </row>
       <row>
3040
	<entry><literal>DD</literal></entry>
3041
	<entry>day of month (01-31)</entry>
3042 3043
       </row>
       <row>
3044
	<entry><literal>D</literal></entry>
3045
	<entry>day of week (1-7; SUN=1)</entry>
3046 3047
       </row>
       <row>
3048
	<entry><literal>W</literal></entry>
3049
	<entry>week of month (1-5) where first week start on the first day of the month</entry>
3050 3051
       </row> 
       <row>
3052
	<entry><literal>WW</literal></entry>
3053
	<entry>week number of year (1-53) where first week start on the first day of the year</entry>
3054
       </row>
Bruce Momjian's avatar
Bruce Momjian committed
3055
       <row>
3056
	<entry><literal>IW</literal></entry>
3057
	<entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
Bruce Momjian's avatar
Bruce Momjian committed
3058
       </row>
3059
       <row>
3060
	<entry><literal>CC</literal></entry>
3061
	<entry>century (2 digits)</entry>
3062 3063
       </row>
       <row>
3064
	<entry><literal>J</literal></entry>
3065
	<entry>Julian Day (days since January 1, 4712 BC)</entry>
3066 3067
       </row>
       <row>
3068
	<entry><literal>Q</literal></entry>
3069
	<entry>quarter</entry>
3070 3071
       </row>
       <row>
3072
	<entry><literal>RM</literal></entry>
3073
	<entry>month in Roman Numerals (I-XII; I=January) - upper case</entry>
3074 3075
       </row>
       <row>
3076
	<entry><literal>rm</literal></entry>
3077
	<entry>month in Roman Numerals (I-XII; I=January) - lower case</entry>
3078
       </row>
3079
       <row>
3080
	<entry><literal>TZ</literal></entry>
3081
	<entry>timezone name - upper case</entry>
3082 3083
       </row>
       <row>
3084
	<entry><literal>tz</literal></entry>
3085
	<entry>timezone name - lower case</entry>
3086
       </row>
3087 3088 3089 3090 3091
      </tbody>
     </tgroup>
    </table>

   <para>
3092 3093 3094 3095
    Certain modifiers may be applied to any template pattern to alter its
    behavior.  For example, <quote><literal>FMMonth</literal></quote>
    is the <quote><literal>Month</literal></quote> pattern with the
    <quote><literal>FM</literal></quote> prefix.
3096 3097 3098
   </para>

    <table tocentry="1">
3099
     <title>Template pattern modifiers for date/time conversions</title>
3100 3101 3102
     <tgroup cols="3">
      <thead>
       <row>
3103
	<entry>Modifier</entry>
3104 3105 3106 3107 3108 3109
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
3110 3111
	<entry><literal>FM</literal> prefix</entry>
	<entry>fill mode (suppress padding blanks and zeroes)</entry>
3112
	<entry><literal>FMMonth</literal></entry>
3113 3114
       </row>
       <row>
3115 3116
	<entry><literal>TH</literal> suffix</entry>
	<entry>add upper-case ordinal number suffix</entry>
3117
	<entry><literal>DDTH</literal></entry>
3118 3119
       </row>	
       <row>
3120 3121
	<entry><literal>th</literal> suffix</entry>
	<entry>add lower-case ordinal number suffix</entry>
3122
	<entry><literal>DDth</literal></entry>
3123 3124
       </row>
       <row>
3125
	<entry><literal>FX</literal> prefix</entry>
3126 3127
	<entry>Fixed format global option (see below)</entry>
	<entry><literal>FX Month DD Day</literal></entry>
3128 3129
       </row>	
       <row>
3130
	<entry><literal>SP</literal> suffix</entry>
3131
	<entry>spell mode (not yet implemented)</entry>
3132
	<entry><literal>DDSP</literal></entry>
3133 3134 3135 3136 3137 3138
       </row>       
      </tbody>
     </tgroup>
    </table>

   <para>
3139 3140 3141
    Usage notes:

    <itemizedlist>
3142 3143 3144 3145 3146 3147 3148 3149
     <listitem>
      <para>
       <literal>FM</literal> suppresses leading zeroes or trailing blanks
       that would otherwise be added to make the output of a pattern be
       fixed-width.
      </para>
     </listitem>

3150 3151 3152
     <listitem>
      <para>
       <function>to_timestamp</function> and <function>to_date</function>
3153
       skip multiple blank spaces in the input string if the <literal>FX</literal> option 
3154 3155 3156
       is not used. <literal>FX</literal> must be specified as the first item
       in the template; for example 
       <literal>to_timestamp('2000    JUN','YYYY MON')</literal> is right, but
3157 3158
       <literal>to_timestamp('2000    JUN','FXYYYY MON')</literal> returns an error,
       because <function>to_timestamp</function> expects one blank space only.
3159 3160 3161 3162 3163
      </para>
     </listitem>

     <listitem>
      <para>
3164 3165 3166 3167
       If a backslash (<quote><literal>\</literal></quote>) is desired
       in a string constant, a double backslash
       (<quote><literal>\\</literal></quote>) must be entered; for
       example <literal>'\\HH\\MI\\SS'</literal>.  This is true for
3168
       any string constant in <productname>PostgreSQL</productname>.
3169 3170 3171 3172 3173
      </para>
     </listitem>

     <listitem>
      <para>
3174
       Ordinary text is allowed in <function>to_char</function>
3175 3176 3177 3178 3179 3180
       templates and will be output literally.  You can put a substring
       in double quotes to force it to be interpreted as literal text
       even if it contains pattern keywords.  For example, in
       <literal>'"Hello Year: "YYYY'</literal>, the <literal>YYYY</literal>
       will be replaced by year data, but the single <literal>Y</literal>
       will not be.
3181 3182 3183 3184 3185
      </para>
     </listitem>

     <listitem>
      <para>
3186 3187 3188
       If you want to have a double quote in the output you must
       precede it with a backslash, for example <literal>'\\"YYYY
       Month\\"'</literal>. <!-- "" font-lock sanity :-) -->
3189 3190
      </para>
     </listitem>
3191 3192 3193

     <listitem>
      <para>
3194 3195
       <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
       <type>date</type> is restricted if you use a year with more than 4 digits. You must
Peter Eisentraut's avatar
Peter Eisentraut committed
3196
       use some non-digit character or template after <literal>YYYY</literal>,
3197
       otherwise the year is always interpreted as 4 digits. For example
Peter Eisentraut's avatar
Peter Eisentraut committed
3198 3199
       (with year 20000):
       <literal>to_date('200001131', 'YYYYMMDD')</literal> will be 
3200
       interpreted as a 4-digit year; better is to use a non-digit 
Peter Eisentraut's avatar
Peter Eisentraut committed
3201 3202 3203
       separator after the year, like
       <literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or
       <literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.
3204 3205
      </para>
     </listitem>
3206 3207 3208

     <listitem>
      <para>
3209
       Millisecond <literal>MS</literal> and microsecond <literal>US</literal>
Peter Eisentraut's avatar
Peter Eisentraut committed
3210 3211
       values in a conversion from string to time stamp are used as part of the
       seconds after the decimal point. For example 
3212
       <literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,
Peter Eisentraut's avatar
Peter Eisentraut committed
3213
       but 300, because the conversion counts it as 12 + 0.3.
3214 3215
       This means for the format <literal>SS:MS</literal>, the input values
       <literal>12:3</literal>, <literal>12:30</literal>, and <literal>12:300</literal> specify the
Peter Eisentraut's avatar
Peter Eisentraut committed
3216
       same number of milliseconds. To get three milliseconds, one must use
3217
       <literal>12:003</literal>, which the conversion counts as
Peter Eisentraut's avatar
Peter Eisentraut committed
3218 3219 3220 3221 3222
       12 + 0.003 = 12.003 seconds.
      </para>

      <para>
       Here is a more 
3223 3224
       complex example: 
       <literal>to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')</literal>
Peter Eisentraut's avatar
Peter Eisentraut committed
3225 3226
       is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
       1230 microseconds = 2.021230 seconds. 
3227 3228
      </para>
     </listitem>
3229
    </itemizedlist>
3230
   </para>
3231

3232
    <table tocentry="1">
3233
     <title>Template patterns for numeric conversions</title>
3234 3235 3236
     <tgroup cols="2">
      <thead>
       <row>
3237
	<entry>Pattern</entry>
3238 3239 3240 3241 3242
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
3243
	<entry><literal>9</literal></entry>
3244
	<entry>value with the specified number of digits</entry>
3245 3246
       </row>
       <row>
3247
	<entry><literal>0</literal></entry>
3248
	<entry>value with leading zeros</entry>
3249 3250
       </row>
       <row>
3251
	<entry><literal>.</literal> (period)</entry>
3252
	<entry>decimal point</entry>
3253 3254
       </row>       
       <row>
3255
	<entry><literal>,</literal> (comma)</entry>
3256
	<entry>group (thousand) separator</entry>
3257 3258
       </row>
       <row>
3259
	<entry><literal>PR</literal></entry>
3260
	<entry>negative value in angle brackets</entry>
3261 3262
       </row>
       <row>
3263
	<entry><literal>S</literal></entry>
3264
	<entry>negative value with minus sign (uses locale)</entry>
3265 3266
       </row>
       <row>
3267
	<entry><literal>L</literal></entry>
3268
	<entry>currency symbol (uses locale)</entry>
3269 3270
       </row>
       <row>
3271
	<entry><literal>D</literal></entry>
3272
	<entry>decimal point (uses locale)</entry>
3273 3274
       </row>
       <row>
3275
	<entry><literal>G</literal></entry>
3276
	<entry>group separator (uses locale)</entry>
3277 3278
       </row>
       <row>
3279
	<entry><literal>MI</literal></entry>
3280
	<entry>minus sign in specified position (if number < 0)</entry>
3281 3282
       </row>
       <row>
3283
	<entry><literal>PL</literal></entry>
3284
	<entry>plus sign in specified position (if number > 0)</entry>
3285 3286
       </row>
       <row>
3287
	<entry><literal>SG</literal></entry>
3288
	<entry>plus/minus sign in specified position</entry>
3289 3290
       </row>
       <row>
3291
	<entry><literal>RN</literal></entry>
3292
	<entry>roman numeral (input between 1 and 3999)</entry>
3293 3294
       </row>
       <row>
3295
	<entry><literal>TH</literal> or <literal>th</literal></entry>
3296
	<entry>convert to ordinal number</entry>
3297 3298
       </row>
       <row>
3299
	<entry><literal>V</literal></entry>
3300
	<entry>shift <replaceable>n</replaceable> digits (see
3301
	 notes)</entry>
3302 3303
       </row>
       <row>
3304
	<entry><literal>EEEE</literal></entry>
Peter Eisentraut's avatar
Peter Eisentraut committed
3305
	<entry>scientific notation (not implemented yet)</entry>
3306 3307 3308 3309 3310 3311
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
3312 3313 3314 3315 3316
    Usage notes:

    <itemizedlist>
     <listitem>
      <para>
3317 3318
       A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
       <literal>MI</literal> is not an anchor in
3319
       the number; for example,
3320 3321
       <literal>to_char(-12, 'S9999')</literal> produces <literal>'  -12'</literal>,
       but <literal>to_char(-12, 'MI9999')</literal> produces <literal>'-  12'</literal>.
3322 3323
       The Oracle implementation does not allow the use of
       <literal>MI</literal> ahead of <literal>9</literal>, but rather
3324
       requires that <literal>9</literal> precede
3325 3326 3327 3328 3329 3330
       <literal>MI</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
3331 3332 3333
       <literal>9</literal> specifies a value with the same number of 
       digits as there are <literal>9</literal>s. If a digit is
       not available use blank space.
3334 3335 3336 3337 3338
      </para>
     </listitem>

     <listitem>
      <para>
3339 3340
       <literal>TH</literal> does not convert values less than zero
       and does not convert decimal numbers.
3341 3342 3343 3344 3345
      </para>
     </listitem>

     <listitem>
      <para>
3346
       <literal>PL</literal>, <literal>SG</literal>, and
3347
       <literal>TH</literal> are <productname>PostgreSQL</productname>
3348
       extensions. 
3349 3350 3351 3352 3353 3354 3355 3356 3357 3358 3359
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>V</literal> effectively
       multiplies the input values by
       <literal>10^<replaceable>n</replaceable></literal>, where
       <replaceable>n</replaceable> is the number of digits following
       <literal>V</literal>. 
       <function>to_char</function> does not support the use of
3360 3361
       <literal>V</literal> combined with a decimal point.
       (E.g., <literal>99.9V99</literal> is not allowed.)
3362 3363 3364
      </para>
     </listitem>
    </itemizedlist>
3365
   </para>   
3366 3367

    <table tocentry="1">
3368
     <title><function>to_char</function> Examples</title>
3369 3370 3371 3372 3373 3374 3375 3376 3377
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Input</entry>
	<entry>Output</entry>
       </row>
      </thead>
      <tbody>
       <row>
3378
        <entry><literal>to_char(now(),'Day, DD  HH12:MI:SS')</literal></entry>
3379
        <entry><literal>'Tuesday  , 06  05:39:18'</literal></entry>
3380
       </row>
3381
       <row>
3382
        <entry><literal>to_char(now(),'FMDay, FMDD  HH12:MI:SS')</literal></entry>
3383
        <entry><literal>'Tuesday, 6  05:39:18'</literal></entry>
3384 3385
       </row>          
       <row>
3386
        <entry><literal>to_char(-0.1,'99.99')</literal></entry>
3387
        <entry><literal>' -.10'</literal></entry>
3388 3389
       </row>
       <row>
3390
        <entry><literal>to_char(-0.1,'FM9.99')</literal></entry>
3391
        <entry><literal>'-.1'</literal></entry>
3392 3393
       </row>
       <row>
3394
        <entry><literal>to_char(0.1,'0.9')</literal></entry>
3395
        <entry><literal>' 0.1'</literal></entry>
3396 3397
       </row>
       <row>
3398
        <entry><literal>to_char(12,'9990999.9')</literal></entry>
3399
        <entry><literal>'    0012.0'</literal></entry>
3400 3401
       </row>
       <row>
3402
        <entry><literal>to_char(12,'FM9990999.9')</literal></entry>
3403
        <entry><literal>'0012'</literal></entry>
3404 3405
       </row>
       <row>
3406
        <entry><literal>to_char(485,'999')</literal></entry>
3407
        <entry><literal>' 485'</literal></entry>
3408 3409
       </row>
       <row>
3410
        <entry><literal>to_char(-485,'999')</literal></entry>
3411
        <entry><literal>'-485'</literal></entry>
3412 3413
       </row>
       <row>
3414
        <entry><literal>to_char(485,'9 9 9')</literal></entry>
3415
        <entry><literal>' 4 8 5'</literal></entry>
3416 3417
       </row>
       <row>
3418
        <entry><literal>to_char(1485,'9,999')</literal></entry>
3419
        <entry><literal>' 1,485'</literal></entry>
3420 3421
       </row>
       <row>
3422
        <entry><literal>to_char(1485,'9G999')</literal></entry>
3423
        <entry><literal>' 1 485'</literal></entry>
3424 3425
       </row>
       <row>
3426
        <entry><literal>to_char(148.5,'999.999')</literal></entry>
3427
        <entry><literal>' 148.500'</literal></entry>
3428 3429
       </row>
       <row>
3430
        <entry><literal>to_char(148.5,'999D999')</literal></entry>
3431
        <entry><literal>' 148,500'</literal></entry>	 
3432 3433
       </row>
       <row>
3434
        <entry><literal>to_char(3148.5,'9G999D999')</literal></entry>
3435
        <entry><literal>' 3 148,500'</literal></entry>
3436 3437
       </row>
       <row>
3438
        <entry><literal>to_char(-485,'999S')</literal></entry>
3439
        <entry><literal>'485-'</literal></entry>
3440 3441
       </row>
       <row>		
3442
        <entry><literal>to_char(-485,'999MI')</literal></entry>
3443
        <entry><literal>'485-'</literal></entry>	
3444 3445
       </row>
       <row>
3446
        <entry><literal>to_char(485,'999MI')</literal></entry>
3447
        <entry><literal>'485'</literal></entry>		
3448 3449
       </row>
       <row>
3450
        <entry><literal>to_char(485,'PL999')</literal></entry>
3451
        <entry><literal>'+485'</literal></entry>	
3452 3453
       </row>
       <row>		
3454
        <entry><literal>to_char(485,'SG999')</literal></entry>
3455
        <entry><literal>'+485'</literal></entry>	
3456 3457
       </row>
       <row>
3458
        <entry><literal>to_char(-485,'SG999')</literal></entry>
3459
        <entry><literal>'-485'</literal></entry>	
3460 3461
       </row>
       <row>
3462
        <entry><literal>to_char(-485,'9SG99')</literal></entry>
3463
        <entry><literal>'4-85'</literal></entry>	
3464 3465
       </row>
       <row>
3466
        <entry><literal>to_char(-485,'999PR')</literal></entry>
3467
        <entry><literal>'&lt;485&gt;'</literal></entry>		
3468 3469
       </row>
       <row>
3470
        <entry><literal>to_char(485,'L999')</literal></entry>
3471
        <entry><literal>'DM 485</literal></entry>	 
3472 3473
       </row>
       <row>
3474
        <entry><literal>to_char(485,'RN')</literal></entry>		
3475
        <entry><literal>'        CDLXXXV'</literal></entry>
3476 3477
       </row>
       <row>
3478
        <entry><literal>to_char(485,'FMRN')</literal></entry>	
3479
        <entry><literal>'CDLXXXV'</literal></entry>
3480 3481
       </row>
       <row>
3482
        <entry><literal>to_char(5.2,'FMRN')</literal></entry>
3483
        <entry><literal>V</literal></entry>		
3484 3485
       </row>
       <row>
3486
        <entry><literal>to_char(482,'999th')</literal></entry>
3487
        <entry><literal>' 482nd'</literal></entry>				
3488 3489
       </row>
       <row>
3490
        <entry><literal>to_char(485, '"Good number:"999')</literal></entry>
3491
        <entry><literal>'Good number: 485'</literal></entry>
3492 3493
       </row>
       <row>
3494
        <entry><literal>to_char(485.8,'"Pre:"999" Post:" .999')</literal></entry>
3495
        <entry><literal>'Pre: 485 Post: .800'</literal></entry>
3496 3497
       </row>
       <row>
3498
        <entry><literal>to_char(12,'99V999')</literal></entry>		
3499
        <entry><literal>' 12000'</literal></entry>
3500 3501
       </row>
       <row>
3502
        <entry><literal>to_char(12.4,'99V999')</literal></entry>
3503
        <entry><literal>' 12400'</literal></entry>
3504 3505
       </row>
       <row>		
3506
        <entry><literal>to_char(12.45, '99V9')</literal></entry>
3507
        <entry><literal>' 125'</literal></entry>
3508 3509 3510 3511
       </row>
      </tbody>
     </tgroup>
    </table>
3512

3513 3514 3515
  </sect1>


3516
  <sect1 id="functions-datetime">
3517
   <title>Date/Time Functions and Operators</title>
3518

3519
   <para>
3520
    <xref linkend="functions-datetime-table"> shows the available
3521 3522 3523 3524 3525
    functions for date/time value processing.  
    <xref linkend="operators-datetime-table"> illustrates the
    behaviors of the basic arithmetic
    operators (<literal>+</literal>, <literal>*</literal>, etc.).
    For formatting functions, refer to <xref
3526
                                             linkend="functions-formatting">.  You should be familiar with the
3527
    background information on date/time data types (see <xref
3528
                                                              linkend="datatype-datetime">).
3529
   </para>
3530

3531 3532 3533 3534 3535 3536 3537
   <para>
    The date/time operators described below behave similarly for types
    involving time zones as well as those without.

    <table id="operators-datetime-table">
     <title>Date/Time Operators</title>

3538
     <tgroup cols="3">
3539 3540 3541 3542 3543 3544 3545 3546 3547 3548
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Example</entry>
        <entry>Result</entry>
       </row>
      </thead>

      <tbody>
       <row>
3549
        <entry> <literal>+</literal> </entry>
3550 3551 3552 3553 3554
        <entry><type>timestamp</type> '2001-09-28 01:00' + <type>interval</type> '23 hours'</entry>
        <entry><type>timestamp</type> '2001-09-29 00:00'</entry>
       </row>

       <row>
3555
        <entry> <literal>+</literal> </entry>
3556 3557 3558 3559 3560
        <entry><type>date</type> '2001-09-28' + <type>interval</type> '1 hour'</entry>
        <entry><type>timestamp</type> '2001-09-28 01:00'</entry>
       </row>

       <row>
3561
        <entry> <literal>+</literal> </entry>
3562 3563 3564 3565 3566
        <entry><type>time</type> '01:00' + <type>interval</type> '3 hours'</entry>
        <entry><type>time</type> '04:00'</entry>
       </row>

       <row>
3567
        <entry> <literal>-</literal> </entry>
3568 3569 3570 3571 3572
        <entry><type>timestamp</type> '2001-09-28 23:00' - <type>interval</type> '23 hours'</entry>
        <entry><type>timestamp</type> '2001-09-28'</entry>
       </row>

       <row>
3573
        <entry> <literal>-</literal> </entry>
3574
        <entry><type>date</type> '2001-09-28' - <type>interval</type> '1 hour'</entry>
3575 3576 3577 3578
        <entry><type>timestamp</type> '2001-09-27 23:00'</entry>
       </row>

       <row>
3579
        <entry> <literal>-</literal> </entry>
3580
        <entry><type>time</type> '05:00' - <type>interval</type> '2 hours'</entry>
3581 3582 3583 3584
        <entry><type>time</type> '03:00'</entry>
       </row>

       <row>
3585
        <entry> <literal>-</literal> </entry>
3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609
        <entry><type>interval</type> '2 hours' - <type>time</type> '05:00'</entry>
        <entry><type>time</type> '03:00:00'</entry>
       </row>

       <row>
        <entry> <literal>*</literal> </entry>
        <entry><type>interval</type> '1 hour' * <type>int</type> '3'</entry>
        <entry><type>interval</type> '03:00'</entry>
       </row>

       <row>
        <entry> <literal>/</literal> </entry>
        <entry><type>interval</type> '1 hour' / <type>int</type> '3'</entry>
        <entry><type>interval</type> '00:20'</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    The date/time functions are summarized below, with additional
    details in subsequent sections.

3610
    <table id="functions-datetime-table">
3611
     <title>Date/Time Functions</title>
3612
     <tgroup cols="5">
3613 3614
      <thead>
       <row>
3615 3616
	<entry>Name</entry>
	<entry>Return Type</entry>
3617 3618
	<entry>Description</entry>
	<entry>Example</entry>
3619
	<entry>Result</entry>
3620 3621
       </row>
      </thead>
3622

3623 3624
      <tbody>
       <row>
3625 3626
	<entry><function>age</function>(<type>timestamp</type>)</entry>
	<entry><type>interval</type></entry>
3627
	<entry>Subtract from today</entry>
3628 3629
	<entry><literal>age(timestamp '1957-06-13')</literal></entry>
	<entry><literal>43 years 8 mons 3 days</literal></entry>
3630
       </row>
3631

3632
       <row>
3633 3634
	<entry><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</entry>
	<entry><type>interval</type></entry>
3635
	<entry>Subtract arguments</entry>
3636 3637
	<entry><literal>age('2001-04-10', timestamp '1957-06-13')</literal></entry>
	<entry><literal>43 years 9 mons 27 days</literal></entry>
3638 3639 3640
       </row>

       <row>
3641 3642
	<entry><function>current_date</function></entry>
	<entry><type>date</type></entry>
3643
	<entry>Today's date; see <link linkend="functions-datetime-current">below</link>
3644 3645 3646
	</entry>
	<entry></entry>
	<entry></entry>
3647
       </row>
3648 3649

       <row>
3650 3651 3652 3653 3654 3655
        <entry><function>current_time</function></entry>
        <entry><type>time with time zone</type></entry>
        <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
        </entry>
        <entry></entry>
        <entry></entry>
3656 3657
       </row>

3658
       <row>
3659
	<entry><function>current_timestamp</function></entry>
3660
	<entry><type>timestamp with time zone</type></entry>
3661
	<entry>Date and time; see <link linkend="functions-datetime-current">below</link>
3662 3663 3664 3665 3666 3667
	</entry>
	<entry></entry>
	<entry></entry>
       </row>

       <row>
3668 3669
	<entry><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</entry>
	<entry><type>double precision</type></entry>
3670
	<entry>Get subfield (equivalent to
3671
	 <function>extract</function>); see also <link
3672
                                                linkend="functions-datetime-datepart">below</link>
3673
        </entry>
3674 3675
	<entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>20</literal></entry>
3676
       </row>
3677

3678
       <row>
3679 3680
	<entry><function>date_part</function>(<type>text</type>, <type>interval</type>)</entry>
	<entry><type>double precision</type></entry>
3681
	<entry>Get subfield (equivalent to
3682
	 <function>extract</function>); see also <link
3683
                                                linkend="functions-datetime-datepart">below</link>
3684
        </entry>
3685 3686
	<entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>
	<entry><literal>3</literal></entry>
3687
       </row>
3688

3689
       <row>
3690 3691
	<entry><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</entry>
	<entry><type>timestamp</type></entry>
3692
	<entry>Truncate to specified precision; see also <link
3693
                                                        linkend="functions-datetime-trunc">below</link>
3694
        </entry>
3695 3696
	<entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>2001-02-16 20:00:00+00</literal></entry>
3697
       </row>
3698

3699
       <row>
3700 3701
	<entry><function>extract</function>(<parameter>field</parameter> from
         <type>timestamp</type>)</entry>
3702
	<entry><type>double precision</type></entry>
3703
	<entry>Get subfield; see also <link
3704
                                     linkend="functions-datetime-extract">below</link>
3705
        </entry>
3706 3707
	<entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>20</literal></entry>
3708
       </row>
3709 3710

       <row>
3711 3712
	<entry><function>extract</function>(<parameter>field</parameter> from
         <type>interval</type>)</entry>
3713
	<entry><type>double precision</type></entry>
3714
	<entry>Get subfield; see also <link
3715
                                     linkend="functions-datetime-extract">below</link>
3716
        </entry>
3717 3718
	<entry><literal>extract(month from interval '2 years 3 months')</literal></entry>
	<entry><literal>3</literal></entry>
3719 3720
       </row>

3721
       <row>
3722 3723
	<entry><function>isfinite</function>(<type>timestamp</type>)</entry>
	<entry><type>boolean</type></entry>
3724
	<entry>Test for finite time stamp (neither invalid nor infinity)</entry>
3725 3726
	<entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>
	<entry><literal>true</literal></entry>
3727
       </row>
3728

3729
       <row>
3730 3731
	<entry><function>isfinite</function>(<type>interval</type>)</entry>
	<entry><type>boolean</type></entry>
3732
	<entry>Test for finite interval</entry>
3733 3734
	<entry><literal>isfinite(interval '4 hours')</literal></entry>
	<entry><literal>true</literal></entry>
3735
       </row>
3736

3737 3738 3739 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 3752 3753 3754
       <row>
        <entry><function>localtime</function></entry>
        <entry><type>time</type></entry>
        <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
        </entry>
        <entry></entry>
        <entry></entry>
       </row>

       <row>
        <entry><function>localtimestamp</function></entry>
        <entry><type>timestamp</type></entry>
        <entry>Date and time; see <link linkend="functions-datetime-current">below</link>
        </entry>
        <entry></entry>
        <entry></entry>
       </row>

3755
       <row>
3756 3757
	<entry><function>now</function>()</entry>
	<entry><type>timestamp</type></entry>
3758
	<entry>Current date and time (equivalent to
3759
	 <function>current_timestamp</function>); see <link
3760
                                                     linkend="functions-datetime-current">below</link>
3761 3762 3763
	</entry>
	<entry></entry>
	<entry></entry>
3764
       </row>
3765

3766
       <row>
3767 3768
	<entry><function>timeofday()</function></entry>
	<entry><type>text</type></entry>
3769
	<entry>Current date and time; see <link
3770
                                         linkend="functions-datetime-current">below</link>
3771
	</entry>
3772 3773
	<entry><literal>timeofday()</literal></entry>
	<entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
3774 3775
       </row>

3776 3777 3778
      </tbody>
     </tgroup>
    </table>
3779
   </para>
3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794

  <sect2 id="functions-datetime-extract">
   <title><function>EXTRACT</function>, <function>date_part</function></title>

<synopsis>
EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
</synopsis>

   <para>
    The <function>extract</function> function retrieves sub-fields
    from date/time values, such as year or hour.
    <replaceable>source</replaceable> is a value expression that
    evaluates to type <type>timestamp</type> or <type>interval</type>.
    (Expressions of type <type>date</type> or <type>time</type> will
    be cast to <type>timestamp</type> and can therefore be used as
3795 3796
    well.)  <replaceable>field</replaceable> is an identifier or
    string that selects what field to extract from the source value.
3797 3798 3799 3800 3801 3802 3803
    The <function>extract</function> function returns values of type
    <type>double precision</type>.
    The following are valid values:

    <!-- alphabetical -->
    <variablelist>
     <varlistentry>
3804
      <term><literal>century</literal></term>
3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 3817
      <listitem>
       <para>
        The year field divided by 100
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
</screen>
       </informalexample>

       <para>
3818 3819 3820
        Note that the result for the century field is simply the year field
        divided by 100, and not the conventional definition which puts most
        years in the 1900's in the twentieth century.
3821 3822 3823 3824 3825
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
3826
      <term><literal>day</literal></term>
3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 3841
      <listitem>
       <para>
        The day (of the month) field (1 - 31)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3842
      <term><literal>decade</literal></term>
3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 3856 3857
      <listitem>
       <para>
        The year field divided by 10
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3858
      <term><literal>dow</literal></term>
3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874
      <listitem>
       <para>
        The day of the week (0 - 6; Sunday is 0) (for
        <type>timestamp</type> values only)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3875
      <term><literal>doy</literal></term>
3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889
      <listitem>
       <para>
        The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
       </para>
       <informalexample>
<screen>
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3890
      <term><literal>epoch</literal></term>
3891 3892 3893
      <listitem>
       <para>
        For <type>date</type> and <type>timestamp</type> values, the
3894
        number of seconds since 1970-01-01 00:00:00-00 (Result may be
3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911
        negative.); for <type>interval</type> values, the total number
        of seconds in the interval
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
<lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3912
      <term><literal>hour</literal></term>
3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927
      <listitem>
       <para>
        The hour field (0 - 23)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3928
      <term><literal>microseconds</literal></term>
3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944
      <listitem>
       <para>
        The seconds field, including fractional parts, multiplied by 1
        000 000.  Note that this includes full seconds.
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3945
      <term><literal>millennium</literal></term>
3946 3947 3948 3949 3950 3951 3952 3953 3954 3955 3956 3957 3958
      <listitem>
       <para>
        The year field divided by 1000
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
</screen>
       </informalexample>

       <para>
3959 3960 3961
        Note that the result for the millennium field is simply the year field
        divided by 1000, and not the conventional definition which puts
        years in the 1900's in the second millennium.
3962 3963 3964 3965 3966
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
3967
      <term><literal>milliseconds</literal></term>
3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 3982 3983
      <listitem>
       <para>
        The seconds field, including fractional parts, multiplied by
        1000.  Note that this includes full seconds.
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
3984
      <term><literal>minute</literal></term>
3985 3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 3997 3998 3999
      <listitem>
       <para>
        The minutes field (0 - 59)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
4000
      <term><literal>month</literal></term>
4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 4023
      <listitem>
       <para>
        For <type>timestamp</type> values, the number of the month
        within the year (1 - 12) ; for <type>interval</type> values
        the number of months, modulo 12 (0 - 11)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
4024
      <term><literal>quarter</literal></term>
4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040
      <listitem>
       <para>
        The quarter of the year (1 - 4) that the day is in (for
        <type>timestamp</type> values only)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
4041
      <term><literal>second</literal></term>
4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059
      <listitem>
       <para>
        The seconds field, including fractional parts (0 -
        59<footnote><simpara>60 if leap seconds are
        implemented by the operating system</simpara></footnote>)
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>
4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087
<!--
     <varlistentry>
      <term><literal>timezone</literal></term>
      <listitem>
       <para>
        The time zone offset. XXX But in what units?
       </para>
      </listitem>
     </varlistentry>
-->

     <varlistentry>
      <term><literal>timezone_hour</literal></term>
      <listitem>
       <para>
        The hour component of the time zone offset.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>timezone_minute</literal></term>
      <listitem>
       <para>
        The minute component of the time zone offset.
       </para>
      </listitem>
     </varlistentry>
4088 4089

     <varlistentry>
4090
      <term><literal>week</literal></term>
4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110
      <listitem>
       <para>
        From a <type>timestamp</type> value, calculate the number of
        the week of the year that the day is in.  By definition
        (<acronym>ISO</acronym> 8601), the first week of a year
        contains January 4 of that year.  (The <acronym>ISO</acronym>
        week starts on Monday.)  In other words, the first Thursday of
        a year is in week 1 of that year.
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

     <varlistentry>
4111
      <term><literal>year</literal></term>
4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 4127
      <listitem>
       <para>
        The year field
       </para>

       <informalexample>
<screen>
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
</screen>
       </informalexample>
      </listitem>
     </varlistentry>

    </variablelist>

4128 4129 4130
   </para>

   <para>
4131 4132 4133
    The <function>extract</function> function is primarily intended
    for computational processing.  For formatting date/time values for
    display, see <xref linkend="functions-formatting">.
4134
   </para>
4135 4136 4137

   <anchor id="functions-datetime-datepart">
   <para>
4138 4139
    The <function>date_part</function> function is modeled on the traditional
    <productname>Ingres</productname> equivalent to the
4140 4141 4142 4143 4144 4145 4146 4147 4148 4149 4150 4151 4152 4153 4154
    <acronym>SQL</acronym>-function <function>extract</function>:
<synopsis>
date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
</synopsis>
    Note that here the <replaceable>field</replaceable> value needs to
    be a string.  The valid field values for
    <function>date_part</function> are the same as for
    <function>extract</function>.
   </para>

   <informalexample>
<screen>
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>

Tatsuo Ishii's avatar
Tatsuo Ishii committed
4155
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215
<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
</screen>
   </informalexample>

  </sect2>

  <sect2 id="functions-datetime-trunc">
   <title><function>date_trunc</function></title>

   <para>
    The function <function>date_trunc</function> is conceptually
    similar to the <function>trunc</function> function for numbers.
   </para>

   <para>
<synopsis>
date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
</synopsis>
    <replaceable>source</replaceable> is a value expression of type
    <type>timestamp</type> (values of type <type>date</type> and
    <type>time</type> are cast automatically).
    <replaceable>field</replaceable> selects to which precision to
    truncate the time stamp value.  The return value is of type
    <type>timestamp</type> with all fields that are less than the
    selected one set to zero (or one, for day and month).
   </para>

   <para>
    Valid values for <replaceable>field</replaceable> are:
    <simplelist>
     <member>microseconds</member>
     <member>milliseconds</member>
     <member>second</member>
     <member>minute</member>
     <member>hour</member>
     <member>day</member>
     <member>month</member>
     <member>year</member>
     <member>decade</member>
     <member>century</member>
     <member>millennium</member>
    </simplelist>
   </para>

   <informalexample>
    <para>
<screen>
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
</screen>
    </para>
   </informalexample>
  </sect2>

  <sect2 id="functions-datetime-current">
   <title>Current Date/Time</title>

4216 4217 4218 4219 4220 4221 4222 4223 4224 4225
   <indexterm>
    <primary>date</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm>
    <primary>time</primary>
    <secondary>current</secondary>
   </indexterm>

4226
   <para>
4227 4228
    The following functions are available to obtain the current date and/or
    time:
4229
     <synopsis>
4230
CURRENT_DATE
4231
CURRENT_TIME
4232
CURRENT_TIMESTAMP
4233 4234
CURRENT_TIME ( <replaceable>precision</replaceable> )
CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )
4235 4236 4237 4238 4239 4240 4241 4242 4243 4244 4245 4246 4247
LOCALTIME
LOCALTIMESTAMP
LOCALTIME ( <replaceable>precision</replaceable> )
LOCALTIMESTAMP ( <replaceable>precision</replaceable> )
     </synopsis>
     <function>CURRENT_TIME</function>,
     <function>CURRENT_TIMESTAMP</function>,
     <function>LOCALTIME</function>, and
     <function>LOCALTIMESTAMP</function>
     can optionally be given
     a precision parameter, which causes the result to be rounded
     to that many fractional digits.  Without a precision parameter,
     the result is given to the full available precision.
4248 4249
    </para>

4250 4251 4252 4253 4254 4255 4256
    <note>
     <para>
      Prior to <productname>PostgreSQL</productname> 7.2, the precision
      parameters were unimplemented, and the result was always given
      in integer seconds.
     </para>
    </note>
4257

4258 4259
    <informalexample>
     <screen>
4260
SELECT CURRENT_TIME;
4261
<computeroutput>14:39:53.662522-05</computeroutput>
4262 4263

SELECT CURRENT_DATE;
4264
<computeroutput>2001-12-23</computeroutput>
4265 4266

SELECT CURRENT_TIMESTAMP;
4267 4268 4269 4270
<computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>

SELECT CURRENT_TIMESTAMP(2);
<computeroutput>2001-12-23 14:39:53.66-05</computeroutput>
4271 4272 4273

SELECT LOCALTIMESTAMP;
<computeroutput>2001-12-23 14:39:53.662522</computeroutput>
4274 4275 4276 4277 4278
</screen>
   </informalexample>

   <para>
    The function <function>now()</function> is the traditional
4279
    <productname>PostgreSQL</productname> equivalent to
4280
    <function>CURRENT_TIMESTAMP</function>.
4281 4282 4283
   </para>

   <para>
4284 4285
    There is also <function>timeofday()</function>, which for historical
    reasons returns a text string rather than a <type>timestamp</type> value:
4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 4299 4300 4301 4302 4303
   </para>

   <informalexample>
<screen>
SELECT timeofday();
 Sat Feb 17 19:07:32.000126 2001 EST
</screen>
   </informalexample>

   <para>
    It is quite important to realize that
    <function>CURRENT_TIMESTAMP</function> and related functions all return
    the time as of the start of the current transaction; their values do not
    increment while a transaction is running.  But
    <function>timeofday()</function> returns the actual current time.
   </para>

   <para>
4304
    All the date/time data types also accept the special literal value
4305
    <literal>now</literal> to specify the current date and time.  Thus,
4306
    the following three all return the same result:
4307 4308 4309 4310 4311 4312 4313 4314
<programlisting>
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
</programlisting>
    <note>
     <para>
      You do not want to use the third form when specifying a DEFAULT
4315
      value while creating a table.  The system will convert <literal>now</literal>
4316
      to a <type>timestamp</type> as soon as the constant is parsed, so that when
4317
      the default value is needed,
4318 4319
      the time of the table creation would be used!  The first two
      forms will not be evaluated until the default value is used,
4320 4321
      because they are function calls.  Thus they will give the desired
      behavior of defaulting to the time of row insertion.
4322 4323 4324 4325 4326
     </para>
    </note>
   </para>
  </sect2>
 </sect1>
4327 4328 4329 4330 4331 4332

  
 <sect1 id="functions-geometry">
   <title>Geometric Functions and Operators</title>

   <para>
4333 4334 4335 4336
    The geometric types <type>point</type>, <type>box</type>,
    <type>lseg</type>, <type>line</type>, <type>path</type>,
    <type>polygon</type>, and <type>circle</type> have a large set of
    native support functions and operators.
4337 4338 4339
   </para>

   <table>
4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376
     <title>Geometric Operators</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Operator</entry>
	<entry>Description</entry>
	<entry>Usage</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> + </entry>
	<entry>Translation</entry>
	<entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> - </entry>
	<entry>Translation</entry>
	<entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> * </entry>
	<entry>Scaling/rotation</entry>
	<entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> / </entry>
	<entry>Scaling/rotation</entry>
	<entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> # </entry>
	<entry>Intersection</entry>
	<entry><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></entry>
       </row>
       <row>
	<entry> # </entry>
4377
	<entry>Number of points in path or polygon</entry>
4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 4398 4399 4400 4401 4402 4403 4404 4405 4406 4407 4408 4409 4410 4411 4412 4413 4414 4415 4416 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 4442 4443 4444 4445 4446 4447 4448 4449 4450 4451 4452 4453 4454 4455 4456 4457 4458 4459 4460 4461 4462 4463 4464 4465 4466 4467 4468 4469 4470 4471 4472
	<entry><literal># '((1,0),(0,1),(-1,0))'</literal></entry>
       </row>
       <row>
	<entry> ## </entry>
	<entry>Point of closest proximity</entry>
	<entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&amp; </entry>
	<entry>Overlaps?</entry>
	<entry><literal>box '((0,0),(1,1))' &amp;&amp; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&lt; </entry>
	<entry>Overlaps to left?</entry>
	<entry><literal>box '((0,0),(1,1))' &amp;&lt; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&gt; </entry>
	<entry>Overlaps to right?</entry>
	<entry><literal>box '((0,0),(3,3))' &amp;&gt; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &lt;-&gt; </entry>
	<entry>Distance between</entry>
	<entry><literal>circle '((0,0),1)' &lt;-&gt; circle '((5,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt; </entry>
	<entry>Left of?</entry>
	<entry><literal>circle '((0,0),1)' &lt;&lt; circle '((5,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &lt;^ </entry>
	<entry>Is below?</entry>
	<entry><literal>circle '((0,0),1)' &lt;^ circle '((0,5),1)'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt; </entry>
	<entry>Is right of?</entry>
	<entry><literal>circle '((5,0),1)' &gt;&gt; circle '((0,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &gt;^ </entry>
	<entry>Is above?</entry>
	<entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>
       </row>
       <row>
	<entry> ?# </entry>
	<entry>Intersects or overlaps</entry>
	<entry><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> ?- </entry>
	<entry>Is horizontal?</entry>
	<entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>
       </row>
       <row>
	<entry> ?-| </entry>
	<entry>Is perpendicular?</entry>
	<entry><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></entry>
       </row>
       <row>
	<entry> @-@  </entry>
	<entry>Length or circumference</entry>
	<entry><literal>@-@ path '((0,0),(1,0))'</literal></entry>
       </row>
       <row>
	<entry> ?| </entry>
	<entry>Is vertical?</entry>
	<entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>
       </row>
       <row>
	<entry> ?|| </entry>
	<entry>Is parallel?</entry>
	<entry><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></entry>
       </row>
       <row>
	<entry> @ </entry>
	<entry>Contained or on</entry>
	<entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>
       </row>
       <row>
	<entry> @@ </entry>
	<entry>Center of</entry>
	<entry><literal>@@ circle '((0,0),10)'</literal></entry>
       </row>
       <row>
	<entry> ~= </entry>
	<entry>Same as</entry>
	<entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>
       </row>
      </tbody>
     </tgroup>
   </table>
4473 4474

   <table>
4475 4476 4477 4478 4479 4480 4481 4482 4483 4484 4485 4486
     <title>Geometric Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
4487 4488
	<entry><function>area</function>(object)</entry>
	<entry><type>double precision</type></entry>
4489
	<entry>area of item</entry>
4490
	<entry><literal>area(box '((0,0),(1,1))')</literal></entry>
4491 4492
       </row>
       <row>
4493 4494
	<entry><function>box</function>(box, box)</entry>
	<entry><type>box</type></entry>
4495
	<entry>intersection box</entry>
4496
	<entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
4497 4498
       </row>
       <row>
4499 4500
	<entry><function>center</function>(object)</entry>
	<entry><type>point</type></entry>
4501
	<entry>center of item</entry>
4502
	<entry><literal>center(box '((0,0),(1,2))')</literal></entry>
4503 4504
       </row>
       <row>
4505 4506
	<entry><function>diameter</function>(circle)</entry>
	<entry><type>double precision</type></entry>
4507
	<entry>diameter of circle</entry>
4508
	<entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
4509 4510
       </row>
       <row>
4511 4512
	<entry><function>height</function>(box)</entry>
	<entry><type>double precision</type></entry>
4513
	<entry>vertical size of box</entry>
4514
	<entry><literal>height(box '((0,0),(1,1))')</literal></entry>
4515 4516
       </row>
       <row>
4517 4518
	<entry><function>isclosed</function>(path)</entry>
	<entry><type>boolean</type></entry>
4519
	<entry>a closed path?</entry>
4520
	<entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
4521 4522
       </row>
       <row>
4523 4524
	<entry><function>isopen</function>(path)</entry>
	<entry><type>boolean</type></entry>
4525
	<entry>an open path?</entry>
4526
	<entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
4527 4528
       </row>
       <row>
4529 4530
	<entry><function>length</function>(object)</entry>
	<entry><type>double precision</type></entry>
4531
	<entry>length of item</entry>
4532
	<entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
4533
       </row>
4534 4535 4536 4537 4538 4539 4540 4541 4542 4543 4544 4545
       <row>
	<entry><function>npoints</function>(path)</entry>
	<entry><type>integer</type></entry>
	<entry>number of points</entry>
	<entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
       </row>
       <row>
	<entry><function>npoints</function>(polygon)</entry>
	<entry><type>integer</type></entry>
	<entry>number of points</entry>
	<entry><literal>npoints(polygon '((1,1),(0,0))')</literal></entry>
       </row>
4546
       <row>
4547 4548
	<entry><function>pclose</function>(path)</entry>
	<entry><type>path</type></entry>
4549
	<entry>convert path to closed</entry>
4550
	<entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
4551
       </row>
4552 4553
<![IGNORE[
<!-- Not defined by this name. Implements the intersection operator '#' -->
4554
       <row>
4555 4556
	<entry><function>point</function>(lseg,lseg)</entry>
	<entry><type>point</type></entry>
4557
	<entry>intersection</entry>
4558
	<entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
4559
       </row>
4560
]]>
4561
       <row>
4562 4563
	<entry><function>popen</function>(path)</entry>
	<entry><type>path</type></entry>
4564
	<entry>convert path to open path</entry>
4565
	<entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
4566 4567
       </row>
       <row>
4568 4569
	<entry><function>radius</function>(circle)</entry>
	<entry><type>double precision</type></entry>
4570
	<entry>radius of circle</entry>
4571
	<entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
4572 4573
       </row>
       <row>
4574 4575
	<entry><function>width</function>(box)</entry>
	<entry><type>double precision</type></entry>
4576
	<entry>horizontal size</entry>
4577
	<entry><literal>width(box '((0,0),(1,1))')</literal></entry>
4578 4579 4580
       </row>
      </tbody>
     </tgroup>
4581
   </table>
4582

4583 4584

   <table>
4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596
     <title>Geometric Type Conversion Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
4597 4598
	<entry><function>box</function>(<type>circle</type>)</entry>
	<entry><type>box</type></entry>
4599
	<entry>circle to box</entry>
4600
	<entry><literal>box(circle '((0,0),2.0)')</literal></entry>
4601 4602
       </row>
       <row>
4603 4604
	<entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
	<entry><type>box</type></entry>
4605
	<entry>points to box</entry>
4606
	<entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
4607 4608
       </row>
       <row>
4609 4610
	<entry><function>box</function>(<type>polygon</type>)</entry>
	<entry><type>box</type></entry>
4611
	<entry>polygon to box</entry>
4612
	<entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4613 4614
       </row>
       <row>
4615 4616
	<entry><function>circle</function>(<type>box</type>)</entry>
	<entry><type>circle</type></entry>
4617
	<entry>to circle</entry>
4618
	<entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
4619 4620
       </row>
       <row>
4621 4622
	<entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
	<entry><type>circle</type></entry>
4623
	<entry>point to circle</entry>
4624
	<entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
4625 4626
       </row>
       <row>
4627 4628
	<entry><function>lseg</function>(<type>box</type>)</entry>
	<entry><type>lseg</type></entry>
4629
	<entry>box diagonal to <type>lseg</type></entry>
4630
	<entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
4631 4632
       </row>
       <row>
4633 4634
	<entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
	<entry><type>lseg</type></entry>
4635
	<entry>points to <type>lseg</type></entry>
4636
	<entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
4637 4638
       </row>
       <row>
4639 4640
	<entry><function>path</function>(<type>polygon</type>)</entry>
	<entry><type>point</type></entry>
4641
	<entry>polygon to path</entry>
4642
	<entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4643 4644
       </row>
       <row>
4645 4646
	<entry><function>point</function>(<type>circle</type>)</entry>
	<entry><type>point</type></entry>
4647
	<entry>center</entry>
4648
	<entry><literal>point(circle '((0,0),2.0)')</literal></entry>
4649 4650
       </row>
       <row>
4651 4652
	<entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
	<entry><type>point</type></entry>
4653
	<entry>intersection</entry>
4654
	<entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
4655 4656
       </row>
       <row>
4657 4658
	<entry><function>point</function>(<type>polygon</type>)</entry>
	<entry><type>point</type></entry>
4659
	<entry>center</entry>
4660
	<entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
4661 4662
       </row>
       <row>
4663 4664
	<entry><function>polygon</function>(<type>box</type>)</entry>
	<entry><type>polygon</type></entry>
4665
	<entry>4-point polygon</entry>
4666
	<entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
4667 4668
       </row>
       <row>
4669 4670
	<entry><function>polygon</function>(<type>circle</type>)</entry>
	<entry><type>polygon</type></entry>
4671
	<entry>12-point polygon</entry>
4672
	<entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
4673 4674
       </row>
       <row>
4675 4676
	<entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
	<entry><type>polygon</type></entry>
4677
	<entry><replaceable class="parameter">npts</replaceable> polygon</entry>
4678
	<entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
4679 4680
       </row>
       <row>
4681 4682
	<entry><function>polygon</function>(<type>path</type>)</entry>
	<entry><type>polygon</type></entry>
4683
	<entry>path to polygon</entry>
4684
	<entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
4685 4686 4687
       </row>
      </tbody>
     </tgroup>
4688 4689
   </table>

4690 4691 4692 4693 4694 4695 4696 4697 4698 4699
    <para>
     It is possible to access the two component numbers of a <type>point</>
     as though it were an array with subscripts 0,1.  For example, if
     <literal>t.p</> is a <type>point</> column then
     <literal>SELECT p[0] FROM t</> retrieves the X coordinate;
     <literal>UPDATE t SET p[1] = ...</> changes the Y coordinate.
     In the same way, a <type>box</> or an <type>lseg</> may be treated
     as an array of two <type>point</>s.
    </para>

4700
  </sect1>
4701

4702 4703

  <sect1 id="functions-net">
4704
   <title>Network Address Type Functions</title>
4705

4706 4707

    <table tocentry="1" id="cidr-inet-operators-table">
4708 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770
     <title><type>cidr</type> and <type>inet</type> Operators</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Operator</entry>
	<entry>Description</entry>
	<entry>Usage</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> &lt; </entry>
	<entry>Less than</entry>
	<entry><literal>inet '192.168.1.5' &lt; inet '192.168.1.6'</literal></entry>
       </row>
       <row>
	<entry> &lt;= </entry>
	<entry>Less than or equal</entry>
	<entry><literal>inet '192.168.1.5' &lt;= inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> = </entry>
	<entry>Equals</entry>
	<entry><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt;= </entry>
	<entry>Greater or equal</entry>
	<entry><literal>inet '192.168.1.5' &gt;= inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt; </entry>
	<entry>Greater</entry>
	<entry><literal>inet '192.168.1.5' &gt; inet '192.168.1.4'</literal></entry>
       </row>
       <row>
	<entry> &lt;&gt; </entry>
	<entry>Not equal</entry>
	<entry><literal>inet '192.168.1.5' &lt;&gt; inet '192.168.1.4'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt; </entry>
	<entry>is contained within</entry>
	<entry><literal>inet '192.168.1.5' &lt;&lt; inet '192.168.1/24'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt;= </entry>
	<entry>is contained within or equals</entry>
	<entry><literal>inet '192.168.1/24' &lt;&lt;= inet '192.168.1/24'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt; </entry>
	<entry>contains</entry>
	<entry><literal>inet'192.168.1/24' &gt;&gt; inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt;= </entry>
	<entry>contains or equals</entry>
	<entry><literal>inet '192.168.1/24' &gt;&gt;= inet '192.168.1/24'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>
4771 4772 4773 4774

    <para>
     All of the operators for <type>inet</type> can be applied to
     <type>cidr</type> values as well.  The operators
4775 4776
     <literal>&lt;&lt;</literal>, <literal>&lt;&lt;=</literal>,
     <literal>&gt;&gt;</literal>, <literal>&gt;&gt;=</literal>
4777 4778 4779 4780 4781 4782
     test for subnet inclusion: they consider only the network parts
     of the two addresses, ignoring any host part, and determine whether
     one network part is identical to or a subnet of the other.
    </para>


4783
    <table tocentry="1" id="cidr-inet-functions">
4784
     <title><type>cidr</type> and <type>inet</type> Functions</title>
4785
     <tgroup cols="5">
4786 4787 4788 4789 4790 4791
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
4792
	<entry>Result</entry>
4793 4794 4795 4796
       </row>
      </thead>
      <tbody>
       <row>
4797 4798
	<entry><function>broadcast</function>(<type>inet</type>)</entry>
	<entry><type>inet</type></entry>
4799
	<entry>broadcast address for network</entry>
4800 4801
	<entry><literal>broadcast('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.255/24</literal></entry>
4802 4803
       </row>
       <row>
4804 4805
	<entry><function>host</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
4806
	<entry>extract IP address as text</entry>
4807 4808
	<entry><literal>host('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.5</literal></entry>
4809 4810
       </row>
       <row>
4811 4812
	<entry><function>masklen</function>(<type>inet</type>)</entry>
	<entry><type>integer</type></entry>
4813
	<entry>extract netmask length</entry>
4814 4815
	<entry><literal>masklen('192.168.1.5/24')</literal></entry>
	<entry><literal>24</literal></entry>
4816
       </row>
Bruce Momjian's avatar
Bruce Momjian committed
4817
       <row>
4818 4819
	<entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
	<entry><type>inet</type></entry>
4820
	<entry>set netmask length for <type>inet</type> value</entry>
4821 4822
	<entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
	<entry><literal>192.168.1.5/16</literal></entry>
Bruce Momjian's avatar
Bruce Momjian committed
4823
       </row>
4824
       <row>
4825 4826
	<entry><function>netmask</function>(<type>inet</type>)</entry>
	<entry><type>inet</type></entry>
4827
	<entry>construct netmask for network</entry>
4828 4829
	<entry><literal>netmask('192.168.1.5/24')</literal></entry>
	<entry><literal>255.255.255.0</literal></entry>
4830 4831
       </row>
       <row>
4832 4833
	<entry><function>network</function>(<type>inet</type>)</entry>
	<entry><type>cidr</type></entry>
4834
	<entry>extract network part of address</entry>
4835 4836
	<entry><literal>network('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.0/24</literal></entry>
4837 4838
       </row>
       <row>
4839 4840
	<entry><function>text</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
4841
	<entry>extract IP address and masklen as text</entry>
4842 4843
	<entry><literal>text(inet '192.168.1.5')</literal></entry>
	<entry><literal>192.168.1.5/32</literal></entry>
4844
       </row>
4845
       <row>
4846 4847
	<entry><function>abbrev</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
4848
	<entry>extract abbreviated display as text</entry>
4849 4850
	<entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
	<entry><literal>10.1/16</literal></entry>
4851
       </row>
4852 4853 4854 4855 4856 4857
      </tbody>
     </tgroup>
    </table>

   <para>
    All of the functions for <type>inet</type> can be applied to
4858 4859
    <type>cidr</type> values as well.  The <function>host</function>(),
    <function>text</function>(), and <function>abbrev</function>() functions are primarily
Bruce Momjian's avatar
Bruce Momjian committed
4860
    intended to offer alternative display formats. You can cast a text
4861 4862
    field to inet using normal casting syntax: <literal>inet(expression)</literal> or 
    <literal>colname::inet</literal>.
4863 4864 4865
   </para>

    <table tocentry="1" id="macaddr-functions">
4866
     <title><type>macaddr</type> Functions</title>
4867 4868 4869 4870 4871 4872 4873 4874
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
4875
       </row>
4876 4877
      </thead>
      <tbody>
4878
       <row>
4879 4880
	<entry><function>trunc</function>(<type>macaddr</type>)</entry>
	<entry><type>macaddr</type></entry>
4881
	<entry>set last 3 bytes to zero</entry>
4882 4883
	<entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
	<entry><literal>12:34:56:00:00:00</literal></entry>
4884
       </row>
4885 4886 4887
      </tbody>
     </tgroup>
    </table>
4888

4889
   <para>
4890
    The function <function>trunc</function>(<type>macaddr</type>) returns a MAC
4891 4892
    address with the last 3 bytes set to 0.  This can be used to
    associate the remaining prefix with a manufacturer.  The directory
4893
    <filename>contrib/mac</filename> in the source distribution contains some
4894
    utilities to create and maintain such an association table.
4895
   </para>
4896

4897
   <para>
4898 4899
    The <type>macaddr</type> type also supports the standard relational
    operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
4900 4901 4902
    lexicographical ordering.
   </para>

4903
  </sect1>
4904

4905

4906 4907 4908 4909 4910 4911 4912 4913 4914 4915 4916 4917 4918 4919 4920 4921 4922
 <sect1 id="functions-sequence">
  <title>Sequence-Manipulation Functions</title>

  <indexterm>
   <primary>sequences</primary>
  </indexterm>
  <indexterm>
   <primary>nextval</primary>
  </indexterm>
  <indexterm>
   <primary>currval</primary>
  </indexterm>
  <indexterm>
   <primary>setval</primary>
  </indexterm>

   <table>
4923
    <title>Sequence Functions</title>
4924 4925
    <tgroup cols="3">
     <thead>
4926
      <row><entry>Function</entry> <entry>Returns</entry> <entry>Description</entry></row>
4927 4928 4929 4930 4931 4932
     </thead>

     <tbody>
      <row>
	<entry><function>nextval</function>(<type>text</type>)</entry>
	<entry><type>bigint</type></entry>
4933
	<entry>Advance sequence and return new value</entry>
4934 4935 4936 4937
      </row>
      <row>
	<entry><function>currval</function>(<type>text</type>)</entry>
	<entry><type>bigint</type></entry>
4938
	<entry>Return value most recently obtained with <function>nextval</function></entry>
4939 4940 4941 4942
      </row>
      <row>
	<entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
	<entry><type>bigint</type></entry>
4943
	<entry>Set sequence's current value</entry>
4944 4945
      </row>
      <row>
4946
	<entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</type>)</entry>
4947
	<entry><type>bigint</type></entry>
4948
	<entry>Set sequence's current value and <literal>is_called</literal> flag</entry>
4949 4950 4951 4952 4953 4954
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
4955
   This section describes <productname>PostgreSQL</productname>'s functions
4956
   for operating on <firstterm>sequence objects</firstterm>.
4957 4958
   Sequence objects (also called sequence generators or
   just sequences) are special single-row tables created with
4959
   <command>CREATE SEQUENCE</command>.  A sequence object is usually used to
4960
   generate unique identifiers for rows of a table.  The sequence functions
4961
   provide simple, multiuser-safe methods for obtaining successive
4962 4963 4964 4965 4966 4967 4968 4969 4970 4971
   sequence values from sequence objects.
  </para>

  <para>
   For largely historical reasons, the sequence to be operated on by
   a sequence-function call is specified by a text-string argument.
   To achieve some compatibility with the handling of ordinary SQL
   names, the sequence functions convert their argument to lower case
   unless the string is double-quoted.  Thus
<programlisting>
4972 4973 4974
nextval('foo')      <lineannotation>operates on sequence </><literal>foo</literal>
nextval('FOO')      <lineannotation>operates on sequence </><literal>foo</literal>
nextval('"Foo"')    <lineannotation>operates on sequence </><literal>Foo</literal>
4975 4976 4977
</programlisting>
   The sequence name can be schema-qualified if necessary:
<programlisting>
4978 4979 4980 4981
nextval('myschema.foo') <lineannotation>operates on </><literal>myschema.foo</literal>
nextval('"myschema".foo') <lineannotation>same as above</lineannotation>
nextval('foo')      <lineannotation>searches search path for
     </><literal>foo</literal>
4982 4983 4984 4985 4986 4987 4988 4989 4990 4991
</programlisting>
   Of course, the text argument can be the result of an expression,
   not only a simple literal, which is occasionally useful.
  </para>

  <para>
   The available sequence functions are:

    <variablelist>
     <varlistentry>
4992
      <term><function>nextval</function></term>
4993 4994 4995 4996
      <listitem>
       <para>
        Advance the sequence object to its next value and return that
	value.  This is done atomically: even if multiple server processes
4997
	execute <function>nextval</function> concurrently, each will safely receive
4998 4999 5000 5001 5002 5003
	a distinct sequence value.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
5004
      <term><function>currval</function></term>
5005 5006
      <listitem>
       <para>
5007
        Return the value most recently obtained by <function>nextval</function>
5008
	for this sequence in the current server process.  (An error is
5009
	reported if <function>nextval</function> has never been called for this
5010 5011
	sequence in this process.)  Notice that because this is returning
	a process-local value, it gives a predictable answer even if other
5012
	server processes are executing <function>nextval</function> meanwhile.
5013 5014 5015 5016 5017
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
5018
      <term><function>setval</function></term>
5019 5020 5021
      <listitem>
       <para>
        Reset the sequence object's counter value.  The two-parameter
5022 5023 5024
	form sets the sequence's <literal>last_value</literal> field to the specified
	value and sets its <literal>is_called</literal> field to <literal>true</literal>,
	meaning that the next <function>nextval</function> will advance the sequence
5025
	before returning a value.  In the three-parameter form,
5026 5027 5028
	<literal>is_called</literal> may be set either <literal>true</literal> or
	<literal>false</literal>.  If it's set to <literal>false</literal>,
	the next <function>nextval</function> will return exactly the specified
5029
	value, and sequence advancement commences with the following
5030
	<function>nextval</function>.  For example,
5031 5032 5033 5034
       </para>

       <informalexample>
<screen>
5035 5036 5037
SELECT setval('foo', 42);           <lineannotation>Next nextval() will return 43</lineannotation>
SELECT setval('foo', 42, true);     <lineannotation>Same as above</lineannotation>
SELECT setval('foo', 42, false);    <lineannotation>Next nextval() will return 42</lineannotation>
5038 5039 5040 5041
</screen>
       </informalexample>

       <para>
5042
        The result returned by <function>setval</function> is just the value of its
5043 5044 5045 5046 5047 5048 5049 5050 5051 5052
	second argument.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
  </para>

  <important>
   <para>
    To avoid blocking of concurrent transactions that obtain numbers from the
5053
    same sequence, a <function>nextval</function> operation is never rolled back;
5054
    that is, once a value has been fetched it is considered used, even if the
5055
    transaction that did the <function>nextval</function> later aborts.  This means
5056
    that aborted transactions may leave unused <quote>holes</quote> in the
5057
    sequence of assigned values.  <function>setval</function> operations are never
5058 5059 5060 5061 5062 5063
    rolled back, either.
   </para>
  </important>

  <para>
   If a sequence object has been created with default parameters,
5064
   <function>nextval()</function> calls on it will return successive values
5065
   beginning with one.  Other behaviors can be obtained by using
5066
   special parameters in the <command>CREATE SEQUENCE</command> command;
5067 5068 5069 5070 5071 5072
   see its command reference page for more information.
  </para>

 </sect1>


5073 5074 5075
 <sect1 id="functions-conditional">
  <title>Conditional Expressions</title>

5076 5077 5078 5079 5080 5081 5082 5083
  <indexterm>
   <primary>case</primary>
  </indexterm>

  <indexterm>
   <primary>conditionals</primary>
  </indexterm>

5084
  <para>
5085
   This section describes the <acronym>SQL</acronym>-compliant conditional expressions
5086
   available in <productname>PostgreSQL</productname>.
5087 5088 5089 5090 5091 5092 5093 5094 5095 5096
  </para>

  <tip>
   <para>
    If your needs go beyond the capabilities of these conditional
    expressions you might want to consider writing a stored procedure
    in a more expressive programming language.
   </para>
  </tip>

5097
  <bridgehead renderas="sect2"><literal>CASE</></bridgehead>
5098 5099 5100 5101 5102 5103 5104 5105 5106 5107 5108

<synopsis>
CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
     <optional>WHEN ...</optional>
     <optional>ELSE <replaceable>result</replaceable></optional>
END
</synopsis>

  <para>
   The <acronym>SQL</acronym> <token>CASE</token> expression is a
   generic conditional expression, similar to if/else statements in
5109
   other languages.  <token>CASE</token> clauses can be used wherever
5110
   an expression is valid.  <replaceable>condition</replaceable> is an
5111
   expression that returns a <type>boolean</type> result.  If the result is true
5112 5113 5114 5115 5116 5117 5118
   then the value of the <token>CASE</token> expression is
   <replaceable>result</replaceable>.  If the result is false any
   subsequent <token>WHEN</token> clauses are searched in the same
   manner.  If no <token>WHEN</token>
   <replaceable>condition</replaceable> is true then the value of the
   case expression is the <replaceable>result</replaceable> in the
   <token>ELSE</token> clause.  If the <token>ELSE</token> clause is
5119
   omitted and no condition matches, the result is null.
5120 5121 5122 5123 5124 5125 5126 5127 5128 5129 5130 5131 5132 5133 5134
  </para>

  <informalexample>
   <para>
    An example:
<screen>
<prompt>=&gt;</prompt> <userinput>SELECT * FROM test;</userinput>
<computeroutput>
 a
---
 1
 2
 3
</computeroutput>

5135 5136 5137 5138 5139 5140
<prompt>=&gt;</prompt> <userinput>SELECT a,
          CASE WHEN a=1 THEN 'one'
               WHEN a=2 THEN 'two'
               ELSE 'other'
          END
    FROM test;</userinput>
5141 5142 5143 5144 5145 5146 5147 5148 5149 5150 5151 5152
<computeroutput>
 a | case
---+-------
 1 | one
 2 | two
 3 | other
</computeroutput>
</screen>
   </para>
  </informalexample>

  <para>
5153 5154 5155
   The data types of all the <replaceable>result</replaceable>
   expressions must be coercible to a single output type.
   See <xref linkend="typeconv-union-case"> for more detail.
5156 5157 5158 5159 5160 5161 5162 5163 5164 5165 5166 5167 5168 5169 5170 5171 5172
  </para>

<synopsis>
CASE <replaceable>expression</replaceable>
    WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
    <optional>WHEN ...</optional>
    <optional>ELSE <replaceable>result</replaceable></optional>
END
</synopsis>

  <para>
   This <quote>simple</quote> <token>CASE</token> expression is a
   specialized variant of the general form above.  The
   <replaceable>expression</replaceable> is computed and compared to
   all the <replaceable>value</replaceable>s in the
   <token>WHEN</token> clauses until one is found that is equal.  If
   no match is found, the <replaceable>result</replaceable> in the
5173
   <token>ELSE</token> clause (or a null value) is returned.  This is similar
5174 5175 5176 5177 5178 5179 5180 5181
   to the <function>switch</function> statement in C.
  </para>

  <informalexample>
   <para>
    The example above can be written using the simple
    <token>CASE</token> syntax:
<screen>
5182 5183 5184 5185 5186 5187
<prompt>=&gt;</prompt> <userinput>SELECT a,
          CASE a WHEN 1 THEN 'one'
                 WHEN 2 THEN 'two'
                 ELSE 'other'
          END
    FROM test;</userinput>
5188 5189 5190 5191 5192 5193 5194 5195 5196 5197 5198
<computeroutput>
 a | case
---+-------
 1 | one
 2 | two
 3 | other
</computeroutput>
</screen>
    </para>
   </informalexample>

5199
   <bridgehead renderas="sect2"><literal>COALESCE</></bridgehead>
5200 5201

<synopsis>
5202 5203
<function>COALESCE</function>(<replaceable>value</replaceable>
<optional>, ...</optional>)
5204 5205 5206 5207
</synopsis>

  <para>
   The <function>COALESCE</function> function returns the first of its
5208 5209
   arguments that is not null.  This is often useful to substitute a
   default value for null values when data is retrieved for display,
5210 5211 5212 5213 5214 5215
   for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
  </para>

5216
 <bridgehead renderas="sect2"><literal>NULLIF</></bridgehead>
5217

5218 5219 5220 5221
  <indexterm>
   <primary>nullif</primary>
  </indexterm>

5222
<synopsis>
5223 5224
<function>NULLIF</function>(<replaceable>value1</replaceable>,
    <replaceable>value2</replaceable>)
5225 5226 5227
</synopsis>

  <para>
5228
   The <function>NULLIF</function> function returns a null value if and only
5229 5230 5231 5232 5233 5234 5235 5236 5237
   if <replaceable>value1</replaceable> and
   <replaceable>value2</replaceable> are equal.  Otherwise it returns
   <replaceable>value1</replaceable>.  This can be used to perform the
   inverse operation of the <function>COALESCE</function> example
   given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
  </para>
5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250

  <tip>
   <para>
    <function>COALESCE</function> and <function>NULLIF</function> are
    just shorthand for <token>CASE</token> expressions.  They are actually
    converted into <token>CASE</token> expressions at a very early stage
    of processing, and subsequent processing thinks it is dealing with
    <token>CASE</token>.  Thus an incorrect <function>COALESCE</function> or
    <function>NULLIF</function> usage may draw an error message that
    refers to <token>CASE</token>.
   </para>
  </tip>

5251 5252 5253 5254
 </sect1>


  <sect1 id="functions-misc">
5255
   <title>Miscellaneous Functions</title>
5256 5257

   <table>
5258
    <title>Session Information Functions</title>
5259 5260
    <tgroup cols="3">
     <thead>
5261
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5262 5263 5264 5265
     </thead>

     <tbody>
      <row>
5266 5267
       <entry><function>current_user</function></entry>
       <entry><type>name</type></entry>
5268
       <entry>user name of current execution context</entry>
5269 5270
      </row>
      <row>
5271 5272
       <entry><function>session_user</function></entry>
       <entry><type>name</type></entry>
5273
       <entry>session user name</entry>
5274 5275
      </row>
      <row>
5276 5277 5278
       <entry><function>user</function></entry>
       <entry><type>name</type></entry>
       <entry>equivalent to <function>current_user</function></entry>
5279 5280
      </row>
      <row>
5281 5282
       <entry><function>current_schema()</function></entry>
       <entry><type>name</type></entry>
5283 5284 5285
       <entry>name of current schema</entry>
      </row>
      <row>
5286
       <entry><function>current_schemas(boolean)</function></entry>
5287
       <entry><type>name[]</type></entry>
5288
       <entry>names of schemas in search path optionally including implicit schemas</entry>
5289
      </row>
Bruce Momjian's avatar
Bruce Momjian committed
5290 5291 5292 5293 5294
      <row>
       <entry><function>current_database()</function></entry>
       <entry><type>name</type></entry>
       <entry>name of current database</entry>
      </row>
5295 5296 5297 5298
     </tbody>
    </tgroup>
   </table>

5299 5300 5301 5302 5303
   <indexterm zone="functions-misc">
    <primary>user</primary>
    <secondary>current</secondary>
   </indexterm>

5304 5305 5306 5307 5308 5309 5310 5311 5312 5313
   <indexterm zone="functions-misc">
    <primary>schema</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>search path</primary>
    <secondary>current</secondary>
   </indexterm>

5314
   <para>
5315
    The <function>session_user</function> is the user that initiated a
5316
    database connection; it is fixed for the duration of that
5317
    connection. The <function>current_user</function> is the user identifier
5318 5319 5320
    that is applicable for permission checking. Normally, it is equal
    to the session user, but it changes during the execution of
    functions with the attribute <literal>SECURITY DEFINER</literal>.
5321 5322
    In Unix parlance, the session user is the <quote>real user</quote> and
    the current user is the <quote>effective user</quote>.
5323 5324
   </para>

5325 5326
   <note>
    <para>
5327 5328
     <function>current_user</function>, <function>session_user</function>, and
     <function>user</function> have special syntactic status in <acronym>SQL</acronym>:
5329 5330 5331
     they must be called without trailing parentheses.
    </para>
   </note>
5332 5333

   <note>
5334
    <title>Deprecated</title>
5335
    <para>
5336 5337
     The function <function>getpgusername()</function> is an obsolete equivalent
     of <function>current_user</function>.
5338 5339
    </para>
   </note>
5340

5341
   <para>
5342
    <function>current_schema</function> returns the name of the schema that is
5343
    at the front of the search path (or a null value if the search path is
5344 5345
    empty).  This is the schema that will be used for any tables or
    other named objects that are created without specifying a target schema.
5346 5347 5348 5349
    <function>current_schemas(boolean)</function> returns an array of the names of all
    schemas presently in the search path.  The boolean option determines whether or not
    implicitly included system schemas such as pg_catalog are included in the search 
    path returned.
5350 5351
   </para>

5352 5353 5354 5355 5356
   <para id=set-search-path xreflabel="SET SEARCH_PATH">
    <indexterm>
     <primary>search path</primary>
     <secondary>changing at runtime</secondary>
    </indexterm>
5357
    The search path may be altered by a run-time setting.  The
5358 5359 5360 5361 5362
    command to use is <command>
     SET SEARCH_PATH '<varname>schema</varname>'[,'<varname>schema</varname>']...
    </command>
   </para>

5363
   <table>
5364
    <title>System Information Functions</title>
5365 5366
    <tgroup cols="3">
     <thead>
5367
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5368 5369 5370 5371
     </thead>

     <tbody>
      <row>
5372 5373 5374
       <entry><function>version</function></entry>
       <entry><type>text</type></entry>
       <entry>PostgreSQL version information</entry>
5375 5376 5377 5378 5379 5380 5381 5382 5383 5384
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>version</primary>
   </indexterm>

   <para>
5385
    <function>version()</function> returns a string describing the PostgreSQL
5386 5387 5388
    server's version.
   </para>

5389 5390 5391 5392 5393 5394 5395 5396 5397 5398 5399 5400 5401 5402 5403 5404 5405 5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 5443 5444 5445 5446 5447 5448 5449
   <table>
    <title>Configuration Settings Information Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry>
        <function>current_setting</function>(<parameter>setting_name</parameter>)
       </entry>
       <entry><type>text</type></entry>
       <entry>value of current setting</entry>
      </row>
      <row>
       <entry>
        <function>set_config(<parameter>setting_name</parameter>,
                             <parameter>new_value</parameter>,
                             <parameter>is_local</parameter>)</function>
       </entry>
       <entry><type>text</type></entry>
       <entry>new value of current setting</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>setting</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>setting</primary>
    <secondary>set</secondary>
   </indexterm>

   <para>
    The <function>current_setting</function> is used to obtain the current
    value of the <parameter>setting_name</parameter> setting, as a query
    result. It is the equivalent to the SQL <command>SHOW</command> command.
    For example:
<programlisting>
select current_setting('DateStyle');
            current_setting
---------------------------------------
 ISO with US (NonEuropean) conventions
(1 row)
</programlisting>
   </para>

   <para>
    <function>set_config</function> allows the <parameter>setting_name
    </parameter> setting to be changed to <parameter>new_value</parameter>.
    If <parameter>is_local</parameter> is set to <literal>true</literal>,
    the new value will only apply to the current transaction. If you want
    the new value to apply for the current session, use
    <literal>false</literal> instead. It is the equivalent to the SQL
    <command>SET</command> command. For example:
<programlisting>
5450
select set_config('show_statement_stats','off','f');
5451 5452 5453 5454 5455 5456 5457
 set_config
------------
 off
(1 row)
</programlisting>
   </para>

5458
   <table>
5459
    <title>Access Privilege Inquiry Functions</title>
5460 5461
    <tgroup cols="3">
     <thead>
5462
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5463 5464 5465 5466
     </thead>

     <tbody>
      <row>
5467
       <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
5468 5469 5470
                                  <parameter>table</parameter>,
                                  <parameter>access</parameter>)
       </entry>
5471 5472
       <entry><type>boolean</type></entry>
       <entry>does user have access to table</entry>
5473 5474
      </row>
      <row>
5475
       <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
5476 5477
                                  <parameter>access</parameter>)
       </entry>
5478 5479
       <entry><type>boolean</type></entry>
       <entry>does current user have access to table</entry>
5480
      </row>
5481 5482 5483 5484 5485 5486 5487 5488 5489 5490 5491 5492 5493 5494 5495 5496 5497 5498 5499 5500 5501 5502 5503 5504 5505 5506 5507 5508 5509 5510 5511 5512 5513 5514 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 5530 5531 5532 5533 5534 5535 5536 5537 5538 5539 5540
      <row>
       <entry><function>has_database_privilege</function>(<parameter>user</parameter>,
                                  <parameter>database</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to database</entry>
      </row>
      <row>
       <entry><function>has_database_privilege</function>(<parameter>database</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to database</entry>
      </row>
      <row>
       <entry><function>has_function_privilege</function>(<parameter>user</parameter>,
                                  <parameter>function</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to function</entry>
      </row>
      <row>
       <entry><function>has_function_privilege</function>(<parameter>function</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to function</entry>
      </row>
      <row>
       <entry><function>has_language_privilege</function>(<parameter>user</parameter>,
                                  <parameter>language</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to language</entry>
      </row>
      <row>
       <entry><function>has_language_privilege</function>(<parameter>language</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to language</entry>
      </row>
      <row>
       <entry><function>has_schema_privilege</function>(<parameter>user</parameter>,
                                  <parameter>schema</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to schema</entry>
      </row>
      <row>
       <entry><function>has_schema_privilege</function>(<parameter>schema</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to schema</entry>
      </row>
5541 5542 5543 5544 5545 5546 5547
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>has_table_privilege</primary>
   </indexterm>
5548 5549 5550 5551 5552 5553 5554 5555 5556 5557 5558 5559
   <indexterm zone="functions-misc">
    <primary>has_database_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_function_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_language_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_schema_privilege</primary>
   </indexterm>
5560 5561

   <para>
5562
    <function>has_table_privilege</function> checks whether a user
5563
    can access a table in a particular way.  The user can be
5564
    specified by name or by ID
5565
    (<classname>pg_user</classname>.<structfield>usesysid</structfield>), or if the argument is
5566
    omitted
5567
    <function>current_user</function> is assumed.  The table can be specified
5568
    by name or by OID.  (Thus, there are actually six variants of
5569
    <function>has_table_privilege</function>, which can be distinguished by
5570 5571 5572
    the number and types of their arguments.)  When specifying by name,
    the name can be schema-qualified if necessary.
    The desired access type
5573
    is specified by a text string, which must evaluate to one of the
5574 5575 5576
    values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
    <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
    <literal>TRIGGER</literal>.  (Case of the string is not significant, however.)
5577 5578 5579 5580
    An example is:
<programlisting>
SELECT has_table_privilege('myschema.mytable', 'select');
</programlisting>
5581 5582
   </para>

5583 5584 5585 5586 5587 5588 5589 5590 5591 5592 5593 5594 5595 5596 5597 5598
   <para>
    <function>has_database_privilege</function> checks whether a user
    can access a database in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must evaluate to
    <literal>CREATE</literal>,
    <literal>TEMPORARY</literal>, or
    <literal>TEMP</literal> (which is equivalent to
    <literal>TEMPORARY</literal>).
   </para>

   <para>
    <function>has_function_privilege</function> checks whether a user
    can access a function in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    When specifying a function by a text string rather than by OID,
5599
    the allowed input is the same as for the <type>regprocedure</> data type.
5600 5601 5602 5603 5604 5605 5606 5607 5608 5609 5610 5611 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 5628 5629 5630 5631 5632 5633 5634 5635 5636 5637 5638 5639 5640 5641 5642 5643 5644 5645 5646 5647 5648 5649 5650 5651 5652 5653 5654 5655 5656 5657 5658 5659 5660 5661 5662 5663 5664 5665 5666 5667 5668 5669 5670 5671 5672 5673 5674 5675 5676 5677 5678 5679 5680 5681 5682 5683 5684 5685 5686 5687 5688 5689 5690 5691 5692 5693 5694 5695 5696 5697 5698 5699 5700
    The desired access type must currently evaluate to
    <literal>EXECUTE</literal>.
   </para>

   <para>
    <function>has_language_privilege</function> checks whether a user
    can access a procedural language in a particular way.  The possibilities
    for its arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must currently evaluate to
    <literal>USAGE</literal>.
   </para>

   <para>
    <function>has_schema_privilege</function> checks whether a user
    can access a schema in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must evaluate to
    <literal>CREATE</literal> or
    <literal>USAGE</literal>.
   </para>

   <table>
    <title>Schema Visibility Inquiry Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>pg_table_is_visible</function>(<parameter>tableOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is table visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_type_is_visible</function>(<parameter>typeOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is type visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_function_is_visible</function>(<parameter>functionOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is function visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_operator_is_visible</function>(<parameter>operatorOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is operator visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_opclass_is_visible</function>(<parameter>opclassOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is operator class visible in search path</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>pg_table_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_type_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_function_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_operator_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_opclass_is_visible</primary>
   </indexterm>

   <para>
    <function>pg_table_is_visible</function> checks whether a table
    (or view, or any other kind of <structname>pg_class</> entry) is
    <firstterm>visible</> in the current schema search path.  A table
    is said to be visible if its containing schema is in the search path
    and no table of the same name appears earlier in the search path.
    This is equivalent to the statement that the table can be referenced
    by name without explicit schema qualification.
    For example, to list the names of all visible tables:
<programlisting>
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
</programlisting>
   </para>

   <para>
    <function>pg_type_is_visible</function>,
    <function>pg_function_is_visible</function>,
    <function>pg_operator_is_visible</function>, and
    <function>pg_opclass_is_visible</function> perform the same sort of
    visibility check for types, functions, operators, and operator classes,
    respectively.  For functions and operators, an object in the search path
    is visible if there is no object of the same name <emphasis>and argument
5701
    data type(s)</> earlier in the path.  For operator classes,
5702 5703 5704 5705 5706 5707 5708 5709 5710 5711 5712 5713 5714 5715 5716
    both name and associated index access method are considered.
   </para>

   <para>
    All these functions require object OIDs to identify the object to be
    checked.  If you want to test an object by name, it is convenient to use
    the OID alias types (<type>regclass</>, <type>regtype</>,
    <type>regprocedure</>, or <type>regoperator</>), for example
<programlisting>
SELECT pg_type_is_visible('myschema.widget'::regtype);
</programlisting>
    Note that it would not make much sense to test an unqualified name in
    this way --- if the name can be recognized at all, it must be visible.
   </para>

5717
   <table>
5718
    <title>Catalog Information Functions</title>
5719 5720
    <tgroup cols="3">
     <thead>
5721
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5722 5723 5724 5725
     </thead>

     <tbody>
      <row>
5726 5727
       <entry><function>pg_get_viewdef</function>(<parameter>viewname</parameter>)</entry>
       <entry><type>text</type></entry>
5728
       <entry>Get CREATE VIEW command for view (<emphasis>deprecated</emphasis>)</entry>
5729 5730
      </row>
      <row>
5731 5732 5733
       <entry><function>pg_get_viewdef</function>(<parameter>viewOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get CREATE VIEW command for view</entry>
5734 5735
      </row>
      <row>
5736 5737 5738
       <entry><function>pg_get_ruledef</function>(<parameter>ruleOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get CREATE RULE command for rule</entry>
5739 5740
      </row>
      <row>
5741 5742 5743
       <entry><function>pg_get_indexdef</function>(<parameter>indexOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get CREATE INDEX command for index</entry>
5744
      </row>
5745 5746 5747 5748 5749
      <row>
       <entry><function>pg_get_constraintdef</function>(<parameter>constraintOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get definition of a constraint</entry>
      </row>
5750
      <row>
5751 5752 5753
       <entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
       <entry><type>name</type></entry>
       <entry>Get user name given ID</entry>
5754 5755 5756 5757 5758 5759
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
5760 5761 5762 5763 5764 5765 5766 5767 5768 5769 5770
    <primary>pg_get_viewdef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_ruledef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_indexdef</primary>
   </indexterm>

5771 5772 5773 5774
   <indexterm zone="functions-misc">
    <primary>pg_get_constraintdef</primary>
   </indexterm>

5775 5776
   <indexterm zone="functions-misc">
    <primary>pg_get_userbyid</primary>
5777 5778 5779
   </indexterm>

   <para>
5780
    These functions extract information from the system catalogs.
5781 5782 5783 5784 5785 5786
    <function>pg_get_viewdef()</function>,
    <function>pg_get_ruledef()</function>,
    <function>pg_get_indexdef()</function>, and
    <function>pg_get_constraintdef()</function> respectively reconstruct the
    creating command for a view, rule, index, or constraint.
    (Note that this is a decompiled
5787
    reconstruction, not the verbatim text of the command.)
5788 5789
    At present <function>pg_get_constraintdef()</function> only works for
    foreign-key constraints.
5790 5791
    <function>pg_get_userbyid()</function> extracts a user's name given a
    <structfield>usesysid</structfield> value.
5792 5793
   </para>

5794
   <table>
5795
    <title>Comment Information Functions</title>
5796 5797
    <tgroup cols="3">
     <thead>
5798
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
5799 5800 5801 5802
     </thead>

     <tbody>
      <row>
5803 5804 5805
       <entry><function>obj_description</function>(<parameter>objectOID</parameter>, <parameter>tablename</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a database object</entry>
5806 5807
      </row>
      <row>
5808 5809 5810
       <entry><function>obj_description</function>(<parameter>objectOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a database object (<emphasis>deprecated</emphasis>)</entry>
5811 5812
      </row>
      <row>
5813 5814 5815
       <entry><function>col_description</function>(<parameter>tableOID</parameter>, <parameter>columnnumber</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a table column</entry>
5816 5817 5818 5819 5820 5821 5822 5823 5824 5825 5826 5827 5828 5829 5830
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>obj_description</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>col_description</primary>
   </indexterm>

   <para>
    These functions extract comments previously stored with the
5831
    <command>COMMENT</command> command.  A null value is returned if
5832 5833 5834 5835
    no comment can be found matching the specified parameters.
   </para>

   <para>
5836
    The two-parameter form of <function>obj_description()</function> returns the
5837 5838
    comment for a database object specified by its OID and the name of the
    containing system catalog.  For example,
5839
    <literal>obj_description(123456,'pg_class')</literal>
5840
    would retrieve the comment for a table with OID 123456.
5841
    The one-parameter form of <function>obj_description()</function> requires only
5842 5843 5844 5845 5846 5847
    the object OID.  It is now deprecated since there is no guarantee that
    OIDs are unique across different system catalogs; therefore, the wrong
    comment could be returned.
   </para>

   <para>
5848
    <function>col_description()</function> returns the comment for a table column,
5849
    which is specified by the OID of its table and its column number.
5850
    <function>obj_description()</function> cannot be used for table columns since
5851 5852 5853
    columns do not have OIDs of their own.
   </para>

5854 5855
  </sect1>

5856

5857 5858
 <sect1 id="functions-aggregate">
  <title>Aggregate Functions</title>
5859

5860 5861
  <note>
   <title>Author</title>
5862
   <para>
5863
    Written by Isaac Wilcox <email>isaac@azartmedia.com</email> on 2000-06-16
5864
   </para>
5865
  </note>
5866

5867 5868 5869 5870
  <para>
   <firstterm>Aggregate functions</firstterm> compute a single result
   value from a set of input values.  The special syntax
   considerations for aggregate functions are explained in <xref
5871
                                                                 linkend="syntax-aggregates">.  Consult the <citetitle>PostgreSQL
5872 5873
   Tutorial</citetitle> for additional introductory information.
  </para>
5874

5875 5876
  <table tocentry="1">
   <title>Aggregate Functions</title>
5877

5878 5879 5880 5881 5882 5883 5884 5885 5886 5887 5888
   <tgroup cols="3">
    <thead>
     <row>
      <entry>Function</entry>
      <entry>Description</entry>
      <entry>Notes</entry>
     </row>
    </thead>

    <tbody>
     <row>
5889
      <entry>avg(<replaceable class="parameter">expression</replaceable>)</entry>
5890 5891
      <entry>the average (arithmetic mean) of all input values</entry>
      <entry>
5892 5893 5894 5895
       <indexterm>
        <primary>average</primary>
        <secondary>function</secondary>
       </indexterm>
5896 5897 5898 5899 5900
       Finding the average value is available on the following data
       types: <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, <type>numeric</type>, <type>interval</type>.
       The result is of type <type>numeric</type> for any integer type
Peter Eisentraut's avatar
Peter Eisentraut committed
5901
       input, <type>double precision</type> for floating-point input,
5902 5903 5904 5905 5906
       otherwise the same as the input data type.
      </entry>
     </row>

     <row>
5907
      <entry><function>count</function>(*)</entry>
5908
      <entry>number of input values</entry>
5909
      <entry>The return value is of type <type>bigint</type>.</entry>
5910 5911 5912
     </row>

     <row>
5913
      <entry><function>count</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5914 5915
      <entry>
       Counts the input values for which the value of <replaceable
5916
                                                                   class="parameter">expression</replaceable> is not null.
5917
      </entry>
5918
      <entry>The return value is of type <type>bigint</type>.</entry>
5919 5920 5921
     </row>

     <row>
5922
      <entry><function>max</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5923 5924 5925 5926 5927 5928 5929 5930
      <entry>the maximum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
      <entry>
       Available for all numeric, string, and date/time types.  The
       result has the same type as the input expression.
      </entry>
     </row>

     <row>
5931
      <entry><function>min</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5932 5933 5934 5935 5936 5937 5938 5939
      <entry>the minimum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
      <entry>
       Available for all numeric, string, and date/time types.  The
       result has the same type as the input expression.
      </entry>
     </row>

     <row>
5940 5941
      <entry><function>stddev</function>(<replaceable
                                                      class="parameter">expression</replaceable>)</entry>
5942 5943
      <entry>the sample standard deviation of the input values</entry>
      <entry>
5944 5945 5946
       <indexterm>
        <primary>standard deviation</primary>
       </indexterm>
5947 5948 5949 5950
       Finding the standard deviation is available on the following
       data types: <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, <type>numeric</type>.  The result is of type
Peter Eisentraut's avatar
Peter Eisentraut committed
5951
       <type>double precision</type> for floating-point input,
5952 5953 5954 5955 5956
       otherwise <type>numeric</type>.
      </entry>
     </row>

     <row>
5957
      <entry><function>sum</function>(<replaceable class="parameter">expression</replaceable>)</entry>
5958 5959 5960 5961 5962 5963
      <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
      <entry>
       Summation is available on the following data types:
       <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, <type>numeric</type>, <type>interval</type>.
5964 5965 5966
       The result is of type <type>bigint</type> for <type>smallint</type>
       or <type>integer</type> input, <type>numeric</type> for
       <type>bigint</type> 
Peter Eisentraut's avatar
Peter Eisentraut committed
5967
       input, <type>double precision</type> for floating-point input,
5968 5969 5970 5971 5972
       otherwise the same as the input data type.
      </entry>
     </row>

     <row>
5973 5974
      <entry><function>variance</function>(<replaceable
                                                        class="parameter">expression</replaceable>)</entry>
5975 5976
      <entry>the sample variance of the input values</entry>
      <entry>
5977 5978 5979
       <indexterm>
        <primary>variance</primary>
       </indexterm>
5980
       The variance is the square of the standard deviation.  The
5981 5982
       supported data types and result types are the same as for
       standard deviation.
5983 5984 5985 5986 5987 5988
      </entry>
     </row>

    </tbody>
   </tgroup>
  </table>
5989

5990 5991
  <para>
   It should be noted that except for <function>COUNT</function>,
5992 5993
   these functions return a null value when no rows are selected.  In
   particular, <function>SUM</function> of no rows returns null, not
5994
   zero as one might expect.  <function>COALESCE</function> may be
5995
   used to substitute zero for null when necessary.
5996
  </para>
5997

5998
 </sect1>
5999

6000 6001 6002 6003 6004 6005 6006 6007 6008 6009 6010 6011 6012 6013 6014 6015 6016 6017 6018 6019 6020 6021 6022 6023 6024 6025 6026 6027 6028 6029 6030 6031 6032 6033 6034 6035 6036 6037 6038 6039 6040 6041 6042 6043 6044 6045

 <sect1 id="functions-subquery">
  <title>Subquery Expressions</title>

  <indexterm>
   <primary>exists</primary>
  </indexterm>

  <indexterm>
   <primary>in</primary>
  </indexterm>

  <indexterm>
   <primary>not in</primary>
  </indexterm>

  <indexterm>
   <primary>any</primary>
  </indexterm>

  <indexterm>
   <primary>all</primary>
  </indexterm>

  <indexterm>
   <primary>some</primary>
  </indexterm>

  <indexterm>
   <primary>subqueries</primary>
  </indexterm>

  <para>
   This section describes the <acronym>SQL</acronym>-compliant subquery
   expressions available in <productname>PostgreSQL</productname>.
   All of the expression forms documented in this section return
   Boolean (true/false) results.
  </para>

  <bridgehead renderas="sect2">EXISTS</bridgehead>

<synopsis>
EXISTS ( <replaceable>subquery</replaceable> )
</synopsis>

  <para>
6046 6047
   The argument of <token>EXISTS</token> is an arbitrary SELECT statement,
   or <firstterm>subquery</firstterm>.  The
6048
   subquery is evaluated to determine whether it returns any rows.
6049 6050
   If it returns at least one row, the result of <token>EXISTS</token> is
   TRUE; if the subquery returns no rows, the result of <token>EXISTS</token> 
6051 6052 6053 6054 6055 6056 6057 6058 6059 6060 6061 6062 6063 6064 6065 6066 6067 6068 6069 6070 6071
   is FALSE.
  </para>

  <para>
   The subquery can refer to variables from the surrounding query,
   which will act as constants during any one evaluation of the subquery.
  </para>

  <para>
   The subquery will generally only be executed far enough to determine
   whether at least one row is returned, not all the way to completion.
   It is unwise to write a subquery that has any side-effects (such as
   calling sequence functions); whether the side-effects occur or not
   may be difficult to predict.
  </para>

  <para>
   Since the result depends only on whether any rows are returned,
   and not on the contents of those rows, the output list of the
   subquery is normally uninteresting.  A common coding convention is
   to write all EXISTS tests in the form
6072 6073
   <literal>EXISTS(SELECT 1 WHERE ...)</literal>.  There are exceptions to
   this rule however, such as subqueries that use <token>INTERSECT</token>.
6074 6075 6076 6077 6078 6079 6080 6081 6082 6083 6084 6085 6086 6087 6088
  </para>

  <para>
   This simple example is like an inner join on col2, but it produces at
   most one output row for each tab1 row, even if there are multiple matching
   tab2 rows:
<screen>
SELECT col1 FROM tab1
    WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</screen>
  </para>

   <bridgehead renderas="sect2">IN (scalar form)</bridgehead>

<synopsis>
6089
<replaceable>expression</replaceable> IN
6090
 <replaceable>value</replaceable><optional>, ...</optional>)
6091 6092 6093
</synopsis>

  <para>
6094
   The right-hand side of this form of <token>IN</token> is a parenthesized list
6095 6096 6097 6098 6099 6100 6101 6102 6103 6104 6105 6106 6107 6108
   of scalar expressions.  The result is TRUE if the left-hand expression's
   result is equal to any of the right-hand expressions.  This is a shorthand
   notation for

<synopsis>
<replaceable>expression</replaceable> = <replaceable>value1</replaceable>
OR
<replaceable>expression</replaceable> = <replaceable>value2</replaceable>
OR
...
</synopsis>

   Note that if the left-hand expression yields NULL, or if there are
   no equal right-hand values and at least one right-hand expression yields
6109
   NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
6110 6111 6112 6113 6114 6115
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <note>
  <para>
6116 6117
   This form of <token>IN</token> is not truly a subquery expression, but it
   seems best to document it in the same place as subquery <token>IN</token>.
6118 6119 6120 6121 6122 6123 6124 6125 6126 6127
  </para>
  </note>

   <bridgehead renderas="sect2">IN (subquery form)</bridgehead>

<synopsis>
<replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
6128
   The right-hand side of this form of <token>IN</token> is a parenthesized
6129 6130
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result.
6131
   The result of <token>IN</token> is TRUE if any equal subquery row is found.
6132 6133 6134 6135 6136 6137 6138
   The result is FALSE if no equal row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
   Note that if the left-hand expression yields NULL, or if there are
   no equal right-hand values and at least one right-hand row yields
6139
   NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
6140 6141 6142 6143 6144
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <para>
6145
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6146 6147 6148 6149
   be evaluated completely.
  </para>

<synopsis>
6150
(<replaceable>expression</replaceable>,
6151
<replaceable>expres</replaceable><optional>,</optional>)
6152
    IN (<replaceable>subquery</replaceable>)
6153 6154 6155
</synopsis>

  <para>
6156
   The right-hand side of this form of <token>IN</token> is a parenthesized
6157 6158 6159
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result.
6160
   The result of <token>IN</token> is TRUE if any equal subquery row is found.
6161 6162 6163 6164 6165
   The result is FALSE if no equal row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
6166
   As usual, null values in the expressions or subquery rows are combined per
6167
   the normal rules of SQL Boolean expressions.  Two rows are considered
6168 6169 6170 6171
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (NULL).
   If all the row results are either unequal or NULL, with at least one NULL,
6172
   then the result of <token>IN</token> is NULL.
6173 6174 6175 6176 6177
  </para>

   <bridgehead renderas="sect2">NOT IN (scalar form)</bridgehead>

<synopsis>
6178
<replaceable>expression</replaceable> NOT IN
6179
 <replaceable>value</replaceable><optional>, ...</optional>)
6180 6181 6182
</synopsis>

  <para>
6183
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized list
6184 6185 6186 6187 6188 6189 6190 6191 6192 6193 6194 6195 6196 6197
   of scalar expressions.  The result is TRUE if the left-hand expression's
   result is unequal to all of the right-hand expressions.  This is a shorthand
   notation for

<synopsis>
<replaceable>expression</replaceable> &lt;&gt; <replaceable>value1</replaceable>
AND
<replaceable>expression</replaceable> &lt;&gt; <replaceable>value2</replaceable>
AND
...
</synopsis>

   Note that if the left-hand expression yields NULL, or if there are
   no equal right-hand values and at least one right-hand expression yields
6198
   NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE
6199 6200 6201 6202 6203 6204 6205
   as one might naively expect.
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <tip>
  <para>
6206
   <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
6207
   cases.  However, null values are much more likely to trip up the novice when
6208
   working with <token>NOT IN</token> than when working with <token>IN</token>.
6209 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219
   It's best to express your condition positively if possible.
  </para>
  </tip>

   <bridgehead renderas="sect2">NOT IN (subquery form)</bridgehead>

<synopsis>
<replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
6220
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized
6221 6222
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result.
6223
   The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
6224 6225 6226 6227 6228 6229 6230
   are found (including the special case where the subquery returns no rows).
   The result is FALSE if any equal row is found.
  </para>

  <para>
   Note that if the left-hand expression yields NULL, or if there are
   no equal right-hand values and at least one right-hand row yields
6231
   NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE.
6232 6233 6234 6235 6236
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <para>
6237
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6238 6239 6240 6241
   be evaluated completely.
  </para>

<synopsis>
6242
(<replaceable>expression</replaceable>,
6243
<replaceable>expres</replaceable><optional>,</optional>)
6244
    NOT IN (<replaceable>subquery</replaceable>)
6245 6246 6247
</synopsis>

  <para>
6248
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized
6249 6250 6251
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result.
6252
   The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
6253 6254 6255 6256 6257
   are found (including the special case where the subquery returns no rows).
   The result is FALSE if any equal row is found.
  </para>

  <para>
6258
   As usual, null values in the expressions or subquery rows are combined per
6259
   the normal rules of SQL Boolean expressions.  Two rows are considered
6260 6261 6262 6263
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (NULL).
   If all the row results are either unequal or NULL, with at least one NULL,
6264
   then the result of <token>NOT IN</token> is NULL.
6265 6266 6267 6268 6269
  </para>

   <bridgehead renderas="sect2">ANY</bridgehead>

<synopsis>
6270
<replaceable>expression</replaceable>
6271
<replaceable>oper</replaceable> ANY (<replaceable>subquery</replaceable>)
6272
<replaceable>expression</replaceable>
6273
<replaceable>oper</replaceable> SOME (<replaceable>subquery</replaceable>)
6274 6275 6276
</synopsis>

  <para>
6277
   The right-hand side of this form of <token>ANY</token> is a parenthesized
6278 6279
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result using the
6280
   given <replaceable>operator</replaceable>, which must yield a Boolean
6281
   result.
6282
   The result of <token>ANY</token> is TRUE if any true result is obtained.
6283 6284 6285 6286 6287
   The result is FALSE if no true result is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
6288 6289
   <token>SOME</token> is a synonym for <token>ANY</token>.
   <token>IN</token> is equivalent to <literal>= ANY</literal>.
6290 6291 6292 6293
  </para>

  <para>
   Note that if there are no successes and at least one right-hand row yields
6294
   NULL for the operator's result, the result of the <token>ANY</token> construct
6295 6296 6297 6298 6299 6300
   will be NULL, not FALSE.
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <para>
6301
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6302 6303 6304 6305
   be evaluated completely.
  </para>

<synopsis>
6306
(<replaceable>expression</replaceable>,
6307
<replaceable>expres</replaceable><optional>,</optional>optiona<replaceable></replaceable> ANY (<replaceable>subquery</replaceable>)
6308
(<replaceable>expression</replaceable>,
6309
<replaceable>expres</replaceable><optional>,</optional>optiona<replaceable></replaceable> SOME (<replaceable>subquery</replaceable>)
6310 6311 6312
</synopsis>

  <para>
6313
   The right-hand side of this form of <token>ANY</token> is a parenthesized
6314 6315 6316 6317
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result,
   using the given <replaceable>operator</replaceable>.  Presently,
6318 6319 6320
   only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
   in row-wise <token>ANY</token> queries.
   The result of <token>ANY</token> is TRUE if any equal or unequal row is
6321 6322 6323 6324 6325 6326
   found, respectively.
   The result is FALSE if no such row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
6327
   As usual, null values in the expressions or subquery rows are combined per
6328
   the normal rules of SQL Boolean expressions.  Two rows are considered
6329 6330 6331
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (NULL).
6332
   If there is at least one NULL row result, then the result of <token>ANY</token>
6333 6334 6335 6336 6337 6338
   cannot be FALSE; it will be TRUE or NULL. 
  </para>

   <bridgehead renderas="sect2">ALL</bridgehead>

<synopsis>
6339
<replaceable>expression</replaceable>
6340
<replaceable>oper</replaceable> ALL (<replaceable>subquery</replaceable>)
6341 6342 6343
</synopsis>

  <para>
6344
   The right-hand side of this form of <token>ALL</token> is a parenthesized
6345 6346
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result using the
6347
   given <replaceable>operator</replaceable>, which must yield a Boolean
6348
   result.
6349
   The result of <token>ALL</token> is TRUE if all rows yield TRUE
6350 6351 6352 6353 6354
   (including the special case where the subquery returns no rows).
   The result is FALSE if any false result is found.
  </para>

  <para>
6355
   <token>NOT IN</token> is equivalent to <literal>&lt;&gt; ALL</literal>.
6356 6357 6358 6359
  </para>

  <para>
   Note that if there are no failures but at least one right-hand row yields
6360
   NULL for the operator's result, the result of the <token>ALL</token> construct
6361 6362 6363 6364 6365 6366
   will be NULL, not TRUE.
   This is in accordance with SQL's normal rules for Boolean combinations
   of NULL values.
  </para>

  <para>
6367
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
6368 6369 6370
   be evaluated completely.
  </para>

6371
   <synopsis>
6372
(<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
6373
   </synopsis>
6374 6375

  <para>
6376
   The right-hand side of this form of <token>ALL</token> is a parenthesized
6377 6378 6379 6380
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result,
   using the given <replaceable>operator</replaceable>.  Presently,
6381 6382 6383
   only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
   in row-wise <token>ALL</token> queries.
   The result of <token>ALL</token> is TRUE if all subquery rows are equal
6384 6385 6386 6387 6388 6389 6390
   or unequal, respectively (including the special
   case where the subquery returns no rows).
   The result is FALSE if any row is found to be unequal or equal,
   respectively.
  </para>

  <para>
6391
   As usual, null values in the expressions or subquery rows are combined per
6392
   the normal rules of SQL Boolean expressions.  Two rows are considered
6393 6394 6395
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (NULL).
6396
   If there is at least one NULL row result, then the result of <token>ALL</token>
6397 6398 6399 6400 6401
   cannot be TRUE; it will be FALSE or NULL. 
  </para>

   <bridgehead renderas="sect2">Row-wise comparison</bridgehead>

6402
   <synopsis>
6403
(<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
6404 6405
(<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <replaceable>expression</replaceable><optional>, ...</optional>)
   </synopsis>
6406 6407 6408 6409 6410 6411 6412 6413 6414

  <para>
   The left-hand side is a list of scalar expressions.  The right-hand side
   can be either a list of scalar expressions of the same length, or a
   parenthesized subquery, which must return exactly as many columns as there
   are expressions on the left-hand side.  Furthermore, the subquery cannot
   return more than one row.  (If it returns zero rows, the result is taken to
   be NULL.)  The left-hand side is evaluated and compared row-wise to the
   single subquery result row, or to the right-hand expression list.
6415
   Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
6416 6417 6418 6419 6420
   in row-wise comparisons.
   The result is TRUE if the two rows are equal or unequal, respectively.
  </para>

  <para>
6421
   As usual, null values in the expressions or subquery rows are combined per
6422
   the normal rules of SQL Boolean expressions.  Two rows are considered
6423 6424 6425 6426 6427 6428 6429
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of the row comparison is unknown (NULL).
  </para>

 </sect1>

6430
</chapter>
6431

6432 6433
<!-- Keep this comment at the end of the file
Local variables:
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
6434
mode:sgml
6435
sgml-omittag:nil
6436 6437 6438 6439 6440 6441 6442 6443
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
6444
sgml-local-catalogs:("/usr/lib/sgml/catalog")
6445 6446 6447
sgml-local-ecat-files:nil
End:
-->