1<!-- doc/src/sgml/syntax.sgml --> 2 3<chapter id="sql-syntax"> 4 <title>SQL Syntax</title> 5 6 <indexterm zone="sql-syntax"> 7 <primary>syntax</primary> 8 <secondary>SQL</secondary> 9 </indexterm> 10 11 <para> 12 This chapter describes the syntax of SQL. It forms the foundation 13 for understanding the following chapters which will go into detail 14 about how SQL commands are applied to define and modify data. 15 </para> 16 17 <para> 18 We also advise users who are already familiar with SQL to read this 19 chapter carefully because it contains several rules and concepts that 20 are implemented inconsistently among SQL databases or that are 21 specific to <productname>PostgreSQL</productname>. 22 </para> 23 24 <sect1 id="sql-syntax-lexical"> 25 <title>Lexical Structure</title> 26 27 <indexterm> 28 <primary>token</primary> 29 </indexterm> 30 31 <para> 32 SQL input consists of a sequence of 33 <firstterm>commands</firstterm>. A command is composed of a 34 sequence of <firstterm>tokens</firstterm>, terminated by a 35 semicolon (<quote>;</quote>). The end of the input stream also 36 terminates a command. Which tokens are valid depends on the syntax 37 of the particular command. 38 </para> 39 40 <para> 41 A token can be a <firstterm>key word</firstterm>, an 42 <firstterm>identifier</firstterm>, a <firstterm>quoted 43 identifier</firstterm>, a <firstterm>literal</firstterm> (or 44 constant), or a special character symbol. Tokens are normally 45 separated by whitespace (space, tab, newline), but need not be if 46 there is no ambiguity (which is generally only the case if a 47 special character is adjacent to some other token type). 48 </para> 49 50 <para> 51 For example, the following is (syntactically) valid SQL input: 52<programlisting> 53SELECT * FROM MY_TABLE; 54UPDATE MY_TABLE SET A = 5; 55INSERT INTO MY_TABLE VALUES (3, 'hi there'); 56</programlisting> 57 This is a sequence of three commands, one per line (although this 58 is not required; more than one command can be on a line, and 59 commands can usefully be split across lines). 60 </para> 61 62 <para> 63 Additionally, <firstterm>comments</firstterm> can occur in SQL 64 input. They are not tokens, they are effectively equivalent to 65 whitespace. 66 </para> 67 68 <para> 69 The SQL syntax is not very consistent regarding what tokens 70 identify commands and which are operands or parameters. The first 71 few tokens are generally the command name, so in the above example 72 we would usually speak of a <quote>SELECT</quote>, an 73 <quote>UPDATE</quote>, and an <quote>INSERT</quote> command. But 74 for instance the <command>UPDATE</command> command always requires 75 a <token>SET</token> token to appear in a certain position, and 76 this particular variation of <command>INSERT</command> also 77 requires a <token>VALUES</token> in order to be complete. The 78 precise syntax rules for each command are described in <xref linkend="reference"/>. 79 </para> 80 81 <sect2 id="sql-syntax-identifiers"> 82 <title>Identifiers and Key Words</title> 83 84 <indexterm zone="sql-syntax-identifiers"> 85 <primary>identifier</primary> 86 <secondary>syntax of</secondary> 87 </indexterm> 88 89 <indexterm zone="sql-syntax-identifiers"> 90 <primary>name</primary> 91 <secondary>syntax of</secondary> 92 </indexterm> 93 94 <indexterm zone="sql-syntax-identifiers"> 95 <primary>key word</primary> 96 <secondary>syntax of</secondary> 97 </indexterm> 98 99 <para> 100 Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or 101 <token>VALUES</token> in the example above are examples of 102 <firstterm>key words</firstterm>, that is, words that have a fixed 103 meaning in the SQL language. The tokens <token>MY_TABLE</token> 104 and <token>A</token> are examples of 105 <firstterm>identifiers</firstterm>. They identify names of 106 tables, columns, or other database objects, depending on the 107 command they are used in. Therefore they are sometimes simply 108 called <quote>names</quote>. Key words and identifiers have the 109 same lexical structure, meaning that one cannot know whether a 110 token is an identifier or a key word without knowing the language. 111 A complete list of key words can be found in <xref 112 linkend="sql-keywords-appendix"/>. 113 </para> 114 115 <para> 116 SQL identifiers and key words must begin with a letter 117 (<literal>a</literal>-<literal>z</literal>, but also letters with 118 diacritical marks and non-Latin letters) or an underscore 119 (<literal>_</literal>). Subsequent characters in an identifier or 120 key word can be letters, underscores, digits 121 (<literal>0</literal>-<literal>9</literal>), or dollar signs 122 (<literal>$</literal>). Note that dollar signs are not allowed in identifiers 123 according to the letter of the SQL standard, so their use might render 124 applications less portable. 125 The SQL standard will not define a key word that contains 126 digits or starts or ends with an underscore, so identifiers of this 127 form are safe against possible conflict with future extensions of the 128 standard. 129 </para> 130 131 <para> 132 <indexterm><primary>identifier</primary><secondary>length</secondary></indexterm> 133 The system uses no more than <symbol>NAMEDATALEN</symbol>-1 134 bytes of an identifier; longer names can be written in 135 commands, but they will be truncated. By default, 136 <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier 137 length is 63 bytes. If this limit is problematic, it can be raised by 138 changing the <symbol>NAMEDATALEN</symbol> constant in 139 <filename>src/include/pg_config_manual.h</filename>. 140 </para> 141 142 <para> 143 <indexterm> 144 <primary>case sensitivity</primary> 145 <secondary>of SQL commands</secondary> 146 </indexterm> 147 Key words and unquoted identifiers are case insensitive. Therefore: 148<programlisting> 149UPDATE MY_TABLE SET A = 5; 150</programlisting> 151 can equivalently be written as: 152<programlisting> 153uPDaTE my_TabLE SeT a = 5; 154</programlisting> 155 A convention often used is to write key words in upper 156 case and names in lower case, e.g.: 157<programlisting> 158UPDATE my_table SET a = 5; 159</programlisting> 160 </para> 161 162 <para> 163 <indexterm> 164 <primary>quotation marks</primary> 165 <secondary>and identifiers</secondary> 166 </indexterm> 167 There is a second kind of identifier: the <firstterm>delimited 168 identifier</firstterm> or <firstterm>quoted 169 identifier</firstterm>. It is formed by enclosing an arbitrary 170 sequence of characters in double-quotes 171 (<literal>"</literal>). <!-- " font-lock mania --> A delimited 172 identifier is always an identifier, never a key word. So 173 <literal>"select"</literal> could be used to refer to a column or 174 table named <quote>select</quote>, whereas an unquoted 175 <literal>select</literal> would be taken as a key word and 176 would therefore provoke a parse error when used where a table or 177 column name is expected. The example can be written with quoted 178 identifiers like this: 179<programlisting> 180UPDATE "my_table" SET "a" = 5; 181</programlisting> 182 </para> 183 184 <para> 185 Quoted identifiers can contain any character, except the character 186 with code zero. (To include a double quote, write two double quotes.) 187 This allows constructing table or column names that would 188 otherwise not be possible, such as ones containing spaces or 189 ampersands. The length limitation still applies. 190 </para> 191 192 <para> 193 Quoting an identifier also makes it case-sensitive, whereas 194 unquoted names are always folded to lower case. For example, the 195 identifiers <literal>FOO</literal>, <literal>foo</literal>, and 196 <literal>"foo"</literal> are considered the same by 197 <productname>PostgreSQL</productname>, but 198 <literal>"Foo"</literal> and <literal>"FOO"</literal> are 199 different from these three and each other. (The folding of 200 unquoted names to lower case in <productname>PostgreSQL</productname> is 201 incompatible with the SQL standard, which says that unquoted names 202 should be folded to upper case. Thus, <literal>foo</literal> 203 should be equivalent to <literal>"FOO"</literal> not 204 <literal>"foo"</literal> according to the standard. If you want 205 to write portable applications you are advised to always quote a 206 particular name or never quote it.) 207 </para> 208 209 <indexterm> 210 <primary>Unicode escape</primary> 211 <secondary>in identifiers</secondary> 212 </indexterm> 213 214 <para> 215 A variant of quoted 216 identifiers allows including escaped Unicode characters identified 217 by their code points. This variant starts 218 with <literal>U&</literal> (upper or lower case U followed by 219 ampersand) immediately before the opening double quote, without 220 any spaces in between, for example <literal>U&"foo"</literal>. 221 (Note that this creates an ambiguity with the 222 operator <literal>&</literal>. Use spaces around the operator to 223 avoid this problem.) Inside the quotes, Unicode characters can be 224 specified in escaped form by writing a backslash followed by the 225 four-digit hexadecimal code point number or alternatively a 226 backslash followed by a plus sign followed by a six-digit 227 hexadecimal code point number. For example, the 228 identifier <literal>"data"</literal> could be written as 229<programlisting> 230U&"d\0061t\+000061" 231</programlisting> 232 The following less trivial example writes the Russian 233 word <quote>slon</quote> (elephant) in Cyrillic letters: 234<programlisting> 235U&"\0441\043B\043E\043D" 236</programlisting> 237 </para> 238 239 <para> 240 If a different escape character than backslash is desired, it can 241 be specified using 242 the <literal>UESCAPE</literal><indexterm><primary>UESCAPE</primary></indexterm> 243 clause after the string, for example: 244<programlisting> 245U&"d!0061t!+000061" UESCAPE '!' 246</programlisting> 247 The escape character can be any single character other than a 248 hexadecimal digit, the plus sign, a single quote, a double quote, 249 or a whitespace character. Note that the escape character is 250 written in single quotes, not double quotes, 251 after <literal>UESCAPE</literal>. 252 </para> 253 254 <para> 255 To include the escape character in the identifier literally, write 256 it twice. 257 </para> 258 259 <para> 260 Either the 4-digit or the 6-digit escape form can be used to 261 specify UTF-16 surrogate pairs to compose characters with code 262 points larger than U+FFFF, although the availability of the 263 6-digit form technically makes this unnecessary. (Surrogate 264 pairs are not stored directly, but are combined into a single 265 code point.) 266 </para> 267 268 <para> 269 If the server encoding is not UTF-8, the Unicode code point identified 270 by one of these escape sequences is converted to the actual server 271 encoding; an error is reported if that's not possible. 272 </para> 273 </sect2> 274 275 276 <sect2 id="sql-syntax-constants"> 277 <title>Constants</title> 278 279 <indexterm zone="sql-syntax-constants"> 280 <primary>constant</primary> 281 </indexterm> 282 283 <para> 284 There are three kinds of <firstterm>implicitly-typed 285 constants</firstterm> in <productname>PostgreSQL</productname>: 286 strings, bit strings, and numbers. 287 Constants can also be specified with explicit types, which can 288 enable more accurate representation and more efficient handling by 289 the system. These alternatives are discussed in the following 290 subsections. 291 </para> 292 293 <sect3 id="sql-syntax-strings"> 294 <title>String Constants</title> 295 296 <indexterm zone="sql-syntax-strings"> 297 <primary>character string</primary> 298 <secondary>constant</secondary> 299 </indexterm> 300 301 <para> 302 <indexterm> 303 <primary>quotation marks</primary> 304 <secondary>escaping</secondary> 305 </indexterm> 306 A string constant in SQL is an arbitrary sequence of characters 307 bounded by single quotes (<literal>'</literal>), for example 308 <literal>'This is a string'</literal>. To include 309 a single-quote character within a string constant, 310 write two adjacent single quotes, e.g., 311 <literal>'Dianne''s horse'</literal>. 312 Note that this is <emphasis>not</emphasis> the same as a double-quote 313 character (<literal>"</literal>). <!-- font-lock sanity: " --> 314 </para> 315 316 <para> 317 Two string constants that are only separated by whitespace 318 <emphasis>with at least one newline</emphasis> are concatenated 319 and effectively treated as if the string had been written as one 320 constant. For example: 321<programlisting> 322SELECT 'foo' 323'bar'; 324</programlisting> 325 is equivalent to: 326<programlisting> 327SELECT 'foobar'; 328</programlisting> 329 but: 330<programlisting> 331SELECT 'foo' 'bar'; 332</programlisting> 333 is not valid syntax. (This slightly bizarre behavior is specified 334 by <acronym>SQL</acronym>; <productname>PostgreSQL</productname> is 335 following the standard.) 336 </para> 337 </sect3> 338 339 <sect3 id="sql-syntax-strings-escape"> 340 <title>String Constants with C-Style Escapes</title> 341 342 <indexterm zone="sql-syntax-strings-escape"> 343 <primary>escape string syntax</primary> 344 </indexterm> 345 <indexterm zone="sql-syntax-strings-escape"> 346 <primary>backslash escapes</primary> 347 </indexterm> 348 349 <para> 350 <productname>PostgreSQL</productname> also accepts <quote>escape</quote> 351 string constants, which are an extension to the SQL standard. 352 An escape string constant is specified by writing the letter 353 <literal>E</literal> (upper or lower case) just before the opening single 354 quote, e.g., <literal>E'foo'</literal>. (When continuing an escape string 355 constant across lines, write <literal>E</literal> only before the first opening 356 quote.) 357 Within an escape string, a backslash character (<literal>\</literal>) begins a 358 C-like <firstterm>backslash escape</firstterm> sequence, in which the combination 359 of backslash and following character(s) represent a special byte 360 value, as shown in <xref linkend="sql-backslash-table"/>. 361 </para> 362 363 <table id="sql-backslash-table"> 364 <title>Backslash Escape Sequences</title> 365 <tgroup cols="2"> 366 <thead> 367 <row> 368 <entry>Backslash Escape Sequence</entry> 369 <entry>Interpretation</entry> 370 </row> 371 </thead> 372 373 <tbody> 374 <row> 375 <entry><literal>\b</literal></entry> 376 <entry>backspace</entry> 377 </row> 378 <row> 379 <entry><literal>\f</literal></entry> 380 <entry>form feed</entry> 381 </row> 382 <row> 383 <entry><literal>\n</literal></entry> 384 <entry>newline</entry> 385 </row> 386 <row> 387 <entry><literal>\r</literal></entry> 388 <entry>carriage return</entry> 389 </row> 390 <row> 391 <entry><literal>\t</literal></entry> 392 <entry>tab</entry> 393 </row> 394 <row> 395 <entry> 396 <literal>\<replaceable>o</replaceable></literal>, 397 <literal>\<replaceable>oo</replaceable></literal>, 398 <literal>\<replaceable>ooo</replaceable></literal> 399 (<replaceable>o</replaceable> = 0–7) 400 </entry> 401 <entry>octal byte value</entry> 402 </row> 403 <row> 404 <entry> 405 <literal>\x<replaceable>h</replaceable></literal>, 406 <literal>\x<replaceable>hh</replaceable></literal> 407 (<replaceable>h</replaceable> = 0–9, A–F) 408 </entry> 409 <entry>hexadecimal byte value</entry> 410 </row> 411 <row> 412 <entry> 413 <literal>\u<replaceable>xxxx</replaceable></literal>, 414 <literal>\U<replaceable>xxxxxxxx</replaceable></literal> 415 (<replaceable>x</replaceable> = 0–9, A–F) 416 </entry> 417 <entry>16 or 32-bit hexadecimal Unicode character value</entry> 418 </row> 419 </tbody> 420 </tgroup> 421 </table> 422 423 <para> 424 Any other 425 character following a backslash is taken literally. Thus, to 426 include a backslash character, write two backslashes (<literal>\\</literal>). 427 Also, a single quote can be included in an escape string by writing 428 <literal>\'</literal>, in addition to the normal way of <literal>''</literal>. 429 </para> 430 431 <para> 432 It is your responsibility that the byte sequences you create, 433 especially when using the octal or hexadecimal escapes, compose 434 valid characters in the server character set encoding. 435 A useful alternative is to use Unicode escapes or the 436 alternative Unicode escape syntax, explained 437 in <xref linkend="sql-syntax-strings-uescape"/>; then the server 438 will check that the character conversion is possible. 439 </para> 440 441 <caution> 442 <para> 443 If the configuration parameter 444 <xref linkend="guc-standard-conforming-strings"/> is <literal>off</literal>, 445 then <productname>PostgreSQL</productname> recognizes backslash escapes 446 in both regular and escape string constants. However, as of 447 <productname>PostgreSQL</productname> 9.1, the default is <literal>on</literal>, meaning 448 that backslash escapes are recognized only in escape string constants. 449 This behavior is more standards-compliant, but might break applications 450 which rely on the historical behavior, where backslash escapes 451 were always recognized. As a workaround, you can set this parameter 452 to <literal>off</literal>, but it is better to migrate away from using backslash 453 escapes. If you need to use a backslash escape to represent a special 454 character, write the string constant with an <literal>E</literal>. 455 </para> 456 457 <para> 458 In addition to <varname>standard_conforming_strings</varname>, the configuration 459 parameters <xref linkend="guc-escape-string-warning"/> and 460 <xref linkend="guc-backslash-quote"/> govern treatment of backslashes 461 in string constants. 462 </para> 463 </caution> 464 465 <para> 466 The character with the code zero cannot be in a string constant. 467 </para> 468 </sect3> 469 470 <sect3 id="sql-syntax-strings-uescape"> 471 <title>String Constants with Unicode Escapes</title> 472 473 <indexterm zone="sql-syntax-strings-uescape"> 474 <primary>Unicode escape</primary> 475 <secondary>in string constants</secondary> 476 </indexterm> 477 478 <para> 479 <productname>PostgreSQL</productname> also supports another type 480 of escape syntax for strings that allows specifying arbitrary 481 Unicode characters by code point. A Unicode escape string 482 constant starts with <literal>U&</literal> (upper or lower case 483 letter U followed by ampersand) immediately before the opening 484 quote, without any spaces in between, for 485 example <literal>U&'foo'</literal>. (Note that this creates an 486 ambiguity with the operator <literal>&</literal>. Use spaces 487 around the operator to avoid this problem.) Inside the quotes, 488 Unicode characters can be specified in escaped form by writing a 489 backslash followed by the four-digit hexadecimal code point 490 number or alternatively a backslash followed by a plus sign 491 followed by a six-digit hexadecimal code point number. For 492 example, the string <literal>'data'</literal> could be written as 493<programlisting> 494U&'d\0061t\+000061' 495</programlisting> 496 The following less trivial example writes the Russian 497 word <quote>slon</quote> (elephant) in Cyrillic letters: 498<programlisting> 499U&'\0441\043B\043E\043D' 500</programlisting> 501 </para> 502 503 <para> 504 If a different escape character than backslash is desired, it can 505 be specified using 506 the <literal>UESCAPE</literal><indexterm><primary>UESCAPE</primary></indexterm> 507 clause after the string, for example: 508<programlisting> 509U&'d!0061t!+000061' UESCAPE '!' 510</programlisting> 511 The escape character can be any single character other than a 512 hexadecimal digit, the plus sign, a single quote, a double quote, 513 or a whitespace character. 514 </para> 515 516 <para> 517 To include the escape character in the string literally, write 518 it twice. 519 </para> 520 521 <para> 522 Either the 4-digit or the 6-digit escape form can be used to 523 specify UTF-16 surrogate pairs to compose characters with code 524 points larger than U+FFFF, although the availability of the 525 6-digit form technically makes this unnecessary. (Surrogate 526 pairs are not stored directly, but are combined into a single 527 code point.) 528 </para> 529 530 <para> 531 If the server encoding is not UTF-8, the Unicode code point identified 532 by one of these escape sequences is converted to the actual server 533 encoding; an error is reported if that's not possible. 534 </para> 535 536 <para> 537 Also, the Unicode escape syntax for string constants only works 538 when the configuration 539 parameter <xref linkend="guc-standard-conforming-strings"/> is 540 turned on. This is because otherwise this syntax could confuse 541 clients that parse the SQL statements to the point that it could 542 lead to SQL injections and similar security issues. If the 543 parameter is set to off, this syntax will be rejected with an 544 error message. 545 </para> 546 </sect3> 547 548 <sect3 id="sql-syntax-dollar-quoting"> 549 <title>Dollar-Quoted String Constants</title> 550 551 <indexterm> 552 <primary>dollar quoting</primary> 553 </indexterm> 554 555 <para> 556 While the standard syntax for specifying string constants is usually 557 convenient, it can be difficult to understand when the desired string 558 contains many single quotes or backslashes, since each of those must 559 be doubled. To allow more readable queries in such situations, 560 <productname>PostgreSQL</productname> provides another way, called 561 <quote>dollar quoting</quote>, to write string constants. 562 A dollar-quoted string constant 563 consists of a dollar sign (<literal>$</literal>), an optional 564 <quote>tag</quote> of zero or more characters, another dollar 565 sign, an arbitrary sequence of characters that makes up the 566 string content, a dollar sign, the same tag that began this 567 dollar quote, and a dollar sign. For example, here are two 568 different ways to specify the string <quote>Dianne's horse</quote> 569 using dollar quoting: 570<programlisting> 571$$Dianne's horse$$ 572$SomeTag$Dianne's horse$SomeTag$ 573</programlisting> 574 Notice that inside the dollar-quoted string, single quotes can be 575 used without needing to be escaped. Indeed, no characters inside 576 a dollar-quoted string are ever escaped: the string content is always 577 written literally. Backslashes are not special, and neither are 578 dollar signs, unless they are part of a sequence matching the opening 579 tag. 580 </para> 581 582 <para> 583 It is possible to nest dollar-quoted string constants by choosing 584 different tags at each nesting level. This is most commonly used in 585 writing function definitions. For example: 586<programlisting> 587$function$ 588BEGIN 589 RETURN ($1 ~ $q$[\t\r\n\v\\]$q$); 590END; 591$function$ 592</programlisting> 593 Here, the sequence <literal>$q$[\t\r\n\v\\]$q$</literal> represents a 594 dollar-quoted literal string <literal>[\t\r\n\v\\]</literal>, which will 595 be recognized when the function body is executed by 596 <productname>PostgreSQL</productname>. But since the sequence does not match 597 the outer dollar quoting delimiter <literal>$function$</literal>, it is 598 just some more characters within the constant so far as the outer 599 string is concerned. 600 </para> 601 602 <para> 603 The tag, if any, of a dollar-quoted string follows the same rules 604 as an unquoted identifier, except that it cannot contain a dollar sign. 605 Tags are case sensitive, so <literal>$tag$String content$tag$</literal> 606 is correct, but <literal>$TAG$String content$tag$</literal> is not. 607 </para> 608 609 <para> 610 A dollar-quoted string that follows a keyword or identifier must 611 be separated from it by whitespace; otherwise the dollar quoting 612 delimiter would be taken as part of the preceding identifier. 613 </para> 614 615 <para> 616 Dollar quoting is not part of the SQL standard, but it is often a more 617 convenient way to write complicated string literals than the 618 standard-compliant single quote syntax. It is particularly useful when 619 representing string constants inside other constants, as is often needed 620 in procedural function definitions. With single-quote syntax, each 621 backslash in the above example would have to be written as four 622 backslashes, which would be reduced to two backslashes in parsing the 623 original string constant, and then to one when the inner string constant 624 is re-parsed during function execution. 625 </para> 626 </sect3> 627 628 <sect3 id="sql-syntax-bit-strings"> 629 <title>Bit-String Constants</title> 630 631 <indexterm zone="sql-syntax-bit-strings"> 632 <primary>bit string</primary> 633 <secondary>constant</secondary> 634 </indexterm> 635 636 <para> 637 Bit-string constants look like regular string constants with a 638 <literal>B</literal> (upper or lower case) immediately before the 639 opening quote (no intervening whitespace), e.g., 640 <literal>B'1001'</literal>. The only characters allowed within 641 bit-string constants are <literal>0</literal> and 642 <literal>1</literal>. 643 </para> 644 645 <para> 646 Alternatively, bit-string constants can be specified in hexadecimal 647 notation, using a leading <literal>X</literal> (upper or lower case), 648 e.g., <literal>X'1FF'</literal>. This notation is equivalent to 649 a bit-string constant with four binary digits for each hexadecimal digit. 650 </para> 651 652 <para> 653 Both forms of bit-string constant can be continued 654 across lines in the same way as regular string constants. 655 Dollar quoting cannot be used in a bit-string constant. 656 </para> 657 </sect3> 658 659 <sect3 id="sql-syntax-constants-numeric"> 660 <title>Numeric Constants</title> 661 662 <indexterm> 663 <primary>number</primary> 664 <secondary>constant</secondary> 665 </indexterm> 666 667 <para> 668 Numeric constants are accepted in these general forms: 669<synopsis> 670<replaceable>digits</replaceable> 671<replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional> 672<optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional> 673<replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable> 674</synopsis> 675 where <replaceable>digits</replaceable> is one or more decimal 676 digits (0 through 9). At least one digit must be before or after the 677 decimal point, if one is used. At least one digit must follow the 678 exponent marker (<literal>e</literal>), if one is present. 679 There cannot be any spaces or other characters embedded in the 680 constant. Note that any leading plus or minus sign is not actually 681 considered part of the constant; it is an operator applied to the 682 constant. 683 </para> 684 685 <para> 686 These are some examples of valid numeric constants: 687<literallayout> 68842 6893.5 6904. 691.001 6925e2 6931.925e-3 694</literallayout> 695 </para> 696 697 <para> 698 <indexterm><primary>integer</primary></indexterm> 699 <indexterm><primary>bigint</primary></indexterm> 700 <indexterm><primary>numeric</primary></indexterm> 701 A numeric constant that contains neither a decimal point nor an 702 exponent is initially presumed to be type <type>integer</type> if its 703 value fits in type <type>integer</type> (32 bits); otherwise it is 704 presumed to be type <type>bigint</type> if its 705 value fits in type <type>bigint</type> (64 bits); otherwise it is 706 taken to be type <type>numeric</type>. Constants that contain decimal 707 points and/or exponents are always initially presumed to be type 708 <type>numeric</type>. 709 </para> 710 711 <para> 712 The initially assigned data type of a numeric constant is just a 713 starting point for the type resolution algorithms. In most cases 714 the constant will be automatically coerced to the most 715 appropriate type depending on context. When necessary, you can 716 force a numeric value to be interpreted as a specific data type 717 by casting it.<indexterm><primary>type cast</primary></indexterm> 718 For example, you can force a numeric value to be treated as type 719 <type>real</type> (<type>float4</type>) by writing: 720 721<programlisting> 722REAL '1.23' -- string style 7231.23::REAL -- PostgreSQL (historical) style 724</programlisting> 725 726 These are actually just special cases of the general casting 727 notations discussed next. 728 </para> 729 </sect3> 730 731 <sect3 id="sql-syntax-constants-generic"> 732 <title>Constants of Other Types</title> 733 734 <indexterm> 735 <primary>data type</primary> 736 <secondary>constant</secondary> 737 </indexterm> 738 739 <para> 740 A constant of an <emphasis>arbitrary</emphasis> type can be 741 entered using any one of the following notations: 742<synopsis> 743<replaceable>type</replaceable> '<replaceable>string</replaceable>' 744'<replaceable>string</replaceable>'::<replaceable>type</replaceable> 745CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> ) 746</synopsis> 747 The string constant's text is passed to the input conversion 748 routine for the type called <replaceable>type</replaceable>. The 749 result is a constant of the indicated type. The explicit type 750 cast can be omitted if there is no ambiguity as to the type the 751 constant must be (for example, when it is assigned directly to a 752 table column), in which case it is automatically coerced. 753 </para> 754 755 <para> 756 The string constant can be written using either regular SQL 757 notation or dollar-quoting. 758 </para> 759 760 <para> 761 It is also possible to specify a type coercion using a function-like 762 syntax: 763<synopsis> 764<replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' ) 765</synopsis> 766 but not all type names can be used in this way; see <xref 767 linkend="sql-syntax-type-casts"/> for details. 768 </para> 769 770 <para> 771 The <literal>::</literal>, <literal>CAST()</literal>, and 772 function-call syntaxes can also be used to specify run-time type 773 conversions of arbitrary expressions, as discussed in <xref 774 linkend="sql-syntax-type-casts"/>. To avoid syntactic ambiguity, the 775 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal> 776 syntax can only be used to specify the type of a simple literal constant. 777 Another restriction on the 778 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal> 779 syntax is that it does not work for array types; use <literal>::</literal> 780 or <literal>CAST()</literal> to specify the type of an array constant. 781 </para> 782 783 <para> 784 The <literal>CAST()</literal> syntax conforms to SQL. The 785 <literal><replaceable>type</replaceable> '<replaceable>string</replaceable>'</literal> 786 syntax is a generalization of the standard: SQL specifies this syntax only 787 for a few data types, but <productname>PostgreSQL</productname> allows it 788 for all types. The syntax with 789 <literal>::</literal> is historical <productname>PostgreSQL</productname> 790 usage, as is the function-call syntax. 791 </para> 792 </sect3> 793 </sect2> 794 795 <sect2 id="sql-syntax-operators"> 796 <title>Operators</title> 797 798 <indexterm zone="sql-syntax-operators"> 799 <primary>operator</primary> 800 <secondary>syntax</secondary> 801 </indexterm> 802 803 <para> 804 An operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1 805 (63 by default) characters from the following list: 806<literallayout> 807+ - * / < > = ~ ! @ # % ^ & | ` ? 808</literallayout> 809 810 There are a few restrictions on operator names, however: 811 <itemizedlist> 812 <listitem> 813 <para> 814 <literal>--</literal> and <literal>/*</literal> cannot appear 815 anywhere in an operator name, since they will be taken as the 816 start of a comment. 817 </para> 818 </listitem> 819 820 <listitem> 821 <para> 822 A multiple-character operator name cannot end in <literal>+</literal> or <literal>-</literal>, 823 unless the name also contains at least one of these characters: 824<literallayout> 825~ ! @ # % ^ & | ` ? 826</literallayout> 827 For example, <literal>@-</literal> is an allowed operator name, 828 but <literal>*-</literal> is not. This restriction allows 829 <productname>PostgreSQL</productname> to parse SQL-compliant 830 queries without requiring spaces between tokens. 831 </para> 832 </listitem> 833 </itemizedlist> 834 </para> 835 836 <para> 837 When working with non-SQL-standard operator names, you will usually 838 need to separate adjacent operators with spaces to avoid ambiguity. 839 For example, if you have defined a left unary operator named <literal>@</literal>, 840 you cannot write <literal>X*@Y</literal>; you must write 841 <literal>X* @Y</literal> to ensure that 842 <productname>PostgreSQL</productname> reads it as two operator names 843 not one. 844 </para> 845 </sect2> 846 847 <sect2 id="sql-syntax-special-chars"> 848 <title>Special Characters</title> 849 850 <para> 851 Some characters that are not alphanumeric have a special meaning 852 that is different from being an operator. Details on the usage can 853 be found at the location where the respective syntax element is 854 described. This section only exists to advise the existence and 855 summarize the purposes of these characters. 856 857 <itemizedlist> 858 <listitem> 859 <para> 860 A dollar sign (<literal>$</literal>) followed by digits is used 861 to represent a positional parameter in the body of a function 862 definition or a prepared statement. In other contexts the 863 dollar sign can be part of an identifier or a dollar-quoted string 864 constant. 865 </para> 866 </listitem> 867 868 <listitem> 869 <para> 870 Parentheses (<literal>()</literal>) have their usual meaning to 871 group expressions and enforce precedence. In some cases 872 parentheses are required as part of the fixed syntax of a 873 particular SQL command. 874 </para> 875 </listitem> 876 877 <listitem> 878 <para> 879 Brackets (<literal>[]</literal>) are used to select the elements 880 of an array. See <xref linkend="arrays"/> for more information 881 on arrays. 882 </para> 883 </listitem> 884 885 <listitem> 886 <para> 887 Commas (<literal>,</literal>) are used in some syntactical 888 constructs to separate the elements of a list. 889 </para> 890 </listitem> 891 892 <listitem> 893 <para> 894 The semicolon (<literal>;</literal>) terminates an SQL command. 895 It cannot appear anywhere within a command, except within a 896 string constant or quoted identifier. 897 </para> 898 </listitem> 899 900 <listitem> 901 <para> 902 The colon (<literal>:</literal>) is used to select 903 <quote>slices</quote> from arrays. (See <xref 904 linkend="arrays"/>.) In certain SQL dialects (such as Embedded 905 SQL), the colon is used to prefix variable names. 906 </para> 907 </listitem> 908 909 <listitem> 910 <para> 911 The asterisk (<literal>*</literal>) is used in some contexts to denote 912 all the fields of a table row or composite value. It also 913 has a special meaning when used as the argument of an 914 aggregate function, namely that the aggregate does not require 915 any explicit parameter. 916 </para> 917 </listitem> 918 919 <listitem> 920 <para> 921 The period (<literal>.</literal>) is used in numeric 922 constants, and to separate schema, table, and column names. 923 </para> 924 </listitem> 925 </itemizedlist> 926 927 </para> 928 </sect2> 929 930 <sect2 id="sql-syntax-comments"> 931 <title>Comments</title> 932 933 <indexterm zone="sql-syntax-comments"> 934 <primary>comment</primary> 935 <secondary sortas="SQL">in SQL</secondary> 936 </indexterm> 937 938 <para> 939 A comment is a sequence of characters beginning with 940 double dashes and extending to the end of the line, e.g.: 941<programlisting> 942-- This is a standard SQL comment 943</programlisting> 944 </para> 945 946 <para> 947 Alternatively, C-style block comments can be used: 948<programlisting> 949/* multiline comment 950 * with nesting: /* nested block comment */ 951 */ 952</programlisting> 953 where the comment begins with <literal>/*</literal> and extends to 954 the matching occurrence of <literal>*/</literal>. These block 955 comments nest, as specified in the SQL standard but unlike C, so that one can 956 comment out larger blocks of code that might contain existing block 957 comments. 958 </para> 959 960 <para> 961 A comment is removed from the input stream before further syntax 962 analysis and is effectively replaced by whitespace. 963 </para> 964 </sect2> 965 966 <sect2 id="sql-precedence"> 967 <title>Operator Precedence</title> 968 969 <indexterm zone="sql-precedence"> 970 <primary>operator</primary> 971 <secondary>precedence</secondary> 972 </indexterm> 973 974 <para> 975 <xref linkend="sql-precedence-table"/> shows the precedence and 976 associativity of the operators in <productname>PostgreSQL</productname>. 977 Most operators have the same precedence and are left-associative. 978 The precedence and associativity of the operators is hard-wired 979 into the parser. 980 Add parentheses if you want an expression with multiple operators 981 to be parsed in some other way than what the precedence rules imply. 982 </para> 983 984 <table id="sql-precedence-table"> 985 <title>Operator Precedence (highest to lowest)</title> 986 987 <tgroup cols="3"> 988 <colspec colname="col1" colwidth="2*"/> 989 <colspec colname="col2" colwidth="1*"/> 990 <colspec colname="col3" colwidth="2*"/> 991 <thead> 992 <row> 993 <entry>Operator/Element</entry> 994 <entry>Associativity</entry> 995 <entry>Description</entry> 996 </row> 997 </thead> 998 999 <tbody> 1000 <row> 1001 <entry><token>.</token></entry> 1002 <entry>left</entry> 1003 <entry>table/column name separator</entry> 1004 </row> 1005 1006 <row> 1007 <entry><token>::</token></entry> 1008 <entry>left</entry> 1009 <entry><productname>PostgreSQL</productname>-style typecast</entry> 1010 </row> 1011 1012 <row> 1013 <entry><token>[</token> <token>]</token></entry> 1014 <entry>left</entry> 1015 <entry>array element selection</entry> 1016 </row> 1017 1018 <row> 1019 <entry><token>+</token> <token>-</token></entry> 1020 <entry>right</entry> 1021 <entry>unary plus, unary minus</entry> 1022 </row> 1023 1024 <row> 1025 <entry><token>^</token></entry> 1026 <entry>left</entry> 1027 <entry>exponentiation</entry> 1028 </row> 1029 1030 <row> 1031 <entry><token>*</token> <token>/</token> <token>%</token></entry> 1032 <entry>left</entry> 1033 <entry>multiplication, division, modulo</entry> 1034 </row> 1035 1036 <row> 1037 <entry><token>+</token> <token>-</token></entry> 1038 <entry>left</entry> 1039 <entry>addition, subtraction</entry> 1040 </row> 1041 1042 <row> 1043 <entry>(any other operator)</entry> 1044 <entry>left</entry> 1045 <entry>all other native and user-defined operators</entry> 1046 </row> 1047 1048 <row> 1049 <entry><token>BETWEEN</token> <token>IN</token> <token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry> 1050 <entry></entry> 1051 <entry>range containment, set membership, string matching</entry> 1052 </row> 1053 1054 <row> 1055 <entry><token><</token> <token>></token> <token>=</token> <token><=</token> <token>>=</token> <token><></token> 1056</entry> 1057 <entry></entry> 1058 <entry>comparison operators</entry> 1059 </row> 1060 1061 <row> 1062 <entry><token>IS</token> <token>ISNULL</token> <token>NOTNULL</token></entry> 1063 <entry></entry> 1064 <entry><literal>IS TRUE</literal>, <literal>IS FALSE</literal>, <literal>IS 1065 NULL</literal>, <literal>IS DISTINCT FROM</literal>, etc</entry> 1066 </row> 1067 1068 <row> 1069 <entry><token>NOT</token></entry> 1070 <entry>right</entry> 1071 <entry>logical negation</entry> 1072 </row> 1073 1074 <row> 1075 <entry><token>AND</token></entry> 1076 <entry>left</entry> 1077 <entry>logical conjunction</entry> 1078 </row> 1079 1080 <row> 1081 <entry><token>OR</token></entry> 1082 <entry>left</entry> 1083 <entry>logical disjunction</entry> 1084 </row> 1085 </tbody> 1086 </tgroup> 1087 </table> 1088 1089 <para> 1090 Note that the operator precedence rules also apply to user-defined 1091 operators that have the same names as the built-in operators 1092 mentioned above. For example, if you define a 1093 <quote>+</quote> operator for some custom data type it will have 1094 the same precedence as the built-in <quote>+</quote> operator, no 1095 matter what yours does. 1096 </para> 1097 1098 <para> 1099 When a schema-qualified operator name is used in the 1100 <literal>OPERATOR</literal> syntax, as for example in: 1101<programlisting> 1102SELECT 3 OPERATOR(pg_catalog.+) 4; 1103</programlisting> 1104 the <literal>OPERATOR</literal> construct is taken to have the default precedence 1105 shown in <xref linkend="sql-precedence-table"/> for 1106 <quote>any other operator</quote>. This is true no matter 1107 which specific operator appears inside <literal>OPERATOR()</literal>. 1108 </para> 1109 1110 <note> 1111 <para> 1112 <productname>PostgreSQL</productname> versions before 9.5 used slightly different 1113 operator precedence rules. In particular, <token><=</token> 1114 <token>>=</token> and <token><></token> used to be treated as 1115 generic operators; <literal>IS</literal> tests used to have higher priority; 1116 and <literal>NOT BETWEEN</literal> and related constructs acted inconsistently, 1117 being taken in some cases as having the precedence of <literal>NOT</literal> 1118 rather than <literal>BETWEEN</literal>. These rules were changed for better 1119 compliance with the SQL standard and to reduce confusion from 1120 inconsistent treatment of logically equivalent constructs. In most 1121 cases, these changes will result in no behavioral change, or perhaps 1122 in <quote>no such operator</quote> failures which can be resolved by adding 1123 parentheses. However there are corner cases in which a query might 1124 change behavior without any parsing error being reported. If you are 1125 concerned about whether these changes have silently broken something, 1126 you can test your application with the configuration 1127 parameter <xref linkend="guc-operator-precedence-warning"/> turned on 1128 to see if any warnings are logged. 1129 </para> 1130 </note> 1131 </sect2> 1132 </sect1> 1133 1134 <sect1 id="sql-expressions"> 1135 <title>Value Expressions</title> 1136 1137 <indexterm zone="sql-expressions"> 1138 <primary>expression</primary> 1139 <secondary>syntax</secondary> 1140 </indexterm> 1141 1142 <indexterm zone="sql-expressions"> 1143 <primary>value expression</primary> 1144 </indexterm> 1145 1146 <indexterm> 1147 <primary>scalar</primary> 1148 <see>expression</see> 1149 </indexterm> 1150 1151 <para> 1152 Value expressions are used in a variety of contexts, such 1153 as in the target list of the <command>SELECT</command> command, as 1154 new column values in <command>INSERT</command> or 1155 <command>UPDATE</command>, or in search conditions in a number of 1156 commands. The result of a value expression is sometimes called a 1157 <firstterm>scalar</firstterm>, to distinguish it from the result of 1158 a table expression (which is a table). Value expressions are 1159 therefore also called <firstterm>scalar expressions</firstterm> (or 1160 even simply <firstterm>expressions</firstterm>). The expression 1161 syntax allows the calculation of values from primitive parts using 1162 arithmetic, logical, set, and other operations. 1163 </para> 1164 1165 <para> 1166 A value expression is one of the following: 1167 1168 <itemizedlist> 1169 <listitem> 1170 <para> 1171 A constant or literal value 1172 </para> 1173 </listitem> 1174 1175 <listitem> 1176 <para> 1177 A column reference 1178 </para> 1179 </listitem> 1180 1181 <listitem> 1182 <para> 1183 A positional parameter reference, in the body of a function definition 1184 or prepared statement 1185 </para> 1186 </listitem> 1187 1188 <listitem> 1189 <para> 1190 A subscripted expression 1191 </para> 1192 </listitem> 1193 1194 <listitem> 1195 <para> 1196 A field selection expression 1197 </para> 1198 </listitem> 1199 1200 <listitem> 1201 <para> 1202 An operator invocation 1203 </para> 1204 </listitem> 1205 1206 <listitem> 1207 <para> 1208 A function call 1209 </para> 1210 </listitem> 1211 1212 <listitem> 1213 <para> 1214 An aggregate expression 1215 </para> 1216 </listitem> 1217 1218 <listitem> 1219 <para> 1220 A window function call 1221 </para> 1222 </listitem> 1223 1224 <listitem> 1225 <para> 1226 A type cast 1227 </para> 1228 </listitem> 1229 1230 <listitem> 1231 <para> 1232 A collation expression 1233 </para> 1234 </listitem> 1235 1236 <listitem> 1237 <para> 1238 A scalar subquery 1239 </para> 1240 </listitem> 1241 1242 <listitem> 1243 <para> 1244 An array constructor 1245 </para> 1246 </listitem> 1247 1248 <listitem> 1249 <para> 1250 A row constructor 1251 </para> 1252 </listitem> 1253 1254 <listitem> 1255 <para> 1256 Another value expression in parentheses (used to group 1257 subexpressions and override 1258 precedence<indexterm><primary>parenthesis</primary></indexterm>) 1259 </para> 1260 </listitem> 1261 </itemizedlist> 1262 </para> 1263 1264 <para> 1265 In addition to this list, there are a number of constructs that can 1266 be classified as an expression but do not follow any general syntax 1267 rules. These generally have the semantics of a function or 1268 operator and are explained in the appropriate location in <xref 1269 linkend="functions"/>. An example is the <literal>IS NULL</literal> 1270 clause. 1271 </para> 1272 1273 <para> 1274 We have already discussed constants in <xref 1275 linkend="sql-syntax-constants"/>. The following sections discuss 1276 the remaining options. 1277 </para> 1278 1279 <sect2 id="sql-expressions-column-refs"> 1280 <title>Column References</title> 1281 1282 <indexterm> 1283 <primary>column reference</primary> 1284 </indexterm> 1285 1286 <para> 1287 A column can be referenced in the form: 1288<synopsis> 1289<replaceable>correlation</replaceable>.<replaceable>columnname</replaceable> 1290</synopsis> 1291 </para> 1292 1293 <para> 1294 <replaceable>correlation</replaceable> is the name of a 1295 table (possibly qualified with a schema name), or an alias for a table 1296 defined by means of a <literal>FROM</literal> clause. 1297 The correlation name and separating dot can be omitted if the column name 1298 is unique across all the tables being used in the current query. (See also <xref linkend="queries"/>.) 1299 </para> 1300 </sect2> 1301 1302 <sect2 id="sql-expressions-parameters-positional"> 1303 <title>Positional Parameters</title> 1304 1305 <indexterm> 1306 <primary>parameter</primary> 1307 <secondary>syntax</secondary> 1308 </indexterm> 1309 1310 <indexterm> 1311 <primary>$</primary> 1312 </indexterm> 1313 1314 <para> 1315 A positional parameter reference is used to indicate a value 1316 that is supplied externally to an SQL statement. Parameters are 1317 used in SQL function definitions and in prepared queries. Some 1318 client libraries also support specifying data values separately 1319 from the SQL command string, in which case parameters are used to 1320 refer to the out-of-line data values. 1321 The form of a parameter reference is: 1322<synopsis> 1323$<replaceable>number</replaceable> 1324</synopsis> 1325 </para> 1326 1327 <para> 1328 For example, consider the definition of a function, 1329 <function>dept</function>, as: 1330 1331<programlisting> 1332CREATE FUNCTION dept(text) RETURNS dept 1333 AS $$ SELECT * FROM dept WHERE name = $1 $$ 1334 LANGUAGE SQL; 1335</programlisting> 1336 1337 Here the <literal>$1</literal> references the value of the first 1338 function argument whenever the function is invoked. 1339 </para> 1340 </sect2> 1341 1342 <sect2 id="sql-expressions-subscripts"> 1343 <title>Subscripts</title> 1344 1345 <indexterm> 1346 <primary>subscript</primary> 1347 </indexterm> 1348 1349 <para> 1350 If an expression yields a value of an array type, then a specific 1351 element of the array value can be extracted by writing 1352<synopsis> 1353<replaceable>expression</replaceable>[<replaceable>subscript</replaceable>] 1354</synopsis> 1355 or multiple adjacent elements (an <quote>array slice</quote>) can be extracted 1356 by writing 1357<synopsis> 1358<replaceable>expression</replaceable>[<replaceable>lower_subscript</replaceable>:<replaceable>upper_subscript</replaceable>] 1359</synopsis> 1360 (Here, the brackets <literal>[ ]</literal> are meant to appear literally.) 1361 Each <replaceable>subscript</replaceable> is itself an expression, 1362 which will be rounded to the nearest integer value. 1363 </para> 1364 1365 <para> 1366 In general the array <replaceable>expression</replaceable> must be 1367 parenthesized, but the parentheses can be omitted when the expression 1368 to be subscripted is just a column reference or positional parameter. 1369 Also, multiple subscripts can be concatenated when the original array 1370 is multidimensional. 1371 For example: 1372 1373<programlisting> 1374mytable.arraycolumn[4] 1375mytable.two_d_column[17][34] 1376$1[10:42] 1377(arrayfunction(a,b))[42] 1378</programlisting> 1379 1380 The parentheses in the last example are required. 1381 See <xref linkend="arrays"/> for more about arrays. 1382 </para> 1383 </sect2> 1384 1385 <sect2 id="field-selection"> 1386 <title>Field Selection</title> 1387 1388 <indexterm> 1389 <primary>field selection</primary> 1390 </indexterm> 1391 1392 <para> 1393 If an expression yields a value of a composite type (row type), then a 1394 specific field of the row can be extracted by writing 1395<synopsis> 1396<replaceable>expression</replaceable>.<replaceable>fieldname</replaceable> 1397</synopsis> 1398 </para> 1399 1400 <para> 1401 In general the row <replaceable>expression</replaceable> must be 1402 parenthesized, but the parentheses can be omitted when the expression 1403 to be selected from is just a table reference or positional parameter. 1404 For example: 1405 1406<programlisting> 1407mytable.mycolumn 1408$1.somecolumn 1409(rowfunction(a,b)).col3 1410</programlisting> 1411 1412 (Thus, a qualified column reference is actually just a special case 1413 of the field selection syntax.) An important special case is 1414 extracting a field from a table column that is of a composite type: 1415 1416<programlisting> 1417(compositecol).somefield 1418(mytable.compositecol).somefield 1419</programlisting> 1420 1421 The parentheses are required here to show that 1422 <structfield>compositecol</structfield> is a column name not a table name, 1423 or that <structname>mytable</structname> is a table name not a schema name 1424 in the second case. 1425 </para> 1426 1427 <para> 1428 You can ask for all fields of a composite value by 1429 writing <literal>.*</literal>: 1430<programlisting> 1431(compositecol).* 1432</programlisting> 1433 This notation behaves differently depending on context; 1434 see <xref linkend="rowtypes-usage"/> for details. 1435 </para> 1436 </sect2> 1437 1438 <sect2 id="sql-expressions-operator-calls"> 1439 <title>Operator Invocations</title> 1440 1441 <indexterm> 1442 <primary>operator</primary> 1443 <secondary>invocation</secondary> 1444 </indexterm> 1445 1446 <para> 1447 There are three possible syntaxes for an operator invocation: 1448 <simplelist> 1449 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member> 1450 <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member> 1451 <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member> 1452 </simplelist> 1453 where the <replaceable>operator</replaceable> token follows the syntax 1454 rules of <xref linkend="sql-syntax-operators"/>, or is one of the 1455 key words <token>AND</token>, <token>OR</token>, and 1456 <token>NOT</token>, or is a qualified operator name in the form: 1457<synopsis> 1458<literal>OPERATOR(</literal><replaceable>schema</replaceable><literal>.</literal><replaceable>operatorname</replaceable><literal>)</literal> 1459</synopsis> 1460 Which particular operators exist and whether 1461 they are unary or binary depends on what operators have been 1462 defined by the system or the user. <xref linkend="functions"/> 1463 describes the built-in operators. 1464 </para> 1465 </sect2> 1466 1467 <sect2 id="sql-expressions-function-calls"> 1468 <title>Function Calls</title> 1469 1470 <indexterm> 1471 <primary>function</primary> 1472 <secondary>invocation</secondary> 1473 </indexterm> 1474 1475 <para> 1476 The syntax for a function call is the name of a function 1477 (possibly qualified with a schema name), followed by its argument list 1478 enclosed in parentheses: 1479 1480<synopsis> 1481<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> ) 1482</synopsis> 1483 </para> 1484 1485 <para> 1486 For example, the following computes the square root of 2: 1487<programlisting> 1488sqrt(2) 1489</programlisting> 1490 </para> 1491 1492 <para> 1493 The list of built-in functions is in <xref linkend="functions"/>. 1494 Other functions can be added by the user. 1495 </para> 1496 1497 <para> 1498 When issuing queries in a database where some users mistrust other users, 1499 observe security precautions from <xref linkend="typeconv-func"/> when 1500 writing function calls. 1501 </para> 1502 1503 <para> 1504 The arguments can optionally have names attached. 1505 See <xref linkend="sql-syntax-calling-funcs"/> for details. 1506 </para> 1507 1508 <note> 1509 <para> 1510 A function that takes a single argument of composite type can 1511 optionally be called using field-selection syntax, and conversely 1512 field selection can be written in functional style. That is, the 1513 notations <literal>col(table)</literal> and <literal>table.col</literal> are 1514 interchangeable. This behavior is not SQL-standard but is provided 1515 in <productname>PostgreSQL</productname> because it allows use of functions to 1516 emulate <quote>computed fields</quote>. For more information see 1517 <xref linkend="rowtypes-usage"/>. 1518 </para> 1519 </note> 1520 </sect2> 1521 1522 <sect2 id="syntax-aggregates"> 1523 <title>Aggregate Expressions</title> 1524 1525 <indexterm zone="syntax-aggregates"> 1526 <primary>aggregate function</primary> 1527 <secondary>invocation</secondary> 1528 </indexterm> 1529 1530 <indexterm zone="syntax-aggregates"> 1531 <primary>ordered-set aggregate</primary> 1532 </indexterm> 1533 1534 <indexterm zone="syntax-aggregates"> 1535 <primary>WITHIN GROUP</primary> 1536 </indexterm> 1537 1538 <indexterm zone="syntax-aggregates"> 1539 <primary>FILTER</primary> 1540 </indexterm> 1541 1542 <para> 1543 An <firstterm>aggregate expression</firstterm> represents the 1544 application of an aggregate function across the rows selected by a 1545 query. An aggregate function reduces multiple inputs to a single 1546 output value, such as the sum or average of the inputs. The 1547 syntax of an aggregate expression is one of the following: 1548 1549<synopsis> 1550<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] 1551<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] 1552<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] 1553<replaceable>aggregate_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] 1554<replaceable>aggregate_name</replaceable> ( [ <replaceable>expression</replaceable> [ , ... ] ] ) WITHIN GROUP ( <replaceable>order_by_clause</replaceable> ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] 1555</synopsis> 1556 1557 where <replaceable>aggregate_name</replaceable> is a previously 1558 defined aggregate (possibly qualified with a schema name) and 1559 <replaceable>expression</replaceable> is 1560 any value expression that does not itself contain an aggregate 1561 expression or a window function call. The optional 1562 <replaceable>order_by_clause</replaceable> and 1563 <replaceable>filter_clause</replaceable> are described below. 1564 </para> 1565 1566 <para> 1567 The first form of aggregate expression invokes the aggregate 1568 once for each input row. 1569 The second form is the same as the first, since 1570 <literal>ALL</literal> is the default. 1571 The third form invokes the aggregate once for each distinct value 1572 of the expression (or distinct set of values, for multiple expressions) 1573 found in the input rows. 1574 The fourth form invokes the aggregate once for each input row; since no 1575 particular input value is specified, it is generally only useful 1576 for the <function>count(*)</function> aggregate function. 1577 The last form is used with <firstterm>ordered-set</firstterm> aggregate 1578 functions, which are described below. 1579 </para> 1580 1581 <para> 1582 Most aggregate functions ignore null inputs, so that rows in which 1583 one or more of the expression(s) yield null are discarded. This 1584 can be assumed to be true, unless otherwise specified, for all 1585 built-in aggregates. 1586 </para> 1587 1588 <para> 1589 For example, <literal>count(*)</literal> yields the total number 1590 of input rows; <literal>count(f1)</literal> yields the number of 1591 input rows in which <literal>f1</literal> is non-null, since 1592 <function>count</function> ignores nulls; and 1593 <literal>count(distinct f1)</literal> yields the number of 1594 distinct non-null values of <literal>f1</literal>. 1595 </para> 1596 1597 <para> 1598 Ordinarily, the input rows are fed to the aggregate function in an 1599 unspecified order. In many cases this does not matter; for example, 1600 <function>min</function> produces the same result no matter what order it 1601 receives the inputs in. However, some aggregate functions 1602 (such as <function>array_agg</function> and <function>string_agg</function>) produce 1603 results that depend on the ordering of the input rows. When using 1604 such an aggregate, the optional <replaceable>order_by_clause</replaceable> can be 1605 used to specify the desired ordering. The <replaceable>order_by_clause</replaceable> 1606 has the same syntax as for a query-level <literal>ORDER BY</literal> clause, as 1607 described in <xref linkend="queries-order"/>, except that its expressions 1608 are always just expressions and cannot be output-column names or numbers. 1609 For example: 1610<programlisting> 1611SELECT array_agg(a ORDER BY b DESC) FROM table; 1612</programlisting> 1613 </para> 1614 1615 <para> 1616 When dealing with multiple-argument aggregate functions, note that the 1617 <literal>ORDER BY</literal> clause goes after all the aggregate arguments. 1618 For example, write this: 1619<programlisting> 1620SELECT string_agg(a, ',' ORDER BY a) FROM table; 1621</programlisting> 1622 not this: 1623<programlisting> 1624SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect 1625</programlisting> 1626 The latter is syntactically valid, but it represents a call of a 1627 single-argument aggregate function with two <literal>ORDER BY</literal> keys 1628 (the second one being rather useless since it's a constant). 1629 </para> 1630 1631 <para> 1632 If <literal>DISTINCT</literal> is specified in addition to an 1633 <replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal> 1634 expressions must match regular arguments of the aggregate; that is, 1635 you cannot sort on an expression that is not included in the 1636 <literal>DISTINCT</literal> list. 1637 </para> 1638 1639 <note> 1640 <para> 1641 The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal> 1642 in an aggregate function is a <productname>PostgreSQL</productname> extension. 1643 </para> 1644 </note> 1645 1646 <para> 1647 Placing <literal>ORDER BY</literal> within the aggregate's regular argument 1648 list, as described so far, is used when ordering the input rows for 1649 general-purpose and statistical aggregates, for which ordering is 1650 optional. There is a 1651 subclass of aggregate functions called <firstterm>ordered-set 1652 aggregates</firstterm> for which an <replaceable>order_by_clause</replaceable> 1653 is <emphasis>required</emphasis>, usually because the aggregate's computation is 1654 only sensible in terms of a specific ordering of its input rows. 1655 Typical examples of ordered-set aggregates include rank and percentile 1656 calculations. For an ordered-set aggregate, 1657 the <replaceable>order_by_clause</replaceable> is written 1658 inside <literal>WITHIN GROUP (...)</literal>, as shown in the final syntax 1659 alternative above. The expressions in 1660 the <replaceable>order_by_clause</replaceable> are evaluated once per 1661 input row just like regular aggregate arguments, sorted as per 1662 the <replaceable>order_by_clause</replaceable>'s requirements, and fed 1663 to the aggregate function as input arguments. (This is unlike the case 1664 for a non-<literal>WITHIN GROUP</literal> <replaceable>order_by_clause</replaceable>, 1665 which is not treated as argument(s) to the aggregate function.) The 1666 argument expressions preceding <literal>WITHIN GROUP</literal>, if any, are 1667 called <firstterm>direct arguments</firstterm> to distinguish them from 1668 the <firstterm>aggregated arguments</firstterm> listed in 1669 the <replaceable>order_by_clause</replaceable>. Unlike regular aggregate 1670 arguments, direct arguments are evaluated only once per aggregate call, 1671 not once per input row. This means that they can contain variables only 1672 if those variables are grouped by <literal>GROUP BY</literal>; this restriction 1673 is the same as if the direct arguments were not inside an aggregate 1674 expression at all. Direct arguments are typically used for things like 1675 percentile fractions, which only make sense as a single value per 1676 aggregation calculation. The direct argument list can be empty; in this 1677 case, write just <literal>()</literal> not <literal>(*)</literal>. 1678 (<productname>PostgreSQL</productname> will actually accept either spelling, but 1679 only the first way conforms to the SQL standard.) 1680 </para> 1681 1682 <para> 1683 <indexterm> 1684 <primary>median</primary> 1685 <seealso>percentile</seealso> 1686 </indexterm> 1687 An example of an ordered-set aggregate call is: 1688 1689<programlisting> 1690SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; 1691 percentile_cont 1692----------------- 1693 50489 1694</programlisting> 1695 1696 which obtains the 50th percentile, or median, value of 1697 the <structfield>income</structfield> column from table <structname>households</structname>. 1698 Here, <literal>0.5</literal> is a direct argument; it would make no sense 1699 for the percentile fraction to be a value varying across rows. 1700 </para> 1701 1702 <para> 1703 If <literal>FILTER</literal> is specified, then only the input 1704 rows for which the <replaceable>filter_clause</replaceable> 1705 evaluates to true are fed to the aggregate function; other rows 1706 are discarded. For example: 1707<programlisting> 1708SELECT 1709 count(*) AS unfiltered, 1710 count(*) FILTER (WHERE i < 5) AS filtered 1711FROM generate_series(1,10) AS s(i); 1712 unfiltered | filtered 1713------------+---------- 1714 10 | 4 1715(1 row) 1716</programlisting> 1717 </para> 1718 1719 <para> 1720 The predefined aggregate functions are described in <xref 1721 linkend="functions-aggregate"/>. Other aggregate functions can be added 1722 by the user. 1723 </para> 1724 1725 <para> 1726 An aggregate expression can only appear in the result list or 1727 <literal>HAVING</literal> clause of a <command>SELECT</command> command. 1728 It is forbidden in other clauses, such as <literal>WHERE</literal>, 1729 because those clauses are logically evaluated before the results 1730 of aggregates are formed. 1731 </para> 1732 1733 <para> 1734 When an aggregate expression appears in a subquery (see 1735 <xref linkend="sql-syntax-scalar-subqueries"/> and 1736 <xref linkend="functions-subquery"/>), the aggregate is normally 1737 evaluated over the rows of the subquery. But an exception occurs 1738 if the aggregate's arguments (and <replaceable>filter_clause</replaceable> 1739 if any) contain only outer-level variables: 1740 the aggregate then belongs to the nearest such outer level, and is 1741 evaluated over the rows of that query. The aggregate expression 1742 as a whole is then an outer reference for the subquery it appears in, 1743 and acts as a constant over any one evaluation of that subquery. 1744 The restriction about 1745 appearing only in the result list or <literal>HAVING</literal> clause 1746 applies with respect to the query level that the aggregate belongs to. 1747 </para> 1748 </sect2> 1749 1750 <sect2 id="syntax-window-functions"> 1751 <title>Window Function Calls</title> 1752 1753 <indexterm zone="syntax-window-functions"> 1754 <primary>window function</primary> 1755 <secondary>invocation</secondary> 1756 </indexterm> 1757 1758 <indexterm zone="syntax-window-functions"> 1759 <primary>OVER clause</primary> 1760 </indexterm> 1761 1762 <para> 1763 A <firstterm>window function call</firstterm> represents the application 1764 of an aggregate-like function over some portion of the rows selected 1765 by a query. Unlike non-window aggregate calls, this is not tied 1766 to grouping of the selected rows into a single output row — each 1767 row remains separate in the query output. However the window function 1768 has access to all the rows that would be part of the current row's 1769 group according to the grouping specification (<literal>PARTITION BY</literal> 1770 list) of the window function call. 1771 The syntax of a window function call is one of the following: 1772 1773<synopsis> 1774<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable> 1775<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> ) 1776<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable> 1777<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> ) 1778</synopsis> 1779 where <replaceable class="parameter">window_definition</replaceable> 1780 has the syntax 1781<synopsis> 1782[ <replaceable class="parameter">existing_window_name</replaceable> ] 1783[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ] 1784[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] 1785[ <replaceable class="parameter">frame_clause</replaceable> ] 1786</synopsis> 1787 The optional <replaceable class="parameter">frame_clause</replaceable> 1788 can be one of 1789<synopsis> 1790{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] 1791{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] 1792</synopsis> 1793 where <replaceable>frame_start</replaceable> 1794 and <replaceable>frame_end</replaceable> can be one of 1795<synopsis> 1796UNBOUNDED PRECEDING 1797<replaceable>offset</replaceable> PRECEDING 1798CURRENT ROW 1799<replaceable>offset</replaceable> FOLLOWING 1800UNBOUNDED FOLLOWING 1801</synopsis> 1802 and <replaceable>frame_exclusion</replaceable> can be one of 1803<synopsis> 1804EXCLUDE CURRENT ROW 1805EXCLUDE GROUP 1806EXCLUDE TIES 1807EXCLUDE NO OTHERS 1808</synopsis> 1809 </para> 1810 1811 <para> 1812 Here, <replaceable>expression</replaceable> represents any value 1813 expression that does not itself contain window function calls. 1814 </para> 1815 1816 <para> 1817 <replaceable>window_name</replaceable> is a reference to a named window 1818 specification defined in the query's <literal>WINDOW</literal> clause. 1819 Alternatively, a full <replaceable>window_definition</replaceable> can 1820 be given within parentheses, using the same syntax as for defining a 1821 named window in the <literal>WINDOW</literal> clause; see the 1822 <xref linkend="sql-select"/> reference page for details. It's worth 1823 pointing out that <literal>OVER wname</literal> is not exactly equivalent to 1824 <literal>OVER (wname ...)</literal>; the latter implies copying and modifying the 1825 window definition, and will be rejected if the referenced window 1826 specification includes a frame clause. 1827 </para> 1828 1829 <para> 1830 The <literal>PARTITION BY</literal> clause groups the rows of the query into 1831 <firstterm>partitions</firstterm>, which are processed separately by the window 1832 function. <literal>PARTITION BY</literal> works similarly to a query-level 1833 <literal>GROUP BY</literal> clause, except that its expressions are always just 1834 expressions and cannot be output-column names or numbers. 1835 Without <literal>PARTITION BY</literal>, all rows produced by the query are 1836 treated as a single partition. 1837 The <literal>ORDER BY</literal> clause determines the order in which the rows 1838 of a partition are processed by the window function. It works similarly 1839 to a query-level <literal>ORDER BY</literal> clause, but likewise cannot use 1840 output-column names or numbers. Without <literal>ORDER BY</literal>, rows are 1841 processed in an unspecified order. 1842 </para> 1843 1844 <para> 1845 The <replaceable class="parameter">frame_clause</replaceable> specifies 1846 the set of rows constituting the <firstterm>window frame</firstterm>, which is a 1847 subset of the current partition, for those window functions that act on 1848 the frame instead of the whole partition. The set of rows in the frame 1849 can vary depending on which row is the current row. The frame can be 1850 specified in <literal>RANGE</literal>, <literal>ROWS</literal> 1851 or <literal>GROUPS</literal> mode; in each case, it runs from 1852 the <replaceable>frame_start</replaceable> to 1853 the <replaceable>frame_end</replaceable>. 1854 If <replaceable>frame_end</replaceable> is omitted, the end defaults 1855 to <literal>CURRENT ROW</literal>. 1856 </para> 1857 1858 <para> 1859 A <replaceable>frame_start</replaceable> of <literal>UNBOUNDED PRECEDING</literal> means 1860 that the frame starts with the first row of the partition, and similarly 1861 a <replaceable>frame_end</replaceable> of <literal>UNBOUNDED FOLLOWING</literal> means 1862 that the frame ends with the last row of the partition. 1863 </para> 1864 1865 <para> 1866 In <literal>RANGE</literal> or <literal>GROUPS</literal> mode, 1867 a <replaceable>frame_start</replaceable> of 1868 <literal>CURRENT ROW</literal> means the frame starts with the current 1869 row's first <firstterm>peer</firstterm> row (a row that the 1870 window's <literal>ORDER BY</literal> clause sorts as equivalent to the 1871 current row), while a <replaceable>frame_end</replaceable> of 1872 <literal>CURRENT ROW</literal> means the frame ends with the current 1873 row's last peer row. 1874 In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply 1875 means the current row. 1876 </para> 1877 1878 <para> 1879 In the <replaceable>offset</replaceable> <literal>PRECEDING</literal> 1880 and <replaceable>offset</replaceable> <literal>FOLLOWING</literal> frame 1881 options, the <replaceable>offset</replaceable> must be an expression not 1882 containing any variables, aggregate functions, or window functions. 1883 The meaning of the <replaceable>offset</replaceable> depends on the 1884 frame mode: 1885 <itemizedlist> 1886 <listitem> 1887 <para> 1888 In <literal>ROWS</literal> mode, 1889 the <replaceable>offset</replaceable> must yield a non-null, 1890 non-negative integer, and the option means that the frame starts or 1891 ends the specified number of rows before or after the current row. 1892 </para> 1893 </listitem> 1894 <listitem> 1895 <para> 1896 In <literal>GROUPS</literal> mode, 1897 the <replaceable>offset</replaceable> again must yield a non-null, 1898 non-negative integer, and the option means that the frame starts or 1899 ends the specified number of <firstterm>peer groups</firstterm> 1900 before or after the current row's peer group, where a peer group is a 1901 set of rows that are equivalent in the <literal>ORDER BY</literal> 1902 ordering. (There must be an <literal>ORDER BY</literal> clause 1903 in the window definition to use <literal>GROUPS</literal> mode.) 1904 </para> 1905 </listitem> 1906 <listitem> 1907 <para> 1908 In <literal>RANGE</literal> mode, these options require that 1909 the <literal>ORDER BY</literal> clause specify exactly one column. 1910 The <replaceable>offset</replaceable> specifies the maximum 1911 difference between the value of that column in the current row and 1912 its value in preceding or following rows of the frame. The data type 1913 of the <replaceable>offset</replaceable> expression varies depending 1914 on the data type of the ordering column. For numeric ordering 1915 columns it is typically of the same type as the ordering column, 1916 but for datetime ordering columns it is an <type>interval</type>. 1917 For example, if the ordering column is of type <type>date</type> 1918 or <type>timestamp</type>, one could write <literal>RANGE BETWEEN 1919 '1 day' PRECEDING AND '10 days' FOLLOWING</literal>. 1920 The <replaceable>offset</replaceable> is still required to be 1921 non-null and non-negative, though the meaning 1922 of <quote>non-negative</quote> depends on its data type. 1923 </para> 1924 </listitem> 1925 </itemizedlist> 1926 In any case, the distance to the end of the frame is limited by the 1927 distance to the end of the partition, so that for rows near the partition 1928 ends the frame might contain fewer rows than elsewhere. 1929 </para> 1930 1931 <para> 1932 Notice that in both <literal>ROWS</literal> and <literal>GROUPS</literal> 1933 mode, <literal>0 PRECEDING</literal> and <literal>0 FOLLOWING</literal> 1934 are equivalent to <literal>CURRENT ROW</literal>. This normally holds 1935 in <literal>RANGE</literal> mode as well, for an appropriate 1936 data-type-specific meaning of <quote>zero</quote>. 1937 </para> 1938 1939 <para> 1940 The <replaceable>frame_exclusion</replaceable> option allows rows around 1941 the current row to be excluded from the frame, even if they would be 1942 included according to the frame start and frame end options. 1943 <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the 1944 frame. 1945 <literal>EXCLUDE GROUP</literal> excludes the current row and its 1946 ordering peers from the frame. 1947 <literal>EXCLUDE TIES</literal> excludes any peers of the current 1948 row from the frame, but not the current row itself. 1949 <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the 1950 default behavior of not excluding the current row or its peers. 1951 </para> 1952 1953 <para> 1954 The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>, 1955 which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND 1956 CURRENT ROW</literal>. With <literal>ORDER BY</literal>, this sets the frame to be 1957 all rows from the partition start up through the current row's last 1958 <literal>ORDER BY</literal> peer. Without <literal>ORDER BY</literal>, 1959 this means all rows of the partition are included in the window frame, 1960 since all rows become peers of the current row. 1961 </para> 1962 1963 <para> 1964 Restrictions are that 1965 <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>, 1966 <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>, 1967 and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the 1968 above list of <replaceable>frame_start</replaceable> 1969 and <replaceable>frame_end</replaceable> options than 1970 the <replaceable>frame_start</replaceable> choice does — for example 1971 <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable> 1972 PRECEDING</literal> is not allowed. 1973 But, for example, <literal>ROWS BETWEEN 7 PRECEDING AND 8 1974 PRECEDING</literal> is allowed, even though it would never select any 1975 rows. 1976 </para> 1977 1978 <para> 1979 If <literal>FILTER</literal> is specified, then only the input 1980 rows for which the <replaceable>filter_clause</replaceable> 1981 evaluates to true are fed to the window function; other rows 1982 are discarded. Only window functions that are aggregates accept 1983 a <literal>FILTER</literal> clause. 1984 </para> 1985 1986 <para> 1987 The built-in window functions are described in <xref 1988 linkend="functions-window-table"/>. Other window functions can be added by 1989 the user. Also, any built-in or user-defined general-purpose or 1990 statistical aggregate can be used as a window function. (Ordered-set 1991 and hypothetical-set aggregates cannot presently be used as window functions.) 1992 </para> 1993 1994 <para> 1995 The syntaxes using <literal>*</literal> are used for calling parameter-less 1996 aggregate functions as window functions, for example 1997 <literal>count(*) OVER (PARTITION BY x ORDER BY y)</literal>. 1998 The asterisk (<literal>*</literal>) is customarily not used for 1999 window-specific functions. Window-specific functions do not 2000 allow <literal>DISTINCT</literal> or <literal>ORDER BY</literal> to be used within the 2001 function argument list. 2002 </para> 2003 2004 <para> 2005 Window function calls are permitted only in the <literal>SELECT</literal> 2006 list and the <literal>ORDER BY</literal> clause of the query. 2007 </para> 2008 2009 <para> 2010 More information about window functions can be found in 2011 <xref linkend="tutorial-window"/>, 2012 <xref linkend="functions-window"/>, and 2013 <xref linkend="queries-window"/>. 2014 </para> 2015 </sect2> 2016 2017 <sect2 id="sql-syntax-type-casts"> 2018 <title>Type Casts</title> 2019 2020 <indexterm> 2021 <primary>data type</primary> 2022 <secondary>type cast</secondary> 2023 </indexterm> 2024 2025 <indexterm> 2026 <primary>type cast</primary> 2027 </indexterm> 2028 2029 <indexterm> 2030 <primary>::</primary> 2031 </indexterm> 2032 2033 <para> 2034 A type cast specifies a conversion from one data type to another. 2035 <productname>PostgreSQL</productname> accepts two equivalent syntaxes 2036 for type casts: 2037<synopsis> 2038CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> ) 2039<replaceable>expression</replaceable>::<replaceable>type</replaceable> 2040</synopsis> 2041 The <literal>CAST</literal> syntax conforms to SQL; the syntax with 2042 <literal>::</literal> is historical <productname>PostgreSQL</productname> 2043 usage. 2044 </para> 2045 2046 <para> 2047 When a cast is applied to a value expression of a known type, it 2048 represents a run-time type conversion. The cast will succeed only 2049 if a suitable type conversion operation has been defined. Notice that this 2050 is subtly different from the use of casts with constants, as shown in 2051 <xref linkend="sql-syntax-constants-generic"/>. A cast applied to an 2052 unadorned string literal represents the initial assignment of a type 2053 to a literal constant value, and so it will succeed for any type 2054 (if the contents of the string literal are acceptable input syntax for the 2055 data type). 2056 </para> 2057 2058 <para> 2059 An explicit type cast can usually be omitted if there is no ambiguity as 2060 to the type that a value expression must produce (for example, when it is 2061 assigned to a table column); the system will automatically apply a 2062 type cast in such cases. However, automatic casting is only done for 2063 casts that are marked <quote>OK to apply implicitly</quote> 2064 in the system catalogs. Other casts must be invoked with 2065 explicit casting syntax. This restriction is intended to prevent 2066 surprising conversions from being applied silently. 2067 </para> 2068 2069 <para> 2070 It is also possible to specify a type cast using a function-like 2071 syntax: 2072<synopsis> 2073<replaceable>typename</replaceable> ( <replaceable>expression</replaceable> ) 2074</synopsis> 2075 However, this only works for types whose names are also valid as 2076 function names. For example, <literal>double precision</literal> 2077 cannot be used this way, but the equivalent <literal>float8</literal> 2078 can. Also, the names <literal>interval</literal>, <literal>time</literal>, and 2079 <literal>timestamp</literal> can only be used in this fashion if they are 2080 double-quoted, because of syntactic conflicts. Therefore, the use of 2081 the function-like cast syntax leads to inconsistencies and should 2082 probably be avoided. 2083 </para> 2084 2085 <note> 2086 <para> 2087 The function-like syntax is in fact just a function call. When 2088 one of the two standard cast syntaxes is used to do a run-time 2089 conversion, it will internally invoke a registered function to 2090 perform the conversion. By convention, these conversion functions 2091 have the same name as their output type, and thus the <quote>function-like 2092 syntax</quote> is nothing more than a direct invocation of the underlying 2093 conversion function. Obviously, this is not something that a portable 2094 application should rely on. For further details see 2095 <xref linkend="sql-createcast"/>. 2096 </para> 2097 </note> 2098 </sect2> 2099 2100 <sect2 id="sql-syntax-collate-exprs"> 2101 <title>Collation Expressions</title> 2102 2103 <indexterm> 2104 <primary>COLLATE</primary> 2105 </indexterm> 2106 2107 <para> 2108 The <literal>COLLATE</literal> clause overrides the collation of 2109 an expression. It is appended to the expression it applies to: 2110<synopsis> 2111<replaceable>expr</replaceable> COLLATE <replaceable>collation</replaceable> 2112</synopsis> 2113 where <replaceable>collation</replaceable> is a possibly 2114 schema-qualified identifier. The <literal>COLLATE</literal> 2115 clause binds tighter than operators; parentheses can be used when 2116 necessary. 2117 </para> 2118 2119 <para> 2120 If no collation is explicitly specified, the database system 2121 either derives a collation from the columns involved in the 2122 expression, or it defaults to the default collation of the 2123 database if no column is involved in the expression. 2124 </para> 2125 2126 <para> 2127 The two common uses of the <literal>COLLATE</literal> clause are 2128 overriding the sort order in an <literal>ORDER BY</literal> clause, for 2129 example: 2130<programlisting> 2131SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C"; 2132</programlisting> 2133 and overriding the collation of a function or operator call that 2134 has locale-sensitive results, for example: 2135<programlisting> 2136SELECT * FROM tbl WHERE a > 'foo' COLLATE "C"; 2137</programlisting> 2138 Note that in the latter case the <literal>COLLATE</literal> clause is 2139 attached to an input argument of the operator we wish to affect. 2140 It doesn't matter which argument of the operator or function call the 2141 <literal>COLLATE</literal> clause is attached to, because the collation that is 2142 applied by the operator or function is derived by considering all 2143 arguments, and an explicit <literal>COLLATE</literal> clause will override the 2144 collations of all other arguments. (Attaching non-matching 2145 <literal>COLLATE</literal> clauses to more than one argument, however, is an 2146 error. For more details see <xref linkend="collation"/>.) 2147 Thus, this gives the same result as the previous example: 2148<programlisting> 2149SELECT * FROM tbl WHERE a COLLATE "C" > 'foo'; 2150</programlisting> 2151 But this is an error: 2152<programlisting> 2153SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C"; 2154</programlisting> 2155 because it attempts to apply a collation to the result of the 2156 <literal>></literal> operator, which is of the non-collatable data type 2157 <type>boolean</type>. 2158 </para> 2159 </sect2> 2160 2161 <sect2 id="sql-syntax-scalar-subqueries"> 2162 <title>Scalar Subqueries</title> 2163 2164 <indexterm> 2165 <primary>subquery</primary> 2166 </indexterm> 2167 2168 <para> 2169 A scalar subquery is an ordinary 2170 <command>SELECT</command> query in parentheses that returns exactly one 2171 row with one column. (See <xref linkend="queries"/> for information about writing queries.) 2172 The <command>SELECT</command> query is executed 2173 and the single returned value is used in the surrounding value expression. 2174 It is an error to use a query that 2175 returns more than one row or more than one column as a scalar subquery. 2176 (But if, during a particular execution, the subquery returns no rows, 2177 there is no error; the scalar result is taken to be null.) 2178 The subquery can refer to variables from the surrounding query, 2179 which will act as constants during any one evaluation of the subquery. 2180 See also <xref linkend="functions-subquery"/> for other expressions involving subqueries. 2181 </para> 2182 2183 <para> 2184 For example, the following finds the largest city population in each 2185 state: 2186<programlisting> 2187SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) 2188 FROM states; 2189</programlisting> 2190 </para> 2191 </sect2> 2192 2193 <sect2 id="sql-syntax-array-constructors"> 2194 <title>Array Constructors</title> 2195 2196 <indexterm> 2197 <primary>array</primary> 2198 <secondary>constructor</secondary> 2199 </indexterm> 2200 2201 <indexterm> 2202 <primary>ARRAY</primary> 2203 </indexterm> 2204 2205 <para> 2206 An array constructor is an expression that builds an 2207 array value using values for its member elements. A simple array 2208 constructor 2209 consists of the key word <literal>ARRAY</literal>, a left square bracket 2210 <literal>[</literal>, a list of expressions (separated by commas) for the 2211 array element values, and finally a right square bracket <literal>]</literal>. 2212 For example: 2213<programlisting> 2214SELECT ARRAY[1,2,3+4]; 2215 array 2216--------- 2217 {1,2,7} 2218(1 row) 2219</programlisting> 2220 By default, 2221 the array element type is the common type of the member expressions, 2222 determined using the same rules as for <literal>UNION</literal> or 2223 <literal>CASE</literal> constructs (see <xref linkend="typeconv-union-case"/>). 2224 You can override this by explicitly casting the array constructor to the 2225 desired type, for example: 2226<programlisting> 2227SELECT ARRAY[1,2,22.7]::integer[]; 2228 array 2229---------- 2230 {1,2,23} 2231(1 row) 2232</programlisting> 2233 This has the same effect as casting each expression to the array 2234 element type individually. 2235 For more on casting, see <xref linkend="sql-syntax-type-casts"/>. 2236 </para> 2237 2238 <para> 2239 Multidimensional array values can be built by nesting array 2240 constructors. 2241 In the inner constructors, the key word <literal>ARRAY</literal> can 2242 be omitted. For example, these produce the same result: 2243 2244<programlisting> 2245SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; 2246 array 2247--------------- 2248 {{1,2},{3,4}} 2249(1 row) 2250 2251SELECT ARRAY[[1,2],[3,4]]; 2252 array 2253--------------- 2254 {{1,2},{3,4}} 2255(1 row) 2256</programlisting> 2257 2258 Since multidimensional arrays must be rectangular, inner constructors 2259 at the same level must produce sub-arrays of identical dimensions. 2260 Any cast applied to the outer <literal>ARRAY</literal> constructor propagates 2261 automatically to all the inner constructors. 2262 </para> 2263 2264 <para> 2265 Multidimensional array constructor elements can be anything yielding 2266 an array of the proper kind, not only a sub-<literal>ARRAY</literal> construct. 2267 For example: 2268<programlisting> 2269CREATE TABLE arr(f1 int[], f2 int[]); 2270 2271INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); 2272 2273SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr; 2274 array 2275------------------------------------------------ 2276 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}} 2277(1 row) 2278</programlisting> 2279 </para> 2280 2281 <para> 2282 You can construct an empty array, but since it's impossible to have an 2283 array with no type, you must explicitly cast your empty array to the 2284 desired type. For example: 2285<programlisting> 2286SELECT ARRAY[]::integer[]; 2287 array 2288------- 2289 {} 2290(1 row) 2291</programlisting> 2292 </para> 2293 2294 <para> 2295 It is also possible to construct an array from the results of a 2296 subquery. In this form, the array constructor is written with the 2297 key word <literal>ARRAY</literal> followed by a parenthesized (not 2298 bracketed) subquery. For example: 2299<programlisting> 2300SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); 2301 array 2302------------------------------------------------------------------ 2303 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412} 2304(1 row) 2305 2306SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); 2307 array 2308---------------------------------- 2309 {{1,2},{2,4},{3,6},{4,8},{5,10}} 2310(1 row) 2311</programlisting> 2312 The subquery must return a single column. 2313 If the subquery's output column is of a non-array type, the resulting 2314 one-dimensional array will have an element for each row in the 2315 subquery result, with an element type matching that of the 2316 subquery's output column. 2317 If the subquery's output column is of an array type, the result will be 2318 an array of the same type but one higher dimension; in this case all 2319 the subquery rows must yield arrays of identical dimensionality, else 2320 the result would not be rectangular. 2321 </para> 2322 2323 <para> 2324 The subscripts of an array value built with <literal>ARRAY</literal> 2325 always begin with one. For more information about arrays, see 2326 <xref linkend="arrays"/>. 2327 </para> 2328 2329 </sect2> 2330 2331 <sect2 id="sql-syntax-row-constructors"> 2332 <title>Row Constructors</title> 2333 2334 <indexterm> 2335 <primary>composite type</primary> 2336 <secondary>constructor</secondary> 2337 </indexterm> 2338 2339 <indexterm> 2340 <primary>row type</primary> 2341 <secondary>constructor</secondary> 2342 </indexterm> 2343 2344 <indexterm> 2345 <primary>ROW</primary> 2346 </indexterm> 2347 2348 <para> 2349 A row constructor is an expression that builds a row value (also 2350 called a composite value) using values 2351 for its member fields. A row constructor consists of the key word 2352 <literal>ROW</literal>, a left parenthesis, zero or more 2353 expressions (separated by commas) for the row field values, and finally 2354 a right parenthesis. For example: 2355<programlisting> 2356SELECT ROW(1,2.5,'this is a test'); 2357</programlisting> 2358 The key word <literal>ROW</literal> is optional when there is more than one 2359 expression in the list. 2360 </para> 2361 2362 <para> 2363 A row constructor can include the syntax 2364 <replaceable>rowvalue</replaceable><literal>.*</literal>, 2365 which will be expanded to a list of the elements of the row value, 2366 just as occurs when the <literal>.*</literal> syntax is used at the top level 2367 of a <command>SELECT</command> list (see <xref linkend="rowtypes-usage"/>). 2368 For example, if table <literal>t</literal> has 2369 columns <literal>f1</literal> and <literal>f2</literal>, these are the same: 2370<programlisting> 2371SELECT ROW(t.*, 42) FROM t; 2372SELECT ROW(t.f1, t.f2, 42) FROM t; 2373</programlisting> 2374 </para> 2375 2376 <note> 2377 <para> 2378 Before <productname>PostgreSQL</productname> 8.2, the 2379 <literal>.*</literal> syntax was not expanded in row constructors, so 2380 that writing <literal>ROW(t.*, 42)</literal> created a two-field row whose first 2381 field was another row value. The new behavior is usually more useful. 2382 If you need the old behavior of nested row values, write the inner 2383 row value without <literal>.*</literal>, for instance 2384 <literal>ROW(t, 42)</literal>. 2385 </para> 2386 </note> 2387 2388 <para> 2389 By default, the value created by a <literal>ROW</literal> expression is of 2390 an anonymous record type. If necessary, it can be cast to a named 2391 composite type — either the row type of a table, or a composite type 2392 created with <command>CREATE TYPE AS</command>. An explicit cast might be needed 2393 to avoid ambiguity. For example: 2394<programlisting> 2395CREATE TABLE mytable(f1 int, f2 float, f3 text); 2396 2397CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; 2398 2399-- No cast needed since only one getf1() exists 2400SELECT getf1(ROW(1,2.5,'this is a test')); 2401 getf1 2402------- 2403 1 2404(1 row) 2405 2406CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); 2407 2408CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; 2409 2410-- Now we need a cast to indicate which function to call: 2411SELECT getf1(ROW(1,2.5,'this is a test')); 2412ERROR: function getf1(record) is not unique 2413 2414SELECT getf1(ROW(1,2.5,'this is a test')::mytable); 2415 getf1 2416------- 2417 1 2418(1 row) 2419 2420SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); 2421 getf1 2422------- 2423 11 2424(1 row) 2425</programlisting> 2426 </para> 2427 2428 <para> 2429 Row constructors can be used to build composite values to be stored 2430 in a composite-type table column, or to be passed to a function that 2431 accepts a composite parameter. Also, 2432 it is possible to compare two row values or test a row with 2433 <literal>IS NULL</literal> or <literal>IS NOT NULL</literal>, for example: 2434<programlisting> 2435SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); 2436 2437SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows 2438</programlisting> 2439 For more detail see <xref linkend="functions-comparisons"/>. 2440 Row constructors can also be used in connection with subqueries, 2441 as discussed in <xref linkend="functions-subquery"/>. 2442 </para> 2443 2444 </sect2> 2445 2446 <sect2 id="syntax-express-eval"> 2447 <title>Expression Evaluation Rules</title> 2448 2449 <indexterm> 2450 <primary>expression</primary> 2451 <secondary>order of evaluation</secondary> 2452 </indexterm> 2453 2454 <para> 2455 The order of evaluation of subexpressions is not defined. In 2456 particular, the inputs of an operator or function are not necessarily 2457 evaluated left-to-right or in any other fixed order. 2458 </para> 2459 2460 <para> 2461 Furthermore, if the result of an expression can be determined by 2462 evaluating only some parts of it, then other subexpressions 2463 might not be evaluated at all. For instance, if one wrote: 2464<programlisting> 2465SELECT true OR somefunc(); 2466</programlisting> 2467 then <literal>somefunc()</literal> would (probably) not be called 2468 at all. The same would be the case if one wrote: 2469<programlisting> 2470SELECT somefunc() OR true; 2471</programlisting> 2472 Note that this is not the same as the left-to-right 2473 <quote>short-circuiting</quote> of Boolean operators that is found 2474 in some programming languages. 2475 </para> 2476 2477 <para> 2478 As a consequence, it is unwise to use functions with side effects 2479 as part of complex expressions. It is particularly dangerous to 2480 rely on side effects or evaluation order in <literal>WHERE</literal> and <literal>HAVING</literal> clauses, 2481 since those clauses are extensively reprocessed as part of 2482 developing an execution plan. Boolean 2483 expressions (<literal>AND</literal>/<literal>OR</literal>/<literal>NOT</literal> combinations) in those clauses can be reorganized 2484 in any manner allowed by the laws of Boolean algebra. 2485 </para> 2486 2487 <para> 2488 When it is essential to force evaluation order, a <literal>CASE</literal> 2489 construct (see <xref linkend="functions-conditional"/>) can be 2490 used. For example, this is an untrustworthy way of trying to 2491 avoid division by zero in a <literal>WHERE</literal> clause: 2492<programlisting> 2493SELECT ... WHERE x > 0 AND y/x > 1.5; 2494</programlisting> 2495 But this is safe: 2496<programlisting> 2497SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; 2498</programlisting> 2499 A <literal>CASE</literal> construct used in this fashion will defeat optimization 2500 attempts, so it should only be done when necessary. (In this particular 2501 example, it would be better to sidestep the problem by writing 2502 <literal>y > 1.5*x</literal> instead.) 2503 </para> 2504 2505 <para> 2506 <literal>CASE</literal> is not a cure-all for such issues, however. 2507 One limitation of the technique illustrated above is that it does not 2508 prevent early evaluation of constant subexpressions. 2509 As described in <xref linkend="xfunc-volatility"/>, functions and 2510 operators marked <literal>IMMUTABLE</literal> can be evaluated when 2511 the query is planned rather than when it is executed. Thus for example 2512<programlisting> 2513SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab; 2514</programlisting> 2515 is likely to result in a division-by-zero failure due to the planner 2516 trying to simplify the constant subexpression, 2517 even if every row in the table has <literal>x > 0</literal> so that the 2518 <literal>ELSE</literal> arm would never be entered at run time. 2519 </para> 2520 2521 <para> 2522 While that particular example might seem silly, related cases that don't 2523 obviously involve constants can occur in queries executed within 2524 functions, since the values of function arguments and local variables 2525 can be inserted into queries as constants for planning purposes. 2526 Within <application>PL/pgSQL</application> functions, for example, using an 2527 <literal>IF</literal>-<literal>THEN</literal>-<literal>ELSE</literal> statement to protect 2528 a risky computation is much safer than just nesting it in a 2529 <literal>CASE</literal> expression. 2530 </para> 2531 2532 <para> 2533 Another limitation of the same kind is that a <literal>CASE</literal> cannot 2534 prevent evaluation of an aggregate expression contained within it, 2535 because aggregate expressions are computed before other 2536 expressions in a <literal>SELECT</literal> list or <literal>HAVING</literal> clause 2537 are considered. For example, the following query can cause a 2538 division-by-zero error despite seemingly having protected against it: 2539<programlisting> 2540SELECT CASE WHEN min(employees) > 0 2541 THEN avg(expenses / employees) 2542 END 2543 FROM departments; 2544</programlisting> 2545 The <function>min()</function> and <function>avg()</function> aggregates are computed 2546 concurrently over all the input rows, so if any row 2547 has <structfield>employees</structfield> equal to zero, the division-by-zero error 2548 will occur before there is any opportunity to test the result of 2549 <function>min()</function>. Instead, use a <literal>WHERE</literal> 2550 or <literal>FILTER</literal> clause to prevent problematic input rows from 2551 reaching an aggregate function in the first place. 2552 </para> 2553 </sect2> 2554 </sect1> 2555 2556 <sect1 id="sql-syntax-calling-funcs"> 2557 <title>Calling Functions</title> 2558 2559 <indexterm zone="sql-syntax-calling-funcs"> 2560 <primary>notation</primary> 2561 <secondary>functions</secondary> 2562 </indexterm> 2563 2564 <para> 2565 <productname>PostgreSQL</productname> allows functions that have named 2566 parameters to be called using either <firstterm>positional</firstterm> or 2567 <firstterm>named</firstterm> notation. Named notation is especially 2568 useful for functions that have a large number of parameters, since it 2569 makes the associations between parameters and actual arguments more 2570 explicit and reliable. 2571 In positional notation, a function call is written with 2572 its argument values in the same order as they are defined in the function 2573 declaration. In named notation, the arguments are matched to the 2574 function parameters by name and can be written in any order. 2575 For each notation, also consider the effect of function argument types, 2576 documented in <xref linkend="typeconv-func"/>. 2577 </para> 2578 2579 <para> 2580 In either notation, parameters that have default values given in the 2581 function declaration need not be written in the call at all. But this 2582 is particularly useful in named notation, since any combination of 2583 parameters can be omitted; while in positional notation parameters can 2584 only be omitted from right to left. 2585 </para> 2586 2587 <para> 2588 <productname>PostgreSQL</productname> also supports 2589 <firstterm>mixed</firstterm> notation, which combines positional and 2590 named notation. In this case, positional parameters are written first 2591 and named parameters appear after them. 2592 </para> 2593 2594 <para> 2595 The following examples will illustrate the usage of all three 2596 notations, using the following function definition: 2597<programlisting> 2598CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) 2599RETURNS text 2600AS 2601$$ 2602 SELECT CASE 2603 WHEN $3 THEN UPPER($1 || ' ' || $2) 2604 ELSE LOWER($1 || ' ' || $2) 2605 END; 2606$$ 2607LANGUAGE SQL IMMUTABLE STRICT; 2608</programlisting> 2609 Function <function>concat_lower_or_upper</function> has two mandatory 2610 parameters, <literal>a</literal> and <literal>b</literal>. Additionally 2611 there is one optional parameter <literal>uppercase</literal> which defaults 2612 to <literal>false</literal>. The <literal>a</literal> and 2613 <literal>b</literal> inputs will be concatenated, and forced to either 2614 upper or lower case depending on the <literal>uppercase</literal> 2615 parameter. The remaining details of this function 2616 definition are not important here (see <xref linkend="extend"/> for 2617 more information). 2618 </para> 2619 2620 <sect2 id="sql-syntax-calling-funcs-positional"> 2621 <title>Using Positional Notation</title> 2622 2623 <indexterm> 2624 <primary>function</primary> 2625 <secondary>positional notation</secondary> 2626 </indexterm> 2627 2628 <para> 2629 Positional notation is the traditional mechanism for passing arguments 2630 to functions in <productname>PostgreSQL</productname>. An example is: 2631<screen> 2632SELECT concat_lower_or_upper('Hello', 'World', true); 2633 concat_lower_or_upper 2634----------------------- 2635 HELLO WORLD 2636(1 row) 2637</screen> 2638 All arguments are specified in order. The result is upper case since 2639 <literal>uppercase</literal> is specified as <literal>true</literal>. 2640 Another example is: 2641<screen> 2642SELECT concat_lower_or_upper('Hello', 'World'); 2643 concat_lower_or_upper 2644----------------------- 2645 hello world 2646(1 row) 2647</screen> 2648 Here, the <literal>uppercase</literal> parameter is omitted, so it 2649 receives its default value of <literal>false</literal>, resulting in 2650 lower case output. In positional notation, arguments can be omitted 2651 from right to left so long as they have defaults. 2652 </para> 2653 </sect2> 2654 2655 <sect2 id="sql-syntax-calling-funcs-named"> 2656 <title>Using Named Notation</title> 2657 2658 <indexterm> 2659 <primary>function</primary> 2660 <secondary>named notation</secondary> 2661 </indexterm> 2662 2663 <para> 2664 In named notation, each argument's name is specified using 2665 <literal>=></literal> to separate it from the argument expression. 2666 For example: 2667<screen> 2668SELECT concat_lower_or_upper(a => 'Hello', b => 'World'); 2669 concat_lower_or_upper 2670----------------------- 2671 hello world 2672(1 row) 2673</screen> 2674 Again, the argument <literal>uppercase</literal> was omitted 2675 so it is set to <literal>false</literal> implicitly. One advantage of 2676 using named notation is that the arguments may be specified in any 2677 order, for example: 2678<screen> 2679SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true); 2680 concat_lower_or_upper 2681----------------------- 2682 HELLO WORLD 2683(1 row) 2684 2685SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World'); 2686 concat_lower_or_upper 2687----------------------- 2688 HELLO WORLD 2689(1 row) 2690</screen> 2691 </para> 2692 2693 <para> 2694 An older syntax based on ":=" is supported for backward compatibility: 2695<screen> 2696SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World'); 2697 concat_lower_or_upper 2698----------------------- 2699 HELLO WORLD 2700(1 row) 2701</screen> 2702 </para> 2703 </sect2> 2704 2705 <sect2 id="sql-syntax-calling-funcs-mixed"> 2706 <title>Using Mixed Notation</title> 2707 2708 <indexterm> 2709 <primary>function</primary> 2710 <secondary>mixed notation</secondary> 2711 </indexterm> 2712 2713 <para> 2714 The mixed notation combines positional and named notation. However, as 2715 already mentioned, named arguments cannot precede positional arguments. 2716 For example: 2717<screen> 2718SELECT concat_lower_or_upper('Hello', 'World', uppercase => true); 2719 concat_lower_or_upper 2720----------------------- 2721 HELLO WORLD 2722(1 row) 2723</screen> 2724 In the above query, the arguments <literal>a</literal> and 2725 <literal>b</literal> are specified positionally, while 2726 <literal>uppercase</literal> is specified by name. In this example, 2727 that adds little except documentation. With a more complex function 2728 having numerous parameters that have default values, named or mixed 2729 notation can save a great deal of writing and reduce chances for error. 2730 </para> 2731 2732 <note> 2733 <para> 2734 Named and mixed call notations currently cannot be used when calling an 2735 aggregate function (but they do work when an aggregate function is used 2736 as a window function). 2737 </para> 2738 </note> 2739 </sect2> 2740 </sect1> 2741 2742</chapter> 2743