Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-select.html1609 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>SELECT</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-security-label.html" title="SECURITY LABEL" /><link rel="next" href="sql-selectinto.html" title="SELECT INTO" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">SELECT</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-security-label.html" title="SECURITY LABEL">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-selectinto.html" title="SELECT INTO">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-SELECT"><div class="titlepage"></div><a id="id-1.9.3.172.1" class="indexterm"></a><a id="id-1.9.3.172.2" class="indexterm"></a><a id="id-1.9.3.172.3" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SELECT</span></h2><p>SELECT, TABLE, WITH — retrieve rows from a table or view</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3[ WITH [ RECURSIVE ] <em class="replaceable"><code>with_query</code></em> [, ...] ]4SELECT [ ALL | DISTINCT [ ON ( <em class="replaceable"><code>expression</code></em> [, ...] ) ] ]5    [ * | <em class="replaceable"><code>expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] [, ...] ]6    [ FROM <em class="replaceable"><code>from_item</code></em> [, ...] ]7    [ WHERE <em class="replaceable"><code>condition</code></em> ]8    [ GROUP BY [ ALL | DISTINCT ] <em class="replaceable"><code>grouping_element</code></em> [, ...] ]9    [ HAVING <em class="replaceable"><code>condition</code></em> ]10    [ WINDOW <em class="replaceable"><code>window_name</code></em> AS ( <em class="replaceable"><code>window_definition</code></em> ) [, ...] ]11    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <em class="replaceable"><code>select</code></em> ]12    [ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...] ]13    [ LIMIT { <em class="replaceable"><code>count</code></em> | ALL } ]14    [ OFFSET <em class="replaceable"><code>start</code></em> [ ROW | ROWS ] ]15    [ FETCH { FIRST | NEXT } [ <em class="replaceable"><code>count</code></em> ] { ROW | ROWS } { ONLY | WITH TIES } ]16    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]17 18<span class="phrase">where <em class="replaceable"><code>from_item</code></em> can be one of:</span>19 20    [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ] [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]21                [ TABLESAMPLE <em class="replaceable"><code>sampling_method</code></em> ( <em class="replaceable"><code>argument</code></em> [, ...] ) [ REPEATABLE ( <em class="replaceable"><code>seed</code></em> ) ] ]22    [ LATERAL ] ( <em class="replaceable"><code>select</code></em> ) [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]23    <em class="replaceable"><code>with_query_name</code></em> [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]24    [ LATERAL ] <em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] )25                [ WITH ORDINALITY ] [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]26    [ LATERAL ] <em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] ) [ AS ] <em class="replaceable"><code>alias</code></em> ( <em class="replaceable"><code>column_definition</code></em> [, ...] )27    [ LATERAL ] <em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] ) AS ( <em class="replaceable"><code>column_definition</code></em> [, ...] )28    [ LATERAL ] ROWS FROM( <em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] ) [ AS ( <em class="replaceable"><code>column_definition</code></em> [, ...] ) ] [, ...] )29                [ WITH ORDINALITY ] [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]30    <em class="replaceable"><code>from_item</code></em> <em class="replaceable"><code>join_type</code></em> <em class="replaceable"><code>from_item</code></em> { ON <em class="replaceable"><code>join_condition</code></em> | USING ( <em class="replaceable"><code>join_column</code></em> [, ...] ) [ AS <em class="replaceable"><code>join_using_alias</code></em> ] }31    <em class="replaceable"><code>from_item</code></em> NATURAL <em class="replaceable"><code>join_type</code></em> <em class="replaceable"><code>from_item</code></em>32    <em class="replaceable"><code>from_item</code></em> CROSS JOIN <em class="replaceable"><code>from_item</code></em>33 34<span class="phrase">and <em class="replaceable"><code>grouping_element</code></em> can be one of:</span>35 36    ( )37    <em class="replaceable"><code>expression</code></em>38    ( <em class="replaceable"><code>expression</code></em> [, ...] )39    ROLLUP ( { <em class="replaceable"><code>expression</code></em> | ( <em class="replaceable"><code>expression</code></em> [, ...] ) } [, ...] )40    CUBE ( { <em class="replaceable"><code>expression</code></em> | ( <em class="replaceable"><code>expression</code></em> [, ...] ) } [, ...] )41    GROUPING SETS ( <em class="replaceable"><code>grouping_element</code></em> [, ...] )42 43<span class="phrase">and <em class="replaceable"><code>with_query</code></em> is:</span>44 45    <em class="replaceable"><code>with_query_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( <em class="replaceable"><code>select</code></em> | <em class="replaceable"><code>values</code></em> | <em class="replaceable"><code>insert</code></em> | <em class="replaceable"><code>update</code></em> | <em class="replaceable"><code>delete</code></em> )46        [ SEARCH { BREADTH | DEPTH } FIRST BY <em class="replaceable"><code>column_name</code></em> [, ...] SET <em class="replaceable"><code>search_seq_col_name</code></em> ]47        [ CYCLE <em class="replaceable"><code>column_name</code></em> [, ...] SET <em class="replaceable"><code>cycle_mark_col_name</code></em> [ TO <em class="replaceable"><code>cycle_mark_value</code></em> DEFAULT <em class="replaceable"><code>cycle_mark_default</code></em> ] USING <em class="replaceable"><code>cycle_path_col_name</code></em> ]48 49TABLE [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ]50</pre></div><div class="refsect1" id="id-1.9.3.172.7"><h2>Description</h2><p>51   <code class="command">SELECT</code> retrieves rows from zero or more tables.52   The general processing of <code class="command">SELECT</code> is as follows:53 54   </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>55      All queries in the <code class="literal">WITH</code> list are computed.56      These effectively serve as temporary tables that can be referenced57      in the <code class="literal">FROM</code> list.  A <code class="literal">WITH</code> query58      that is referenced more than once in <code class="literal">FROM</code> is59      computed only once,60      unless specified otherwise with <code class="literal">NOT MATERIALIZED</code>.61      (See <a class="xref" href="sql-select.html#SQL-WITH" title="WITH Clause">WITH Clause</a> below.)62     </p></li><li class="listitem"><p>63      All elements in the <code class="literal">FROM</code> list are computed.64      (Each element in the <code class="literal">FROM</code> list is a real or65      virtual table.)  If more than one element is specified in the66      <code class="literal">FROM</code> list, they are cross-joined together.67      (See <a class="xref" href="sql-select.html#SQL-FROM" title="FROM Clause">FROM Clause</a> below.)68     </p></li><li class="listitem"><p>69      If the <code class="literal">WHERE</code> clause is specified, all rows70      that do not satisfy the condition are eliminated from the71      output.  (See <a class="xref" href="sql-select.html#SQL-WHERE" title="WHERE Clause">WHERE Clause</a> below.)72     </p></li><li class="listitem"><p>73      If the <code class="literal">GROUP BY</code> clause is specified,74      or if there are aggregate function calls, the75      output is combined into groups of rows that match on one or more76      values, and the results of aggregate functions are computed.77      If the <code class="literal">HAVING</code> clause is present, it78      eliminates groups that do not satisfy the given condition.  (See79      <a class="xref" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause">GROUP BY Clause</a> and80      <a class="xref" href="sql-select.html#SQL-HAVING" title="HAVING Clause">HAVING Clause</a> below.)81      Although query output columns are nominally computed in the next82      step, they can also be referenced (by name or ordinal number)83      in the <code class="literal">GROUP BY</code> clause.84     </p></li><li class="listitem"><p>85      The actual output rows are computed using the86      <code class="command">SELECT</code> output expressions for each selected87      row or row group.  (See <a class="xref" href="sql-select.html#SQL-SELECT-LIST" title="SELECT List">SELECT List</a> below.)88     </p></li><li class="listitem"><p><code class="literal">SELECT DISTINCT</code> eliminates duplicate rows from the89      result.  <code class="literal">SELECT DISTINCT ON</code> eliminates rows that90      match on all the specified expressions.  <code class="literal">SELECT ALL</code>91      (the default) will return all candidate rows, including92      duplicates.  (See <a class="xref" href="sql-select.html#SQL-DISTINCT" title="DISTINCT Clause">DISTINCT Clause</a> below.)93     </p></li><li class="listitem"><p>94      Using the operators <code class="literal">UNION</code>,95      <code class="literal">INTERSECT</code>, and <code class="literal">EXCEPT</code>, the96      output of more than one <code class="command">SELECT</code> statement can97      be combined to form a single result set.  The98      <code class="literal">UNION</code> operator returns all rows that are in99      one or both of the result sets.  The100      <code class="literal">INTERSECT</code> operator returns all rows that are101      strictly in both result sets.  The <code class="literal">EXCEPT</code>102      operator returns the rows that are in the first result set but103      not in the second.  In all three cases, duplicate rows are104      eliminated unless <code class="literal">ALL</code> is specified.  The noise105      word <code class="literal">DISTINCT</code> can be added to explicitly specify106      eliminating duplicate rows.  Notice that <code class="literal">DISTINCT</code> is107      the default behavior here, even though <code class="literal">ALL</code> is108      the default for <code class="command">SELECT</code> itself.  (See109      <a class="xref" href="sql-select.html#SQL-UNION" title="UNION Clause">UNION Clause</a>, <a class="xref" href="sql-select.html#SQL-INTERSECT" title="INTERSECT Clause">INTERSECT Clause</a>, and110      <a class="xref" href="sql-select.html#SQL-EXCEPT" title="EXCEPT Clause">EXCEPT Clause</a> below.)111     </p></li><li class="listitem"><p>112      If the <code class="literal">ORDER BY</code> clause is specified, the113      returned rows are sorted in the specified order.  If114      <code class="literal">ORDER BY</code> is not given, the rows are returned115      in whatever order the system finds fastest to produce.  (See116      <a class="xref" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause">ORDER BY Clause</a> below.)117     </p></li><li class="listitem"><p>118      If the <code class="literal">LIMIT</code> (or <code class="literal">FETCH FIRST</code>) or <code class="literal">OFFSET</code>119      clause is specified, the <code class="command">SELECT</code> statement120      only returns a subset of the result rows. (See <a class="xref" href="sql-select.html#SQL-LIMIT" title="LIMIT Clause">LIMIT Clause</a> below.)121     </p></li><li class="listitem"><p>122      If <code class="literal">FOR UPDATE</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code>123      or <code class="literal">FOR KEY SHARE</code>124      is specified, the125      <code class="command">SELECT</code> statement locks the selected rows126      against concurrent updates.  (See <a class="xref" href="sql-select.html#SQL-FOR-UPDATE-SHARE" title="The Locking Clause">The Locking Clause</a>127      below.)128     </p></li></ol></div><p>129  </p><p>130   You must have <code class="literal">SELECT</code> privilege on each column used131   in a <code class="command">SELECT</code> command.  The use of <code class="literal">FOR NO KEY UPDATE</code>,132   <code class="literal">FOR UPDATE</code>,133   <code class="literal">FOR SHARE</code> or <code class="literal">FOR KEY SHARE</code> requires134   <code class="literal">UPDATE</code> privilege as well (for at least one column135   of each table so selected).136  </p></div><div class="refsect1" id="id-1.9.3.172.8"><h2>Parameters</h2><div class="refsect2" id="SQL-WITH"><h3><code class="literal">WITH</code> Clause</h3><p>137    The <code class="literal">WITH</code> clause allows you to specify one or more138    subqueries that can be referenced by name in the primary query.139    The subqueries effectively act as temporary tables or views140    for the duration of the primary query.141    Each subquery can be a <code class="command">SELECT</code>, <code class="command">TABLE</code>, <code class="command">VALUES</code>,142    <code class="command">INSERT</code>, <code class="command">UPDATE</code> or143    <code class="command">DELETE</code> statement.144    When writing a data-modifying statement (<code class="command">INSERT</code>,145    <code class="command">UPDATE</code> or <code class="command">DELETE</code>) in146    <code class="literal">WITH</code>, it is usual to include a <code class="literal">RETURNING</code> clause.147    It is the output of <code class="literal">RETURNING</code>, <span class="emphasis"><em>not</em></span> the underlying148    table that the statement modifies, that forms the temporary table that is149    read by the primary query.  If <code class="literal">RETURNING</code> is omitted, the150    statement is still executed, but it produces no output so it cannot be151    referenced as a table by the primary query.152   </p><p>153    A name (without schema qualification) must be specified for each154    <code class="literal">WITH</code> query.  Optionally, a list of column names155    can be specified; if this is omitted,156    the column names are inferred from the subquery.157   </p><p>158    If <code class="literal">RECURSIVE</code> is specified, it allows a159    <code class="command">SELECT</code> subquery to reference itself by name.  Such a160    subquery must have the form161</p><pre class="synopsis">162<em class="replaceable"><code>non_recursive_term</code></em> UNION [ ALL | DISTINCT ] <em class="replaceable"><code>recursive_term</code></em>163</pre><p>164    where the recursive self-reference must appear on the right-hand165    side of the <code class="literal">UNION</code>.  Only one recursive self-reference166    is permitted per query.  Recursive data-modifying statements are not167    supported, but you can use the results of a recursive168    <code class="command">SELECT</code> query in169    a data-modifying statement.  See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> for170    an example.171   </p><p>172    Another effect of <code class="literal">RECURSIVE</code> is that173    <code class="literal">WITH</code> queries need not be ordered: a query174    can reference another one that is later in the list.  (However,175    circular references, or mutual recursion, are not implemented.)176    Without <code class="literal">RECURSIVE</code>, <code class="literal">WITH</code> queries177    can only reference sibling <code class="literal">WITH</code> queries178    that are earlier in the <code class="literal">WITH</code> list.179   </p><p>180    When there are multiple queries in the <code class="literal">WITH</code>181    clause, <code class="literal">RECURSIVE</code> should be written only once,182    immediately after <code class="literal">WITH</code>.  It applies to all queries183    in the <code class="literal">WITH</code> clause, though it has no effect on184    queries that do not use recursion or forward references.185   </p><p>186    The optional <code class="literal">SEARCH</code> clause computes a <em class="firstterm">search187    sequence column</em> that can be used for ordering the results of a188    recursive query in either breadth-first or depth-first order.  The189    supplied column name list specifies the row key that is to be used for190    keeping track of visited rows.  A column named191    <em class="replaceable"><code>search_seq_col_name</code></em> will be added to the result192    column list of the <code class="literal">WITH</code> query.  This column can be193    ordered by in the outer query to achieve the respective ordering.  See194    <a class="xref" href="queries-with.html#QUERIES-WITH-SEARCH" title="7.8.2.1. Search Order">Section 7.8.2.1</a> for examples.195   </p><p>196    The optional <code class="literal">CYCLE</code> clause is used to detect cycles in197    recursive queries.  The supplied column name list specifies the row key198    that is to be used for keeping track of visited rows.  A column named199    <em class="replaceable"><code>cycle_mark_col_name</code></em> will be added to the result200    column list of the <code class="literal">WITH</code> query.  This column will be set201    to <em class="replaceable"><code>cycle_mark_value</code></em> when a cycle has been202    detected, else to <em class="replaceable"><code>cycle_mark_default</code></em>.203    Furthermore, processing of the recursive union will stop when a cycle has204    been detected.  <em class="replaceable"><code>cycle_mark_value</code></em> and205    <em class="replaceable"><code>cycle_mark_default</code></em> must be constants and they206    must be coercible to a common data type, and the data type must have an207    inequality operator.  (The SQL standard requires that they be Boolean208    constants or character strings, but PostgreSQL does not require that.)  By209    default, <code class="literal">TRUE</code> and <code class="literal">FALSE</code> (of type210    <code class="type">boolean</code>) are used.  Furthermore, a column211    named <em class="replaceable"><code>cycle_path_col_name</code></em> will be added to the212    result column list of the <code class="literal">WITH</code> query.  This column is213    used internally for tracking visited rows.  See <a class="xref" href="queries-with.html#QUERIES-WITH-CYCLE" title="7.8.2.2. Cycle Detection">Section 7.8.2.2</a> for examples.214   </p><p>215    Both the <code class="literal">SEARCH</code> and the <code class="literal">CYCLE</code> clause216    are only valid for recursive <code class="literal">WITH</code> queries.  The217    <em class="replaceable"><code>with_query</code></em> must be a <code class="literal">UNION</code>218    (or <code class="literal">UNION ALL</code>) of two <code class="literal">SELECT</code> (or219    equivalent) commands (no nested <code class="literal">UNION</code>s).  If both220    clauses are used, the column added by the <code class="literal">SEARCH</code> clause221    appears before the columns added by the <code class="literal">CYCLE</code> clause.222   </p><p>223    The primary query and the <code class="literal">WITH</code> queries are all224    (notionally) executed at the same time.  This implies that the effects of225    a data-modifying statement in <code class="literal">WITH</code> cannot be seen from226    other parts of the query, other than by reading its <code class="literal">RETURNING</code>227    output.  If two such data-modifying statements attempt to modify the same228    row, the results are unspecified.229   </p><p>230    A key property of <code class="literal">WITH</code> queries is that they231    are normally evaluated only once per execution of the primary query,232    even if the primary query refers to them more than once.233    In particular, data-modifying statements are guaranteed to be234    executed once and only once, regardless of whether the primary query235    reads all or any of their output.236   </p><p>237    However, a <code class="literal">WITH</code> query can be marked238    <code class="literal">NOT MATERIALIZED</code> to remove this guarantee.  In that239    case, the <code class="literal">WITH</code> query can be folded into the primary240    query much as though it were a simple sub-<code class="literal">SELECT</code> in241    the primary query's <code class="literal">FROM</code> clause.  This results in242    duplicate computations if the primary query refers to243    that <code class="literal">WITH</code> query more than once; but if each such use244    requires only a few rows of the <code class="literal">WITH</code> query's total245    output, <code class="literal">NOT MATERIALIZED</code> can provide a net savings by246    allowing the queries to be optimized jointly.247    <code class="literal">NOT MATERIALIZED</code> is ignored if it is attached to248    a <code class="literal">WITH</code> query that is recursive or is not249    side-effect-free (i.e., is not a plain <code class="literal">SELECT</code>250    containing no volatile functions).251   </p><p>252    By default, a side-effect-free <code class="literal">WITH</code> query is folded253    into the primary query if it is used exactly once in the primary254    query's <code class="literal">FROM</code> clause.  This allows joint optimization255    of the two query levels in situations where that should be semantically256    invisible.  However, such folding can be prevented by marking the257    <code class="literal">WITH</code> query as <code class="literal">MATERIALIZED</code>.258    That might be useful, for example, if the <code class="literal">WITH</code> query259    is being used as an optimization fence to prevent the planner from260    choosing a bad plan.261    <span class="productname">PostgreSQL</span> versions before v12 never did262    such folding, so queries written for older versions might rely on263    <code class="literal">WITH</code> to act as an optimization fence.264   </p><p>265    See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> for additional information.266   </p></div><div class="refsect2" id="SQL-FROM"><h3><code class="literal">FROM</code> Clause</h3><p>267    The <code class="literal">FROM</code> clause specifies one or more source268    tables for the <code class="command">SELECT</code>.  If multiple sources are269    specified, the result is the Cartesian product (cross join) of all270    the sources.  But usually qualification conditions are added (via271    <code class="literal">WHERE</code>) to restrict the returned rows to a small subset of the272    Cartesian product.273   </p><p>274    The <code class="literal">FROM</code> clause can contain the following275    elements:276 277    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>278        The name (optionally schema-qualified) of an existing table or view.279        If <code class="literal">ONLY</code> is specified before the table name, only that280        table is scanned.  If <code class="literal">ONLY</code> is not specified, the table281        and all its descendant tables (if any) are scanned.  Optionally,282        <code class="literal">*</code> can be specified after the table name to explicitly283        indicate that descendant tables are included.284       </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>285        A substitute name for the <code class="literal">FROM</code> item containing the286        alias.  An alias is used for brevity or to eliminate ambiguity287        for self-joins (where the same table is scanned multiple288        times).  When an alias is provided, it completely hides the289        actual name of the table or function; for example given290        <code class="literal">FROM foo AS f</code>, the remainder of the291        <code class="command">SELECT</code> must refer to this <code class="literal">FROM</code>292        item as <code class="literal">f</code> not <code class="literal">foo</code>.  If an alias is293        written, a column alias list can also be written to provide294        substitute names for one or more columns of the table.295       </p></dd><dt><span class="term"><code class="literal">TABLESAMPLE <em class="replaceable"><code>sampling_method</code></em> ( <em class="replaceable"><code>argument</code></em> [, ...] ) [ REPEATABLE ( <em class="replaceable"><code>seed</code></em> ) ]</code></span></dt><dd><p>296        A <code class="literal">TABLESAMPLE</code> clause after297        a <em class="replaceable"><code>table_name</code></em> indicates that the298        specified <em class="replaceable"><code>sampling_method</code></em>299        should be used to retrieve a subset of the rows in that table.300        This sampling precedes the application of any other filters such301        as <code class="literal">WHERE</code> clauses.302        The standard <span class="productname">PostgreSQL</span> distribution303        includes two sampling methods, <code class="literal">BERNOULLI</code>304        and <code class="literal">SYSTEM</code>, and other sampling methods can be305        installed in the database via extensions.306       </p><p>307        The <code class="literal">BERNOULLI</code> and <code class="literal">SYSTEM</code> sampling methods308        each accept a single <em class="replaceable"><code>argument</code></em>309        which is the fraction of the table to sample, expressed as a310        percentage between 0 and 100.  This argument can be311        any <code class="type">real</code>-valued expression.  (Other sampling methods might312        accept more or different arguments.)  These two methods each return313        a randomly-chosen sample of the table that will contain314        approximately the specified percentage of the table's rows.315        The <code class="literal">BERNOULLI</code> method scans the whole table and316        selects or ignores individual rows independently with the specified317        probability.318        The <code class="literal">SYSTEM</code> method does block-level sampling with319        each block having the specified chance of being selected; all rows320        in each selected block are returned.321        The <code class="literal">SYSTEM</code> method is significantly faster than322        the <code class="literal">BERNOULLI</code> method when small sampling323        percentages are specified, but it may return a less-random sample of324        the table as a result of clustering effects.325       </p><p>326        The optional <code class="literal">REPEATABLE</code> clause specifies327        a <em class="replaceable"><code>seed</code></em> number or expression to use328        for generating random numbers within the sampling method.  The seed329        value can be any non-null floating-point value.  Two queries that330        specify the same seed and <em class="replaceable"><code>argument</code></em>331        values will select the same sample of the table, if the table has332        not been changed meanwhile.  But different seed values will usually333        produce different samples.334        If <code class="literal">REPEATABLE</code> is not given then a new random335        sample is selected for each query, based upon a system-generated seed.336        Note that some add-on sampling methods do not337        accept <code class="literal">REPEATABLE</code>, and will always produce new338        samples on each use.339       </p></dd><dt><span class="term"><em class="replaceable"><code>select</code></em></span></dt><dd><p>340        A sub-<code class="command">SELECT</code> can appear in the341        <code class="literal">FROM</code> clause.  This acts as though its342        output were created as a temporary table for the duration of343        this single <code class="command">SELECT</code> command.  Note that the344        sub-<code class="command">SELECT</code> must be surrounded by345        parentheses, and an alias can be provided in the same way as for a346        table.  A347        <a class="link" href="sql-values.html" title="VALUES"><code class="command">VALUES</code></a> command348        can also be used here.349       </p></dd><dt><span class="term"><em class="replaceable"><code>with_query_name</code></em></span></dt><dd><p>350        A <code class="literal">WITH</code> query is referenced by writing its name,351        just as though the query's name were a table name.  (In fact,352        the <code class="literal">WITH</code> query hides any real table of the same name353        for the purposes of the primary query.  If necessary, you can354        refer to a real table of the same name by schema-qualifying355        the table's name.)356        An alias can be provided in the same way as for a table.357       </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>358        Function calls can appear in the <code class="literal">FROM</code>359        clause.  (This is especially useful for functions that return360        result sets, but any function can be used.)  This acts as361        though the function's output were created as a temporary table for the362        duration of this single <code class="command">SELECT</code> command.363        If the function's result type is composite (including the case of a364        function with multiple <code class="literal">OUT</code> parameters), each365        attribute becomes a separate column in the implicit table.366       </p><p>367        When the optional <code class="command">WITH ORDINALITY</code> clause is added368        to the function call, an additional column of type <code class="type">bigint</code>369        will be appended to the function's result column(s).  This column370        numbers the rows of the function's result set, starting from 1.371        By default, this column is named <code class="literal">ordinality</code>.372       </p><p>373        An alias can be provided in the same way as for a table.374        If an alias is written, a column375        alias list can also be written to provide substitute names for376        one or more attributes of the function's composite return377        type, including the ordinality column if present.378       </p><p>379        Multiple function calls can be combined into a380        single <code class="literal">FROM</code>-clause item by surrounding them381        with <code class="literal">ROWS FROM( ... )</code>.  The output of such an item is the382        concatenation of the first row from each function, then the second383        row from each function, etc.  If some of the functions produce fewer384        rows than others, null values are substituted for the missing data, so385        that the total number of rows returned is always the same as for the386        function that produced the most rows.387       </p><p>388        If the function has been defined as returning the389        <code class="type">record</code> data type, then an alias or the key word390        <code class="literal">AS</code> must be present, followed by a column391        definition list in the form <code class="literal">( <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [<span class="optional">, ...392        </span>])</code>.  The column definition list must match the393        actual number and types of columns returned by the function.394       </p><p>395        When using the <code class="literal">ROWS FROM( ... )</code> syntax, if one of the396        functions requires a column definition list, it's preferred to put397        the column definition list after the function call inside398        <code class="literal">ROWS FROM( ... )</code>.  A column definition list can be placed399        after the <code class="literal">ROWS FROM( ... )</code> construct only if there's just400        a single function and no <code class="literal">WITH ORDINALITY</code> clause.401       </p><p>402        To use <code class="literal">ORDINALITY</code> together with a column definition403        list, you must use the <code class="literal">ROWS FROM( ... )</code> syntax and put the404        column definition list inside <code class="literal">ROWS FROM( ... )</code>.405       </p></dd><dt><span class="term"><em class="replaceable"><code>join_type</code></em></span></dt><dd><p>406        One of407        </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">[ INNER ] JOIN</code></p></li><li class="listitem"><p><code class="literal">LEFT [ OUTER ] JOIN</code></p></li><li class="listitem"><p><code class="literal">RIGHT [ OUTER ] JOIN</code></p></li><li class="listitem"><p><code class="literal">FULL [ OUTER ] JOIN</code></p></li></ul></div><p>408 409        For the <code class="literal">INNER</code> and <code class="literal">OUTER</code> join types, a410        join condition must be specified, namely exactly one of411        <code class="literal">ON <em class="replaceable"><code>join_condition</code></em></code>,412        <code class="literal">USING (<em class="replaceable"><code>join_column</code></em> [, ...])</code>,413        or <code class="literal">NATURAL</code>.  See below for the meaning.414       </p><p>415        A <code class="literal">JOIN</code> clause combines two <code class="literal">FROM</code>416        items, which for convenience we will refer to as <span class="quote">“<span class="quote">tables</span>”</span>,417        though in reality they can be any type of <code class="literal">FROM</code> item.418        Use parentheses if necessary to determine the order of nesting.419        In the absence of parentheses, <code class="literal">JOIN</code>s nest420        left-to-right.  In any case <code class="literal">JOIN</code> binds more421        tightly than the commas separating <code class="literal">FROM</code>-list items.422        All the <code class="literal">JOIN</code> options are just a notational423        convenience, since they do nothing you couldn't do with plain424        <code class="literal">FROM</code> and <code class="literal">WHERE</code>.425       </p><p><code class="literal">LEFT OUTER JOIN</code> returns all rows in the qualified426        Cartesian product (i.e., all combined rows that pass its join427        condition), plus one copy of each row in the left-hand table428        for which there was no right-hand row that passed the join429        condition.  This left-hand row is extended to the full width430        of the joined table by inserting null values for the431        right-hand columns.  Note that only the <code class="literal">JOIN</code>432        clause's own condition is considered while deciding which rows433        have matches.  Outer conditions are applied afterwards.434       </p><p>435        Conversely, <code class="literal">RIGHT OUTER JOIN</code> returns all the436        joined rows, plus one row for each unmatched right-hand row437        (extended with nulls on the left).  This is just a notational438        convenience, since you could convert it to a <code class="literal">LEFT439        OUTER JOIN</code> by switching the left and right tables.440       </p><p><code class="literal">FULL OUTER JOIN</code> returns all the joined rows, plus441        one row for each unmatched left-hand row (extended with nulls442        on the right), plus one row for each unmatched right-hand row443        (extended with nulls on the left).444       </p></dd><dt><span class="term"><code class="literal">ON <em class="replaceable"><code>join_condition</code></em></code></span></dt><dd><p><em class="replaceable"><code>join_condition</code></em> is445        an expression resulting in a value of type446        <code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>447        clause) that specifies which rows in a join are considered to448        match.449       </p></dd><dt><span class="term"><code class="literal">USING ( <em class="replaceable"><code>join_column</code></em> [, ...] ) [ AS <em class="replaceable"><code>join_using_alias</code></em> ]</code></span></dt><dd><p>450        A clause of the form <code class="literal">USING ( a, b, ... )</code> is451        shorthand for <code class="literal">ON left_table.a = right_table.a AND452        left_table.b = right_table.b ...</code>.  Also,453        <code class="literal">USING</code> implies that only one of each pair of454        equivalent columns will be included in the join output, not455        both.456       </p><p>457        If a <em class="replaceable"><code>join_using_alias</code></em>458        name is specified, it provides a table alias for the join columns.459        Only the join columns listed in the <code class="literal">USING</code> clause460        are addressable by this name.  Unlike a regular <em class="replaceable"><code>alias</code></em>, this does not hide the names of461        the joined tables from the rest of the query.  Also unlike a regular462        <em class="replaceable"><code>alias</code></em>, you cannot write a463        column alias list — the output names of the join columns are the464        same as they appear in the <code class="literal">USING</code> list.465       </p></dd><dt><span class="term"><code class="literal">NATURAL</code></span></dt><dd><p>466        <code class="literal">NATURAL</code> is shorthand for a467        <code class="literal">USING</code> list that mentions all columns in the two468        tables that have matching names.  If there are no common469        column names, <code class="literal">NATURAL</code> is equivalent470        to <code class="literal">ON TRUE</code>.471       </p></dd><dt><span class="term"><code class="literal">CROSS JOIN</code></span></dt><dd><p>472        <code class="literal">CROSS JOIN</code> is equivalent to <code class="literal">INNER JOIN ON473        (TRUE)</code>, that is, no rows are removed by qualification.474        They produce a simple Cartesian product, the same result as you get from475        listing the two tables at the top level of <code class="literal">FROM</code>,476        but restricted by the join condition (if any).477       </p></dd><dt><span class="term"><code class="literal">LATERAL</code></span></dt><dd><p>478        The <code class="literal">LATERAL</code> key word can precede a479        sub-<code class="command">SELECT</code> <code class="literal">FROM</code> item.  This allows the480        sub-<code class="command">SELECT</code> to refer to columns of <code class="literal">FROM</code>481        items that appear before it in the <code class="literal">FROM</code> list.  (Without482        <code class="literal">LATERAL</code>, each sub-<code class="command">SELECT</code> is483        evaluated independently and so cannot cross-reference any other484        <code class="literal">FROM</code> item.)485       </p><p><code class="literal">LATERAL</code> can also precede a function-call486        <code class="literal">FROM</code> item, but in this case it is a noise word, because487        the function expression can refer to earlier <code class="literal">FROM</code> items488        in any case.489       </p><p>490        A <code class="literal">LATERAL</code> item can appear at top level in the491        <code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree.  In the492        latter case it can also refer to any items that are on the left-hand493        side of a <code class="literal">JOIN</code> that it is on the right-hand side of.494       </p><p>495        When a <code class="literal">FROM</code> item contains <code class="literal">LATERAL</code>496        cross-references, evaluation proceeds as follows: for each row of the497        <code class="literal">FROM</code> item providing the cross-referenced column(s), or498        set of rows of multiple <code class="literal">FROM</code> items providing the499        columns, the <code class="literal">LATERAL</code> item is evaluated using that500        row or row set's values of the columns.  The resulting row(s) are501        joined as usual with the rows they were computed from.  This is502        repeated for each row or set of rows from the column source table(s).503       </p><p>504        The column source table(s) must be <code class="literal">INNER</code> or505        <code class="literal">LEFT</code> joined to the <code class="literal">LATERAL</code> item, else506        there would not be a well-defined set of rows from which to compute507        each set of rows for the <code class="literal">LATERAL</code> item.  Thus,508        although a construct such as <code class="literal"><em class="replaceable"><code>X</code></em> RIGHT JOIN509        LATERAL <em class="replaceable"><code>Y</code></em></code> is syntactically valid, it is510        not actually allowed for <em class="replaceable"><code>Y</code></em> to reference511        <em class="replaceable"><code>X</code></em>.512       </p></dd></dl></div><p>513   </p></div><div class="refsect2" id="SQL-WHERE"><h3><code class="literal">WHERE</code> Clause</h3><p>514    The optional <code class="literal">WHERE</code> clause has the general form515</p><pre class="synopsis">516WHERE <em class="replaceable"><code>condition</code></em>517</pre><p>518    where <em class="replaceable"><code>condition</code></em> is519    any expression that evaluates to a result of type520    <code class="type">boolean</code>.  Any row that does not satisfy this521    condition will be eliminated from the output.  A row satisfies the522    condition if it returns true when the actual row values are523    substituted for any variable references.524   </p></div><div class="refsect2" id="SQL-GROUPBY"><h3><code class="literal">GROUP BY</code> Clause</h3><p>525    The optional <code class="literal">GROUP BY</code> clause has the general form526</p><pre class="synopsis">527GROUP BY [ ALL | DISTINCT ] <em class="replaceable"><code>grouping_element</code></em> [, ...]528</pre><p>529   </p><p>530    <code class="literal">GROUP BY</code> will condense into a single row all531    selected rows that share the same values for the grouped532    expressions.  An <em class="replaceable"><code>expression</code></em> used inside a533    <em class="replaceable"><code>grouping_element</code></em>534    can be an input column name, or the name or ordinal number of an535    output column (<code class="command">SELECT</code> list item), or an arbitrary536    expression formed from input-column values.  In case of ambiguity,537    a <code class="literal">GROUP BY</code> name will be interpreted as an538    input-column name rather than an output column name.539   </p><p>540    If any of <code class="literal">GROUPING SETS</code>, <code class="literal">ROLLUP</code> or541    <code class="literal">CUBE</code> are present as grouping elements, then the542    <code class="literal">GROUP BY</code> clause as a whole defines some number of543    independent <em class="replaceable"><code>grouping sets</code></em>.  The effect of this is544    equivalent to constructing a <code class="literal">UNION ALL</code> between545    subqueries with the individual grouping sets as their546    <code class="literal">GROUP BY</code> clauses.  The optional <code class="literal">DISTINCT</code>547    clause removes duplicate sets before processing; it does <span class="emphasis"><em>not</em></span>548    transform the <code class="literal">UNION ALL</code> into a <code class="literal">UNION DISTINCT</code>.549    For further details on the handling550    of grouping sets see <a class="xref" href="queries-table-expressions.html#QUERIES-GROUPING-SETS" title="7.2.4. GROUPING SETS, CUBE, and ROLLUP">Section 7.2.4</a>.551   </p><p>552    Aggregate functions, if any are used, are computed across all rows553    making up each group, producing a separate value for each group.554    (If there are aggregate functions but no <code class="literal">GROUP BY</code>555    clause, the query is treated as having a single group comprising all556    the selected rows.)557    The set of rows fed to each aggregate function can be further filtered by558    attaching a <code class="literal">FILTER</code> clause to the aggregate function559    call; see <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a> for more information.  When560    a <code class="literal">FILTER</code> clause is present, only those rows matching it561    are included in the input to that aggregate function.562   </p><p>563    When <code class="literal">GROUP BY</code> is present,564    or any aggregate functions are present, it is not valid for565    the <code class="command">SELECT</code> list expressions to refer to566    ungrouped columns except within aggregate functions or when the567    ungrouped column is functionally dependent on the grouped columns,568    since there would otherwise be more than one possible value to569    return for an ungrouped column.  A functional dependency exists if570    the grouped columns (or a subset thereof) are the primary key of571    the table containing the ungrouped column.572   </p><p>573    Keep in mind that all aggregate functions are evaluated before574    evaluating any <span class="quote">“<span class="quote">scalar</span>”</span> expressions in the <code class="literal">HAVING</code>575    clause or <code class="literal">SELECT</code> list.  This means that, for example,576    a <code class="literal">CASE</code> expression cannot be used to skip evaluation of577    an aggregate function; see <a class="xref" href="sql-expressions.html#SYNTAX-EXPRESS-EVAL" title="4.2.14. Expression Evaluation Rules">Section 4.2.14</a>.578   </p><p>579    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,580    <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be581    specified with <code class="literal">GROUP BY</code>.582   </p></div><div class="refsect2" id="SQL-HAVING"><h3><code class="literal">HAVING</code> Clause</h3><p>583    The optional <code class="literal">HAVING</code> clause has the general form584</p><pre class="synopsis">585HAVING <em class="replaceable"><code>condition</code></em>586</pre><p>587    where <em class="replaceable"><code>condition</code></em> is588    the same as specified for the <code class="literal">WHERE</code> clause.589   </p><p>590    <code class="literal">HAVING</code> eliminates group rows that do not591    satisfy the condition.  <code class="literal">HAVING</code> is different592    from <code class="literal">WHERE</code>: <code class="literal">WHERE</code> filters593    individual rows before the application of <code class="literal">GROUP594    BY</code>, while <code class="literal">HAVING</code> filters group rows595    created by <code class="literal">GROUP BY</code>.  Each column referenced in596    <em class="replaceable"><code>condition</code></em> must597    unambiguously reference a grouping column, unless the reference598    appears within an aggregate function or the ungrouped column is599    functionally dependent on the grouping columns.600   </p><p>601    The presence of <code class="literal">HAVING</code> turns a query into a grouped602    query even if there is no <code class="literal">GROUP BY</code> clause.  This is the603    same as what happens when the query contains aggregate functions but604    no <code class="literal">GROUP BY</code> clause.  All the selected rows are considered to605    form a single group, and the <code class="command">SELECT</code> list and606    <code class="literal">HAVING</code> clause can only reference table columns from607    within aggregate functions.  Such a query will emit a single row if the608    <code class="literal">HAVING</code> condition is true, zero rows if it is not true.609   </p><p>610    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,611    <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be612    specified with <code class="literal">HAVING</code>.613   </p></div><div class="refsect2" id="SQL-WINDOW"><h3><code class="literal">WINDOW</code> Clause</h3><p>614    The optional <code class="literal">WINDOW</code> clause has the general form615</p><pre class="synopsis">616WINDOW <em class="replaceable"><code>window_name</code></em> AS ( <em class="replaceable"><code>window_definition</code></em> ) [, ...]617</pre><p>618    where <em class="replaceable"><code>window_name</code></em> is619    a name that can be referenced from <code class="literal">OVER</code> clauses or620    subsequent window definitions, and621    <em class="replaceable"><code>window_definition</code></em> is622</p><pre class="synopsis">623[ <em class="replaceable"><code>existing_window_name</code></em> ]624[ PARTITION BY <em class="replaceable"><code>expression</code></em> [, ...] ]625[ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...] ]626[ <em class="replaceable"><code>frame_clause</code></em> ]627</pre><p>628   </p><p>629    If an <em class="replaceable"><code>existing_window_name</code></em>630    is specified it must refer to an earlier entry in the <code class="literal">WINDOW</code>631    list; the new window copies its partitioning clause from that entry,632    as well as its ordering clause if any.  In this case the new window cannot633    specify its own <code class="literal">PARTITION BY</code> clause, and it can specify634    <code class="literal">ORDER BY</code> only if the copied window does not have one.635    The new window always uses its own frame clause; the copied window636    must not specify a frame clause.637   </p><p>638    The elements of the <code class="literal">PARTITION BY</code> list are interpreted in639    much the same fashion as elements of a <a class="link" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code></a> clause, except that640    they are always simple expressions and never the name or number of an641    output column.642    Another difference is that these expressions can contain aggregate643    function calls, which are not allowed in a regular <code class="literal">GROUP BY</code>644    clause.  They are allowed here because windowing occurs after grouping645    and aggregation.646   </p><p>647    Similarly, the elements of the <code class="literal">ORDER BY</code> list are interpreted648    in much the same fashion as elements of a statement-level <a class="link" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code></a> clause, except that649    the expressions are always taken as simple expressions and never the name650    or number of an output column.651   </p><p>652    The optional <em class="replaceable"><code>frame_clause</code></em> defines653    the <em class="firstterm">window frame</em> for window functions that depend on the654    frame (not all do).  The window frame is a set of related rows for655    each row of the query (called the <em class="firstterm">current row</em>).656    The <em class="replaceable"><code>frame_clause</code></em> can be one of657 658</p><pre class="synopsis">659{ RANGE | ROWS | GROUPS } <em class="replaceable"><code>frame_start</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]660{ RANGE | ROWS | GROUPS } BETWEEN <em class="replaceable"><code>frame_start</code></em> AND <em class="replaceable"><code>frame_end</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]661</pre><p>662 663    where <em class="replaceable"><code>frame_start</code></em>664    and <em class="replaceable"><code>frame_end</code></em> can be one of665 666</p><pre class="synopsis">667UNBOUNDED PRECEDING668<em class="replaceable"><code>offset</code></em> PRECEDING669CURRENT ROW670<em class="replaceable"><code>offset</code></em> FOLLOWING671UNBOUNDED FOLLOWING672</pre><p>673 674    and <em class="replaceable"><code>frame_exclusion</code></em> can be one of675 676</p><pre class="synopsis">677EXCLUDE CURRENT ROW678EXCLUDE GROUP679EXCLUDE TIES680EXCLUDE NO OTHERS681</pre><p>682 683    If <em class="replaceable"><code>frame_end</code></em> is omitted it defaults to <code class="literal">CURRENT684    ROW</code>.  Restrictions are that685    <em class="replaceable"><code>frame_start</code></em> cannot be <code class="literal">UNBOUNDED FOLLOWING</code>,686    <em class="replaceable"><code>frame_end</code></em> cannot be <code class="literal">UNBOUNDED PRECEDING</code>,687    and the <em class="replaceable"><code>frame_end</code></em> choice cannot appear earlier in the688    above list of <em class="replaceable"><code>frame_start</code></em>689    and <em class="replaceable"><code>frame_end</code></em> options than690    the <em class="replaceable"><code>frame_start</code></em> choice does — for example691    <code class="literal">RANGE BETWEEN CURRENT ROW AND <em class="replaceable"><code>offset</code></em>692    PRECEDING</code> is not allowed.693   </p><p>694    The default framing option is <code class="literal">RANGE UNBOUNDED PRECEDING</code>,695    which is the same as <code class="literal">RANGE BETWEEN UNBOUNDED PRECEDING AND696    CURRENT ROW</code>; it sets the frame to be all rows from the partition start697    up through the current row's last <em class="firstterm">peer</em> (a row698    that the window's <code class="literal">ORDER BY</code> clause considers699    equivalent to the current row; all rows are peers if there700    is no <code class="literal">ORDER BY</code>).701    In general, <code class="literal">UNBOUNDED PRECEDING</code> means that the frame702    starts with the first row of the partition, and similarly703    <code class="literal">UNBOUNDED FOLLOWING</code> means that the frame ends with the last704    row of the partition, regardless705    of <code class="literal">RANGE</code>, <code class="literal">ROWS</code>706    or <code class="literal">GROUPS</code> mode.707    In <code class="literal">ROWS</code> mode, <code class="literal">CURRENT ROW</code> means708    that the frame starts or ends with the current row; but709    in <code class="literal">RANGE</code> or <code class="literal">GROUPS</code> mode it means710    that the frame starts or ends with the current row's first or last peer711    in the <code class="literal">ORDER BY</code> ordering.712    The <em class="replaceable"><code>offset</code></em> <code class="literal">PRECEDING</code> and713    <em class="replaceable"><code>offset</code></em> <code class="literal">FOLLOWING</code> options714    vary in meaning depending on the frame mode.715    In <code class="literal">ROWS</code> mode, the <em class="replaceable"><code>offset</code></em>716    is an integer indicating that the frame starts or ends that many rows717    before or after the current row.718    In <code class="literal">GROUPS</code> mode, the <em class="replaceable"><code>offset</code></em>719    is an integer indicating that the frame starts or ends that many peer720    groups before or after the current row's peer group, where721    a <em class="firstterm">peer group</em> is a group of rows that are722    equivalent according to the window's <code class="literal">ORDER BY</code> clause.723    In <code class="literal">RANGE</code> mode, use of724    an <em class="replaceable"><code>offset</code></em> option requires that there be725    exactly one <code class="literal">ORDER BY</code> column in the window definition.726    Then the frame contains those rows whose ordering column value is no727    more than <em class="replaceable"><code>offset</code></em> less than728    (for <code class="literal">PRECEDING</code>) or more than729    (for <code class="literal">FOLLOWING</code>) the current row's ordering column730    value.  In these cases the data type of731    the <em class="replaceable"><code>offset</code></em> expression depends on the data732    type of the ordering column.  For numeric ordering columns it is733    typically of the same type as the ordering column, but for datetime734    ordering columns it is an <code class="type">interval</code>.735    In all these cases, the value of the <em class="replaceable"><code>offset</code></em>736    must be non-null and non-negative.  Also, while737    the <em class="replaceable"><code>offset</code></em> does not have to be a simple738    constant, it cannot contain variables, aggregate functions, or window739    functions.740   </p><p>741    The <em class="replaceable"><code>frame_exclusion</code></em> option allows rows around742    the current row to be excluded from the frame, even if they would be743    included according to the frame start and frame end options.744    <code class="literal">EXCLUDE CURRENT ROW</code> excludes the current row from the745    frame.746    <code class="literal">EXCLUDE GROUP</code> excludes the current row and its747    ordering peers from the frame.748    <code class="literal">EXCLUDE TIES</code> excludes any peers of the current749    row from the frame, but not the current row itself.750    <code class="literal">EXCLUDE NO OTHERS</code> simply specifies explicitly the751    default behavior of not excluding the current row or its peers.752   </p><p>753    Beware that the <code class="literal">ROWS</code> mode can produce unpredictable754    results if the <code class="literal">ORDER BY</code> ordering does not order the rows755    uniquely.  The <code class="literal">RANGE</code> and <code class="literal">GROUPS</code>756    modes are designed to ensure that rows that are peers in757    the <code class="literal">ORDER BY</code> ordering are treated alike: all rows of758    a given peer group will be in the frame or excluded from it.759   </p><p>760    The purpose of a <code class="literal">WINDOW</code> clause is to specify the761    behavior of <em class="firstterm">window functions</em> appearing in the query's762    <a class="link" href="sql-select.html#SQL-SELECT-LIST" title="SELECT List"><code class="command">SELECT</code> list</a> or763    <a class="link" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code></a> clause.764    These functions765    can reference the <code class="literal">WINDOW</code> clause entries by name766    in their <code class="literal">OVER</code> clauses.  A <code class="literal">WINDOW</code> clause767    entry does not have to be referenced anywhere, however; if it is not768    used in the query it is simply ignored.  It is possible to use window769    functions without any <code class="literal">WINDOW</code> clause at all, since770    a window function call can specify its window definition directly in771    its <code class="literal">OVER</code> clause.  However, the <code class="literal">WINDOW</code>772    clause saves typing when the same window definition is needed for more773    than one window function.774   </p><p>775    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,776    <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be777    specified with <code class="literal">WINDOW</code>.778   </p><p>779    Window functions are described in detail in780    <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,781    <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>, and782    <a class="xref" href="queries-table-expressions.html#QUERIES-WINDOW" title="7.2.5. Window Function Processing">Section 7.2.5</a>.783   </p></div><div class="refsect2" id="SQL-SELECT-LIST"><h3><code class="command">SELECT</code> List</h3><p>784    The <code class="command">SELECT</code> list (between the key words785    <code class="literal">SELECT</code> and <code class="literal">FROM</code>) specifies expressions786    that form the output rows of the <code class="command">SELECT</code>787    statement.  The expressions can (and usually do) refer to columns788    computed in the <code class="literal">FROM</code> clause.789   </p><p>790    Just as in a table, every output column of a <code class="command">SELECT</code>791    has a name.  In a simple <code class="command">SELECT</code> this name is just792    used to label the column for display, but when the <code class="command">SELECT</code>793    is a sub-query of a larger query, the name is seen by the larger query794    as the column name of the virtual table produced by the sub-query.795    To specify the name to use for an output column, write796    <code class="literal">AS</code> <em class="replaceable"><code>output_name</code></em>797    after the column's expression.  (You can omit <code class="literal">AS</code>,798    but only if the desired output name does not match any799    <span class="productname">PostgreSQL</span> keyword (see <a class="xref" href="sql-keywords-appendix.html" title="Appendix C. SQL Key Words">Appendix C</a>).  For protection against possible800    future keyword additions, it is recommended that you always either801    write <code class="literal">AS</code> or double-quote the output name.)802    If you do not specify a column name, a name is chosen automatically803    by <span class="productname">PostgreSQL</span>.  If the column's expression804    is a simple column reference then the chosen name is the same as that805    column's name.  In more complex cases a function or type name may be806    used, or the system may fall back on a generated name such as807    <code class="literal">?column?</code>.808   </p><p>809    An output column's name can be used to refer to the column's value in810    <code class="literal">ORDER BY</code> and <code class="literal">GROUP BY</code> clauses, but not in the811    <code class="literal">WHERE</code> or <code class="literal">HAVING</code> clauses; there you must write812    out the expression instead.813   </p><p>814    Instead of an expression, <code class="literal">*</code> can be written in815    the output list as a shorthand for all the columns of the selected816    rows.  Also, you can write <code class="literal"><em class="replaceable"><code>table_name</code></em>.*</code> as a817    shorthand for the columns coming from just that table.  In these818    cases it is not possible to specify new names with <code class="literal">AS</code>;819    the output column names will be the same as the table columns' names.820   </p><p>821    According to the SQL standard, the expressions in the output list should822    be computed before applying <code class="literal">DISTINCT</code>, <code class="literal">ORDER823    BY</code>, or <code class="literal">LIMIT</code>.  This is obviously necessary824    when using <code class="literal">DISTINCT</code>, since otherwise it's not clear825    what values are being made distinct.  However, in many cases it is826    convenient if output expressions are computed after <code class="literal">ORDER827    BY</code> and <code class="literal">LIMIT</code>; particularly if the output list828    contains any volatile or expensive functions.  With that behavior, the829    order of function evaluations is more intuitive and there will not be830    evaluations corresponding to rows that never appear in the output.831    <span class="productname">PostgreSQL</span> will effectively evaluate output expressions832    after sorting and limiting, so long as those expressions are not833    referenced in <code class="literal">DISTINCT</code>, <code class="literal">ORDER BY</code>834    or <code class="literal">GROUP BY</code>.  (As a counterexample, <code class="literal">SELECT835    f(x) FROM tab ORDER BY 1</code> clearly must evaluate <code class="function">f(x)</code>836    before sorting.)  Output expressions that contain set-returning functions837    are effectively evaluated after sorting and before limiting, so838    that <code class="literal">LIMIT</code> will act to cut off the output from a839    set-returning function.840   </p><div class="note"><h3 class="title">Note</h3><p>841     <span class="productname">PostgreSQL</span> versions before 9.6 did not provide any842     guarantees about the timing of evaluation of output expressions versus843     sorting and limiting; it depended on the form of the chosen query plan.844    </p></div></div><div class="refsect2" id="SQL-DISTINCT"><h3><code class="literal">DISTINCT</code> Clause</h3><p>845    If <code class="literal">SELECT DISTINCT</code> is specified, all duplicate rows are846    removed from the result set (one row is kept from each group of847    duplicates).  <code class="literal">SELECT ALL</code> specifies the opposite: all rows are848    kept; that is the default.849   </p><p>850    <code class="literal">SELECT DISTINCT ON ( <em class="replaceable"><code>expression</code></em> [, ...] )</code>851    keeps only the first row of each set of rows where the given852    expressions evaluate to equal.  The <code class="literal">DISTINCT ON</code>853    expressions are interpreted using the same rules as for854    <code class="literal">ORDER BY</code> (see above).  Note that the <span class="quote">“<span class="quote">first855    row</span>”</span> of each set is unpredictable unless <code class="literal">ORDER856    BY</code> is used to ensure that the desired row appears first.  For857    example:858</p><pre class="programlisting">859SELECT DISTINCT ON (location) location, time, report860    FROM weather_reports861    ORDER BY location, time DESC;862</pre><p>863    retrieves the most recent weather report for each location.  But864    if we had not used <code class="literal">ORDER BY</code> to force descending order865    of time values for each location, we'd have gotten a report from866    an unpredictable time for each location.867   </p><p>868    The <code class="literal">DISTINCT ON</code> expression(s) must match the leftmost869    <code class="literal">ORDER BY</code> expression(s).  The <code class="literal">ORDER BY</code> clause870    will normally contain additional expression(s) that determine the871    desired precedence of rows within each <code class="literal">DISTINCT ON</code> group.872   </p><p>873    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,874    <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be875    specified with <code class="literal">DISTINCT</code>.876   </p></div><div class="refsect2" id="SQL-UNION"><h3><code class="literal">UNION</code> Clause</h3><p>877    The <code class="literal">UNION</code> clause has this general form:878</p><pre class="synopsis">879<em class="replaceable"><code>select_statement</code></em> UNION [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>880</pre><p><em class="replaceable"><code>select_statement</code></em> is881    any <code class="command">SELECT</code> statement without an <code class="literal">ORDER882    BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,883    <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.884    (<code class="literal">ORDER BY</code> and <code class="literal">LIMIT</code> can be attached to a885    subexpression if it is enclosed in parentheses.  Without886    parentheses, these clauses will be taken to apply to the result of887    the <code class="literal">UNION</code>, not to its right-hand input888    expression.)889   </p><p>890    The <code class="literal">UNION</code> operator computes the set union of891    the rows returned by the involved <code class="command">SELECT</code>892    statements.  A row is in the set union of two result sets if it893    appears in at least one of the result sets.  The two894    <code class="command">SELECT</code> statements that represent the direct895    operands of the <code class="literal">UNION</code> must produce the same896    number of columns, and corresponding columns must be of compatible897    data types.898   </p><p>899    The result of <code class="literal">UNION</code> does not contain any duplicate900    rows unless the <code class="literal">ALL</code> option is specified.901    <code class="literal">ALL</code> prevents elimination of duplicates.  (Therefore,902    <code class="literal">UNION ALL</code> is usually significantly quicker than903    <code class="literal">UNION</code>; use <code class="literal">ALL</code> when you can.)904    <code class="literal">DISTINCT</code> can be written to explicitly specify the905    default behavior of eliminating duplicate rows.906   </p><p>907    Multiple <code class="literal">UNION</code> operators in the same908    <code class="command">SELECT</code> statement are evaluated left to right,909    unless otherwise indicated by parentheses.910   </p><p>911    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and912    <code class="literal">FOR KEY SHARE</code> cannot be913    specified either for a <code class="literal">UNION</code> result or for any input of a914    <code class="literal">UNION</code>.915   </p></div><div class="refsect2" id="SQL-INTERSECT"><h3><code class="literal">INTERSECT</code> Clause</h3><p>916    The <code class="literal">INTERSECT</code> clause has this general form:917</p><pre class="synopsis">918<em class="replaceable"><code>select_statement</code></em> INTERSECT [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>919</pre><p><em class="replaceable"><code>select_statement</code></em> is920    any <code class="command">SELECT</code> statement without an <code class="literal">ORDER921    BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,922    <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.923   </p><p>924    The <code class="literal">INTERSECT</code> operator computes the set925    intersection of the rows returned by the involved926    <code class="command">SELECT</code> statements.  A row is in the927    intersection of two result sets if it appears in both result sets.928   </p><p>929    The result of <code class="literal">INTERSECT</code> does not contain any930    duplicate rows unless the <code class="literal">ALL</code> option is specified.931    With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the932    left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear933    min(<em class="replaceable"><code>m</code></em>,<em class="replaceable"><code>n</code></em>) times in the result set.934    <code class="literal">DISTINCT</code> can be written to explicitly specify the935    default behavior of eliminating duplicate rows.936   </p><p>937    Multiple <code class="literal">INTERSECT</code> operators in the same938    <code class="command">SELECT</code> statement are evaluated left to right,939    unless parentheses dictate otherwise.940    <code class="literal">INTERSECT</code> binds more tightly than941    <code class="literal">UNION</code>.  That is, <code class="literal">A UNION B INTERSECT942    C</code> will be read as <code class="literal">A UNION (B INTERSECT943    C)</code>.944   </p><p>945    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and946    <code class="literal">FOR KEY SHARE</code> cannot be947    specified either for an <code class="literal">INTERSECT</code> result or for any input of948    an <code class="literal">INTERSECT</code>.949   </p></div><div class="refsect2" id="SQL-EXCEPT"><h3><code class="literal">EXCEPT</code> Clause</h3><p>950    The <code class="literal">EXCEPT</code> clause has this general form:951</p><pre class="synopsis">952<em class="replaceable"><code>select_statement</code></em> EXCEPT [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>953</pre><p><em class="replaceable"><code>select_statement</code></em> is954    any <code class="command">SELECT</code> statement without an <code class="literal">ORDER955    BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,956    <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.957   </p><p>958    The <code class="literal">EXCEPT</code> operator computes the set of rows959    that are in the result of the left <code class="command">SELECT</code>960    statement but not in the result of the right one.961   </p><p>962    The result of <code class="literal">EXCEPT</code> does not contain any963    duplicate rows unless the <code class="literal">ALL</code> option is specified.964    With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the965    left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear966    max(<em class="replaceable"><code>m</code></em>-<em class="replaceable"><code>n</code></em>,0) times in the result set.967    <code class="literal">DISTINCT</code> can be written to explicitly specify the968    default behavior of eliminating duplicate rows.969   </p><p>970    Multiple <code class="literal">EXCEPT</code> operators in the same971    <code class="command">SELECT</code> statement are evaluated left to right,972    unless parentheses dictate otherwise.  <code class="literal">EXCEPT</code> binds at973    the same level as <code class="literal">UNION</code>.974   </p><p>975    Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and976    <code class="literal">FOR KEY SHARE</code> cannot be977    specified either for an <code class="literal">EXCEPT</code> result or for any input of978    an <code class="literal">EXCEPT</code>.979   </p></div><div class="refsect2" id="SQL-ORDERBY"><h3><code class="literal">ORDER BY</code> Clause</h3><p>980    The optional <code class="literal">ORDER BY</code> clause has this general form:981</p><pre class="synopsis">982ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...]983</pre><p>984    The <code class="literal">ORDER BY</code> clause causes the result rows to985    be sorted according to the specified expression(s).  If two rows are986    equal according to the leftmost expression, they are compared987    according to the next expression and so on.  If they are equal988    according to all specified expressions, they are returned in989    an implementation-dependent order.990   </p><p>991    Each <em class="replaceable"><code>expression</code></em> can be the992    name or ordinal number of an output column993    (<code class="command">SELECT</code> list item), or it can be an arbitrary994    expression formed from input-column values.995   </p><p>996    The ordinal number refers to the ordinal (left-to-right) position997    of the output column. This feature makes it possible to define an998    ordering on the basis of a column that does not have a unique999    name.  This is never absolutely necessary because it is always1000    possible to assign a name to an output column using the1001    <code class="literal">AS</code> clause.1002   </p><p>1003    It is also possible to use arbitrary expressions in the1004    <code class="literal">ORDER BY</code> clause, including columns that do not1005    appear in the <code class="command">SELECT</code> output list.  Thus the1006    following statement is valid:1007</p><pre class="programlisting">1008SELECT name FROM distributors ORDER BY code;1009</pre><p>1010    A limitation of this feature is that an <code class="literal">ORDER BY</code>1011    clause applying to the result of a <code class="literal">UNION</code>,1012    <code class="literal">INTERSECT</code>, or <code class="literal">EXCEPT</code> clause can only1013    specify an output column name or number, not an expression.1014   </p><p>1015    If an <code class="literal">ORDER BY</code> expression is a simple name that1016    matches both an output column name and an input column name,1017    <code class="literal">ORDER BY</code> will interpret it as the output column name.1018    This is the opposite of the choice that <code class="literal">GROUP BY</code> will1019    make in the same situation.  This inconsistency is made to be1020    compatible with the SQL standard.1021   </p><p>1022    Optionally one can add the key word <code class="literal">ASC</code> (ascending) or1023    <code class="literal">DESC</code> (descending) after any expression in the1024    <code class="literal">ORDER BY</code> clause.  If not specified, <code class="literal">ASC</code> is1025    assumed by default.  Alternatively, a specific ordering operator1026    name can be specified in the <code class="literal">USING</code> clause.1027    An ordering operator must be a less-than or greater-than1028    member of some B-tree operator family.1029    <code class="literal">ASC</code> is usually equivalent to <code class="literal">USING &lt;</code> and1030    <code class="literal">DESC</code> is usually equivalent to <code class="literal">USING &gt;</code>.1031    (But the creator of a user-defined data type can define exactly what the1032    default sort ordering is, and it might correspond to operators with other1033    names.)1034   </p><p>1035    If <code class="literal">NULLS LAST</code> is specified, null values sort after all1036    non-null values; if <code class="literal">NULLS FIRST</code> is specified, null values1037    sort before all non-null values.  If neither is specified, the default1038    behavior is <code class="literal">NULLS LAST</code> when <code class="literal">ASC</code> is specified1039    or implied, and <code class="literal">NULLS FIRST</code> when <code class="literal">DESC</code> is specified1040    (thus, the default is to act as though nulls are larger than non-nulls).1041    When <code class="literal">USING</code> is specified, the default nulls ordering depends1042    on whether the operator is a less-than or greater-than operator.1043   </p><p>1044    Note that ordering options apply only to the expression they follow;1045    for example <code class="literal">ORDER BY x, y DESC</code> does not mean1046    the same thing as <code class="literal">ORDER BY x DESC, y DESC</code>.1047   </p><p>1048    Character-string data is sorted according to the collation that applies1049    to the column being sorted.  That can be overridden at need by including1050    a <code class="literal">COLLATE</code> clause in the1051    <em class="replaceable"><code>expression</code></em>, for example1052    <code class="literal">ORDER BY mycolumn COLLATE "en_US"</code>.1053    For more information see <a class="xref" href="sql-expressions.html#SQL-SYNTAX-COLLATE-EXPRS" title="4.2.10. Collation Expressions">Section 4.2.10</a> and1054    <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>.1055   </p></div><div class="refsect2" id="SQL-LIMIT"><h3><code class="literal">LIMIT</code> Clause</h3><p>1056    The <code class="literal">LIMIT</code> clause consists of two independent1057    sub-clauses:1058</p><pre class="synopsis">1059LIMIT { <em class="replaceable"><code>count</code></em> | ALL }1060OFFSET <em class="replaceable"><code>start</code></em>1061</pre><p>1062    The parameter <em class="replaceable"><code>count</code></em> specifies the1063    maximum number of rows to return, while <em class="replaceable"><code>start</code></em> specifies the number of rows1064    to skip before starting to return rows.  When both are specified,1065    <em class="replaceable"><code>start</code></em> rows are skipped1066    before starting to count the <em class="replaceable"><code>count</code></em> rows to be returned.1067   </p><p>1068    If the <em class="replaceable"><code>count</code></em> expression1069    evaluates to NULL, it is treated as <code class="literal">LIMIT ALL</code>, i.e., no1070    limit.  If <em class="replaceable"><code>start</code></em> evaluates1071    to NULL, it is treated the same as <code class="literal">OFFSET 0</code>.1072   </p><p>1073    SQL:2008 introduced a different syntax to achieve the same result,1074    which <span class="productname">PostgreSQL</span> also supports.  It is:1075</p><pre class="synopsis">1076OFFSET <em class="replaceable"><code>start</code></em> { ROW | ROWS }1077FETCH { FIRST | NEXT } [ <em class="replaceable"><code>count</code></em> ] { ROW | ROWS } { ONLY | WITH TIES }1078</pre><p>1079    In this syntax, the <em class="replaceable"><code>start</code></em>1080    or <em class="replaceable"><code>count</code></em> value is required by1081    the standard to be a literal constant, a parameter, or a variable name;1082    as a <span class="productname">PostgreSQL</span> extension, other expressions1083    are allowed, but will generally need to be enclosed in parentheses to avoid1084    ambiguity.1085    If <em class="replaceable"><code>count</code></em> is1086    omitted in a <code class="literal">FETCH</code> clause, it defaults to 1.1087    The <code class="literal">WITH TIES</code> option is used to return any additional1088    rows that tie for the last place in the result set according to1089    the <code class="literal">ORDER BY</code> clause; <code class="literal">ORDER BY</code>1090    is mandatory in this case, and <code class="literal">SKIP LOCKED</code> is1091    not allowed.1092    <code class="literal">ROW</code> and <code class="literal">ROWS</code> as well as1093    <code class="literal">FIRST</code> and <code class="literal">NEXT</code> are noise1094    words that don't influence the effects of these clauses.1095    According to the standard, the <code class="literal">OFFSET</code> clause must come1096    before the <code class="literal">FETCH</code> clause if both are present; but1097    <span class="productname">PostgreSQL</span> is laxer and allows either order.1098   </p><p>1099    When using <code class="literal">LIMIT</code>, it is a good idea to use an1100    <code class="literal">ORDER BY</code> clause that constrains the result rows into a1101    unique order.  Otherwise you will get an unpredictable subset of1102    the query's rows — you might be asking for the tenth through1103    twentieth rows, but tenth through twentieth in what ordering?  You1104    don't know what ordering unless you specify <code class="literal">ORDER BY</code>.1105   </p><p>1106    The query planner takes <code class="literal">LIMIT</code> into account when1107    generating a query plan, so you are very likely to get different1108    plans (yielding different row orders) depending on what you use1109    for <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>.  Thus, using1110    different <code class="literal">LIMIT</code>/<code class="literal">OFFSET</code> values to select1111    different subsets of a query result <span class="emphasis"><em>will give1112    inconsistent results</em></span> unless you enforce a predictable1113    result ordering with <code class="literal">ORDER BY</code>.  This is not a bug; it1114    is an inherent consequence of the fact that SQL does not promise1115    to deliver the results of a query in any particular order unless1116    <code class="literal">ORDER BY</code> is used to constrain the order.1117   </p><p>1118    It is even possible for repeated executions of the same <code class="literal">LIMIT</code>1119    query to return different subsets of the rows of a table, if there1120    is not an <code class="literal">ORDER BY</code> to enforce selection of a deterministic1121    subset.  Again, this is not a bug; determinism of the results is1122    simply not guaranteed in such a case.1123   </p></div><div class="refsect2" id="SQL-FOR-UPDATE-SHARE"><h3>The Locking Clause</h3><p>1124    <code class="literal">FOR UPDATE</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code>1125    and <code class="literal">FOR KEY SHARE</code>1126    are <em class="firstterm">locking clauses</em>; they affect how <code class="literal">SELECT</code>1127    locks rows as they are obtained from the table.1128   </p><p>1129    The locking clause has the general form1130 1131</p><pre class="synopsis">1132FOR <em class="replaceable"><code>lock_strength</code></em> [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT | SKIP LOCKED ]1133</pre><p>1134 1135    where <em class="replaceable"><code>lock_strength</code></em> can be one of1136 1137</p><pre class="synopsis">1138UPDATE1139NO KEY UPDATE1140SHARE1141KEY SHARE1142</pre><p>1143   </p><p>1144    For more information on each row-level lock mode, refer to1145    <a class="xref" href="explicit-locking.html#LOCKING-ROWS" title="13.3.2. Row-Level Locks">Section 13.3.2</a>.1146   </p><p>1147    To prevent the operation from waiting for other transactions to commit,1148    use either the <code class="literal">NOWAIT</code> or <code class="literal">SKIP LOCKED</code>1149    option.  With <code class="literal">NOWAIT</code>, the statement reports an error, rather1150    than waiting, if a selected row cannot be locked immediately.1151    With <code class="literal">SKIP LOCKED</code>, any selected rows that cannot be1152    immediately locked are skipped.  Skipping locked rows provides an1153    inconsistent view of the data, so this is not suitable for general purpose1154    work, but can be used to avoid lock contention with multiple consumers1155    accessing a queue-like table.1156    Note that <code class="literal">NOWAIT</code> and <code class="literal">SKIP LOCKED</code> apply only1157    to the row-level lock(s) — the required <code class="literal">ROW SHARE</code>1158    table-level lock is still taken in the ordinary way (see1159    <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>).  You can use1160    <a class="link" href="sql-lock.html" title="LOCK"><code class="command">LOCK</code></a>1161    with the <code class="literal">NOWAIT</code> option first,1162    if you need to acquire the table-level lock without waiting.1163   </p><p>1164    If specific tables are named in a locking clause,1165    then only rows coming from those tables are locked; any other1166    tables used in the <code class="command">SELECT</code> are simply read as1167    usual.  A locking1168    clause without a table list affects all tables used in the statement.1169    If a locking clause is1170    applied to a view or sub-query, it affects all tables used in1171    the view or sub-query.1172    However, these clauses1173    do not apply to <code class="literal">WITH</code> queries referenced by the primary query.1174    If you want row locking to occur within a <code class="literal">WITH</code> query, specify1175    a locking clause within the <code class="literal">WITH</code> query.1176   </p><p>1177    Multiple locking1178    clauses can be written if it is necessary to specify different locking1179    behavior for different tables.  If the same table is mentioned (or1180    implicitly affected) by more than one locking clause,1181    then it is processed as if it was only specified by the strongest one.1182    Similarly, a table is processed1183    as <code class="literal">NOWAIT</code> if that is specified in any of the clauses1184    affecting it.  Otherwise, it is processed1185    as <code class="literal">SKIP LOCKED</code> if that is specified in any of the1186    clauses affecting it.1187   </p><p>1188    The locking clauses cannot be1189    used in contexts where returned rows cannot be clearly identified with1190    individual table rows; for example they cannot be used with aggregation.1191   </p><p>1192    When a locking clause1193    appears at the top level of a <code class="command">SELECT</code> query, the rows that1194    are locked are exactly those that are returned by the query; in the1195    case of a join query, the rows locked are those that contribute to1196    returned join rows.  In addition, rows that satisfied the query1197    conditions as of the query snapshot will be locked, although they1198    will not be returned if they were updated after the snapshot1199    and no longer satisfy the query conditions.  If a1200    <code class="literal">LIMIT</code> is used, locking stops

Showing the first 1,200 of 1609 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai