doc/src/sgml/syntax.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.72 2002/10/24 17:48:54 petere Exp $
   3 -->
   4
   5 <chapter id="sql-syntax">
   6  <title>SQL Syntax</title>
   7
   8  <indexterm zone="sql-syntax">
   9   <primary>syntax</primary>
  10   <secondary>SQL</secondary>
  11  </indexterm>
  12
  13  <para>
  14   This chapter describes the syntax of SQL.  It forms the foundation
  15   for understanding the following chapters which will go into detail
  16   about how the SQL commands are applied to define and modify data.
  17  </para>
  18
  19  <para>
  20   We also advise users who are already familiar with SQL to read this
  21   chapter carefully because there are several rules and concepts that
  22   are implemented inconsistently among SQL databases or that are
  23   specific to <productname>PostgreSQL</productname>.
  24  </para>
  25
  26  <sect1 id="sql-syntax-lexical">
  27   <title>Lexical Structure</title>
  28
  29   <para>
  30    SQL input consists of a sequence of
  31    <firstterm>commands</firstterm>.  A command is composed of a
  32    sequence of <firstterm>tokens</firstterm>, terminated by a
  33    semicolon (<quote>;</quote>).  The end of the input stream also
  34    terminates a command.  Which tokens are valid depends on the syntax
  35    of the particular command.
  36   </para>
  37
  38   <para>
  39    A token can be a <firstterm>key word</firstterm>, an
  40    <firstterm>identifier</firstterm>, a <firstterm>quoted
  41    identifier</firstterm>, a <firstterm>literal</firstterm> (or
  42    constant), or a special character symbol.  Tokens are normally
  43    separated by whitespace (space, tab, newline), but need not be if
  44    there is no ambiguity (which is generally only the case if a
  45    special character is adjacent to some other token type).
  46   </para>
  47
  48   <para>
  49    Additionally, <firstterm>comments</firstterm> can occur in SQL
  50    input.  They are not tokens, they are effectively equivalent to
  51    whitespace.
  52   </para>
  53
  54    <para>
  55     For example, the following is (syntactically) valid SQL input:
  56 <programlisting>
  57 SELECT * FROM MY_TABLE;
  58 UPDATE MY_TABLE SET A = 5;
  59 INSERT INTO MY_TABLE VALUES (3, 'hi there');
  60 </programlisting>
  61     This is a sequence of three commands, one per line (although this
  62     is not required; more than one command can be on a line, and
  63     commands can usefully be split across lines).
  64    </para>
  65
  66   <para>
  67    The SQL syntax is not very consistent regarding what tokens
  68    identify commands and which are operands or parameters.  The first
  69    few tokens are generally the command name, so in the above example
  70    we would usually speak of a <quote>SELECT</quote>, an
  71    <quote>UPDATE</quote>, and an <quote>INSERT</quote> command.  But
  72    for instance the <command>UPDATE</command> command always requires
  73    a <token>SET</token> token to appear in a certain position, and
  74    this particular variation of <command>INSERT</command> also
  75    requires a <token>VALUES</token> in order to be complete.  The
  76    precise syntax rules for each command are described in the
  77    &cite-reference;.
  78   </para>
  79
  80   <sect2 id="sql-syntax-identifiers">
  81    <title>Identifiers and Key Words</title>
  82
  83    <indexterm zone="sql-syntax-identifiers">
  84     <primary>identifiers</primary>
  85    </indexterm>
  86
  87    <indexterm zone="sql-syntax-identifiers">
  88     <primary>key words</primary>
  89     <secondary>syntax</secondary>
  90    </indexterm>
  91
  92    <para>
  93     Tokens such as <token>SELECT</token>, <token>UPDATE</token>, or
  94     <token>VALUES</token> in the example above are examples of
  95     <firstterm>key words</firstterm>, that is, words that have a fixed
  96     meaning in the SQL language.  The tokens <token>MY_TABLE</token>
  97     and <token>A</token> are examples of
  98     <firstterm>identifiers</firstterm>.  They identify names of
  99     tables, columns, or other database objects, depending on the
 100     command they are used in.  Therefore they are sometimes simply
 101     called <quote>names</quote>.  Key words and identifiers have the
 102     same lexical structure, meaning that one cannot know whether a
 103     token is an identifier or a key word without knowing the language.
 104     A complete list of key words can be found in <xref
 105     linkend="sql-keywords-appendix">.
 106    </para>
 107
 108    <para>
 109     SQL identifiers and key words must begin with a letter
 110     (<literal>a</literal>-<literal>z</literal>, but also letters with
 111     diacritical marks and non-Latin letters) or an underscore
 112     (<literal>_</literal>).  Subsequent characters in an identifier or
 113     key word can be letters, digits
 114     (<literal>0</literal>-<literal>9</literal>), or underscores,
 115     although the SQL standard will not define a key word that contains
 116     digits or starts or ends with an underscore.
 117    </para>
 118
 119    <para>
 120     The system uses no more than <symbol>NAMEDATALEN</symbol>-1
 121     characters of an identifier; longer names can be written in
 122     commands, but they will be truncated.  By default,
 123     <symbol>NAMEDATALEN</symbol> is 64 so the maximum identifier length
 124     is 63 (but at the time the system is built,
 125     <symbol>NAMEDATALEN</symbol> can be changed in
 126     <filename>src/include/postgres_ext.h</filename>).
 127    </para>
 128
 129    <para>
 130     <indexterm>
 131      <primary>case sensitivity</primary>
 132      <secondary>SQL commands</secondary>
 133     </indexterm>
 134     Identifier and key word names are case insensitive.  Therefore
 135 <programlisting>
 136 UPDATE MY_TABLE SET A = 5;
 137 </programlisting>
 138     can equivalently be written as
 139 <programlisting>
 140 uPDaTE my_TabLE SeT a = 5;
 141 </programlisting>
 142     A convention often used is to write key words in upper
 143     case and names in lower case, e.g.,
 144 <programlisting>
 145 UPDATE my_table SET a = 5;
 146 </programlisting>
 147    </para>
 148
 149    <para>
 150     <indexterm>
 151      <primary>quotes</primary>
 152      <secondary>and identifiers</secondary>
 153     </indexterm>
 154     There is a second kind of identifier:  the <firstterm>delimited
 155     identifier</firstterm> or <firstterm>quoted
 156     identifier</firstterm>.  It is formed by enclosing an arbitrary
 157     sequence of characters in double-quotes
 158     (<literal>"</literal>). <!-- " font-lock mania --> A delimited
 159     identifier is always an identifier, never a key word.  So
 160     <literal>"select"</literal> could be used to refer to a column or
 161     table named <quote>select</quote>, whereas an unquoted
 162     <literal>select</literal> would be taken as a key word and
 163     would therefore provoke a parse error when used where a table or
 164     column name is expected.  The example can be written with quoted
 165     identifiers like this:
 166 <programlisting>
 167 UPDATE "my_table" SET "a" = 5;
 168 </programlisting>
 169    </para>
 170
 171    <para>
 172     Quoted identifiers can contain any character other than a double
 173     quote itself.  This allows constructing table or column names that
 174     would otherwise not be possible, such as ones containing spaces or
 175     ampersands.  The length limitation still applies.
 176    </para>
 177
 178    <para>
 179     Quoting an identifier also makes it case-sensitive, whereas
 180     unquoted names are always folded to lower case.  For example, the
 181     identifiers <literal>FOO</literal>, <literal>foo</literal> and
 182     <literal>"foo"</literal> are considered the same by
 183     <productname>PostgreSQL</productname>, but <literal>"Foo"</literal>
 184     and <literal>"FOO"</literal> are different from these three and
 185     each other.
 186     <footnote>
 187      <para>
 188       The folding of unquoted names to lower case in <productname>PostgreSQL</>
 189       is incompatible with the SQL standard, which says that unquoted
 190       names should be folded to upper case.  Thus, <literal>foo</literal>
 191       should be equivalent to <literal>"FOO"</literal> not
 192       <literal>"foo"</literal> according to the standard.  If you want to
 193       write portable applications you are advised to always quote a particular
 194       name or never quote it.
 195      </para>
 196     </footnote>
 197    </para>
 198   </sect2>
 199
 200
 201   <sect2 id="sql-syntax-constants">
 202    <title>Constants</title>
 203
 204    <indexterm zone="sql-syntax-constants">
 205     <primary>constants</primary>
 206    </indexterm>
 207
 208    <para>
 209     There are three kinds of <firstterm>implicitly-typed
 210     constants</firstterm> in <productname>PostgreSQL</productname>:
 211     strings, bit strings, and numbers.
 212     Constants can also be specified with explicit types, which can
 213     enable more accurate representation and more efficient handling by
 214     the system. The implicit constants are described below; explicit
 215     constants are discussed afterwards.
 216    </para>
 217
 218    <sect3 id="sql-syntax-strings">
 219     <title>String Constants</title>
 220
 221     <indexterm zone="sql-syntax-strings">
 222      <primary>character strings</primary>
 223      <secondary>constants</secondary>
 224     </indexterm>
 225
 226     <para>
 227      <indexterm>
 228       <primary>quotes</primary>
 229       <secondary>escaping</secondary>
 230      </indexterm>
 231      A string constant in SQL is an arbitrary sequence of characters
 232      bounded by single quotes (<quote>'</quote>), e.g., <literal>'This
 233      is a string'</literal>.  SQL allows single quotes to be embedded
 234      in strings by typing two adjacent single quotes (e.g.,
 235      <literal>'Dianne''s horse'</literal>).  In
 236      <productname>PostgreSQL</productname> single quotes may
 237      alternatively be escaped with a backslash (<quote>\</quote>,
 238      e.g., <literal>'Dianne\'s horse'</literal>).
 239     </para>
 240
 241     <para>
 242      C-style backslash escapes are also available:
 243      <literal>\b</literal> is a backspace, <literal>\f</literal> is a
 244      form feed, <literal>\n</literal> is a newline,
 245      <literal>\r</literal> is a carriage return, <literal>\t</literal>
 246      is a tab, and <literal>\<replaceable>xxx</replaceable></literal>,
 247      where <replaceable>xxx</replaceable> is an octal number, is the
 248      character with the corresponding ASCII code.  Any other character
 249      following a backslash is taken literally.  Thus, to include a
 250      backslash in a string constant, type two backslashes.
 251     </para>
 252
 253     <para>
 254      The character with the code zero cannot be in a string constant.
 255     </para>
 256
 257     <para>
 258      Two string constants that are only separated by whitespace
 259      <emphasis>with at least one newline</emphasis> are concatenated
 260      and effectively treated as if the string had been written in one
 261      constant.  For example:
 262 <programlisting>
 263 SELECT 'foo'
 264 'bar';
 265 </programlisting>
 266      is equivalent to
 267 <programlisting>
 268 SELECT 'foobar';
 269 </programlisting>
 270      but
 271 <programlisting>
 272 SELECT 'foo'      'bar';
 273 </programlisting>
 274      is not valid syntax.  (This slightly bizarre behavior is specified
 275      by <acronym>SQL9x</acronym>; <productname>PostgreSQL</productname> is
 276      following the standard.)
 277     </para>
 278    </sect3>
 279
 280    <sect3 id="sql-syntax-bit-strings">
 281     <title>Bit-String Constants</title>
 282
 283     <indexterm zone="sql-syntax-bit-strings">
 284      <primary>bit strings</primary>
 285      <secondary>constants</secondary>
 286     </indexterm>
 287
 288     <para>
 289      Bit-string constants look like string constants with a
 290      <literal>B</literal> (upper or lower case) immediately before the
 291      opening quote (no intervening whitespace), e.g.,
 292      <literal>B'1001'</literal>.  The only characters allowed within
 293      bit-string constants are <literal>0</literal> and
 294      <literal>1</literal>.
 295     </para>
 296
 297     <para>
 298      Alternatively, bit-string constants can be specified in hexadecimal
 299      notation, using a leading <literal>X</literal> (upper or lower case),
 300      e.g., <literal>X'1FF'</literal>.  This notation is equivalent to
 301      a bit-string constant with four binary digits for each hex digit.
 302     </para>
 303
 304     <para>
 305      Both forms of bit-string constant can be continued
 306      across lines in the same way as regular string constants.
 307     </para>
 308    </sect3>
 309
 310    <sect3>
 311     <title>Numeric Constants</title>
 312
 313     <indexterm>
 314      <primary>numeric</primary>
 315      <secondary>constants</secondary>
 316     </indexterm>
 317
 318     <para>
 319      Numeric constants are accepted in these general forms:
 320 <synopsis>
 321 <replaceable>digits</replaceable>
 322 <replaceable>digits</replaceable>.<optional><replaceable>digits</replaceable></optional><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
 323 <optional><replaceable>digits</replaceable></optional>.<replaceable>digits</replaceable><optional>e<optional>+-</optional><replaceable>digits</replaceable></optional>
 324 <replaceable>digits</replaceable>e<optional>+-</optional><replaceable>digits</replaceable>
 325 </synopsis>
 326      where <replaceable>digits</replaceable> is one or more decimal
 327      digits (0 through 9).  At least one digit must be before or after the
 328      decimal point, if one is used.  At least one digit must follow the
 329      exponent marker (<literal>e</literal>), if one is present.
 330      There may not be any spaces or other characters embedded in the
 331      constant.  Notice that any leading plus or minus sign is not actually
 332      considered part of the constant; it is an operator applied to the
 333      constant.
 334     </para>
 335
 336     <para>
 337      These are some examples of valid numeric constants:
 338 <literallayout>
 339 42
 340 3.5
 341 4.
 342 .001
 343 5e2
 344 1.925e-3
 345 </literallayout>
 346     </para>
 347
 348     <para>
 349      A numeric constant that contains neither a decimal point nor an
 350      exponent is initially presumed to be type <type>integer</> if its
 351      value fits in type <type>integer</> (32 bits); otherwise it is
 352      presumed to be type <type>bigint</> if its
 353      value fits in type <type>bigint</> (64 bits); otherwise it is
 354      taken to be type <type>numeric</>.  Constants that contain decimal
 355      points and/or exponents are always initially presumed to be type
 356      <type>numeric</>.
 357     </para>
 358
 359     <para>
 360      The initially assigned data type of a numeric constant is just a
 361      starting point for the type resolution algorithms.  In most
 362      cases the constant will be automatically coerced to the most
 363      appropriate type depending on context.  When necessary, you
 364      can force a numeric value to be interpreted as a specific
 365      data type by casting it.  For example, you can force a numeric
 366      value to be treated as type <type>real</> (<type>float4</>)
 367      by writing
 368
 369 <programlisting>
 370 REAL '1.23'  -- string style
 371 1.23::REAL   -- PostgreSQL (historical) style
 372 </programlisting>
 373      </para>
 374     </sect3>
 375
 376    <sect3 id="sql-syntax-constants-generic">
 377     <title>Constants of Other Types</title>
 378
 379     <indexterm>
 380      <primary>data types</primary>
 381      <secondary>constants</secondary>
 382     </indexterm>
 383
 384     <para>
 385      A constant of an <emphasis>arbitrary</emphasis> type can be
 386      entered using any one of the following notations:
 387 <synopsis>
 388 <replaceable>type</replaceable> '<replaceable>string</replaceable>'
 389 '<replaceable>string</replaceable>'::<replaceable>type</replaceable>
 390 CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
 391 </synopsis>
 392      The string's text is passed to the input conversion
 393      routine for the type called <replaceable>type</replaceable>. The
 394      result is a constant of the indicated type.  The explicit type
 395      cast may be omitted if there is no ambiguity as to the type the
 396      constant must be (for example, when it is passed as an argument
 397      to a non-overloaded function), in which case it is automatically
 398      coerced.
 399     </para>
 400
 401     <para>
 402      It is also possible to specify a type coercion using a function-like
 403      syntax:
 404 <synopsis>
 405 <replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
 406 </synopsis>
 407      but not all type names may be used in this way; see <xref
 408      linkend="sql-syntax-type-casts"> for details.
 409     </para>
 410
 411     <para>
 412      The <literal>::</literal>, <literal>CAST()</literal>, and
 413      function-call syntaxes can also be used to specify run-time type
 414      conversions of arbitrary expressions, as discussed in <xref
 415      linkend="sql-syntax-type-casts">.  But the form
 416      <replaceable>type</replaceable> '<replaceable>string</replaceable>'
 417      can only be used to specify the type of a literal constant.
 418      Another restriction on
 419      <replaceable>type</replaceable> '<replaceable>string</replaceable>'
 420      is that it does not work for array types; use <literal>::</literal>
 421      or <literal>CAST()</literal> to specify the type of an array constant.
 422     </para>
 423    </sect3>
 424
 425    <sect3>
 426     <title>Array constants</title>
 427
 428     <indexterm>
 429      <primary>arrays</primary>
 430      <secondary>constants</secondary>
 431     </indexterm>
 432
 433     <para>
 434      The general format of an array constant is the following:
 435 <synopsis>
 436 '{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
 437 </synopsis>
 438      where <replaceable>delim</replaceable> is the delimiter character
 439      for the type, as recorded in its <literal>pg_type</literal>
 440      entry.  (For all built-in types, this is the comma character
 441      <quote><literal>,</literal></>.)  Each <replaceable>val</replaceable> is either a constant
 442      of the array element type, or a subarray.  An example of an
 443      array constant is
 444 <programlisting>
 445 '{{1,2,3},{4,5,6},{7,8,9}}'
 446 </programlisting>
 447      This constant is a two-dimensional, 3-by-3 array consisting of three
 448      subarrays of integers.
 449     </para>
 450
 451     <para>
 452      Individual array elements can be placed between double-quote
 453      marks (<literal>"</literal>) <!-- " --> to avoid ambiguity
 454      problems with respect to whitespace.  Without quote marks, the
 455      array-value parser will skip leading whitespace.
 456     </para>
 457
 458     <para>
 459      (Array constants are actually only a special case of the generic
 460      type constants discussed in the previous section.  The constant
 461      is initially treated as a string and passed to the array input
 462      conversion routine.  An explicit type specification might be
 463      necessary.)
 464     </para>
 465    </sect3>
 466   </sect2>
 467
 468
 469   <sect2 id="sql-syntax-operators">
 470    <title>Operators</title>
 471
 472    <indexterm zone="sql-syntax-operators">
 473     <primary>operators</primary>
 474     <secondary>syntax</secondary>
 475    </indexterm>
 476
 477    <para>
 478     An operator is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
 479     (63 by default) characters from the following list:
 480 <literallayout>
 481 + - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ? $
 482 </literallayout>
 483
 484     There are a few restrictions on operator names, however:
 485     <itemizedlist>
 486      <listitem>
 487       <para>
 488        <literal>$</> (dollar) cannot be a single-character operator, although it
 489        can be part of a multiple-character operator name.
 490       </para>
 491      </listitem>
 492
 493      <listitem>
 494       <para>
 495        <literal>--</literal> and <literal>/*</literal> cannot appear
 496        anywhere in an operator name, since they will be taken as the
 497        start of a comment.
 498       </para>
 499      </listitem>
 500
 501      <listitem>
 502       <para>
 503        A multiple-character operator name cannot end in <literal>+</> or <literal>-</>,
 504        unless the name also contains at least one of these characters:
 505 <literallayout>
 506 ~ ! @ # % ^ &amp; | ` ? $
 507 </literallayout>
 508        For example, <literal>@-</literal> is an allowed operator name,
 509        but <literal>*-</literal> is not.  This restriction allows
 510        <productname>PostgreSQL</productname> to parse SQL-compliant
 511        queries without requiring spaces between tokens.
 512       </para>
 513      </listitem>
 514     </itemizedlist>
 515    </para>
 516
 517    <para>
 518     When working with non-SQL-standard operator names, you will usually
 519     need to separate adjacent operators with spaces to avoid ambiguity.
 520     For example, if you have defined a left unary operator named <literal>@</literal>,
 521     you cannot write <literal>X*@Y</literal>; you must write
 522     <literal>X* @Y</literal> to ensure that
 523     <productname>PostgreSQL</productname> reads it as two operator names
 524     not one.
 525    </para>
 526   </sect2>
 527
 528   <sect2>
 529    <title>Special Characters</title>
 530
 531   <para>
 532    Some characters that are not alphanumeric have a special meaning
 533    that is different from being an operator.  Details on the usage can
 534    be found at the location where the respective syntax element is
 535    described.  This section only exists to advise the existence and
 536    summarize the purposes of these characters.
 537
 538    <itemizedlist>
 539     <listitem>
 540      <para>
 541       A dollar sign (<literal>$</literal>) followed by digits is used
 542       to represent the positional parameters in the body of a function
 543       definition.  In other contexts the dollar sign may be part of an
 544       operator name.
 545      </para>
 546     </listitem>
 547
 548     <listitem>
 549      <para>
 550       Parentheses (<literal>()</literal>) have their usual meaning to
 551       group expressions and enforce precedence.  In some cases
 552       parentheses are required as part of the fixed syntax of a
 553       particular SQL command.
 554      </para>
 555     </listitem>
 556
 557     <listitem>
 558      <para>
 559       Brackets (<literal>[]</literal>) are used to select the elements
 560       of an array.  See <xref linkend="arrays"> for more information
 561       on arrays.
 562      </para>
 563     </listitem>
 564
 565     <listitem>
 566      <para>
 567       Commas (<literal>,</literal>) are used in some syntactical
 568       constructs to separate the elements of a list.
 569      </para>
 570     </listitem>
 571
 572     <listitem>
 573      <para>
 574       The semicolon (<literal>;</literal>) terminates an SQL command.
 575       It cannot appear anywhere within a command, except within a
 576       string constant or quoted identifier.
 577      </para>
 578     </listitem>
 579
 580     <listitem>
 581      <para>
 582       The colon (<literal>:</literal>) is used to select
 583       <quote>slices</quote> from arrays. (See <xref
 584       linkend="arrays">.)  In certain SQL dialects (such as Embedded
 585       SQL), the colon is used to prefix variable names.
 586      </para>
 587     </listitem>
 588
 589     <listitem>
 590      <para>
 591       The asterisk (<literal>*</literal>) has a special meaning when
 592       used in the <command>SELECT</command> command or with the
 593       <function>COUNT</function> aggregate function.
 594      </para>
 595     </listitem>
 596
 597     <listitem>
 598      <para>
 599       The period (<literal>.</literal>) is used in floating-point
 600       constants, and to separate schema, table, and column names.
 601      </para>
 602     </listitem>
 603    </itemizedlist>
 604
 605    </para>
 606   </sect2>
 607
 608   <sect2 id="sql-syntax-comments">
 609    <title>Comments</title>
 610
 611    <indexterm zone="sql-syntax-comments">
 612     <primary>comments</primary>
 613     <secondary>in SQL</secondary>
 614    </indexterm>
 615
 616    <para>
 617     A comment is an arbitrary sequence of characters beginning with
 618     double dashes and extending to the end of the line, e.g.:
 619 <programlisting>
 620 -- This is a standard SQL92 comment
 621 </programlisting>
 622    </para>
 623
 624    <para>
 625     Alternatively, C-style block comments can be used:
 626 <programlisting>
 627 /* multiline comment
 628  * with nesting: /* nested block comment */
 629  */
 630 </programlisting>
 631     where the comment begins with <literal>/*</literal> and extends to
 632     the matching occurrence of <literal>*/</literal>. These block
 633     comments nest, as specified in SQL99 but unlike C, so that one can
 634     comment out larger blocks of code that may contain existing block
 635     comments.
 636    </para>
 637
 638    <para>
 639     A comment is removed from the input stream before further syntax
 640     analysis and is effectively replaced by whitespace.
 641    </para>
 642   </sect2>
 643
 644   <sect2 id="sql-precedence">
 645    <title>Lexical Precedence</title>
 646
 647    <indexterm zone="sql-precedence">
 648     <primary>operators</primary>
 649     <secondary>precedence</secondary>
 650    </indexterm>
 651
 652    <para>
 653     The precedence and associativity of the operators is hard-wired
 654     into the parser.  Most operators have the same precedence and are
 655     left-associative.  This may lead to non-intuitive behavior; for
 656     example the Boolean operators <literal>&lt;</> and <literal>&gt;</> have a different
 657     precedence than the Boolean operators <literal>&lt;=</> and <literal>&gt;=</>.  Also,
 658     you will sometimes need to add parentheses when using combinations
 659     of binary and unary operators.  For instance
 660 <programlisting>
 661 SELECT 5 ! - 6;
 662 </programlisting>
 663    will be parsed as
 664 <programlisting>
 665 SELECT 5 ! (- 6);
 666 </programlisting>
 667     because the parser has no idea -- until it is too late -- that
 668     <token>!</token> is defined as a postfix operator, not an infix one.
 669     To get the desired behavior in this case, you must write
 670 <programlisting>
 671 SELECT (5 !) - 6;
 672 </programlisting>
 673     This is the price one pays for extensibility.
 674    </para>
 675
 676    <table tocentry="1">
 677     <title>Operator Precedence (decreasing)</title>
 678
 679     <tgroup cols="3">
 680      <thead>
 681       <row>
 682        <entry>Operator/Element</entry>
 683        <entry>Associativity</entry>
 684        <entry>Description</entry>
 685       </row>
 686      </thead>
 687
 688      <tbody>
 689       <row>
 690        <entry><token>.</token></entry>
 691        <entry>left</entry>
 692        <entry>table/column name separator</entry>
 693       </row>
 694
 695       <row>
 696        <entry><token>::</token></entry>
 697        <entry>left</entry>
 698        <entry><productname>PostgreSQL</productname>-style typecast</entry>
 699       </row>
 700
 701       <row>
 702        <entry><token>[</token> <token>]</token></entry>
 703        <entry>left</entry>
 704        <entry>array element selection</entry>
 705       </row>
 706
 707       <row>
 708        <entry><token>-</token></entry>
 709        <entry>right</entry>
 710        <entry>unary minus</entry>
 711       </row>
 712
 713       <row>
 714        <entry><token>^</token></entry>
 715        <entry>left</entry>
 716        <entry>exponentiation</entry>
 717       </row>
 718
 719       <row>
 720        <entry><token>*</token> <token>/</token> <token>%</token></entry>
 721        <entry>left</entry>
 722        <entry>multiplication, division, modulo</entry>
 723       </row>
 724
 725       <row>
 726        <entry><token>+</token> <token>-</token></entry>
 727        <entry>left</entry>
 728        <entry>addition, subtraction</entry>
 729       </row>
 730
 731       <row>
 732        <entry><token>IS</token></entry>
 733        <entry></entry>
 734        <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS UNKNOWN</>, <literal>IS NULL</></entry>
 735       </row>
 736
 737       <row>
 738        <entry><token>ISNULL</token></entry>
 739        <entry></entry>
 740        <entry>test for null</entry>
 741       </row>
 742
 743       <row>
 744        <entry><token>NOTNULL</token></entry>
 745        <entry></entry>
 746        <entry>test for not null</entry>
 747       </row>
 748
 749       <row>
 750        <entry>(any other)</entry>
 751        <entry>left</entry>
 752        <entry>all other native and user-defined operators</entry>
 753       </row>
 754
 755       <row>
 756        <entry><token>IN</token></entry>
 757        <entry></entry>
 758        <entry>set membership</entry>
 759       </row>
 760
 761       <row>
 762        <entry><token>BETWEEN</token></entry>
 763        <entry></entry>
 764        <entry>containment</entry>
 765       </row>
 766
 767       <row>
 768        <entry><token>OVERLAPS</token></entry>
 769        <entry></entry>
 770        <entry>time interval overlap</entry>
 771       </row>
 772
 773       <row>
 774        <entry><token>LIKE</token> <token>ILIKE</token> <token>SIMILAR</token></entry>
 775        <entry></entry>
 776        <entry>string pattern matching</entry>
 777       </row>
 778
 779       <row>
 780        <entry><token>&lt;</token> <token>&gt;</token></entry>
 781        <entry></entry>
 782        <entry>less than, greater than</entry>
 783       </row>
 784
 785       <row>
 786        <entry><token>=</token></entry>
 787        <entry>right</entry>
 788        <entry>equality, assignment</entry>
 789       </row>
 790
 791       <row>
 792        <entry><token>NOT</token></entry>
 793        <entry>right</entry>
 794        <entry>logical negation</entry>
 795       </row>
 796
 797       <row>
 798        <entry><token>AND</token></entry>
 799        <entry>left</entry>
 800        <entry>logical conjunction</entry>
 801       </row>
 802
 803       <row>
 804        <entry><token>OR</token></entry>
 805        <entry>left</entry>
 806        <entry>logical disjunction</entry>
 807       </row>
 808      </tbody>
 809     </tgroup>
 810    </table>
 811
 812    <para>
 813     Note that the operator precedence rules also apply to user-defined
 814     operators that have the same names as the built-in operators
 815     mentioned above.  For example, if you define a
 816     <quote>+</quote> operator for some custom data type it will have
 817     the same precedence as the built-in <quote>+</quote> operator, no
 818     matter what yours does.
 819    </para>
 820
 821    <para>
 822     When a schema-qualified operator name is used in the
 823     <literal>OPERATOR</> syntax, as for example in
 824 <programlisting>
 825 SELECT 3 OPERATOR(pg_catalog.+) 4;
 826 </programlisting>
 827     the <literal>OPERATOR</> construct is taken to have the default precedence
 828     shown above for <quote>any other</> operator.  This is true no matter
 829     which specific operator name appears inside <literal>OPERATOR()</>.
 830    </para>
 831   </sect2>
 832  </sect1>
 833
 834  <sect1 id="sql-expressions">
 835   <title>Value Expressions</title>
 836
 837   <para>
 838    Value expressions are used in a variety of contexts, such
 839    as in the target list of the <command>SELECT</command> command, as
 840    new column values in <command>INSERT</command> or
 841    <command>UPDATE</command>, or in search conditions in a number of
 842    commands.  The result of a value expression is sometimes called a
 843    <firstterm>scalar</firstterm>, to distinguish it from the result of
 844    a table expression (which is a table).  Value expressions are
 845    therefore also called <firstterm>scalar expressions</firstterm> (or
 846    even simply <firstterm>expressions</firstterm>).  The expression
 847    syntax allows the calculation of values from primitive parts using
 848    arithmetic, logical, set, and other operations.
 849   </para>
 850
 851   <para>
 852    A value expression is one of the following:
 853
 854    <itemizedlist>
 855     <listitem>
 856      <para>
 857       A constant or literal value; see <xref linkend="sql-syntax-constants">.
 858      </para>
 859     </listitem>
 860
 861     <listitem>
 862      <para>
 863       A column reference.
 864      </para>
 865     </listitem>
 866
 867     <listitem>
 868      <para>
 869       A positional parameter reference, in the body of a function declaration.
 870      </para>
 871     </listitem>
 872
 873     <listitem>
 874      <para>
 875       An operator invocation.
 876      </para>
 877     </listitem>
 878
 879     <listitem>
 880      <para>
 881       A function call.
 882      </para>
 883     </listitem>
 884
 885     <listitem>
 886      <para>
 887       An aggregate expression.
 888      </para>
 889     </listitem>
 890
 891     <listitem>
 892      <para>
 893       A type cast.
 894      </para>
 895     </listitem>
 896
 897     <listitem>
 898      <para>
 899       A scalar subquery.
 900      </para>
 901     </listitem>
 902
 903     <listitem>
 904 <synopsis>( <replaceable>expression</replaceable> )</synopsis>
 905      <para>
 906       Parentheses are used to group subexpressions and override precedence.
 907      </para>
 908     </listitem>
 909    </itemizedlist>
 910   </para>
 911
 912   <para>
 913    In addition to this list, there are a number of constructs that can
 914    be classified as an expression but do not follow any general syntax
 915    rules.  These generally have the semantics of a function or
 916    operator and are explained in the appropriate location in <xref
 917    linkend="functions">.  An example is the <literal>IS NULL</literal>
 918    clause.
 919   </para>
 920
 921   <para>
 922    We have already discussed constants in <xref
 923    linkend="sql-syntax-constants">.  The following sections discuss
 924    the remaining options.
 925   </para>
 926
 927   <sect2>
 928    <title>Column References</title>
 929
 930    <para>
 931     A column can be referenced in the form:
 932 <synopsis>
 933 <replaceable>correlation</replaceable>.<replaceable>columnname</replaceable> `['<replaceable>subscript</replaceable>`]'
 934 </synopsis>
 935
 936     <replaceable>correlation</replaceable> is the name of a
 937     table (possibly qualified), or an alias for a table defined by means of a
 938     FROM clause, or
 939     the key words <literal>NEW</literal> or <literal>OLD</literal>.
 940     (NEW and OLD can only appear in rules,
 941     while other correlation names can be used in any SQL statement.)
 942     The correlation name and separating dot may be omitted if the column name
 943     is unique
 944     across all the tables being used in the current query.  If
 945     <replaceable>column</replaceable> is of an array type, then the
 946     optional <replaceable>subscript</replaceable> selects a specific
 947     element or elements in the array.  If no subscript is provided, then the
 948     whole array is selected.  (See <xref linkend="arrays"> for more about
 949     arrays.)
 950    </para>
 951   </sect2>
 952
 953   <sect2>
 954    <title>Positional Parameters</title>
 955
 956    <para>
 957     A positional parameter reference is used to indicate a parameter
 958     that is supplied externally to an SQL statement.  Parameters are
 959     used in SQL function definitions and in prepared queries.
 960     The form of a parameter reference is:
 961 <synopsis>
 962 $<replaceable>number</replaceable>
 963 </synopsis>
 964    </para>
 965
 966    <para>
 967     For example, consider the definition of a function,
 968     <function>dept</function>, as
 969
 970 <programlisting>
 971 CREATE FUNCTION dept (text) RETURNS dept
 972   AS 'SELECT * FROM dept WHERE name = $1'
 973   LANGUAGE SQL;
 974 </programlisting>
 975
 976     Here the <literal>$1</literal> will be replaced by the first
 977     function argument when the function is invoked.
 978    </para>
 979   </sect2>
 980
 981   <sect2>
 982    <title>Operator Invocations</title>
 983
 984    <para>
 985     There are three possible syntaxes for an operator invocation:
 986     <simplelist>
 987      <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
 988      <member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
 989      <member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
 990     </simplelist>
 991     where the <replaceable>operator</replaceable> token follows the syntax
 992     rules of <xref linkend="sql-syntax-operators">, or is one of the
 993     keywords <token>AND</token>, <token>OR</token>, and
 994     <token>NOT</token>, or is a qualified operator name
 995 <synopsis>
 996     <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operatorname</><literal>)</>
 997 </synopsis>
 998     Which particular operators exist and whether
 999     they are unary or binary depends on what operators have been
1000     defined by the system or the user.  <xref linkend="functions">
1001     describes the built-in operators.
1002    </para>
1003   </sect2>
1004
1005   <sect2>
1006    <title>Function Calls</title>
1007
1008    <para>
1009     The syntax for a function call is the name of a function
1010     (possibly qualified with a schema name), followed by its argument list
1011     enclosed in parentheses:
1012
1013 <synopsis>
1014 <replaceable>function</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional> )
1015 </synopsis>
1016    </para>
1017
1018    <para>
1019     For example, the following computes the square root of 2:
1020 <programlisting>
1021 sqrt(2)
1022 </programlisting>
1023    </para>
1024
1025    <para>
1026     The list of built-in functions is in <xref linkend="functions">.
1027     Other functions may be added by the user.
1028    </para>
1029   </sect2>
1030
1031   <sect2 id="syntax-aggregates">
1032    <title>Aggregate Expressions</title>
1033
1034    <indexterm zone="syntax-aggregates">
1035     <primary>aggregate functions</primary>
1036    </indexterm>
1037
1038    <para>
1039     An <firstterm>aggregate expression</firstterm> represents the
1040     application of an aggregate function across the rows selected by a
1041     query.  An aggregate function reduces multiple inputs to a single
1042     output value, such as the sum or average of the inputs.  The
1043     syntax of an aggregate expression is one of the following:
1044
1045     <simplelist>
1046      <member><replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>)</member>
1047      <member><replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>)</member>
1048      <member><replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)</member>
1049      <member><replaceable>aggregate_name</replaceable> ( * )</member>
1050     </simplelist>
1051
1052     where <replaceable>aggregate_name</replaceable> is a previously
1053     defined aggregate (possibly a qualified name), and
1054     <replaceable>expression</replaceable> is
1055     any value expression that does not itself contain an aggregate
1056     expression.
1057    </para>
1058
1059    <para>
1060     The first form of aggregate expression invokes the aggregate
1061     across all input rows for which the given expression yields a
1062     non-null value.  (Actually, it is up to the aggregate function
1063     whether to ignore null values or not --- but all the standard ones do.)
1064     The second form is the same as the first, since
1065     <literal>ALL</literal> is the default.  The third form invokes the
1066     aggregate for all distinct non-null values of the expression found
1067     in the input rows.  The last form invokes the aggregate once for
1068     each input row regardless of null or non-null values; since no
1069     particular input value is specified, it is generally only useful
1070     for the <function>count()</function> aggregate function.
1071    </para>
1072
1073    <para>
1074     For example, <literal>count(*)</literal> yields the total number
1075     of input rows; <literal>count(f1)</literal> yields the number of
1076     input rows in which <literal>f1</literal> is non-null;
1077     <literal>count(distinct f1)</literal> yields the number of
1078     distinct non-null values of <literal>f1</literal>.
1079    </para>
1080
1081    <para>
1082     The predefined aggregate functions are described in <xref
1083     linkend="functions-aggregate">.  Other aggregate functions may be added
1084     by the user.
1085    </para>
1086   </sect2>
1087
1088   <sect2 id="sql-syntax-type-casts">
1089    <title>Type Casts</title>
1090
1091     <indexterm>
1092      <primary>data types</primary>
1093      <secondary>type casts</secondary>
1094     </indexterm>
1095
1096    <para>
1097     A type cast specifies a conversion from one data type to another.
1098     <productname>PostgreSQL</productname> accepts two equivalent syntaxes
1099     for type casts:
1100 <synopsis>
1101 CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1102 <replaceable>expression</replaceable>::<replaceable>type</replaceable>
1103 </synopsis>
1104     The <literal>CAST</> syntax conforms to SQL92; the syntax with
1105     <literal>::</literal> is historical <productname>PostgreSQL</productname>
1106     usage.
1107    </para>
1108
1109    <para>
1110     When a cast is applied to a value expression of a known type, it
1111     represents a run-time type conversion.  The cast will succeed only
1112     if a suitable type conversion function is available.  Notice that this
1113     is subtly different from the use of casts with constants, as shown in
1114     <xref linkend="sql-syntax-constants-generic">.  A cast applied to an
1115     unadorned string literal represents the initial assignment of a type
1116     to a literal constant value, and so it will succeed for any type
1117     (if the contents of the string literal are acceptable input syntax for the
1118     data type).
1119    </para>
1120
1121    <para>
1122     An explicit type cast may usually be omitted if there is no ambiguity as
1123     to the type that a value expression must produce (for example, when it is
1124     assigned to a table column); the system will automatically apply a
1125     type cast in such cases.  However, automatic casting is only done for
1126     cast functions that are marked <quote>OK to apply implicitly</>
1127     in the system catalogs.  Other cast functions must be invoked with
1128     explicit casting syntax.  This restriction is intended to prevent
1129     surprising conversions from being applied silently.
1130    </para>
1131
1132    <para>
1133     It is also possible to specify a type cast using a function-like
1134     syntax:
1135 <synopsis>
1136 <replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1137 </synopsis>
1138     However, this only works for types whose names are also valid as
1139     function names.  For example, <literal>double precision</literal>
1140     can't be used this way, but the equivalent <literal>float8</literal>
1141     can.  Also, the names <literal>interval</>, <literal>time</>, and
1142     <literal>timestamp</> can only be used in this fashion if they are
1143     double-quoted, because of syntactic conflicts.  Therefore, the use of
1144     the function-like cast syntax leads to inconsistencies and should
1145     probably be avoided in new applications.
1146    </para>
1147   </sect2>
1148
1149   <sect2>
1150    <title>Scalar Subqueries</title>
1151
1152    <para>
1153     A scalar subquery is an ordinary
1154     <command>SELECT</command> in parentheses that returns exactly one
1155     row with one column.  The <command>SELECT</command> query is executed
1156     and the single returned value is used in the surrounding value expression.
1157     It is an error to use a query that
1158     returns more than one row or more than one column as a scalar subquery.
1159     (But if, during a particular execution, the subquery returns no rows,
1160     there is no error; the scalar result is taken to be null.)
1161     The subquery can refer to variables from the surrounding query,
1162     which will act as constants during any one evaluation of the subquery.
1163     See also <xref linkend="functions-subquery">.
1164    </para>
1165
1166    <para>
1167     For example, the following finds the largest city population in each
1168     state:
1169 <programlisting>
1170 SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1171 FROM states;
1172 </programlisting>
1173    </para>
1174   </sect2>
1175
1176   <sect2>
1177    <title>Expression Evaluation</title>
1178
1179    <para>
1180     The order of evaluation of subexpressions is not defined.  In
1181     particular, the inputs of an operator or function are not necessarily
1182     evaluated left-to-right or in any other fixed order.
1183    </para>
1184
1185    <para>
1186     Furthermore, if the result of an expression can be determined by
1187     evaluating only some parts of it, then other subexpressions
1188     might not be evaluated at all.  For instance, if one wrote
1189 <programlisting>
1190 SELECT true OR somefunc();
1191 </programlisting>
1192     then <literal>somefunc()</literal> would (probably) not be called
1193     at all. The same would be the case if one wrote
1194 <programlisting>
1195 SELECT somefunc() OR true;
1196 </programlisting>
1197     Note that this is not the same as the left-to-right
1198     <quote>short-circuiting</quote> of Boolean operators that is found
1199     in some programming languages.
1200    </para>
1201
1202    <para>
1203     As a consequence, it is unwise to use functions with side effects
1204     as part of complex expressions.  It is particularly dangerous to
1205     rely on side effects or evaluation order in WHERE and HAVING clauses,
1206     since those clauses are extensively reprocessed as part of
1207     developing an execution plan.  Boolean
1208     expressions (AND/OR/NOT combinations) in those clauses may be reorganized
1209     in any manner allowed by the laws of Boolean algebra.
1210    </para>
1211
1212    <para>
1213     When it is essential to force evaluation order, a CASE construct may
1214     be used.  For example, this is an untrustworthy way of trying to
1215     avoid division by zero in a WHERE clause:
1216 <programlisting>
1217 SELECT ... WHERE x &lt;&gt; 0 AND y/x &gt; 1.5;
1218 </programlisting>
1219     but this is safe:
1220 <programlisting>
1221 SELECT ... WHERE CASE WHEN x &lt;&gt; 0 THEN y/x &gt; 1.5 ELSE false END;
1222 </programlisting>
1223     A CASE construct used in this fashion will defeat optimization attempts,
1224     so it should only be done when necessary.
1225    </para>
1226   </sect2>
1227  </sect1>
1228
1229 </chapter>
1230
1231 <!-- Keep this comment at the end of the file
1232 Local variables:
1233 mode:sgml
1234 sgml-omittag:nil
1235 sgml-shorttag:t
1236 sgml-minimize-attributes:nil
1237 sgml-always-quote-attributes:t
1238 sgml-indent-step:1
1239 sgml-indent-data:t
1240 sgml-parent-document:nil
1241 sgml-default-dtd-file:"./reference.ced"
1242 sgml-exposed-tags:nil
1243 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1244 sgml-local-ecat-files:nil
1245 End:
1246 -->