doc/src/sgml/ref/select.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-SELECT">
   7  <refmeta>
   8   <refentrytitle id="sql-select-title">SELECT</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>SELECT</refname>
  15   <refname>TABLE</refname>
  16   <refname>WITH</refname>
  17   <refpurpose>retrieve rows from a table or view</refpurpose>
  18  </refnamediv>
  19
  20  <indexterm zone="sql-select">
  21   <primary>SELECT</primary>
  22  </indexterm>
  23
  24  <indexterm zone="sql-select">
  25   <primary>TABLE command</primary>
  26  </indexterm>
  27
  28  <indexterm zone="sql-select">
  29   <primary>WITH</primary>
  30   <secondary>in SELECT</secondary>
  31  </indexterm>
  32
  33  <refsynopsisdiv>
  34 <synopsis>
  35 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
  36 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
  37     * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
  38     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
  39     [ WHERE <replaceable class="parameter">condition</replaceable> ]
  40     [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
  41     [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
  42     [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
  43     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
  44     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
  45     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
  46     [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
  47     [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
  48     [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
  49
  50 where <replaceable class="parameter">from_item</replaceable> can be one of:
  51
  52     [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
  53     ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
  54     <replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
  55     <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]
  56     <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
  57     <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
  58
  59 and <replaceable class="parameter">with_query</replaceable> is:
  60
  61     <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
  62
  63 TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable class="parameter">with_query_name</replaceable> }
  64 </synopsis>
  65
  66  </refsynopsisdiv>
  67
  68  <refsect1>
  69   <title>Description</title>
  70
  71   <para>
  72    <command>SELECT</command> retrieves rows from zero or more tables.
  73    The general processing of <command>SELECT</command> is as follows:
  74
  75    <orderedlist>
  76     <listitem>
  77      <para>
  78       All queries in the <literal>WITH</literal> list are computed.
  79       These effectively serve as temporary tables that can be referenced
  80       in the <literal>FROM</literal> list.  A <literal>WITH</literal> query
  81       that is referenced more than once in <literal>FROM</literal> is
  82       computed only once.
  83       (See <xref linkend="sql-with" endterm="sql-with-title"> below.)
  84      </para>
  85     </listitem>
  86
  87     <listitem>
  88      <para>
  89       All elements in the <literal>FROM</literal> list are computed.
  90       (Each element in the <literal>FROM</literal> list is a real or
  91       virtual table.)  If more than one element is specified in the
  92       <literal>FROM</literal> list, they are cross-joined together.
  93       (See <xref linkend="sql-from" endterm="sql-from-title"> below.)
  94      </para>
  95     </listitem>
  96
  97     <listitem>
  98      <para>
  99       If the <literal>WHERE</literal> clause is specified, all rows
 100       that do not satisfy the condition are eliminated from the
 101       output.  (See <xref linkend="sql-where"
 102       endterm="sql-where-title"> below.)
 103      </para>
 104     </listitem>
 105
 106     <listitem>
 107      <para>
 108       If the <literal>GROUP BY</literal> clause is specified, the
 109       output is divided into groups of rows that match on one or more
 110       values.  If the <literal>HAVING</literal> clause is present, it
 111       eliminates groups that do not satisfy the given condition.  (See
 112       <xref linkend="sql-groupby" endterm="sql-groupby-title"> and
 113       <xref linkend="sql-having" endterm="sql-having-title"> below.)
 114      </para>
 115     </listitem>
 116
 117     <listitem>
 118      <para>
 119       The actual output rows are computed using the
 120       <command>SELECT</command> output expressions for each selected
 121       row.  (See
 122       <xref linkend="sql-select-list" endterm="sql-select-list-title">
 123       below.)
 124      </para>
 125     </listitem>
 126
 127     <listitem>
 128      <para>
 129       Using the operators <literal>UNION</literal>,
 130       <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
 131       output of more than one <command>SELECT</command> statement can
 132       be combined to form a single result set.  The
 133       <literal>UNION</literal> operator returns all rows that are in
 134       one or both of the result sets.  The
 135       <literal>INTERSECT</literal> operator returns all rows that are
 136       strictly in both result sets.  The <literal>EXCEPT</literal>
 137       operator returns the rows that are in the first result set but
 138       not in the second.  In all three cases, duplicate rows are
 139       eliminated unless <literal>ALL</literal> is specified. (See
 140       <xref linkend="sql-union" endterm="sql-union-title">, <xref
 141       linkend="sql-intersect" endterm="sql-intersect-title">, and
 142       <xref linkend="sql-except" endterm="sql-except-title"> below.)
 143      </para>
 144     </listitem>
 145
 146     <listitem>
 147      <para>
 148       If the <literal>ORDER BY</literal> clause is specified, the
 149       returned rows are sorted in the specified order.  If
 150       <literal>ORDER BY</literal> is not given, the rows are returned
 151       in whatever order the system finds fastest to produce.  (See
 152       <xref linkend="sql-orderby" endterm="sql-orderby-title"> below.)
 153      </para>
 154     </listitem>
 155
 156     <listitem>
 157      <para>
 158       <literal>DISTINCT</literal> eliminates duplicate rows from the
 159       result.  <literal>DISTINCT ON</literal> eliminates rows that
 160       match on all the specified expressions.  <literal>ALL</literal>
 161       (the default) will return all candidate rows, including
 162       duplicates.  (See <xref linkend="sql-distinct"
 163       endterm="sql-distinct-title"> below.)
 164      </para>
 165     </listitem>
 166
 167     <listitem>
 168      <para>
 169       If the <literal>LIMIT</literal> (or <literal>FETCH FIRST</literal>) or <literal>OFFSET</literal>
 170       clause is specified, the <command>SELECT</command> statement
 171       only returns a subset of the result rows. (See <xref
 172       linkend="sql-limit" endterm="sql-limit-title"> below.)
 173      </para>
 174     </listitem>
 175
 176     <listitem>
 177      <para>
 178       If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
 179       is specified, the
 180       <command>SELECT</command> statement locks the selected rows
 181       against concurrent updates.  (See <xref linkend="sql-for-update-share"
 182       endterm="sql-for-update-share-title"> below.)
 183      </para>
 184     </listitem>
 185    </orderedlist>
 186   </para>
 187
 188   <para>
 189    You must have <literal>SELECT</literal> privilege on each column used
 190    in a <command>SELECT</> command.  The use of <literal>FOR UPDATE</literal>
 191    or <literal>FOR SHARE</literal> requires
 192    <literal>UPDATE</literal> privilege as well (for at least one column
 193    of each table so selected).
 194   </para>
 195  </refsect1>
 196
 197  <refsect1>
 198   <title>Parameters</title>
 199
 200   <refsect2 id="SQL-WITH">
 201    <title id="sql-with-title"><literal>WITH</literal> Clause</title>
 202
 203    <para>
 204     The <literal>WITH</literal> clause allows you to specify one or more
 205     subqueries that can be referenced by name in the primary query.
 206     The subqueries effectively act as temporary tables or views
 207     for the duration of the primary query.
 208    </para>
 209
 210    <para>
 211     A name (without schema qualification) must be specified for each
 212     <literal>WITH</literal> query.  Optionally, a list of column names
 213     can be specified; if this is omitted,
 214     the column names are inferred from the subquery.
 215    </para>
 216
 217    <para>
 218     If <literal>RECURSIVE</literal> is specified, it allows a
 219     subquery to reference itself by name.  Such a subquery must have
 220     the form
 221 <synopsis>
 222 <replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL ] <replaceable class="parameter">recursive_term</replaceable>
 223 </synopsis>
 224     where the recursive self-reference must appear on the right-hand
 225     side of the <literal>UNION</>.  Only one recursive self-reference
 226     is permitted per query.
 227    </para>
 228
 229    <para>
 230     Another effect of <literal>RECURSIVE</literal> is that
 231     <literal>WITH</literal> queries need not be ordered: a query
 232     can reference another one that is later in the list.  (However,
 233     circular references, or mutual recursion, are not implemented.)
 234     Without <literal>RECURSIVE</literal>, <literal>WITH</literal> queries
 235     can only reference sibling <literal>WITH</literal> queries
 236     that are earlier in the <literal>WITH</literal> list.
 237    </para>
 238
 239    <para>
 240     A useful property of <literal>WITH</literal> queries is that they
 241     are evaluated only once per execution of the primary query,
 242     even if the primary query refers to them more than once.
 243    </para>
 244
 245    <para>
 246     See <xref linkend="queries-with"> for additional information.
 247    </para>
 248   </refsect2>
 249
 250   <refsect2 id="SQL-FROM">
 251    <title id="sql-from-title"><literal>FROM</literal> Clause</title>
 252
 253    <para>
 254     The <literal>FROM</literal> clause specifies one or more source
 255     tables for the <command>SELECT</command>.  If multiple sources are
 256     specified, the result is the Cartesian product (cross join) of all
 257     the sources.  But usually qualification conditions
 258     are added to restrict the returned rows to a small subset of the
 259     Cartesian product.
 260    </para>
 261
 262    <para>
 263     The <literal>FROM</literal> clause can contain the following
 264     elements:
 265
 266     <variablelist>
 267      <varlistentry>
 268       <term><replaceable class="parameter">table_name</replaceable></term>
 269       <listitem>
 270        <para>
 271         The name (optionally schema-qualified) of an existing table or
 272         view.  If <literal>ONLY</> is specified, only that table is
 273         scanned.  If <literal>ONLY</> is not specified, the table and
 274         any descendant tables are scanned.
 275        </para>
 276       </listitem>
 277      </varlistentry>
 278
 279      <varlistentry>
 280       <term><replaceable class="parameter">alias</replaceable></term>
 281       <listitem>
 282        <para>
 283         A substitute name for the <literal>FROM</> item containing the
 284         alias.  An alias is used for brevity or to eliminate ambiguity
 285         for self-joins (where the same table is scanned multiple
 286         times).  When an alias is provided, it completely hides the
 287         actual name of the table or function; for example given
 288         <literal>FROM foo AS f</>, the remainder of the
 289         <command>SELECT</command> must refer to this <literal>FROM</>
 290         item as <literal>f</> not <literal>foo</>.  If an alias is
 291         written, a column alias list can also be written to provide
 292         substitute names for one or more columns of the table.
 293        </para>
 294       </listitem>
 295      </varlistentry>
 296
 297      <varlistentry>
 298       <term><replaceable class="parameter">select</replaceable></term>
 299       <listitem>
 300        <para>
 301         A sub-<command>SELECT</command> can appear in the
 302         <literal>FROM</literal> clause.  This acts as though its
 303         output were created as a temporary table for the duration of
 304         this single <command>SELECT</command> command.  Note that the
 305         sub-<command>SELECT</command> must be surrounded by
 306         parentheses, and an alias <emphasis>must</emphasis> be
 307         provided for it.  A
 308         <xref linkend="sql-values" endterm="sql-values-title"> command
 309         can also be used here.
 310        </para>
 311       </listitem>
 312      </varlistentry>
 313
 314      <varlistentry>
 315       <term><replaceable class="parameter">with_query_name</replaceable></term>
 316       <listitem>
 317        <para>
 318         A <literal>WITH</> query is referenced by writing its name,
 319         just as though the query's name were a table name.  (In fact,
 320         the <literal>WITH</> query hides any real table of the same name
 321         for the purposes of the primary query.  If necessary, you can
 322         refer to a real table of the same name by schema-qualifying
 323         the table's name.)
 324         An alias can be provided in the same way as for a table.
 325        </para>
 326       </listitem>
 327      </varlistentry>
 328
 329      <varlistentry>
 330       <term><replaceable class="parameter">function_name</replaceable></term>
 331       <listitem>
 332        <para>
 333         Function calls can appear in the <literal>FROM</literal>
 334         clause.  (This is especially useful for functions that return
 335         result sets, but any function can be used.)  This acts as
 336         though its output were created as a temporary table for the
 337         duration of this single <command>SELECT</command> command. An
 338         alias can also be used. If an alias is written, a column alias
 339         list can also be written to provide substitute names for one
 340         or more attributes of the function's composite return type. If
 341         the function has been defined as returning the <type>record</>
 342         data type, then an alias or the key word <literal>AS</> must
 343         be present, followed by a column definition list in the form
 344         <literal>( <replaceable
 345         class="parameter">column_name</replaceable> <replaceable
 346         class="parameter">data_type</replaceable> <optional>, ... </>
 347         )</literal>.  The column definition list must match the actual
 348         number and types of columns returned by the function.
 349        </para>
 350       </listitem>
 351      </varlistentry>
 352
 353      <varlistentry>
 354       <term><replaceable class="parameter">join_type</replaceable></term>
 355       <listitem>
 356        <para>
 357         One of
 358         <itemizedlist>
 359          <listitem>
 360           <para><literal>[ INNER ] JOIN</literal></para>
 361          </listitem>
 362          <listitem>
 363           <para><literal>LEFT [ OUTER ] JOIN</literal></para>
 364          </listitem>
 365          <listitem>
 366           <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
 367          </listitem>
 368          <listitem>
 369           <para><literal>FULL [ OUTER ] JOIN</literal></para>
 370          </listitem>
 371          <listitem>
 372           <para><literal>CROSS JOIN</literal></para>
 373          </listitem>
 374         </itemizedlist>
 375
 376         For the <literal>INNER</> and <literal>OUTER</> join types, a
 377         join condition must be specified, namely exactly one of
 378         <literal>NATURAL</>, <literal>ON <replaceable
 379         class="parameter">join_condition</replaceable></literal>, or
 380         <literal>USING (<replaceable
 381         class="parameter">join_column</replaceable> [, ...])</literal>.
 382         See below for the meaning.  For <literal>CROSS JOIN</literal>,
 383         none of these clauses can appear.
 384        </para>
 385
 386        <para>
 387         A <literal>JOIN</literal> clause combines two
 388         <literal>FROM</> items.  Use parentheses if necessary to
 389         determine the order of nesting.  In the absence of parentheses,
 390         <literal>JOIN</literal>s nest left-to-right.  In any case
 391         <literal>JOIN</literal> binds more tightly than the commas
 392         separating <literal>FROM</> items.
 393        </para>
 394
 395        <para>
 396         <literal>CROSS JOIN</> and <literal>INNER JOIN</literal>
 397         produce a simple Cartesian product, the same result as you get from
 398         listing the two items at the top level of <literal>FROM</>,
 399         but restricted by the join condition (if any).
 400         <literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON
 401         (TRUE)</>, that is, no rows are removed by qualification.
 402         These join types are just a notational convenience, since they
 403         do nothing you couldn't do with plain <literal>FROM</> and
 404         <literal>WHERE</>.
 405        </para>
 406
 407        <para>
 408         <literal>LEFT OUTER JOIN</> returns all rows in the qualified
 409         Cartesian product (i.e., all combined rows that pass its join
 410         condition), plus one copy of each row in the left-hand table
 411         for which there was no right-hand row that passed the join
 412         condition.  This left-hand row is extended to the full width
 413         of the joined table by inserting null values for the
 414         right-hand columns.  Note that only the <literal>JOIN</>
 415         clause's own condition is considered while deciding which rows
 416         have matches.  Outer conditions are applied afterwards.
 417        </para>
 418
 419        <para>
 420         Conversely, <literal>RIGHT OUTER JOIN</> returns all the
 421         joined rows, plus one row for each unmatched right-hand row
 422         (extended with nulls on the left).  This is just a notational
 423         convenience, since you could convert it to a <literal>LEFT
 424         OUTER JOIN</> by switching the left and right inputs.
 425        </para>
 426
 427        <para>
 428         <literal>FULL OUTER JOIN</> returns all the joined rows, plus
 429         one row for each unmatched left-hand row (extended with nulls
 430         on the right), plus one row for each unmatched right-hand row
 431         (extended with nulls on the left).
 432        </para>
 433       </listitem>
 434      </varlistentry>
 435
 436      <varlistentry>
 437       <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>
 438       <listitem>
 439        <para>
 440         <replaceable class="parameter">join_condition</replaceable> is
 441         an expression resulting in a value of type
 442         <type>boolean</type> (similar to a <literal>WHERE</literal>
 443         clause) that specifies which rows in a join are considered to
 444         match.
 445        </para>
 446       </listitem>
 447      </varlistentry>
 448
 449      <varlistentry>
 450       <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] )</literal></term>
 451       <listitem>
 452        <para>
 453         A clause of the form <literal>USING ( a, b, ... )</literal> is
 454         shorthand for <literal>ON left_table.a = right_table.a AND
 455         left_table.b = right_table.b ...</literal>.  Also,
 456         <literal>USING</> implies that only one of each pair of
 457         equivalent columns will be included in the join output, not
 458         both.
 459        </para>
 460       </listitem>
 461      </varlistentry>
 462
 463      <varlistentry>
 464       <term><literal>NATURAL</literal></term>
 465       <listitem>
 466        <para>
 467         <literal>NATURAL</literal> is shorthand for a
 468         <literal>USING</> list that mentions all columns in the two
 469         tables that have the same names.
 470        </para>
 471       </listitem>
 472      </varlistentry>
 473     </variablelist>
 474    </para>
 475   </refsect2>
 476
 477   <refsect2 id="SQL-WHERE">
 478    <title id="sql-where-title"><literal>WHERE</literal> Clause</title>
 479
 480    <para>
 481     The optional <literal>WHERE</literal> clause has the general form
 482 <synopsis>
 483 WHERE <replaceable class="parameter">condition</replaceable>
 484 </synopsis>
 485     where <replaceable class="parameter">condition</replaceable> is
 486     any expression that evaluates to a result of type
 487     <type>boolean</type>.  Any row that does not satisfy this
 488     condition will be eliminated from the output.  A row satisfies the
 489     condition if it returns true when the actual row values are
 490     substituted for any variable references.
 491    </para>
 492   </refsect2>
 493
 494   <refsect2 id="SQL-GROUPBY">
 495    <title id="sql-groupby-title"><literal>GROUP BY</literal> Clause</title>
 496
 497    <para>
 498     The optional <literal>GROUP BY</literal> clause has the general form
 499 <synopsis>
 500 GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
 501 </synopsis>
 502    </para>
 503
 504    <para>
 505     <literal>GROUP BY</literal> will condense into a single row all
 506     selected rows that share the same values for the grouped
 507     expressions.  <replaceable
 508     class="parameter">expression</replaceable> can be an input column
 509     name, or the name or ordinal number of an output column
 510     (<command>SELECT</command> list item), or an arbitrary
 511     expression formed from input-column values.  In case of ambiguity,
 512     a <literal>GROUP BY</literal> name will be interpreted as an
 513     input-column name rather than an output column name.
 514    </para>
 515
 516    <para>
 517     Aggregate functions, if any are used, are computed across all rows
 518     making up each group, producing a separate value for each group
 519     (whereas without <literal>GROUP BY</literal>, an aggregate
 520     produces a single value computed across all the selected rows).
 521     When <literal>GROUP BY</literal> is present, it is not valid for
 522     the <command>SELECT</command> list expressions to refer to
 523     ungrouped columns except within aggregate functions, since there
 524     would be more than one possible value to return for an ungrouped
 525     column.
 526    </para>
 527   </refsect2>
 528
 529   <refsect2 id="SQL-HAVING">
 530    <title id="sql-having-title"><literal>HAVING</literal> Clause</title>
 531
 532    <para>
 533     The optional <literal>HAVING</literal> clause has the general form
 534 <synopsis>
 535 HAVING <replaceable class="parameter">condition</replaceable>
 536 </synopsis>
 537     where <replaceable class="parameter">condition</replaceable> is
 538     the same as specified for the <literal>WHERE</literal> clause.
 539    </para>
 540
 541    <para>
 542     <literal>HAVING</literal> eliminates group rows that do not
 543     satisfy the condition.  <literal>HAVING</literal> is different
 544     from <literal>WHERE</literal>: <literal>WHERE</literal> filters
 545     individual rows before the application of <literal>GROUP
 546     BY</literal>, while <literal>HAVING</literal> filters group rows
 547     created by <literal>GROUP BY</literal>.  Each column referenced in
 548     <replaceable class="parameter">condition</replaceable> must
 549     unambiguously reference a grouping column, unless the reference
 550     appears within an aggregate function.
 551    </para>
 552
 553    <para>
 554     The presence of <literal>HAVING</literal> turns a query into a grouped
 555     query even if there is no <literal>GROUP BY</> clause.  This is the
 556     same as what happens when the query contains aggregate functions but
 557     no <literal>GROUP BY</> clause.  All the selected rows are considered to
 558     form a single group, and the <command>SELECT</command> list and
 559     <literal>HAVING</literal> clause can only reference table columns from
 560     within aggregate functions.  Such a query will emit a single row if the
 561     <literal>HAVING</literal> condition is true, zero rows if it is not true.
 562    </para>
 563   </refsect2>
 564
 565   <refsect2 id="SQL-WINDOW">
 566    <title id="sql-window-title"><literal>WINDOW</literal> Clause</title>
 567
 568    <para>
 569     The optional <literal>WINDOW</literal> clause has the general form
 570 <synopsis>
 571 WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...]
 572 </synopsis>
 573     where <replaceable class="parameter">window_name</replaceable> is
 574     a name that can be referenced from subsequent window definitions or
 575     <literal>OVER</> clauses, and
 576     <replaceable class="parameter">window_definition</replaceable> is
 577 <synopsis>
 578 [ <replaceable class="parameter">existing_window_name</replaceable> ]
 579 [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
 580 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
 581 [ <replaceable class="parameter">frame_clause</replaceable> ]
 582 </synopsis>
 583    </para>
 584
 585    <para>
 586     If an <replaceable class="parameter">existing_window_name</replaceable>
 587     is specified it must refer to an earlier entry in the <literal>WINDOW</>
 588     list; the new window copies its partitioning clause from that entry,
 589     as well as its ordering clause if any.  In this case the new window cannot
 590     specify its own <literal>PARTITION BY</> clause, and it can specify
 591     <literal>ORDER BY</> only if the copied window does not have one.
 592     The new window always uses its own frame clause; the copied window
 593     must not specify a frame clause.
 594    </para>
 595
 596    <para>
 597     The elements of the <literal>PARTITION BY</> list are interpreted in
 598     the same fashion as elements of a
 599     <xref linkend="sql-groupby" endterm="sql-groupby-title">, and
 600     the elements of the <literal>ORDER BY</> list are interpreted in the
 601     same fashion as elements of an
 602     <xref linkend="sql-orderby" endterm="sql-orderby-title">.
 603     The only difference is that these expressions can contain aggregate
 604     function calls, which are not allowed in a regular <literal>GROUP BY</>
 605     clause.  They are allowed here because windowing occurs after grouping
 606     and aggregation.
 607    </para>
 608
 609    <para>
 610     The optional <replaceable class="parameter">frame_clause</> defines
 611     the <firstterm>window frame</> for window functions that depend on the
 612     frame (not all do).  It can be one of
 613 <synopsis>
 614 RANGE UNBOUNDED PRECEDING
 615 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 616 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 617 ROWS UNBOUNDED PRECEDING
 618 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 619 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 620 </synopsis>
 621     The first two are equivalent and are also the default: they set the
 622     frame to be all rows from the partition start up through the current row's
 623     last peer in the <literal>ORDER BY</> ordering (which means all rows if
 624     there is no <literal>ORDER BY</>).  The options
 625     <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
 626     <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
 627     are also equivalent: they always select all rows in the partition.
 628     Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
 629     <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
 630     all rows up through the current row (regardless of duplicates).
 631     Beware that this option can produce implementation-dependent results
 632     if the <literal>ORDER BY</> ordering does not order the rows uniquely.
 633    </para>
 634
 635    <para>
 636     The purpose of a <literal>WINDOW</literal> clause is to specify the
 637     behavior of <firstterm>window functions</> appearing in the query's
 638     <xref linkend="sql-select-list" endterm="sql-select-list-title"> or
 639     <xref linkend="sql-orderby" endterm="sql-orderby-title">.  These functions
 640     can reference the <literal>WINDOW</literal> clause entries by name
 641     in their <literal>OVER</> clauses.  A <literal>WINDOW</literal> clause
 642     entry does not have to be referenced anywhere, however; if it is not
 643     used in the query it is simply ignored.  It is possible to use window
 644     functions without any <literal>WINDOW</literal> clause at all, since
 645     a window function call can specify its window definition directly in
 646     its <literal>OVER</> clause.  However, the <literal>WINDOW</literal>
 647     clause saves typing when the same window definition is needed for more
 648     than one window function.
 649    </para>
 650
 651    <para>
 652     Window functions are described in detail in
 653     <xref linkend="tutorial-window">,
 654     <xref linkend="syntax-window-functions">, and
 655     <xref linkend="queries-window">.
 656    </para>
 657   </refsect2>
 658
 659   <refsect2 id="sql-select-list">
 660    <title id="sql-select-list-title"><command>SELECT</command> List</title>
 661
 662    <para>
 663     The <command>SELECT</command> list (between the key words
 664     <literal>SELECT</> and <literal>FROM</>) specifies expressions
 665     that form the output rows of the <command>SELECT</command>
 666     statement.  The expressions can (and usually do) refer to columns
 667     computed in the <literal>FROM</> clause.
 668    </para>
 669
 670    <para>
 671     Just as in a table, every output column of a <command>SELECT</command>
 672     has a name.  In a simple <command>SELECT</command> this name is just
 673     used to label the column for display, but when the <command>SELECT</>
 674     is a sub-query of a larger query, the name is seen by the larger query
 675     as the column name of the virtual table produced by the sub-query.
 676     To specify the name to use for an output column, write
 677     <literal>AS</> <replaceable class="parameter">output_name</replaceable>
 678     after the column's expression.  (You can omit <literal>AS</literal>,
 679     but only if the desired output name does not match any
 680     <productname>PostgreSQL</productname> keyword (see <xref
 681     linkend="sql-keywords-appendix">).  For protection against possible
 682     future keyword additions, it is recommended that you always either
 683     write <literal>AS</literal> or double-quote the output name.)
 684     If you do not specify a column name, a name is chosen automatically
 685     by <productname>PostgreSQL</productname>.  If the column's expression
 686     is a simple column reference then the chosen name is the same as that
 687     column's name; in more complex cases a generated name looking like
 688     <literal>?column<replaceable>N</>?</literal> is usually chosen.
 689    </para>
 690
 691    <para>
 692     An output column's name can be used to refer to the column's value in
 693     <literal>ORDER BY</> and <literal>GROUP BY</> clauses, but not in the
 694     <literal>WHERE</> or <literal>HAVING</> clauses; there you must write
 695     out the expression instead.
 696    </para>
 697
 698    <para>
 699     Instead of an expression, <literal>*</literal> can be written in
 700     the output list as a shorthand for all the columns of the selected
 701     rows.  Also, you can write <literal><replaceable
 702     class="parameter">table_name</replaceable>.*</literal> as a
 703     shorthand for the columns coming from just that table.  In these
 704     cases it is not possible to specify new names with <literal>AS</>;
 705     the output column names will be the same as the table columns' names.
 706    </para>
 707   </refsect2>
 708
 709   <refsect2 id="SQL-UNION">
 710    <title id="sql-union-title"><literal>UNION</literal> Clause</title>
 711
 712    <para>
 713     The <literal>UNION</literal> clause has this general form:
 714 <synopsis>
 715 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 716 </synopsis>
 717     <replaceable class="parameter">select_statement</replaceable> is
 718     any <command>SELECT</command> statement without an <literal>ORDER
 719     BY</>, <literal>LIMIT</>, <literal>FOR UPDATE</literal>, or
 720     <literal>FOR SHARE</literal> clause.
 721     (<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
 722     subexpression if it is enclosed in parentheses.  Without
 723     parentheses, these clauses will be taken to apply to the result of
 724     the <literal>UNION</literal>, not to its right-hand input
 725     expression.)
 726    </para>
 727
 728    <para>
 729     The <literal>UNION</literal> operator computes the set union of
 730     the rows returned by the involved <command>SELECT</command>
 731     statements.  A row is in the set union of two result sets if it
 732     appears in at least one of the result sets.  The two
 733     <command>SELECT</command> statements that represent the direct
 734     operands of the <literal>UNION</literal> must produce the same
 735     number of columns, and corresponding columns must be of compatible
 736     data types.
 737    </para>
 738
 739    <para>
 740     The result of <literal>UNION</> does not contain any duplicate
 741     rows unless the <literal>ALL</> option is specified.
 742     <literal>ALL</> prevents elimination of duplicates.  (Therefore,
 743     <literal>UNION ALL</> is usually significantly quicker than
 744     <literal>UNION</>; use <literal>ALL</> when you can.)
 745    </para>
 746
 747    <para>
 748     Multiple <literal>UNION</> operators in the same
 749     <command>SELECT</command> statement are evaluated left to right,
 750     unless otherwise indicated by parentheses.
 751    </para>
 752
 753    <para>
 754     Currently, <literal>FOR UPDATE</> and <literal>FOR SHARE</> cannot be
 755     specified either for a <literal>UNION</> result or for any input of a
 756     <literal>UNION</>.
 757    </para>
 758   </refsect2>
 759
 760   <refsect2 id="SQL-INTERSECT">
 761    <title id="sql-intersect-title"><literal>INTERSECT</literal> Clause</title>
 762
 763    <para>
 764     The <literal>INTERSECT</literal> clause has this general form:
 765 <synopsis>
 766 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 767 </synopsis>
 768     <replaceable class="parameter">select_statement</replaceable> is
 769     any <command>SELECT</command> statement without an <literal>ORDER
 770     BY</>, <literal>LIMIT</>, <literal>FOR UPDATE</literal>, or
 771     <literal>FOR SHARE</literal> clause.
 772    </para>
 773
 774    <para>
 775     The <literal>INTERSECT</literal> operator computes the set
 776     intersection of the rows returned by the involved
 777     <command>SELECT</command> statements.  A row is in the
 778     intersection of two result sets if it appears in both result sets.
 779    </para>
 780
 781    <para>
 782     The result of <literal>INTERSECT</literal> does not contain any
 783     duplicate rows unless the <literal>ALL</> option is specified.
 784     With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
 785     left table and <replaceable>n</> duplicates in the right table will appear
 786     min(<replaceable>m</>,<replaceable>n</>) times in the result set.
 787    </para>
 788
 789    <para>
 790     Multiple <literal>INTERSECT</literal> operators in the same
 791     <command>SELECT</command> statement are evaluated left to right,
 792     unless parentheses dictate otherwise.
 793     <literal>INTERSECT</literal> binds more tightly than
 794     <literal>UNION</literal>.  That is, <literal>A UNION B INTERSECT
 795     C</literal> will be read as <literal>A UNION (B INTERSECT
 796     C)</literal>.
 797    </para>
 798
 799    <para>
 800     Currently, <literal>FOR UPDATE</> and <literal>FOR SHARE</> cannot be
 801     specified either for an <literal>INTERSECT</> result or for any input of
 802     an <literal>INTERSECT</>.
 803    </para>
 804   </refsect2>
 805
 806   <refsect2 id="SQL-EXCEPT">
 807    <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>
 808
 809    <para>
 810     The <literal>EXCEPT</literal> clause has this general form:
 811 <synopsis>
 812 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 813 </synopsis>
 814     <replaceable class="parameter">select_statement</replaceable> is
 815     any <command>SELECT</command> statement without an <literal>ORDER
 816     BY</>, <literal>LIMIT</>, <literal>FOR UPDATE</literal>, or
 817     <literal>FOR SHARE</literal> clause.
 818    </para>
 819
 820    <para>
 821     The <literal>EXCEPT</literal> operator computes the set of rows
 822     that are in the result of the left <command>SELECT</command>
 823     statement but not in the result of the right one.
 824    </para>
 825
 826    <para>
 827     The result of <literal>EXCEPT</literal> does not contain any
 828     duplicate rows unless the <literal>ALL</> option is specified.
 829     With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
 830     left table and <replaceable>n</> duplicates in the right table will appear
 831     max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
 832    </para>
 833
 834    <para>
 835     Multiple <literal>EXCEPT</literal> operators in the same
 836     <command>SELECT</command> statement are evaluated left to right,
 837     unless parentheses dictate otherwise.  <literal>EXCEPT</> binds at
 838     the same level as <literal>UNION</>.
 839    </para>
 840
 841    <para>
 842     Currently, <literal>FOR UPDATE</> and <literal>FOR SHARE</> cannot be
 843     specified either for an <literal>EXCEPT</> result or for any input of
 844     an <literal>EXCEPT</>.
 845    </para>
 846   </refsect2>
 847
 848   <refsect2 id="SQL-ORDERBY">
 849    <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>
 850
 851    <para>
 852     The optional <literal>ORDER BY</literal> clause has this general form:
 853 <synopsis>
 854 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
 855 </synopsis>
 856     The <literal>ORDER BY</literal> clause causes the result rows to
 857     be sorted according to the specified expression(s).  If two rows are
 858     equal according to the leftmost expression, they are compared
 859     according to the next expression and so on.  If they are equal
 860     according to all specified expressions, they are returned in
 861     an implementation-dependent order.
 862    </para>
 863
 864    <para>
 865     Each <replaceable class="parameter">expression</replaceable> can be the
 866     name or ordinal number of an output column
 867     (<command>SELECT</command> list item), or it can be an arbitrary
 868     expression formed from input-column values.
 869    </para>
 870
 871    <para>
 872     The ordinal number refers to the ordinal (left-to-right) position
 873     of the output column. This feature makes it possible to define an
 874     ordering on the basis of a column that does not have a unique
 875     name.  This is never absolutely necessary because it is always
 876     possible to assign a name to an output column using the
 877     <literal>AS</> clause.
 878    </para>
 879
 880    <para>
 881     It is also possible to use arbitrary expressions in the
 882     <literal>ORDER BY</literal> clause, including columns that do not
 883     appear in the <command>SELECT</command> output list.  Thus the
 884     following statement is valid:
 885 <programlisting>
 886 SELECT name FROM distributors ORDER BY code;
 887 </programlisting>
 888     A limitation of this feature is that an <literal>ORDER BY</>
 889     clause applying to the result of a <literal>UNION</>,
 890     <literal>INTERSECT</>, or <literal>EXCEPT</> clause can only
 891     specify an output column name or number, not an expression.
 892    </para>
 893
 894    <para>
 895     If an <literal>ORDER BY</> expression is a simple name that
 896     matches both an output column name and an input column name,
 897     <literal>ORDER BY</> will interpret it as the output column name.
 898     This is the opposite of the choice that <literal>GROUP BY</> will
 899     make in the same situation.  This inconsistency is made to be
 900     compatible with the SQL standard.
 901    </para>
 902
 903    <para>
 904     Optionally one can add the key word <literal>ASC</> (ascending) or
 905     <literal>DESC</> (descending) after any expression in the
 906     <literal>ORDER BY</> clause.  If not specified, <literal>ASC</> is
 907     assumed by default.  Alternatively, a specific ordering operator
 908     name can be specified in the <literal>USING</> clause.
 909     An ordering operator must be a less-than or greater-than
 910     member of some B-tree operator family.
 911     <literal>ASC</> is usually equivalent to <literal>USING &lt;</> and
 912     <literal>DESC</> is usually equivalent to <literal>USING &gt;</>.
 913     (But the creator of a user-defined data type can define exactly what the
 914     default sort ordering is, and it might correspond to operators with other
 915     names.)
 916    </para>
 917
 918    <para>
 919     If <literal>NULLS LAST</> is specified, null values sort after all
 920     non-null values; if <literal>NULLS FIRST</> is specified, null values
 921     sort before all non-null values.  If neither is specified, the default
 922     behavior is <literal>NULLS LAST</> when <literal>ASC</> is specified
 923     or implied, and <literal>NULLS FIRST</> when <literal>DESC</> is specified
 924     (thus, the default is to act as though nulls are larger than non-nulls).
 925     When <literal>USING</> is specified, the default nulls ordering depends
 926     on whether the operator is a less-than or greater-than operator.
 927    </para>
 928
 929    <para>
 930     Note that ordering options apply only to the expression they follow;
 931     for example <literal>ORDER BY x, y DESC</> does not mean
 932     the same thing as <literal>ORDER BY x DESC, y DESC</>.
 933    </para>
 934
 935    <para>
 936     Character-string data is sorted according to the locale-specific
 937     collation order that was established when the database was created.
 938    </para>
 939   </refsect2>
 940
 941   <refsect2 id="sql-distinct">
 942    <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
 943
 944    <para>
 945     If <literal>DISTINCT</> is specified, all duplicate rows are
 946     removed from the result set (one row is kept from each group of
 947     duplicates).  <literal>ALL</> specifies the opposite: all rows are
 948     kept; that is the default.
 949    </para>
 950
 951    <para>
 952     <literal>DISTINCT ON ( <replaceable
 953     class="parameter">expression</replaceable> [, ...] )</literal>
 954     keeps only the first row of each set of rows where the given
 955     expressions evaluate to equal.  The <literal>DISTINCT ON</literal>
 956     expressions are interpreted using the same rules as for
 957     <literal>ORDER BY</> (see above).  Note that the <quote>first
 958     row</quote> of each set is unpredictable unless <literal>ORDER
 959     BY</> is used to ensure that the desired row appears first.  For
 960     example:
 961 <programlisting>
 962 SELECT DISTINCT ON (location) location, time, report
 963     FROM weather_reports
 964     ORDER BY location, time DESC;
 965 </programlisting>
 966     retrieves the most recent weather report for each location.  But
 967     if we had not used <literal>ORDER BY</> to force descending order
 968     of time values for each location, we'd have gotten a report from
 969     an unpredictable time for each location.
 970    </para>
 971
 972    <para>
 973     The <literal>DISTINCT ON</> expression(s) must match the leftmost
 974     <literal>ORDER BY</> expression(s).  The <literal>ORDER BY</> clause
 975     will normally contain additional expression(s) that determine the
 976     desired precedence of rows within each <literal>DISTINCT ON</> group.
 977    </para>
 978   </refsect2>
 979
 980   <refsect2 id="SQL-LIMIT">
 981    <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
 982
 983    <para>
 984     The <literal>LIMIT</literal> clause consists of two independent
 985     sub-clauses:
 986 <synopsis>
 987 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
 988 OFFSET <replaceable class="parameter">start</replaceable>
 989 </synopsis>
 990     <replaceable class="parameter">count</replaceable> specifies the
 991     maximum number of rows to return, while <replaceable
 992     class="parameter">start</replaceable> specifies the number of rows
 993     to skip before starting to return rows.  When both are specified,
 994     <replaceable class="parameter">start</replaceable> rows are skipped
 995     before starting to count the <replaceable
 996     class="parameter">count</replaceable> rows to be returned.
 997    </para>
 998
 999    <para>
1000     If the <replaceable class="parameter">count</replaceable> expression
1001     evaluates to NULL, it is treated as <literal>LIMIT ALL</>, i.e., no
1002     limit.  If <replaceable class="parameter">start</replaceable> evaluates
1003     to NULL, it is treated the same as <literal>OFFSET 0</>.
1004    </para>
1005
1006    <para>
1007     SQL:2008 introduced a different syntax to achieve the same thing,
1008     which PostgreSQL also supports.  It is:
1009 <synopsis>
1010 OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
1011 FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
1012 </synopsis>
1013     Both clauses are optional, but if present
1014     the <literal>OFFSET</literal> clause must come before
1015     the <literal>FETCH</literal> clause.  <literal>ROW</literal>
1016     and <literal>ROWS</literal> as well as <literal>FIRST</literal>
1017     and <literal>NEXT</literal> are noise words that don't influence
1018     the effects of these clauses.  In this syntax, when using expressions
1019     other than simple constants for <replaceable class="parameter">start</>
1020     or <replaceable class="parameter">count</replaceable>, parentheses will be
1021     necessary in most cases.  If <replaceable class="parameter">count</> is
1022     omitted in <literal>FETCH</>, it defaults to 1.
1023    </para>
1024
1025    <para>
1026     When using <literal>LIMIT</>, it is a good idea to use an
1027     <literal>ORDER BY</> clause that constrains the result rows into a
1028     unique order.  Otherwise you will get an unpredictable subset of
1029     the query's rows &mdash; you might be asking for the tenth through
1030     twentieth rows, but tenth through twentieth in what ordering?  You
1031     don't know what ordering unless you specify <literal>ORDER BY</>.
1032    </para>
1033
1034    <para>
1035     The query planner takes <literal>LIMIT</> into account when
1036     generating a query plan, so you are very likely to get different
1037     plans (yielding different row orders) depending on what you use
1038     for <literal>LIMIT</> and <literal>OFFSET</>.  Thus, using
1039     different <literal>LIMIT</>/<literal>OFFSET</> values to select
1040     different subsets of a query result <emphasis>will give
1041     inconsistent results</emphasis> unless you enforce a predictable
1042     result ordering with <literal>ORDER BY</>.  This is not a bug; it
1043     is an inherent consequence of the fact that SQL does not promise
1044     to deliver the results of a query in any particular order unless
1045     <literal>ORDER BY</> is used to constrain the order.
1046    </para>
1047
1048    <para>
1049     It is even possible for repeated executions of the same <literal>LIMIT</>
1050     query to return different subsets of the rows of a table, if there
1051     is not an <literal>ORDER BY</> to enforce selection of a deterministic
1052     subset.  Again, this is not a bug; determinism of the results is
1053     simply not guaranteed in such a case.
1054    </para>
1055   </refsect2>
1056
1057   <refsect2 id="SQL-FOR-UPDATE-SHARE">
1058    <title id="sql-for-update-share-title"><literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal> Clause</title>
1059
1060    <para>
1061     The <literal>FOR UPDATE</literal> clause has this form:
1062 <synopsis>
1063 FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1064 </synopsis>
1065    </para>
1066
1067    <para>
1068     The closely related <literal>FOR SHARE</literal> clause has this form:
1069 <synopsis>
1070 FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1071 </synopsis>
1072    </para>
1073
1074    <para>
1075     <literal>FOR UPDATE</literal> causes the rows retrieved by the
1076     <command>SELECT</command> statement to be locked as though for
1077     update.  This prevents them from being modified or deleted by
1078     other transactions until the current transaction ends.  That is,
1079     other transactions that attempt <command>UPDATE</command>,
1080     <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
1081     of these rows will be blocked until the current transaction ends.
1082     Also, if an <command>UPDATE</command>, <command>DELETE</command>,
1083     or <command>SELECT FOR UPDATE</command> from another transaction
1084     has already locked a selected row or rows, <command>SELECT FOR
1085     UPDATE</command> will wait for the other transaction to complete,
1086     and will then lock and return the updated row (or no row, if the
1087     row was deleted).  For further discussion see <xref
1088     linkend="mvcc">.
1089    </para>
1090
1091    <para>
1092     To prevent the operation from waiting for other transactions to commit,
1093     use the <literal>NOWAIT</> option.  <command>SELECT FOR UPDATE
1094     NOWAIT</command> reports an error, rather than waiting, if a selected row
1095     cannot be locked immediately.  Note that <literal>NOWAIT</> applies only
1096     to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
1097     table-level lock is still taken in the ordinary way (see
1098     <xref linkend="mvcc">).  You can use the <literal>NOWAIT</> option of
1099     <xref linkend="sql-lock" endterm="sql-lock-title">
1100     if you need to acquire the table-level lock without waiting.
1101    </para>
1102
1103    <para>
1104     <literal>FOR SHARE</literal> behaves similarly, except that it
1105     acquires a shared rather than exclusive lock on each retrieved
1106     row.  A shared lock blocks other transactions from performing
1107     <command>UPDATE</command>, <command>DELETE</command>, or <command>SELECT
1108     FOR UPDATE</command> on these rows, but it does not prevent them
1109     from performing <command>SELECT FOR SHARE</command>.
1110    </para>
1111
1112    <para>
1113     If specific tables are named in <literal>FOR UPDATE</literal>
1114     or <literal>FOR SHARE</literal>,
1115     then only rows coming from those tables are locked; any other
1116     tables used in the <command>SELECT</command> are simply read as
1117     usual.  A <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>
1118     clause without a table list affects all tables used in the command.
1119     If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is
1120     applied to a view or sub-query, it affects all tables used in
1121     the view or sub-query.
1122    </para>
1123
1124    <para>
1125     Multiple <literal>FOR UPDATE</literal> and <literal>FOR SHARE</literal>
1126     clauses can be written if it is necessary to specify different locking
1127     behavior for different tables.  If the same table is mentioned (or
1128     implicitly affected) by both <literal>FOR UPDATE</literal> and
1129     <literal>FOR SHARE</literal> clauses, then it is processed as
1130     <literal>FOR UPDATE</literal>.  Similarly, a table is processed
1131     as <literal>NOWAIT</> if that is specified in any of the clauses
1132     affecting it.
1133    </para>
1134
1135    <para>
1136     <literal>FOR UPDATE</literal> and <literal>FOR SHARE</literal> cannot be
1137     used in contexts where returned rows cannot be clearly identified with
1138     individual table rows; for example they cannot be used with aggregation.
1139    </para>
1140
1141   <caution>
1142    <para>
1143     Avoid locking a row and then modifying it within a later savepoint or
1144     <application>PL/pgSQL</application> exception block.  A subsequent
1145     rollback would cause the lock to be lost.  For example:
1146 <programlisting>
1147 BEGIN;
1148 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
1149 SAVEPOINT s;
1150 UPDATE mytable SET ... WHERE key = 1;
1151 ROLLBACK TO s;
1152 </programlisting>
1153     After the <command>ROLLBACK</>, the row is effectively unlocked, rather
1154     than returned to its pre-savepoint state of being locked but not modified.
1155     This hazard occurs if a row locked in the current transaction is updated
1156     or deleted, or if a shared lock is upgraded to exclusive: in all these
1157     cases, the former lock state is forgotten.  If the transaction is then
1158     rolled back to a state between the original locking command and the
1159     subsequent change, the row will appear not to be locked at all.  This is
1160     an implementation deficiency which will be addressed in a future release
1161     of <productname>PostgreSQL</productname>.
1162    </para>
1163   </caution>
1164
1165   <caution>
1166    <para>
1167     It is possible for a <command>SELECT</> command using both
1168     <literal>LIMIT</literal> and <literal>FOR UPDATE/SHARE</literal>
1169     clauses to return fewer rows than specified by <literal>LIMIT</literal>.
1170     This is because <literal>LIMIT</> is applied first.  The command
1171     selects the specified number of rows,
1172     but might then block trying to obtain a lock on one or more of them.
1173     Once the <literal>SELECT</> unblocks, the row might have been deleted
1174     or updated so that it does not meet the query <literal>WHERE</> condition
1175     anymore, in which case it will not be returned.
1176    </para>
1177   </caution>
1178
1179   <caution>
1180    <para>
1181     Similarly, it is possible for a <command>SELECT</> command
1182     using <literal>ORDER BY</literal> and <literal>FOR
1183     UPDATE/SHARE</literal> to return rows out of order.  This is
1184     because <literal>ORDER BY</> is applied first.  The command
1185     orders the result, but might then block trying to obtain a lock
1186     on one or more of the rows.  Once the <literal>SELECT</>
1187     unblocks, one of the ordered columns might have been modified
1188     and be returned out of order.  A workaround is to perform
1189     <command>SELECT ... FOR UPDATE/SHARE</> and then <command>SELECT
1190     ... ORDER BY</>.
1191    </para>
1192   </caution>
1193   </refsect2>
1194
1195   <refsect2 id="SQL-TABLE">
1196    <title><literal>TABLE</literal> Command</title>
1197
1198    <para>
1199     The command
1200 <programlisting>
1201 TABLE <replaceable class="parameter">name</replaceable>
1202 </programlisting>
1203     is completely equivalent to
1204 <programlisting>
1205 SELECT * FROM <replaceable class="parameter">name</replaceable>
1206 </programlisting>
1207     It can be used as a top-level command or as a space-saving syntax
1208     variant in parts of complex queries.
1209    </para>
1210   </refsect2>
1211  </refsect1>
1212
1213  <refsect1>
1214   <title>Examples</title>
1215
1216   <para>
1217    To join the table <literal>films</literal> with the table
1218    <literal>distributors</literal>:
1219
1220 <programlisting>
1221 SELECT f.title, f.did, d.name, f.date_prod, f.kind
1222     FROM distributors d, films f
1223     WHERE f.did = d.did
1224
1225        title       | did |     name     | date_prod  |   kind
1226 -------------------+-----+--------------+------------+----------
1227  The Third Man     | 101 | British Lion | 1949-12-23 | Drama
1228  The African Queen | 101 | British Lion | 1951-08-11 | Romantic
1229  ...
1230 </programlisting>
1231   </para>
1232
1233   <para>
1234    To sum the column <literal>len</literal> of all films and group
1235    the results by <literal>kind</literal>:
1236
1237 <programlisting>
1238 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
1239
1240    kind   | total
1241 ----------+-------
1242  Action   | 07:34
1243  Comedy   | 02:58
1244  Drama    | 14:28
1245  Musical  | 06:42
1246  Romantic | 04:38
1247 </programlisting>
1248   </para>
1249
1250   <para>
1251    To sum the column <literal>len</literal> of all films, group
1252    the results by <literal>kind</literal> and show those group totals
1253    that are less than 5 hours:
1254
1255 <programlisting>
1256 SELECT kind, sum(len) AS total
1257     FROM films
1258     GROUP BY kind
1259     HAVING sum(len) &lt; interval '5 hours';
1260
1261    kind   | total
1262 ----------+-------
1263  Comedy   | 02:58
1264  Romantic | 04:38
1265 </programlisting>
1266   </para>
1267
1268   <para>
1269    The following two examples are identical ways of sorting the individual
1270    results according to the contents of the second column
1271    (<literal>name</literal>):
1272
1273 <programlisting>
1274 SELECT * FROM distributors ORDER BY name;
1275 SELECT * FROM distributors ORDER BY 2;
1276
1277  did |       name
1278 -----+------------------
1279  109 | 20th Century Fox
1280  110 | Bavaria Atelier
1281  101 | British Lion
1282  107 | Columbia
1283  102 | Jean Luc Godard
1284  113 | Luso films
1285  104 | Mosfilm
1286  103 | Paramount
1287  106 | Toho
1288  105 | United Artists
1289  111 | Walt Disney
1290  112 | Warner Bros.
1291  108 | Westward
1292 </programlisting>
1293   </para>
1294
1295   <para>
1296    The next example shows how to obtain the union of the tables
1297    <literal>distributors</literal> and
1298    <literal>actors</literal>, restricting the results to those that begin
1299    with the letter W in each table.  Only distinct rows are wanted, so the
1300    key word <literal>ALL</literal> is omitted.
1301
1302 <programlisting>
1303 distributors:               actors:
1304  did |     name              id |     name
1305 -----+--------------        ----+----------------
1306  108 | Westward               1 | Woody Allen
1307  111 | Walt Disney            2 | Warren Beatty
1308  112 | Warner Bros.           3 | Walter Matthau
1309  ...                         ...
1310
1311 SELECT distributors.name
1312     FROM distributors
1313     WHERE distributors.name LIKE 'W%'
1314 UNION
1315 SELECT actors.name
1316     FROM actors
1317     WHERE actors.name LIKE 'W%';
1318
1319       name
1320 ----------------
1321  Walt Disney
1322  Walter Matthau
1323  Warner Bros.
1324  Warren Beatty
1325  Westward
1326  Woody Allen
1327 </programlisting>
1328   </para>
1329
1330   <para>
1331    This example shows how to use a function in the <literal>FROM</>
1332    clause, both with and without a column definition list:
1333
1334 <programlisting>
1335 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
1336     SELECT * FROM distributors WHERE did = $1;
1337 $$ LANGUAGE SQL;
1338
1339 SELECT * FROM distributors(111);
1340  did |    name
1341 -----+-------------
1342  111 | Walt Disney
1343
1344 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
1345     SELECT * FROM distributors WHERE did = $1;
1346 $$ LANGUAGE SQL;
1347
1348 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
1349  f1  |     f2
1350 -----+-------------
1351  111 | Walt Disney
1352 </programlisting>
1353   </para>
1354
1355   <para>
1356    This example shows how to use a simple <literal>WITH</> clause:
1357
1358 <programlisting>
1359 WITH t AS (
1360     SELECT random() as x FROM generate_series(1, 3)
1361   )
1362 SELECT * FROM t
1363 UNION ALL
1364 SELECT * FROM t
1365
1366          x
1367 --------------------
1368   0.534150459803641
1369   0.520092216785997
1370  0.0735620250925422
1371   0.534150459803641
1372   0.520092216785997
1373  0.0735620250925422
1374 </programlisting>
1375
1376    Notice that the <literal>WITH</> query was evaluated only once,
1377    so that we got two sets of the same three random values.
1378   </para>
1379
1380   <para>
1381    This example uses <literal>WITH RECURSIVE</literal> to find all
1382    subordinates (direct or indirect) of the employee Mary, and their
1383    level of indirectness, from a table that shows only direct
1384    subordinates:
1385
1386 <programlisting>
1387 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
1388     SELECT 1, employee_name, manager_name
1389     FROM employee
1390     WHERE manager_name = 'Mary'
1391   UNION ALL
1392     SELECT er.distance + 1, e.employee_name, e.manager_name
1393     FROM employee_recursive er, employee e
1394     WHERE er.employee_name = e.manager_name
1395   )
1396 SELECT distance, employee_name FROM employee_recursive;
1397 </programlisting>
1398
1399    Notice the typical form of recursive queries:
1400    an initial condition, followed by <literal>UNION</literal>,
1401    followed by the recursive part of the query. Be sure that the
1402    recursive part of the query will eventually return no tuples, or
1403    else the query will loop indefinitely.  (See <xref linkend="queries-with">
1404    for more examples.)
1405   </para>
1406  </refsect1>
1407
1408  <refsect1>
1409   <title>Compatibility</title>
1410
1411   <para>
1412    Of course, the <command>SELECT</command> statement is compatible
1413    with the SQL standard.  But there are some extensions and some
1414    missing features.
1415   </para>
1416
1417   <refsect2>
1418    <title>Omitted <literal>FROM</literal> Clauses</title>
1419
1420    <para>
1421     <productname>PostgreSQL</productname> allows one to omit the
1422     <literal>FROM</literal> clause.  It has a straightforward use to
1423     compute the results of simple expressions:
1424 <programlisting>
1425 SELECT 2+2;
1426
1427  ?column?
1428 ----------
1429         4
1430 </programlisting>
1431     Some other <acronym>SQL</acronym> databases cannot do this except
1432     by introducing a dummy one-row table from which to do the
1433     <command>SELECT</command>.
1434    </para>
1435
1436    <para>
1437     Note that if a <literal>FROM</literal> clause is not specified,
1438     the query cannot reference any database tables. For example, the
1439     following query is invalid:
1440 <programlisting>
1441 SELECT distributors.* WHERE distributors.name = 'Westward';
1442 </programlisting>
1443     <productname>PostgreSQL</productname> releases prior to
1444     8.1 would accept queries of this form, and add an implicit entry
1445     to the query's <literal>FROM</literal> clause for each table
1446     referenced by the query. This is no longer the default behavior,
1447     because it does not comply with the SQL standard, and is
1448     considered by many to be error-prone. For compatibility with
1449     applications that rely on this behavior the <xref
1450     linkend="guc-add-missing-from"> configuration variable can be
1451     enabled.
1452    </para>
1453   </refsect2>
1454
1455   <refsect2>
1456    <title>Omitting the <literal>AS</literal> Key Word</title>
1457
1458    <para>
1459     In the SQL standard, the optional key word <literal>AS</> can be
1460     omitted before an output column name whenever the new column name
1461     is a valid column name (that is, not the same as any reserved
1462     keyword).  <productname>PostgreSQL</productname> is slightly more
1463     restrictive: <literal>AS</> is required if the new column name
1464     matches any keyword at all, reserved or not.  Recommended practice is
1465     to use <literal>AS</> or double-quote output column names, to prevent
1466     any possible conflict against future keyword additions.
1467    </para>
1468
1469    <para>
1470     In <literal>FROM</literal> items, both the standard and
1471     <productname>PostgreSQL</productname> allow <literal>AS</> to
1472     be omitted before an alias that is an unreserved keyword.  But
1473     this is impractical for output column names, because of syntactic
1474     ambiguities.
1475    </para>
1476   </refsect2>
1477
1478   <refsect2>
1479    <title><literal>ONLY</literal> and Parentheses</title>
1480
1481    <para>
1482     The SQL standard requires parentheses around the table name
1483     after <literal>ONLY</literal>, as in <literal>SELECT * FROM ONLY
1484     (tab1), ONLY (tab2) WHERE ...</literal>.  PostgreSQL supports that
1485     as well, but the parentheses are optional.  (This point applies
1486     equally to all SQL commands supporting the <literal>ONLY</literal>
1487     option.)
1488    </para>
1489   </refsect2>
1490
1491   <refsect2>
1492    <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
1493
1494    <para>
1495     In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
1496     only use output column names or numbers, while a <literal>GROUP
1497     BY</literal> clause can only use expressions based on input column
1498     names.  <productname>PostgreSQL</productname> extends each of
1499     these clauses to allow the other choice as well (but it uses the
1500     standard's interpretation if there is ambiguity).
1501     <productname>PostgreSQL</productname> also allows both clauses to
1502     specify arbitrary expressions.  Note that names appearing in an
1503     expression will always be taken as input-column names, not as
1504     output-column names.
1505    </para>
1506
1507    <para>
1508     SQL:1999 and later use a slightly different definition which is not
1509     entirely upward compatible with SQL-92.
1510     In most cases, however, <productname>PostgreSQL</productname>
1511     will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1512     BY</literal> expression the same way SQL:1999 does.
1513    </para>
1514   </refsect2>
1515
1516   <refsect2>
1517    <title><literal>WINDOW</literal> Clause Restrictions</title>
1518
1519    <para>
1520     The SQL standard provides additional options for the window
1521     <replaceable class="parameter">frame_clause</>.
1522     <productname>PostgreSQL</productname> currently supports only the
1523     options listed above.
1524    </para>
1525   </refsect2>
1526
1527   <refsect2>
1528    <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1529
1530    <para>
1531     The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
1532     are <productname>PostgreSQL</productname>-specific syntax, also
1533     used by <productname>MySQL</productname>.  The SQL:2008 standard
1534     has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
1535     ...</literal> for the same functionality, as shown above
1536     in <xref linkend="sql-limit" endterm="sql-limit-title">, and this
1537     syntax is also used by <productname>IBM DB2</productname>.
1538     (Applications written for <productname>Oracle</productname>
1539     frequently use a workaround involving the automatically
1540     generated <literal>rownum</literal> column, not available in
1541     PostgreSQL, to implement the effects of these clauses.)
1542    </para>
1543   </refsect2>
1544
1545   <refsect2>
1546    <title>Nonstandard Clauses</title>
1547
1548    <para>
1549     The clause <literal>DISTINCT ON</literal> is not defined in the
1550     SQL standard.
1551    </para>
1552   </refsect2>
1553  </refsect1>
1554 </refentry>