Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Прати 1
Волим 0
Вики 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Грана: master
Гране Ознаке
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/sql-select.html

1556 lines
102KB
Датотека Пермалинк Blame Историја 

  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 V1.79.1" /><link rel="prev" href="sql-security-label.html" title="SECURITY LABEL" /><link rel="next" href="sql-selectinto.html" title="SELECT INTO" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 12.4 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></hr></div><div class="refentry" id="SQL-SELECT"><div class="titlepage"></div><a id="id-1.9.3.171.1" class="indexterm"></a><a id="id-1.9.3.171.2" class="indexterm"></a><a id="id-1.9.3.171.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> [, ...] ]

  4. SELECT [ 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 <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 ]

  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> [ NATURAL ] <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> [, ...] ) ]

  31. 

  32. <span class="phrase">and <em class="replaceable"><code>grouping_element</code></em> can be one of:</span>

  33. 

  34.     ( )

  35.     <em class="replaceable"><code>expression</code></em>

  36.     ( <em class="replaceable"><code>expression</code></em> [, ...] )

  37.     ROLLUP ( { <em class="replaceable"><code>expression</code></em> | ( <em class="replaceable"><code>expression</code></em> [, ...] ) } [, ...] )

  38.     CUBE ( { <em class="replaceable"><code>expression</code></em> | ( <em class="replaceable"><code>expression</code></em> [, ...] ) } [, ...] )

  39.     GROUPING SETS ( <em class="replaceable"><code>grouping_element</code></em> [, ...] )

  40. 

  41. <span class="phrase">and <em class="replaceable"><code>with_query</code></em> is:</span>

  42. 

  43.     <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> )

  44. 

  45. TABLE [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ]

  46. </pre></div><div class="refsect1" id="id-1.9.3.171.7"><h2>Description</h2><p>

  47.    <code class="command">SELECT</code> retrieves rows from zero or more tables.

  48.    The general processing of <code class="command">SELECT</code> is as follows:

  49. 

  50.    </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>

  51.       All queries in the <code class="literal">WITH</code> list are computed.

  52.       These effectively serve as temporary tables that can be referenced

  53.       in the <code class="literal">FROM</code> list.  A <code class="literal">WITH</code> query

  54.       that is referenced more than once in <code class="literal">FROM</code> is

  55.       computed only once,

  56.       unless specified otherwise with <code class="literal">NOT MATERIALIZED</code>.

  57.       (See <a class="xref" href="sql-select.html#SQL-WITH" title="WITH Clause"><code class="literal">WITH</code> Clause</a> below.)

  58.      </p></li><li class="listitem"><p>

  59.       All elements in the <code class="literal">FROM</code> list are computed.

  60.       (Each element in the <code class="literal">FROM</code> list is a real or

  61.       virtual table.)  If more than one element is specified in the

  62.       <code class="literal">FROM</code> list, they are cross-joined together.

  63.       (See <a class="xref" href="sql-select.html#SQL-FROM" title="FROM Clause"><code class="literal">FROM</code> Clause</a> below.)

  64.      </p></li><li class="listitem"><p>

  65.       If the <code class="literal">WHERE</code> clause is specified, all rows

  66.       that do not satisfy the condition are eliminated from the

  67.       output.  (See <a class="xref" href="sql-select.html#SQL-WHERE" title="WHERE Clause"><code class="literal">WHERE</code> Clause</a> below.)

  68.      </p></li><li class="listitem"><p>

  69.       If the <code class="literal">GROUP BY</code> clause is specified,

  70.       or if there are aggregate function calls, the

  71.       output is combined into groups of rows that match on one or more

  72.       values, and the results of aggregate functions are computed.

  73.       If the <code class="literal">HAVING</code> clause is present, it

  74.       eliminates groups that do not satisfy the given condition.  (See

  75.       <a class="xref" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code> Clause</a> and

  76.       <a class="xref" href="sql-select.html#SQL-HAVING" title="HAVING Clause"><code class="literal">HAVING</code> Clause</a> below.)

  77.      </p></li><li class="listitem"><p>

  78.       The actual output rows are computed using the

  79.       <code class="command">SELECT</code> output expressions for each selected

  80.       row or row group.  (See

  81.       <a class="xref" href="sql-select.html#SQL-SELECT-LIST" title="SELECT List"><code class="command">SELECT</code> List</a>

  82.       below.)

  83.      </p></li><li class="listitem"><p><code class="literal">SELECT DISTINCT</code> eliminates duplicate rows from the

  84.       result.  <code class="literal">SELECT DISTINCT ON</code> eliminates rows that

  85.       match on all the specified expressions.  <code class="literal">SELECT ALL</code>

  86.       (the default) will return all candidate rows, including

  87.       duplicates.  (See <a class="xref" href="sql-select.html#SQL-DISTINCT" title="DISTINCT Clause"><code class="literal">DISTINCT</code> Clause</a> below.)

  88.      </p></li><li class="listitem"><p>

  89.       Using the operators <code class="literal">UNION</code>,

  90.       <code class="literal">INTERSECT</code>, and <code class="literal">EXCEPT</code>, the

  91.       output of more than one <code class="command">SELECT</code> statement can

  92.       be combined to form a single result set.  The

  93.       <code class="literal">UNION</code> operator returns all rows that are in

  94.       one or both of the result sets.  The

  95.       <code class="literal">INTERSECT</code> operator returns all rows that are

  96.       strictly in both result sets.  The <code class="literal">EXCEPT</code>

  97.       operator returns the rows that are in the first result set but

  98.       not in the second.  In all three cases, duplicate rows are

  99.       eliminated unless <code class="literal">ALL</code> is specified.  The noise

  100.       word <code class="literal">DISTINCT</code> can be added to explicitly specify

  101.       eliminating duplicate rows.  Notice that <code class="literal">DISTINCT</code> is

  102.       the default behavior here, even though <code class="literal">ALL</code> is

  103.       the default for <code class="command">SELECT</code> itself.  (See

  104.       <a class="xref" href="sql-select.html#SQL-UNION" title="UNION Clause"><code class="literal">UNION</code> Clause</a>, <a class="xref" href="sql-select.html#SQL-INTERSECT" title="INTERSECT Clause"><code class="literal">INTERSECT</code> Clause</a>, and

  105.       <a class="xref" href="sql-select.html#SQL-EXCEPT" title="EXCEPT Clause"><code class="literal">EXCEPT</code> Clause</a> below.)

  106.      </p></li><li class="listitem"><p>

  107.       If the <code class="literal">ORDER BY</code> clause is specified, the

  108.       returned rows are sorted in the specified order.  If

  109.       <code class="literal">ORDER BY</code> is not given, the rows are returned

  110.       in whatever order the system finds fastest to produce.  (See

  111.       <a class="xref" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code> Clause</a> below.)

  112.      </p></li><li class="listitem"><p>

  113.       If the <code class="literal">LIMIT</code> (or <code class="literal">FETCH FIRST</code>) or <code class="literal">OFFSET</code>

  114.       clause is specified, the <code class="command">SELECT</code> statement

  115.       only returns a subset of the result rows. (See <a class="xref" href="sql-select.html#SQL-LIMIT" title="LIMIT Clause"><code class="literal">LIMIT</code> Clause</a> below.)

  116.      </p></li><li class="listitem"><p>

  117.       If <code class="literal">FOR UPDATE</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code>

  118.       or <code class="literal">FOR KEY SHARE</code>

  119.       is specified, the

  120.       <code class="command">SELECT</code> statement locks the selected rows

  121.       against concurrent updates.  (See <a class="xref" href="sql-select.html#SQL-FOR-UPDATE-SHARE" title="The Locking Clause">The Locking Clause</a> below.)

  122.      </p></li></ol></div><p>

  123.   </p><p>

  124.    You must have <code class="literal">SELECT</code> privilege on each column used

  125.    in a <code class="command">SELECT</code> command.  The use of <code class="literal">FOR NO KEY UPDATE</code>,

  126.    <code class="literal">FOR UPDATE</code>,

  127.    <code class="literal">FOR SHARE</code> or <code class="literal">FOR KEY SHARE</code> requires

  128.    <code class="literal">UPDATE</code> privilege as well (for at least one column

  129.    of each table so selected).

  130.   </p></div><div class="refsect1" id="id-1.9.3.171.8"><h2>Parameters</h2><div class="refsect2" id="SQL-WITH"><h3><code class="literal">WITH</code> Clause</h3><p>

  131.     The <code class="literal">WITH</code> clause allows you to specify one or more

  132.     subqueries that can be referenced by name in the primary query.

  133.     The subqueries effectively act as temporary tables or views

  134.     for the duration of the primary query.

  135.     Each subquery can be a <code class="command">SELECT</code>, <code class="command">TABLE</code>, <code class="command">VALUES</code>,

  136.     <code class="command">INSERT</code>, <code class="command">UPDATE</code> or

  137.     <code class="command">DELETE</code> statement.

  138.     When writing a data-modifying statement (<code class="command">INSERT</code>,

  139.     <code class="command">UPDATE</code> or <code class="command">DELETE</code>) in

  140.     <code class="literal">WITH</code>, it is usual to include a <code class="literal">RETURNING</code> clause.

  141.     It is the output of <code class="literal">RETURNING</code>, <span class="emphasis"><em>not</em></span> the underlying

  142.     table that the statement modifies, that forms the temporary table that is

  143.     read by the primary query.  If <code class="literal">RETURNING</code> is omitted, the

  144.     statement is still executed, but it produces no output so it cannot be

  145.     referenced as a table by the primary query.

  146.    </p><p>

  147.     A name (without schema qualification) must be specified for each

  148.     <code class="literal">WITH</code> query.  Optionally, a list of column names

  149.     can be specified; if this is omitted,

  150.     the column names are inferred from the subquery.

  151.    </p><p>

  152.     If <code class="literal">RECURSIVE</code> is specified, it allows a

  153.     <code class="command">SELECT</code> subquery to reference itself by name.  Such a

  154.     subquery must have the form

  155. </p><pre class="synopsis">

  156. <em class="replaceable"><code>non_recursive_term</code></em> UNION [ ALL | DISTINCT ] <em class="replaceable"><code>recursive_term</code></em>

  157. </pre><p>

  158.     where the recursive self-reference must appear on the right-hand

  159.     side of the <code class="literal">UNION</code>.  Only one recursive self-reference

  160.     is permitted per query.  Recursive data-modifying statements are not

  161.     supported, but you can use the results of a recursive

  162.     <code class="command">SELECT</code> query in

  163.     a data-modifying statement.  See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> for

  164.     an example.

  165.    </p><p>

  166.     Another effect of <code class="literal">RECURSIVE</code> is that

  167.     <code class="literal">WITH</code> queries need not be ordered: a query

  168.     can reference another one that is later in the list.  (However,

  169.     circular references, or mutual recursion, are not implemented.)

  170.     Without <code class="literal">RECURSIVE</code>, <code class="literal">WITH</code> queries

  171.     can only reference sibling <code class="literal">WITH</code> queries

  172.     that are earlier in the <code class="literal">WITH</code> list.

  173.    </p><p>

  174.     When there are multiple queries in the <code class="literal">WITH</code>

  175.     clause, <code class="literal">RECURSIVE</code> should be written only once,

  176.     immediately after <code class="literal">WITH</code>.  It applies to all queries

  177.     in the <code class="literal">WITH</code> clause, though it has no effect on

  178.     queries that do not use recursion or forward references.

  179.    </p><p>

  180.     The primary query and the <code class="literal">WITH</code> queries are all

  181.     (notionally) executed at the same time.  This implies that the effects of

  182.     a data-modifying statement in <code class="literal">WITH</code> cannot be seen from

  183.     other parts of the query, other than by reading its <code class="literal">RETURNING</code>

  184.     output.  If two such data-modifying statements attempt to modify the same

  185.     row, the results are unspecified.

  186.    </p><p>

  187.     A key property of <code class="literal">WITH</code> queries is that they

  188.     are normally evaluated only once per execution of the primary query,

  189.     even if the primary query refers to them more than once.

  190.     In particular, data-modifying statements are guaranteed to be

  191.     executed once and only once, regardless of whether the primary query

  192.     reads all or any of their output.

  193.    </p><p>

  194.     However, a <code class="literal">WITH</code> query can be marked

  195.     <code class="literal">NOT MATERIALIZED</code> to remove this guarantee.  In that

  196.     case, the <code class="literal">WITH</code> query can be folded into the primary

  197.     query much as though it were a simple sub-<code class="literal">SELECT</code> in

  198.     the primary query's <code class="literal">FROM</code> clause.  This results in

  199.     duplicate computations if the primary query refers to

  200.     that <code class="literal">WITH</code> query more than once; but if each such use

  201.     requires only a few rows of the <code class="literal">WITH</code> query's total

  202.     output, <code class="literal">NOT MATERIALIZED</code> can provide a net savings by

  203.     allowing the queries to be optimized jointly.

  204.     <code class="literal">NOT MATERIALIZED</code> is ignored if it is attached to

  205.     a <code class="literal">WITH</code> query that is recursive or is not

  206.     side-effect-free (i.e., is not a plain <code class="literal">SELECT</code>

  207.     containing no volatile functions).

  208.    </p><p>

  209.     By default, a side-effect-free <code class="literal">WITH</code> query is folded

  210.     into the primary query if it is used exactly once in the primary

  211.     query's <code class="literal">FROM</code> clause.  This allows joint optimization

  212.     of the two query levels in situations where that should be semantically

  213.     invisible.  However, such folding can be prevented by marking the

  214.     <code class="literal">WITH</code> query as <code class="literal">MATERIALIZED</code>.

  215.     That might be useful, for example, if the <code class="literal">WITH</code> query

  216.     is being used as an optimization fence to prevent the planner from

  217.     choosing a bad plan.

  218.     <span class="productname">PostgreSQL</span> versions before v12 never did

  219.     such folding, so queries written for older versions might rely on

  220.     <code class="literal">WITH</code> to act as an optimization fence.

  221.    </p><p>

  222.     See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> for additional information.

  223.    </p></div><div class="refsect2" id="SQL-FROM"><h3><code class="literal">FROM</code> Clause</h3><p>

  224.     The <code class="literal">FROM</code> clause specifies one or more source

  225.     tables for the <code class="command">SELECT</code>.  If multiple sources are

  226.     specified, the result is the Cartesian product (cross join) of all

  227.     the sources.  But usually qualification conditions are added (via

  228.     <code class="literal">WHERE</code>) to restrict the returned rows to a small subset of the

  229.     Cartesian product.

  230.    </p><p>

  231.     The <code class="literal">FROM</code> clause can contain the following

  232.     elements:

  233. 

  234.     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>

  235.         The name (optionally schema-qualified) of an existing table or view.

  236.         If <code class="literal">ONLY</code> is specified before the table name, only that

  237.         table is scanned.  If <code class="literal">ONLY</code> is not specified, the table

  238.         and all its descendant tables (if any) are scanned.  Optionally,

  239.         <code class="literal">*</code> can be specified after the table name to explicitly

  240.         indicate that descendant tables are included.

  241.        </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>

  242.         A substitute name for the <code class="literal">FROM</code> item containing the

  243.         alias.  An alias is used for brevity or to eliminate ambiguity

  244.         for self-joins (where the same table is scanned multiple

  245.         times).  When an alias is provided, it completely hides the

  246.         actual name of the table or function; for example given

  247.         <code class="literal">FROM foo AS f</code>, the remainder of the

  248.         <code class="command">SELECT</code> must refer to this <code class="literal">FROM</code>

  249.         item as <code class="literal">f</code> not <code class="literal">foo</code>.  If an alias is

  250.         written, a column alias list can also be written to provide

  251.         substitute names for one or more columns of the table.

  252.        </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>

  253.         A <code class="literal">TABLESAMPLE</code> clause after

  254.         a <em class="replaceable"><code>table_name</code></em> indicates that the

  255.         specified <em class="replaceable"><code>sampling_method</code></em>

  256.         should be used to retrieve a subset of the rows in that table.

  257.         This sampling precedes the application of any other filters such

  258.         as <code class="literal">WHERE</code> clauses.

  259.         The standard <span class="productname">PostgreSQL</span> distribution

  260.         includes two sampling methods, <code class="literal">BERNOULLI</code>

  261.         and <code class="literal">SYSTEM</code>, and other sampling methods can be

  262.         installed in the database via extensions.

  263.        </p><p>

  264.         The <code class="literal">BERNOULLI</code> and <code class="literal">SYSTEM</code> sampling methods

  265.         each accept a single <em class="replaceable"><code>argument</code></em>

  266.         which is the fraction of the table to sample, expressed as a

  267.         percentage between 0 and 100.  This argument can be

  268.         any <code class="type">real</code>-valued expression.  (Other sampling methods might

  269.         accept more or different arguments.)  These two methods each return

  270.         a randomly-chosen sample of the table that will contain

  271.         approximately the specified percentage of the table's rows.

  272.         The <code class="literal">BERNOULLI</code> method scans the whole table and

  273.         selects or ignores individual rows independently with the specified

  274.         probability.

  275.         The <code class="literal">SYSTEM</code> method does block-level sampling with

  276.         each block having the specified chance of being selected; all rows

  277.         in each selected block are returned.

  278.         The <code class="literal">SYSTEM</code> method is significantly faster than

  279.         the <code class="literal">BERNOULLI</code> method when small sampling

  280.         percentages are specified, but it may return a less-random sample of

  281.         the table as a result of clustering effects.

  282.        </p><p>

  283.         The optional <code class="literal">REPEATABLE</code> clause specifies

  284.         a <em class="replaceable"><code>seed</code></em> number or expression to use

  285.         for generating random numbers within the sampling method.  The seed

  286.         value can be any non-null floating-point value.  Two queries that

  287.         specify the same seed and <em class="replaceable"><code>argument</code></em>

  288.         values will select the same sample of the table, if the table has

  289.         not been changed meanwhile.  But different seed values will usually

  290.         produce different samples.

  291.         If <code class="literal">REPEATABLE</code> is not given then a new random

  292.         sample is selected for each query, based upon a system-generated seed.

  293.         Note that some add-on sampling methods do not

  294.         accept <code class="literal">REPEATABLE</code>, and will always produce new

  295.         samples on each use.

  296.        </p></dd><dt><span class="term"><em class="replaceable"><code>select</code></em></span></dt><dd><p>

  297.         A sub-<code class="command">SELECT</code> can appear in the

  298.         <code class="literal">FROM</code> clause.  This acts as though its

  299.         output were created as a temporary table for the duration of

  300.         this single <code class="command">SELECT</code> command.  Note that the

  301.         sub-<code class="command">SELECT</code> must be surrounded by

  302.         parentheses, and an alias <span class="emphasis"><em>must</em></span> be

  303.         provided for it.  A

  304.         <a class="xref" href="sql-values.html" title="VALUES"><span class="refentrytitle">VALUES</span></a> command

  305.         can also be used here.

  306.        </p></dd><dt><span class="term"><em class="replaceable"><code>with_query_name</code></em></span></dt><dd><p>

  307.         A <code class="literal">WITH</code> query is referenced by writing its name,

  308.         just as though the query's name were a table name.  (In fact,

  309.         the <code class="literal">WITH</code> query hides any real table of the same name

  310.         for the purposes of the primary query.  If necessary, you can

  311.         refer to a real table of the same name by schema-qualifying

  312.         the table's name.)

  313.         An alias can be provided in the same way as for a table.

  314.        </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>

  315.         Function calls can appear in the <code class="literal">FROM</code>

  316.         clause.  (This is especially useful for functions that return

  317.         result sets, but any function can be used.)  This acts as

  318.         though the function's output were created as a temporary table for the

  319.         duration of this single <code class="command">SELECT</code> command.

  320.         When the optional <code class="command">WITH ORDINALITY</code> clause is

  321.         added to the function call, a new column is appended after

  322.         all the function's output columns with numbering for each row.

  323.        </p><p>

  324.         An alias can be provided in the same way as for a table.

  325.         If an alias is written, a column

  326.         alias list can also be written to provide substitute names for

  327.         one or more attributes of the function's composite return

  328.         type, including the column added by <code class="literal">ORDINALITY</code>

  329.         if present.

  330.        </p><p>

  331.         Multiple function calls can be combined into a

  332.         single <code class="literal">FROM</code>-clause item by surrounding them

  333.         with <code class="literal">ROWS FROM( ... )</code>.  The output of such an item is the

  334.         concatenation of the first row from each function, then the second

  335.         row from each function, etc.  If some of the functions produce fewer

  336.         rows than others, null values are substituted for the missing data, so

  337.         that the total number of rows returned is always the same as for the

  338.         function that produced the most rows.

  339.        </p><p>

  340.         If the function has been defined as returning the

  341.         <code class="type">record</code> data type, then an alias or the key word

  342.         <code class="literal">AS</code> must be present, followed by a column

  343.         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">, ...

  344.         </span>])</code>.  The column definition list must match the

  345.         actual number and types of columns returned by the function.

  346.        </p><p>

  347.         When using the <code class="literal">ROWS FROM( ... )</code> syntax, if one of the

  348.         functions requires a column definition list, it's preferred to put

  349.         the column definition list after the function call inside

  350.         <code class="literal">ROWS FROM( ... )</code>.  A column definition list can be placed

  351.         after the <code class="literal">ROWS FROM( ... )</code> construct only if there's just

  352.         a single function and no <code class="literal">WITH ORDINALITY</code> clause.

  353.        </p><p>

  354.         To use <code class="literal">ORDINALITY</code> together with a column definition

  355.         list, you must use the <code class="literal">ROWS FROM( ... )</code> syntax and put the

  356.         column definition list inside <code class="literal">ROWS FROM( ... )</code>.

  357.        </p></dd><dt><span class="term"><em class="replaceable"><code>join_type</code></em></span></dt><dd><p>

  358.         One of

  359.         </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><li class="listitem"><p><code class="literal">CROSS JOIN</code></p></li></ul></div><p>

  360. 

  361.         For the <code class="literal">INNER</code> and <code class="literal">OUTER</code> join types, a

  362.         join condition must be specified, namely exactly one of

  363.         <code class="literal">NATURAL</code>, <code class="literal">ON <em class="replaceable"><code>join_condition</code></em></code>, or

  364.         <code class="literal">USING (<em class="replaceable"><code>join_column</code></em> [, ...])</code>.

  365.         See below for the meaning.  For <code class="literal">CROSS JOIN</code>,

  366.         none of these clauses can appear.

  367.        </p><p>

  368.         A <code class="literal">JOIN</code> clause combines two <code class="literal">FROM</code>

  369.         items, which for convenience we will refer to as <span class="quote">“<span class="quote">tables</span>”</span>,

  370.         though in reality they can be any type of <code class="literal">FROM</code> item.

  371.         Use parentheses if necessary to determine the order of nesting.

  372.         In the absence of parentheses, <code class="literal">JOIN</code>s nest

  373.         left-to-right.  In any case <code class="literal">JOIN</code> binds more

  374.         tightly than the commas separating <code class="literal">FROM</code>-list items.

  375.        </p><p><code class="literal">CROSS JOIN</code> and <code class="literal">INNER JOIN</code>

  376.         produce a simple Cartesian product, the same result as you get from

  377.         listing the two tables at the top level of <code class="literal">FROM</code>,

  378.         but restricted by the join condition (if any).

  379.         <code class="literal">CROSS JOIN</code> is equivalent to <code class="literal">INNER JOIN ON

  380.         (TRUE)</code>, that is, no rows are removed by qualification.

  381.         These join types are just a notational convenience, since they

  382.         do nothing you couldn't do with plain <code class="literal">FROM</code> and

  383.         <code class="literal">WHERE</code>.

  384.        </p><p><code class="literal">LEFT OUTER JOIN</code> returns all rows in the qualified

  385.         Cartesian product (i.e., all combined rows that pass its join

  386.         condition), plus one copy of each row in the left-hand table

  387.         for which there was no right-hand row that passed the join

  388.         condition.  This left-hand row is extended to the full width

  389.         of the joined table by inserting null values for the

  390.         right-hand columns.  Note that only the <code class="literal">JOIN</code>

  391.         clause's own condition is considered while deciding which rows

  392.         have matches.  Outer conditions are applied afterwards.

  393.        </p><p>

  394.         Conversely, <code class="literal">RIGHT OUTER JOIN</code> returns all the

  395.         joined rows, plus one row for each unmatched right-hand row

  396.         (extended with nulls on the left).  This is just a notational

  397.         convenience, since you could convert it to a <code class="literal">LEFT

  398.         OUTER JOIN</code> by switching the left and right tables.

  399.        </p><p><code class="literal">FULL OUTER JOIN</code> returns all the joined rows, plus

  400.         one row for each unmatched left-hand row (extended with nulls

  401.         on the right), plus one row for each unmatched right-hand row

  402.         (extended with nulls on the left).

  403.        </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> is

  404.         an expression resulting in a value of type

  405.         <code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>

  406.         clause) that specifies which rows in a join are considered to

  407.         match.

  408.        </p></dd><dt><span class="term"><code class="literal">USING ( <em class="replaceable"><code>join_column</code></em> [, ...] )</code></span></dt><dd><p>

  409.         A clause of the form <code class="literal">USING ( a, b, ... )</code> is

  410.         shorthand for <code class="literal">ON left_table.a = right_table.a AND

  411.         left_table.b = right_table.b ...</code>.  Also,

  412.         <code class="literal">USING</code> implies that only one of each pair of

  413.         equivalent columns will be included in the join output, not

  414.         both.

  415.        </p></dd><dt><span class="term"><code class="literal">NATURAL</code></span></dt><dd><p>

  416.         <code class="literal">NATURAL</code> is shorthand for a

  417.         <code class="literal">USING</code> list that mentions all columns in the two

  418.         tables that have matching names.  If there are no common

  419.         column names, <code class="literal">NATURAL</code> is equivalent

  420.         to <code class="literal">ON TRUE</code>.

  421.        </p></dd><dt><span class="term"><code class="literal">LATERAL</code></span></dt><dd><p>

  422.         The <code class="literal">LATERAL</code> key word can precede a

  423.         sub-<code class="command">SELECT</code> <code class="literal">FROM</code> item.  This allows the

  424.         sub-<code class="command">SELECT</code> to refer to columns of <code class="literal">FROM</code>

  425.         items that appear before it in the <code class="literal">FROM</code> list.  (Without

  426.         <code class="literal">LATERAL</code>, each sub-<code class="command">SELECT</code> is

  427.         evaluated independently and so cannot cross-reference any other

  428.         <code class="literal">FROM</code> item.)

  429.        </p><p><code class="literal">LATERAL</code> can also precede a function-call

  430.         <code class="literal">FROM</code> item, but in this case it is a noise word, because

  431.         the function expression can refer to earlier <code class="literal">FROM</code> items

  432.         in any case.

  433.        </p><p>

  434.         A <code class="literal">LATERAL</code> item can appear at top level in the

  435.         <code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree.  In the

  436.         latter case it can also refer to any items that are on the left-hand

  437.         side of a <code class="literal">JOIN</code> that it is on the right-hand side of.

  438.        </p><p>

  439.         When a <code class="literal">FROM</code> item contains <code class="literal">LATERAL</code>

  440.         cross-references, evaluation proceeds as follows: for each row of the

  441.         <code class="literal">FROM</code> item providing the cross-referenced column(s), or

  442.         set of rows of multiple <code class="literal">FROM</code> items providing the

  443.         columns, the <code class="literal">LATERAL</code> item is evaluated using that

  444.         row or row set's values of the columns.  The resulting row(s) are

  445.         joined as usual with the rows they were computed from.  This is

  446.         repeated for each row or set of rows from the column source table(s).

  447.        </p><p>

  448.         The column source table(s) must be <code class="literal">INNER</code> or

  449.         <code class="literal">LEFT</code> joined to the <code class="literal">LATERAL</code> item, else

  450.         there would not be a well-defined set of rows from which to compute

  451.         each set of rows for the <code class="literal">LATERAL</code> item.  Thus,

  452.         although a construct such as <code class="literal"><em class="replaceable"><code>X</code></em> RIGHT JOIN

  453.         LATERAL <em class="replaceable"><code>Y</code></em></code> is syntactically valid, it is

  454.         not actually allowed for <em class="replaceable"><code>Y</code></em> to reference

  455.         <em class="replaceable"><code>X</code></em>.

  456.        </p></dd></dl></div><p>

  457.    </p></div><div class="refsect2" id="SQL-WHERE"><h3><code class="literal">WHERE</code> Clause</h3><p>

  458.     The optional <code class="literal">WHERE</code> clause has the general form

  459. </p><pre class="synopsis">

  460. WHERE <em class="replaceable"><code>condition</code></em>

  461. </pre><p>

  462.     where <em class="replaceable"><code>condition</code></em> is

  463.     any expression that evaluates to a result of type

  464.     <code class="type">boolean</code>.  Any row that does not satisfy this

  465.     condition will be eliminated from the output.  A row satisfies the

  466.     condition if it returns true when the actual row values are

  467.     substituted for any variable references.

  468.    </p></div><div class="refsect2" id="SQL-GROUPBY"><h3><code class="literal">GROUP BY</code> Clause</h3><p>

  469.     The optional <code class="literal">GROUP BY</code> clause has the general form

  470. </p><pre class="synopsis">

  471. GROUP BY <em class="replaceable"><code>grouping_element</code></em> [, ...]

  472. </pre><p>

  473.    </p><p>

  474.     <code class="literal">GROUP BY</code> will condense into a single row all

  475.     selected rows that share the same values for the grouped

  476.     expressions.  An <em class="replaceable"><code>expression</code></em> used inside a

  477.     <em class="replaceable"><code>grouping_element</code></em>

  478.     can be an input column name, or the name or ordinal number of an

  479.     output column (<code class="command">SELECT</code> list item), or an arbitrary

  480.     expression formed from input-column values.  In case of ambiguity,

  481.     a <code class="literal">GROUP BY</code> name will be interpreted as an

  482.     input-column name rather than an output column name.

  483.    </p><p>

  484.     If any of <code class="literal">GROUPING SETS</code>, <code class="literal">ROLLUP</code> or

  485.     <code class="literal">CUBE</code> are present as grouping elements, then the

  486.     <code class="literal">GROUP BY</code> clause as a whole defines some number of

  487.     independent <em class="replaceable"><code>grouping sets</code></em>.  The effect of this is

  488.     equivalent to constructing a <code class="literal">UNION ALL</code> between

  489.     subqueries with the individual grouping sets as their

  490.     <code class="literal">GROUP BY</code> clauses.  For further details on the handling

  491.     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>.

  492.    </p><p>

  493.     Aggregate functions, if any are used, are computed across all rows

  494.     making up each group, producing a separate value for each group.

  495.     (If there are aggregate functions but no <code class="literal">GROUP BY</code>

  496.     clause, the query is treated as having a single group comprising all

  497.     the selected rows.)

  498.     The set of rows fed to each aggregate function can be further filtered by

  499.     attaching a <code class="literal">FILTER</code> clause to the aggregate function

  500.     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.  When

  501.     a <code class="literal">FILTER</code> clause is present, only those rows matching it

  502.     are included in the input to that aggregate function.

  503.    </p><p>

  504.     When <code class="literal">GROUP BY</code> is present,

  505.     or any aggregate functions are present, it is not valid for

  506.     the <code class="command">SELECT</code> list expressions to refer to

  507.     ungrouped columns except within aggregate functions or when the

  508.     ungrouped column is functionally dependent on the grouped columns,

  509.     since there would otherwise be more than one possible value to

  510.     return for an ungrouped column.  A functional dependency exists if

  511.     the grouped columns (or a subset thereof) are the primary key of

  512.     the table containing the ungrouped column.

  513.    </p><p>

  514.     Keep in mind that all aggregate functions are evaluated before

  515.     evaluating any <span class="quote">“<span class="quote">scalar</span>”</span> expressions in the <code class="literal">HAVING</code>

  516.     clause or <code class="literal">SELECT</code> list.  This means that, for example,

  517.     a <code class="literal">CASE</code> expression cannot be used to skip evaluation of

  518.     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>.

  519.    </p><p>

  520.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  521.     <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be

  522.     specified with <code class="literal">GROUP BY</code>.

  523.    </p></div><div class="refsect2" id="SQL-HAVING"><h3><code class="literal">HAVING</code> Clause</h3><p>

  524.     The optional <code class="literal">HAVING</code> clause has the general form

  525. </p><pre class="synopsis">

  526. HAVING <em class="replaceable"><code>condition</code></em>

  527. </pre><p>

  528.     where <em class="replaceable"><code>condition</code></em> is

  529.     the same as specified for the <code class="literal">WHERE</code> clause.

  530.    </p><p>

  531.     <code class="literal">HAVING</code> eliminates group rows that do not

  532.     satisfy the condition.  <code class="literal">HAVING</code> is different

  533.     from <code class="literal">WHERE</code>: <code class="literal">WHERE</code> filters

  534.     individual rows before the application of <code class="literal">GROUP

  535.     BY</code>, while <code class="literal">HAVING</code> filters group rows

  536.     created by <code class="literal">GROUP BY</code>.  Each column referenced in

  537.     <em class="replaceable"><code>condition</code></em> must

  538.     unambiguously reference a grouping column, unless the reference

  539.     appears within an aggregate function or the ungrouped column is

  540.     functionally dependent on the grouping columns.

  541.    </p><p>

  542.     The presence of <code class="literal">HAVING</code> turns a query into a grouped

  543.     query even if there is no <code class="literal">GROUP BY</code> clause.  This is the

  544.     same as what happens when the query contains aggregate functions but

  545.     no <code class="literal">GROUP BY</code> clause.  All the selected rows are considered to

  546.     form a single group, and the <code class="command">SELECT</code> list and

  547.     <code class="literal">HAVING</code> clause can only reference table columns from

  548.     within aggregate functions.  Such a query will emit a single row if the

  549.     <code class="literal">HAVING</code> condition is true, zero rows if it is not true.

  550.    </p><p>

  551.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  552.     <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be

  553.     specified with <code class="literal">HAVING</code>.

  554.    </p></div><div class="refsect2" id="SQL-WINDOW"><h3><code class="literal">WINDOW</code> Clause</h3><p>

  555.     The optional <code class="literal">WINDOW</code> clause has the general form

  556. </p><pre class="synopsis">

  557. WINDOW <em class="replaceable"><code>window_name</code></em> AS ( <em class="replaceable"><code>window_definition</code></em> ) [, ...]

  558. </pre><p>

  559.     where <em class="replaceable"><code>window_name</code></em> is

  560.     a name that can be referenced from <code class="literal">OVER</code> clauses or

  561.     subsequent window definitions, and

  562.     <em class="replaceable"><code>window_definition</code></em> is

  563. </p><pre class="synopsis">

  564. [ <em class="replaceable"><code>existing_window_name</code></em> ]

  565. [ PARTITION BY <em class="replaceable"><code>expression</code></em> [, ...] ]

  566. [ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...] ]

  567. [ <em class="replaceable"><code>frame_clause</code></em> ]

  568. </pre><p>

  569.    </p><p>

  570.     If an <em class="replaceable"><code>existing_window_name</code></em>

  571.     is specified it must refer to an earlier entry in the <code class="literal">WINDOW</code>

  572.     list; the new window copies its partitioning clause from that entry,

  573.     as well as its ordering clause if any.  In this case the new window cannot

  574.     specify its own <code class="literal">PARTITION BY</code> clause, and it can specify

  575.     <code class="literal">ORDER BY</code> only if the copied window does not have one.

  576.     The new window always uses its own frame clause; the copied window

  577.     must not specify a frame clause.

  578.    </p><p>

  579.     The elements of the <code class="literal">PARTITION BY</code> list are interpreted in

  580.     much the same fashion as elements of a

  581.     <a class="xref" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code> Clause</a>, except that

  582.     they are always simple expressions and never the name or number of an

  583.     output column.

  584.     Another difference is that these expressions can contain aggregate

  585.     function calls, which are not allowed in a regular <code class="literal">GROUP BY</code>

  586.     clause.  They are allowed here because windowing occurs after grouping

  587.     and aggregation.

  588.    </p><p>

  589.     Similarly, the elements of the <code class="literal">ORDER BY</code> list are interpreted

  590.     in much the same fashion as elements of an

  591.     <a class="xref" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code> Clause</a>, except that

  592.     the expressions are always taken as simple expressions and never the name

  593.     or number of an output column.

  594.    </p><p>

  595.     The optional <em class="replaceable"><code>frame_clause</code></em> defines

  596.     the <em class="firstterm">window frame</em> for window functions that depend on the

  597.     frame (not all do).  The window frame is a set of related rows for

  598.     each row of the query (called the <em class="firstterm">current row</em>).

  599.     The <em class="replaceable"><code>frame_clause</code></em> can be one of

  600. 

  601. </p><pre class="synopsis">

  602. { RANGE | ROWS | GROUPS } <em class="replaceable"><code>frame_start</code></em> [ <em class="replaceable"><code>frame_exclusion</code></em> ]

  603. { 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> ]

  604. </pre><p>

  605. 

  606.     where <em class="replaceable"><code>frame_start</code></em>

  607.     and <em class="replaceable"><code>frame_end</code></em> can be one of

  608. 

  609. </p><pre class="synopsis">

  610. UNBOUNDED PRECEDING

  611. <em class="replaceable"><code>offset</code></em> PRECEDING

  612. CURRENT ROW

  613. <em class="replaceable"><code>offset</code></em> FOLLOWING

  614. UNBOUNDED FOLLOWING

  615. </pre><p>

  616. 

  617.     and <em class="replaceable"><code>frame_exclusion</code></em> can be one of

  618. 

  619. </p><pre class="synopsis">

  620. EXCLUDE CURRENT ROW

  621. EXCLUDE GROUP

  622. EXCLUDE TIES

  623. EXCLUDE NO OTHERS

  624. </pre><p>

  625. 

  626.     If <em class="replaceable"><code>frame_end</code></em> is omitted it defaults to <code class="literal">CURRENT

  627.     ROW</code>.  Restrictions are that

  628.     <em class="replaceable"><code>frame_start</code></em> cannot be <code class="literal">UNBOUNDED FOLLOWING</code>,

  629.     <em class="replaceable"><code>frame_end</code></em> cannot be <code class="literal">UNBOUNDED PRECEDING</code>,

  630.     and the <em class="replaceable"><code>frame_end</code></em> choice cannot appear earlier in the

  631.     above list of <em class="replaceable"><code>frame_start</code></em>

  632.     and <em class="replaceable"><code>frame_end</code></em> options than

  633.     the <em class="replaceable"><code>frame_start</code></em> choice does — for example

  634.     <code class="literal">RANGE BETWEEN CURRENT ROW AND <em class="replaceable"><code>offset</code></em>

  635.     PRECEDING</code> is not allowed.

  636.    </p><p>

  637.     The default framing option is <code class="literal">RANGE UNBOUNDED PRECEDING</code>,

  638.     which is the same as <code class="literal">RANGE BETWEEN UNBOUNDED PRECEDING AND

  639.     CURRENT ROW</code>; it sets the frame to be all rows from the partition start

  640.     up through the current row's last <em class="firstterm">peer</em> (a row

  641.     that the window's <code class="literal">ORDER BY</code> clause considers

  642.     equivalent to the current row; all rows are peers if there

  643.     is no <code class="literal">ORDER BY</code>).

  644.     In general, <code class="literal">UNBOUNDED PRECEDING</code> means that the frame

  645.     starts with the first row of the partition, and similarly

  646.     <code class="literal">UNBOUNDED FOLLOWING</code> means that the frame ends with the last

  647.     row of the partition, regardless

  648.     of <code class="literal">RANGE</code>, <code class="literal">ROWS</code>

  649.     or <code class="literal">GROUPS</code> mode.

  650.     In <code class="literal">ROWS</code> mode, <code class="literal">CURRENT ROW</code> means

  651.     that the frame starts or ends with the current row; but

  652.     in <code class="literal">RANGE</code> or <code class="literal">GROUPS</code> mode it means

  653.     that the frame starts or ends with the current row's first or last peer

  654.     in the <code class="literal">ORDER BY</code> ordering.

  655.     The <em class="replaceable"><code>offset</code></em> <code class="literal">PRECEDING</code> and

  656.     <em class="replaceable"><code>offset</code></em> <code class="literal">FOLLOWING</code> options

  657.     vary in meaning depending on the frame mode.

  658.     In <code class="literal">ROWS</code> mode, the <em class="replaceable"><code>offset</code></em>

  659.     is an integer indicating that the frame starts or ends that many rows

  660.     before or after the current row.

  661.     In <code class="literal">GROUPS</code> mode, the <em class="replaceable"><code>offset</code></em>

  662.     is an integer indicating that the frame starts or ends that many peer

  663.     groups before or after the current row's peer group, where

  664.     a <em class="firstterm">peer group</em> is a group of rows that are

  665.     equivalent according to the window's <code class="literal">ORDER BY</code> clause.

  666.     In <code class="literal">RANGE</code> mode, use of

  667.     an <em class="replaceable"><code>offset</code></em> option requires that there be

  668.     exactly one <code class="literal">ORDER BY</code> column in the window definition.

  669.     Then the frame contains those rows whose ordering column value is no

  670.     more than <em class="replaceable"><code>offset</code></em> less than

  671.     (for <code class="literal">PRECEDING</code>) or more than

  672.     (for <code class="literal">FOLLOWING</code>) the current row's ordering column

  673.     value.  In these cases the data type of

  674.     the <em class="replaceable"><code>offset</code></em> expression depends on the data

  675.     type of the ordering column.  For numeric ordering columns it is

  676.     typically of the same type as the ordering column, but for datetime

  677.     ordering columns it is an <code class="type">interval</code>.

  678.     In all these cases, the value of the <em class="replaceable"><code>offset</code></em>

  679.     must be non-null and non-negative.  Also, while

  680.     the <em class="replaceable"><code>offset</code></em> does not have to be a simple

  681.     constant, it cannot contain variables, aggregate functions, or window

  682.     functions.

  683.    </p><p>

  684.     The <em class="replaceable"><code>frame_exclusion</code></em> option allows rows around

  685.     the current row to be excluded from the frame, even if they would be

  686.     included according to the frame start and frame end options.

  687.     <code class="literal">EXCLUDE CURRENT ROW</code> excludes the current row from the

  688.     frame.

  689.     <code class="literal">EXCLUDE GROUP</code> excludes the current row and its

  690.     ordering peers from the frame.

  691.     <code class="literal">EXCLUDE TIES</code> excludes any peers of the current

  692.     row from the frame, but not the current row itself.

  693.     <code class="literal">EXCLUDE NO OTHERS</code> simply specifies explicitly the

  694.     default behavior of not excluding the current row or its peers.

  695.    </p><p>

  696.     Beware that the <code class="literal">ROWS</code> mode can produce unpredictable

  697.     results if the <code class="literal">ORDER BY</code> ordering does not order the rows

  698.     uniquely.  The <code class="literal">RANGE</code> and <code class="literal">GROUPS</code>

  699.     modes are designed to ensure that rows that are peers in

  700.     the <code class="literal">ORDER BY</code> ordering are treated alike: all rows of

  701.     a given peer group will be in the frame or excluded from it.

  702.    </p><p>

  703.     The purpose of a <code class="literal">WINDOW</code> clause is to specify the

  704.     behavior of <em class="firstterm">window functions</em> appearing in the query's

  705.     <a class="xref" href="sql-select.html#SQL-SELECT-LIST" title="SELECT List"><code class="command">SELECT</code> List</a> or

  706.     <a class="xref" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code> Clause</a>.  These functions

  707.     can reference the <code class="literal">WINDOW</code> clause entries by name

  708.     in their <code class="literal">OVER</code> clauses.  A <code class="literal">WINDOW</code> clause

  709.     entry does not have to be referenced anywhere, however; if it is not

  710.     used in the query it is simply ignored.  It is possible to use window

  711.     functions without any <code class="literal">WINDOW</code> clause at all, since

  712.     a window function call can specify its window definition directly in

  713.     its <code class="literal">OVER</code> clause.  However, the <code class="literal">WINDOW</code>

  714.     clause saves typing when the same window definition is needed for more

  715.     than one window function.

  716.    </p><p>

  717.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  718.     <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be

  719.     specified with <code class="literal">WINDOW</code>.

  720.    </p><p>

  721.     Window functions are described in detail in

  722.     <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,

  723.     <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>, and

  724.     <a class="xref" href="queries-table-expressions.html#QUERIES-WINDOW" title="7.2.5. Window Function Processing">Section 7.2.5</a>.

  725.    </p></div><div class="refsect2" id="SQL-SELECT-LIST"><h3><code class="command">SELECT</code> List</h3><p>

  726.     The <code class="command">SELECT</code> list (between the key words

  727.     <code class="literal">SELECT</code> and <code class="literal">FROM</code>) specifies expressions

  728.     that form the output rows of the <code class="command">SELECT</code>

  729.     statement.  The expressions can (and usually do) refer to columns

  730.     computed in the <code class="literal">FROM</code> clause.

  731.    </p><p>

  732.     Just as in a table, every output column of a <code class="command">SELECT</code>

  733.     has a name.  In a simple <code class="command">SELECT</code> this name is just

  734.     used to label the column for display, but when the <code class="command">SELECT</code>

  735.     is a sub-query of a larger query, the name is seen by the larger query

  736.     as the column name of the virtual table produced by the sub-query.

  737.     To specify the name to use for an output column, write

  738.     <code class="literal">AS</code> <em class="replaceable"><code>output_name</code></em>

  739.     after the column's expression.  (You can omit <code class="literal">AS</code>,

  740.     but only if the desired output name does not match any

  741.     <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 possible

  742.     future keyword additions, it is recommended that you always either

  743.     write <code class="literal">AS</code> or double-quote the output name.)

  744.     If you do not specify a column name, a name is chosen automatically

  745.     by <span class="productname">PostgreSQL</span>.  If the column's expression

  746.     is a simple column reference then the chosen name is the same as that

  747.     column's name.  In more complex cases a function or type name may be

  748.     used, or the system may fall back on a generated name such as

  749.     <code class="literal">?column?</code>.

  750.    </p><p>

  751.     An output column's name can be used to refer to the column's value in

  752.     <code class="literal">ORDER BY</code> and <code class="literal">GROUP BY</code> clauses, but not in the

  753.     <code class="literal">WHERE</code> or <code class="literal">HAVING</code> clauses; there you must write

  754.     out the expression instead.

  755.    </p><p>

  756.     Instead of an expression, <code class="literal">*</code> can be written in

  757.     the output list as a shorthand for all the columns of the selected

  758.     rows.  Also, you can write <code class="literal"><em class="replaceable"><code>table_name</code></em>.*</code> as a

  759.     shorthand for the columns coming from just that table.  In these

  760.     cases it is not possible to specify new names with <code class="literal">AS</code>;

  761.     the output column names will be the same as the table columns' names.

  762.    </p><p>

  763.     According to the SQL standard, the expressions in the output list should

  764.     be computed before applying <code class="literal">DISTINCT</code>, <code class="literal">ORDER

  765.     BY</code>, or <code class="literal">LIMIT</code>.  This is obviously necessary

  766.     when using <code class="literal">DISTINCT</code>, since otherwise it's not clear

  767.     what values are being made distinct.  However, in many cases it is

  768.     convenient if output expressions are computed after <code class="literal">ORDER

  769.     BY</code> and <code class="literal">LIMIT</code>; particularly if the output list

  770.     contains any volatile or expensive functions.  With that behavior, the

  771.     order of function evaluations is more intuitive and there will not be

  772.     evaluations corresponding to rows that never appear in the output.

  773.     <span class="productname">PostgreSQL</span> will effectively evaluate output expressions

  774.     after sorting and limiting, so long as those expressions are not

  775.     referenced in <code class="literal">DISTINCT</code>, <code class="literal">ORDER BY</code>

  776.     or <code class="literal">GROUP BY</code>.  (As a counterexample, <code class="literal">SELECT

  777.     f(x) FROM tab ORDER BY 1</code> clearly must evaluate <code class="function">f(x)</code>

  778.     before sorting.)  Output expressions that contain set-returning functions

  779.     are effectively evaluated after sorting and before limiting, so

  780.     that <code class="literal">LIMIT</code> will act to cut off the output from a

  781.     set-returning function.

  782.    </p><div class="note"><h3 class="title">Note</h3><p>

  783.      <span class="productname">PostgreSQL</span> versions before 9.6 did not provide any

  784.      guarantees about the timing of evaluation of output expressions versus

  785.      sorting and limiting; it depended on the form of the chosen query plan.

  786.     </p></div></div><div class="refsect2" id="SQL-DISTINCT"><h3><code class="literal">DISTINCT</code> Clause</h3><p>

  787.     If <code class="literal">SELECT DISTINCT</code> is specified, all duplicate rows are

  788.     removed from the result set (one row is kept from each group of

  789.     duplicates).  <code class="literal">SELECT ALL</code> specifies the opposite: all rows are

  790.     kept; that is the default.

  791.    </p><p>

  792.     <code class="literal">SELECT DISTINCT ON ( <em class="replaceable"><code>expression</code></em> [, ...] )</code>

  793.     keeps only the first row of each set of rows where the given

  794.     expressions evaluate to equal.  The <code class="literal">DISTINCT ON</code>

  795.     expressions are interpreted using the same rules as for

  796.     <code class="literal">ORDER BY</code> (see above).  Note that the <span class="quote">“<span class="quote">first

  797.     row</span>”</span> of each set is unpredictable unless <code class="literal">ORDER

  798.     BY</code> is used to ensure that the desired row appears first.  For

  799.     example:

  800. </p><pre class="programlisting">

  801. SELECT DISTINCT ON (location) location, time, report

  802.     FROM weather_reports

  803.     ORDER BY location, time DESC;

  804. </pre><p>

  805.     retrieves the most recent weather report for each location.  But

  806.     if we had not used <code class="literal">ORDER BY</code> to force descending order

  807.     of time values for each location, we'd have gotten a report from

  808.     an unpredictable time for each location.

  809.    </p><p>

  810.     The <code class="literal">DISTINCT ON</code> expression(s) must match the leftmost

  811.     <code class="literal">ORDER BY</code> expression(s).  The <code class="literal">ORDER BY</code> clause

  812.     will normally contain additional expression(s) that determine the

  813.     desired precedence of rows within each <code class="literal">DISTINCT ON</code> group.

  814.    </p><p>

  815.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  816.     <code class="literal">FOR SHARE</code> and <code class="literal">FOR KEY SHARE</code> cannot be

  817.     specified with <code class="literal">DISTINCT</code>.

  818.    </p></div><div class="refsect2" id="SQL-UNION"><h3><code class="literal">UNION</code> Clause</h3><p>

  819.     The <code class="literal">UNION</code> clause has this general form:

  820. </p><pre class="synopsis">

  821. <em class="replaceable"><code>select_statement</code></em> UNION [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>

  822. </pre><p><em class="replaceable"><code>select_statement</code></em> is

  823.     any <code class="command">SELECT</code> statement without an <code class="literal">ORDER

  824.     BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  825.     <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.

  826.     (<code class="literal">ORDER BY</code> and <code class="literal">LIMIT</code> can be attached to a

  827.     subexpression if it is enclosed in parentheses.  Without

  828.     parentheses, these clauses will be taken to apply to the result of

  829.     the <code class="literal">UNION</code>, not to its right-hand input

  830.     expression.)

  831.    </p><p>

  832.     The <code class="literal">UNION</code> operator computes the set union of

  833.     the rows returned by the involved <code class="command">SELECT</code>

  834.     statements.  A row is in the set union of two result sets if it

  835.     appears in at least one of the result sets.  The two

  836.     <code class="command">SELECT</code> statements that represent the direct

  837.     operands of the <code class="literal">UNION</code> must produce the same

  838.     number of columns, and corresponding columns must be of compatible

  839.     data types.

  840.    </p><p>

  841.     The result of <code class="literal">UNION</code> does not contain any duplicate

  842.     rows unless the <code class="literal">ALL</code> option is specified.

  843.     <code class="literal">ALL</code> prevents elimination of duplicates.  (Therefore,

  844.     <code class="literal">UNION ALL</code> is usually significantly quicker than

  845.     <code class="literal">UNION</code>; use <code class="literal">ALL</code> when you can.)

  846.     <code class="literal">DISTINCT</code> can be written to explicitly specify the

  847.     default behavior of eliminating duplicate rows.

  848.    </p><p>

  849.     Multiple <code class="literal">UNION</code> operators in the same

  850.     <code class="command">SELECT</code> statement are evaluated left to right,

  851.     unless otherwise indicated by parentheses.

  852.    </p><p>

  853.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and

  854.     <code class="literal">FOR KEY SHARE</code> cannot be

  855.     specified either for a <code class="literal">UNION</code> result or for any input of a

  856.     <code class="literal">UNION</code>.

  857.    </p></div><div class="refsect2" id="SQL-INTERSECT"><h3><code class="literal">INTERSECT</code> Clause</h3><p>

  858.     The <code class="literal">INTERSECT</code> clause has this general form:

  859. </p><pre class="synopsis">

  860. <em class="replaceable"><code>select_statement</code></em> INTERSECT [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>

  861. </pre><p><em class="replaceable"><code>select_statement</code></em> is

  862.     any <code class="command">SELECT</code> statement without an <code class="literal">ORDER

  863.     BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  864.     <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.

  865.    </p><p>

  866.     The <code class="literal">INTERSECT</code> operator computes the set

  867.     intersection of the rows returned by the involved

  868.     <code class="command">SELECT</code> statements.  A row is in the

  869.     intersection of two result sets if it appears in both result sets.

  870.    </p><p>

  871.     The result of <code class="literal">INTERSECT</code> does not contain any

  872.     duplicate rows unless the <code class="literal">ALL</code> option is specified.

  873.     With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the

  874.     left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear

  875.     min(<em class="replaceable"><code>m</code></em>,<em class="replaceable"><code>n</code></em>) times in the result set.

  876.     <code class="literal">DISTINCT</code> can be written to explicitly specify the

  877.     default behavior of eliminating duplicate rows.

  878.    </p><p>

  879.     Multiple <code class="literal">INTERSECT</code> operators in the same

  880.     <code class="command">SELECT</code> statement are evaluated left to right,

  881.     unless parentheses dictate otherwise.

  882.     <code class="literal">INTERSECT</code> binds more tightly than

  883.     <code class="literal">UNION</code>.  That is, <code class="literal">A UNION B INTERSECT

  884.     C</code> will be read as <code class="literal">A UNION (B INTERSECT

  885.     C)</code>.

  886.    </p><p>

  887.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and

  888.     <code class="literal">FOR KEY SHARE</code> cannot be

  889.     specified either for an <code class="literal">INTERSECT</code> result or for any input of

  890.     an <code class="literal">INTERSECT</code>.

  891.    </p></div><div class="refsect2" id="SQL-EXCEPT"><h3><code class="literal">EXCEPT</code> Clause</h3><p>

  892.     The <code class="literal">EXCEPT</code> clause has this general form:

  893. </p><pre class="synopsis">

  894. <em class="replaceable"><code>select_statement</code></em> EXCEPT [ ALL | DISTINCT ] <em class="replaceable"><code>select_statement</code></em>

  895. </pre><p><em class="replaceable"><code>select_statement</code></em> is

  896.     any <code class="command">SELECT</code> statement without an <code class="literal">ORDER

  897.     BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>,

  898.     <code class="literal">FOR SHARE</code>, or <code class="literal">FOR KEY SHARE</code> clause.

  899.    </p><p>

  900.     The <code class="literal">EXCEPT</code> operator computes the set of rows

  901.     that are in the result of the left <code class="command">SELECT</code>

  902.     statement but not in the result of the right one.

  903.    </p><p>

  904.     The result of <code class="literal">EXCEPT</code> does not contain any

  905.     duplicate rows unless the <code class="literal">ALL</code> option is specified.

  906.     With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the

  907.     left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear

  908.     max(<em class="replaceable"><code>m</code></em>-<em class="replaceable"><code>n</code></em>,0) times in the result set.

  909.     <code class="literal">DISTINCT</code> can be written to explicitly specify the

  910.     default behavior of eliminating duplicate rows.

  911.    </p><p>

  912.     Multiple <code class="literal">EXCEPT</code> operators in the same

  913.     <code class="command">SELECT</code> statement are evaluated left to right,

  914.     unless parentheses dictate otherwise.  <code class="literal">EXCEPT</code> binds at

  915.     the same level as <code class="literal">UNION</code>.

  916.    </p><p>

  917.     Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and

  918.     <code class="literal">FOR KEY SHARE</code> cannot be

  919.     specified either for an <code class="literal">EXCEPT</code> result or for any input of

  920.     an <code class="literal">EXCEPT</code>.

  921.    </p></div><div class="refsect2" id="SQL-ORDERBY"><h3><code class="literal">ORDER BY</code> Clause</h3><p>

  922.     The optional <code class="literal">ORDER BY</code> clause has this general form:

  923. </p><pre class="synopsis">

  924. ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...]

  925. </pre><p>

  926.     The <code class="literal">ORDER BY</code> clause causes the result rows to

  927.     be sorted according to the specified expression(s).  If two rows are

  928.     equal according to the leftmost expression, they are compared

  929.     according to the next expression and so on.  If they are equal

  930.     according to all specified expressions, they are returned in

  931.     an implementation-dependent order.

  932.    </p><p>

  933.     Each <em class="replaceable"><code>expression</code></em> can be the

  934.     name or ordinal number of an output column

  935.     (<code class="command">SELECT</code> list item), or it can be an arbitrary

  936.     expression formed from input-column values.

  937.    </p><p>

  938.     The ordinal number refers to the ordinal (left-to-right) position

  939.     of the output column. This feature makes it possible to define an

  940.     ordering on the basis of a column that does not have a unique

  941.     name.  This is never absolutely necessary because it is always

  942.     possible to assign a name to an output column using the

  943.     <code class="literal">AS</code> clause.

  944.    </p><p>

  945.     It is also possible to use arbitrary expressions in the

  946.     <code class="literal">ORDER BY</code> clause, including columns that do not

  947.     appear in the <code class="command">SELECT</code> output list.  Thus the

  948.     following statement is valid:

  949. </p><pre class="programlisting">

  950. SELECT name FROM distributors ORDER BY code;

  951. </pre><p>

  952.     A limitation of this feature is that an <code class="literal">ORDER BY</code>

  953.     clause applying to the result of a <code class="literal">UNION</code>,

  954.     <code class="literal">INTERSECT</code>, or <code class="literal">EXCEPT</code> clause can only

  955.     specify an output column name or number, not an expression.

  956.    </p><p>

  957.     If an <code class="literal">ORDER BY</code> expression is a simple name that

  958.     matches both an output column name and an input column name,

  959.     <code class="literal">ORDER BY</code> will interpret it as the output column name.

  960.     This is the opposite of the choice that <code class="literal">GROUP BY</code> will

  961.     make in the same situation.  This inconsistency is made to be

  962.     compatible with the SQL standard.

  963.    </p><p>

  964.     Optionally one can add the key word <code class="literal">ASC</code> (ascending) or

  965.     <code class="literal">DESC</code> (descending) after any expression in the

  966.     <code class="literal">ORDER BY</code> clause.  If not specified, <code class="literal">ASC</code> is

  967.     assumed by default.  Alternatively, a specific ordering operator

  968.     name can be specified in the <code class="literal">USING</code> clause.

  969.     An ordering operator must be a less-than or greater-than

  970.     member of some B-tree operator family.

  971.     <code class="literal">ASC</code> is usually equivalent to <code class="literal">USING &lt;</code> and

  972.     <code class="literal">DESC</code> is usually equivalent to <code class="literal">USING &gt;</code>.

  973.     (But the creator of a user-defined data type can define exactly what the

  974.     default sort ordering is, and it might correspond to operators with other

  975.     names.)

  976.    </p><p>

  977.     If <code class="literal">NULLS LAST</code> is specified, null values sort after all

  978.     non-null values; if <code class="literal">NULLS FIRST</code> is specified, null values

  979.     sort before all non-null values.  If neither is specified, the default

  980.     behavior is <code class="literal">NULLS LAST</code> when <code class="literal">ASC</code> is specified

  981.     or implied, and <code class="literal">NULLS FIRST</code> when <code class="literal">DESC</code> is specified

  982.     (thus, the default is to act as though nulls are larger than non-nulls).

  983.     When <code class="literal">USING</code> is specified, the default nulls ordering depends

  984.     on whether the operator is a less-than or greater-than operator.

  985.    </p><p>

  986.     Note that ordering options apply only to the expression they follow;

  987.     for example <code class="literal">ORDER BY x, y DESC</code> does not mean

  988.     the same thing as <code class="literal">ORDER BY x DESC, y DESC</code>.

  989.    </p><p>

  990.     Character-string data is sorted according to the collation that applies

  991.     to the column being sorted.  That can be overridden at need by including

  992.     a <code class="literal">COLLATE</code> clause in the

  993.     <em class="replaceable"><code>expression</code></em>, for example

  994.     <code class="literal">ORDER BY mycolumn COLLATE "en_US"</code>.

  995.     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> and

  996.     <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a>.

  997.    </p></div><div class="refsect2" id="SQL-LIMIT"><h3><code class="literal">LIMIT</code> Clause</h3><p>

  998.     The <code class="literal">LIMIT</code> clause consists of two independent

  999.     sub-clauses:

  1000. </p><pre class="synopsis">

  1001. LIMIT { <em class="replaceable"><code>count</code></em> | ALL }

  1002. OFFSET <em class="replaceable"><code>start</code></em>

  1003. </pre><p>

  1004.     <em class="replaceable"><code>count</code></em> specifies the

  1005.     maximum number of rows to return, while <em class="replaceable"><code>start</code></em> specifies the number of rows

  1006.     to skip before starting to return rows.  When both are specified,

  1007.     <em class="replaceable"><code>start</code></em> rows are skipped

  1008.     before starting to count the <em class="replaceable"><code>count</code></em> rows to be returned.

  1009.    </p><p>

  1010.     If the <em class="replaceable"><code>count</code></em> expression

  1011.     evaluates to NULL, it is treated as <code class="literal">LIMIT ALL</code>, i.e., no

  1012.     limit.  If <em class="replaceable"><code>start</code></em> evaluates

  1013.     to NULL, it is treated the same as <code class="literal">OFFSET 0</code>.

  1014.    </p><p>

  1015.     SQL:2008 introduced a different syntax to achieve the same result,

  1016.     which <span class="productname">PostgreSQL</span> also supports.  It is:

  1017. </p><pre class="synopsis">

  1018. OFFSET <em class="replaceable"><code>start</code></em> { ROW | ROWS }

  1019. FETCH { FIRST | NEXT } [ <em class="replaceable"><code>count</code></em> ] { ROW | ROWS } ONLY

  1020. </pre><p>

  1021.     In this syntax, the <em class="replaceable"><code>start</code></em>

  1022.     or <em class="replaceable"><code>count</code></em> value is required by

  1023.     the standard to be a literal constant, a parameter, or a variable name;

  1024.     as a <span class="productname">PostgreSQL</span> extension, other expressions

  1025.     are allowed, but will generally need to be enclosed in parentheses to avoid

  1026.     ambiguity.

  1027.     If <em class="replaceable"><code>count</code></em> is

  1028.     omitted in a <code class="literal">FETCH</code> clause, it defaults to 1.

  1029.     <code class="literal">ROW</code>

  1030.     and <code class="literal">ROWS</code> as well as <code class="literal">FIRST</code>

  1031.     and <code class="literal">NEXT</code> are noise words that don't influence

  1032.     the effects of these clauses.

  1033.     According to the standard, the <code class="literal">OFFSET</code> clause must come

  1034.     before the <code class="literal">FETCH</code> clause if both are present; but

  1035.     <span class="productname">PostgreSQL</span> is laxer and allows either order.

  1036.    </p><p>

  1037.     When using <code class="literal">LIMIT</code>, it is a good idea to use an

  1038.     <code class="literal">ORDER BY</code> clause that constrains the result rows into a

  1039.     unique order.  Otherwise you will get an unpredictable subset of

  1040.     the query's rows — you might be asking for the tenth through

  1041.     twentieth rows, but tenth through twentieth in what ordering?  You

  1042.     don't know what ordering unless you specify <code class="literal">ORDER BY</code>.

  1043.    </p><p>

  1044.     The query planner takes <code class="literal">LIMIT</code> into account when

  1045.     generating a query plan, so you are very likely to get different

  1046.     plans (yielding different row orders) depending on what you use

  1047.     for <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>.  Thus, using

  1048.     different <code class="literal">LIMIT</code>/<code class="literal">OFFSET</code> values to select

  1049.     different subsets of a query result <span class="emphasis"><em>will give

  1050.     inconsistent results</em></span> unless you enforce a predictable

  1051.     result ordering with <code class="literal">ORDER BY</code>.  This is not a bug; it

  1052.     is an inherent consequence of the fact that SQL does not promise

  1053.     to deliver the results of a query in any particular order unless

  1054.     <code class="literal">ORDER BY</code> is used to constrain the order.

  1055.    </p><p>

  1056.     It is even possible for repeated executions of the same <code class="literal">LIMIT</code>

  1057.     query to return different subsets of the rows of a table, if there

  1058.     is not an <code class="literal">ORDER BY</code> to enforce selection of a deterministic

  1059.     subset.  Again, this is not a bug; determinism of the results is

  1060.     simply not guaranteed in such a case.

  1061.    </p></div><div class="refsect2" id="SQL-FOR-UPDATE-SHARE"><h3>The Locking Clause</h3><p>

  1062.     <code class="literal">FOR UPDATE</code>, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code>

  1063.     and <code class="literal">FOR KEY SHARE</code>

  1064.     are <em class="firstterm">locking clauses</em>; they affect how <code class="literal">SELECT</code>

  1065.     locks rows as they are obtained from the table.

  1066.    </p><p>

  1067.     The locking clause has the general form

  1068. 

  1069. </p><pre class="synopsis">

  1070. FOR <em class="replaceable"><code>lock_strength</code></em> [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT | SKIP LOCKED ]

  1071. </pre><p>

  1072. 

  1073.     where <em class="replaceable"><code>lock_strength</code></em> can be one of

  1074. 

  1075. </p><pre class="synopsis">

  1076. UPDATE

  1077. NO KEY UPDATE

  1078. SHARE

  1079. KEY SHARE

  1080. </pre><p>

  1081.    </p><p>

  1082.     For more information on each row-level lock mode, refer to

  1083.     <a class="xref" href="explicit-locking.html#LOCKING-ROWS" title="13.3.2. Row-Level Locks">Section 13.3.2</a>.

  1084.    </p><p>

  1085.     To prevent the operation from waiting for other transactions to commit,

  1086.     use either the <code class="literal">NOWAIT</code> or <code class="literal">SKIP LOCKED</code>

  1087.     option.  With <code class="literal">NOWAIT</code>, the statement reports an error, rather

  1088.     than waiting, if a selected row cannot be locked immediately.

  1089.     With <code class="literal">SKIP LOCKED</code>, any selected rows that cannot be

  1090.     immediately locked are skipped.  Skipping locked rows provides an

  1091.     inconsistent view of the data, so this is not suitable for general purpose

  1092.     work, but can be used to avoid lock contention with multiple consumers

  1093.     accessing a queue-like table.

  1094.     Note that <code class="literal">NOWAIT</code> and <code class="literal">SKIP LOCKED</code> apply only

  1095.     to the row-level lock(s) — the required <code class="literal">ROW SHARE</code>

  1096.     table-level lock is still taken in the ordinary way (see

  1097.     <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>).  You can use

  1098.     <a class="xref" href="sql-lock.html" title="LOCK"><span class="refentrytitle">LOCK</span></a>

  1099.     with the <code class="literal">NOWAIT</code> option first,

  1100.     if you need to acquire the table-level lock without waiting.

  1101.    </p><p>

  1102.     If specific tables are named in a locking clause,

  1103.     then only rows coming from those tables are locked; any other

  1104.     tables used in the <code class="command">SELECT</code> are simply read as

  1105.     usual.  A locking

  1106.     clause without a table list affects all tables used in the statement.

  1107.     If a locking clause is

  1108.     applied to a view or sub-query, it affects all tables used in

  1109.     the view or sub-query.

  1110.     However, these clauses

  1111.     do not apply to <code class="literal">WITH</code> queries referenced by the primary query.

  1112.     If you want row locking to occur within a <code class="literal">WITH</code> query, specify

  1113.     a locking clause within the <code class="literal">WITH</code> query.

  1114.    </p><p>

  1115.     Multiple locking

  1116.     clauses can be written if it is necessary to specify different locking

  1117.     behavior for different tables.  If the same table is mentioned (or

  1118.     implicitly affected) by more than one locking clause,

  1119.     then it is processed as if it was only specified by the strongest one.

  1120.     Similarly, a table is processed

  1121.     as <code class="literal">NOWAIT</code> if that is specified in any of the clauses

  1122.     affecting it.  Otherwise, it is processed

  1123.     as <code class="literal">SKIP LOCKED</code> if that is specified in any of the

  1124.     clauses affecting it.

  1125.    </p><p>

  1126.     The locking clauses cannot be

  1127.     used in contexts where returned rows cannot be clearly identified with

  1128.     individual table rows; for example they cannot be used with aggregation.

  1129.    </p><p>

  1130.     When a locking clause

  1131.     appears at the top level of a <code class="command">SELECT</code> query, the rows that

  1132.     are locked are exactly those that are returned by the query; in the

  1133.     case of a join query, the rows locked are those that contribute to

  1134.     returned join rows.  In addition, rows that satisfied the query

  1135.     conditions as of the query snapshot will be locked, although they

  1136.     will not be returned if they were updated after the snapshot

  1137.     and no longer satisfy the query conditions.  If a

  1138.     <code class="literal">LIMIT</code> is used, locking stops

  1139.     once enough rows have been returned to satisfy the limit (but note that

  1140.     rows skipped over by <code class="literal">OFFSET</code> will get locked).  Similarly,

  1141.     if a locking clause

  1142.     is used in a cursor's query, only rows actually fetched or stepped past

  1143.     by the cursor will be locked.

  1144.    </p><p>

  1145.     When a locking clause

  1146.     appears in a sub-<code class="command">SELECT</code>, the rows locked are those

  1147.     returned to the outer query by the sub-query.  This might involve

  1148.     fewer rows than inspection of the sub-query alone would suggest,

  1149.     since conditions from the outer query might be used to optimize

  1150.     execution of the sub-query.  For example,

  1151. </p><pre class="programlisting">

  1152. SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

  1153. </pre><p>

  1154.     will lock only rows having <code class="literal">col1 = 5</code>, even though that

  1155.     condition is not textually within the sub-query.

  1156.    </p><p>

  1157.    Previous releases failed to preserve a lock which is upgraded by a later

  1158.    savepoint.  For example, this code:

  1159. </p><pre class="programlisting">

  1160. BEGIN;

  1161. SELECT * FROM mytable WHERE key = 1 FOR UPDATE;

  1162. SAVEPOINT s;

  1163. UPDATE mytable SET ... WHERE key = 1;

  1164. ROLLBACK TO s;

  1165. </pre><p>

  1166.    would fail to preserve the <code class="literal">FOR UPDATE</code> lock after the

  1167.    <code class="command">ROLLBACK TO</code>.  This has been fixed in release 9.3.

  1168.   </p><div class="caution"><h3 class="title">Caution</h3><p>

  1169.     It is possible for a <code class="command">SELECT</code> command running at the <code class="literal">READ

  1170.     COMMITTED</code> transaction isolation level and using <code class="literal">ORDER

  1171.     BY</code> and a locking clause to return rows out of

  1172.     order.  This is because <code class="literal">ORDER BY</code> is applied first.

  1173.     The command sorts the result, but might then block trying to obtain a lock

  1174.     on one or more of the rows.  Once the <code class="literal">SELECT</code> unblocks, some

  1175.     of the ordering column values might have been modified, leading to those

  1176.     rows appearing to be out of order (though they are in order in terms

  1177.     of the original column values).  This can be worked around at need by

  1178.     placing the <code class="literal">FOR UPDATE/SHARE</code> clause in a sub-query,

  1179.     for example

  1180. </p><pre class="programlisting">

  1181. SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

  1182. </pre><p>

  1183.     Note that this will result in locking all rows of <code class="structname">mytable</code>,

  1184.     whereas <code class="literal">FOR UPDATE</code> at the top level would lock only the

  1185.     actually returned rows.  This can make for a significant performance

  1186.     difference, particularly if the <code class="literal">ORDER BY</code> is combined with

  1187.     <code class="literal">LIMIT</code> or other restrictions.  So this technique is recommended

  1188.     only if concurrent updates of the ordering columns are expected and a

  1189.     strictly sorted result is required.

  1190.    </p><p>

  1191.     At the <code class="literal">REPEATABLE READ</code> or <code class="literal">SERIALIZABLE</code>

  1192.     transaction isolation level this would cause a serialization failure (with

  1193.     a <code class="literal">SQLSTATE</code> of <code class="literal">'40001'</code>), so there is

  1194.     no possibility of receiving rows out of order under these isolation levels.

  1195.    </p></div></div><div class="refsect2" id="SQL-TABLE"><h3><code class="literal">TABLE</code> Command</h3><p>

  1196.     The command

  1197. </p><pre class="programlisting">

  1198. TABLE <em class="replaceable"><code>name</code></em>

  1199. </pre><p>

  1200.     is equivalent to

  1201. </p><pre class="programlisting">

  1202. SELECT * FROM <em class="replaceable"><code>name</code></em>

  1203. </pre><p>

  1204.     It can be used as a top-level command or as a space-saving syntax

  1205.     variant in parts of complex queries. Only the <code class="literal">WITH</code>,

  1206.     <code class="literal">UNION</code>, <code class="literal">INTERSECT</code>, <code class="literal">EXCEPT</code>,

  1207.     <code class="literal">ORDER BY</code>, <code class="literal">LIMIT</code>, <code class="literal">OFFSET</code>,

  1208.     <code class="literal">FETCH</code> and <code class="literal">FOR</code> locking clauses can be used

  1209.     with <code class="command">TABLE</code>; the <code class="literal">WHERE</code> clause and any form of

  1210.     aggregation cannot

  1211.     be used.

  1212.    </p></div></div><div class="refsect1" id="id-1.9.3.171.9"><h2>Examples</h2><p>

  1213.    To join the table <code class="literal">films</code> with the table

  1214.    <code class="literal">distributors</code>:

  1215. 

  1216. </p><pre class="programlisting">

  1217. SELECT f.title, f.did, d.name, f.date_prod, f.kind

  1218.     FROM distributors d, films f

  1219.     WHERE f.did = d.did

  1220. 

  1221.        title       | did |     name     | date_prod  |   kind

  1222. -------------------+-----+--------------+------------+----------

  1223.  The Third Man     | 101 | British Lion | 1949-12-23 | Drama

  1224.  The African Queen | 101 | British Lion | 1951-08-11 | Romantic

  1225.  ...

  1226. </pre><p>

  1227.   </p><p>

  1228.    To sum the column <code class="literal">len</code> of all films and group

  1229.    the results by <code class="literal">kind</code>:

  1230. 

  1231. </p><pre class="programlisting">

  1232. SELECT kind, sum(len) AS total FROM films GROUP BY kind;

  1233. 

  1234.    kind   | total

  1235. ----------+-------

  1236.  Action   | 07:34

  1237.  Comedy   | 02:58

  1238.  Drama    | 14:28

  1239.  Musical  | 06:42

  1240.  Romantic | 04:38

  1241. </pre><p>

  1242.   </p><p>

  1243.    To sum the column <code class="literal">len</code> of all films, group

  1244.    the results by <code class="literal">kind</code> and show those group totals

  1245.    that are less than 5 hours:

  1246. 

  1247. </p><pre class="programlisting">

  1248. SELECT kind, sum(len) AS total

  1249.     FROM films

  1250.     GROUP BY kind

  1251.     HAVING sum(len) &lt; interval '5 hours';

  1252. 

  1253.    kind   | total

  1254. ----------+-------

  1255.  Comedy   | 02:58

  1256.  Romantic | 04:38

  1257. </pre><p>

  1258.   </p><p>

  1259.    The following two examples are identical ways of sorting the individual

  1260.    results according to the contents of the second column

  1261.    (<code class="literal">name</code>):

  1262. 

  1263. </p><pre class="programlisting">

  1264. SELECT * FROM distributors ORDER BY name;

  1265. SELECT * FROM distributors ORDER BY 2;

  1266. 

  1267.  did |       name

  1268. -----+------------------

  1269.  109 | 20th Century Fox

  1270.  110 | Bavaria Atelier

  1271.  101 | British Lion

  1272.  107 | Columbia

  1273.  102 | Jean Luc Godard

  1274.  113 | Luso films

  1275.  104 | Mosfilm

  1276.  103 | Paramount

  1277.  106 | Toho

  1278.  105 | United Artists

  1279.  111 | Walt Disney

  1280.  112 | Warner Bros.

  1281.  108 | Westward

  1282. </pre><p>

  1283.   </p><p>

  1284.    The next example shows how to obtain the union of the tables

  1285.    <code class="literal">distributors</code> and

  1286.    <code class="literal">actors</code>, restricting the results to those that begin

  1287.    with the letter W in each table.  Only distinct rows are wanted, so the

  1288.    key word <code class="literal">ALL</code> is omitted.

  1289. 

  1290. </p><pre class="programlisting">

  1291. distributors:               actors:

  1292.  did |     name              id |     name

  1293. -----+--------------        ----+----------------

  1294.  108 | Westward               1 | Woody Allen

  1295.  111 | Walt Disney            2 | Warren Beatty

  1296.  112 | Warner Bros.           3 | Walter Matthau

  1297.  ...                         ...

  1298. 

  1299. SELECT distributors.name

  1300.     FROM distributors

  1301.     WHERE distributors.name LIKE 'W%'

  1302. UNION

  1303. SELECT actors.name

  1304.     FROM actors

  1305.     WHERE actors.name LIKE 'W%';

  1306. 

  1307.       name

  1308. ----------------

  1309.  Walt Disney

  1310.  Walter Matthau

  1311.  Warner Bros.

  1312.  Warren Beatty

  1313.  Westward

  1314.  Woody Allen

  1315. </pre><p>

  1316.   </p><p>

  1317.    This example shows how to use a function in the <code class="literal">FROM</code>

  1318.    clause, both with and without a column definition list:

  1319. 

  1320. </p><pre class="programlisting">

  1321. CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$

  1322.     SELECT * FROM distributors WHERE did = $1;

  1323. $$ LANGUAGE SQL;

  1324. 

  1325. SELECT * FROM distributors(111);

  1326.  did |    name

  1327. -----+-------------

  1328.  111 | Walt Disney

  1329. 

  1330. CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$

  1331.     SELECT * FROM distributors WHERE did = $1;

  1332. $$ LANGUAGE SQL;

  1333. 

  1334. SELECT * FROM distributors_2(111) AS (f1 int, f2 text);

  1335.  f1  |     f2

  1336. -----+-------------

  1337.  111 | Walt Disney

  1338. </pre><p>

  1339.   </p><p>

  1340.    Here is an example of a function with an ordinality column added:

  1341. 

  1342. </p><pre class="programlisting">

  1343. SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;

  1344.  unnest | ordinality

  1345. --------+----------

  1346.  a      |        1

  1347.  b      |        2

  1348.  c      |        3

  1349.  d      |        4

  1350.  e      |        5

  1351.  f      |        6

  1352. (6 rows)

  1353. </pre><p>

  1354.   </p><p>

  1355.    This example shows how to use a simple <code class="literal">WITH</code> clause:

  1356. 

  1357. </p><pre class="programlisting">

  1358. WITH t AS (

  1359.     SELECT random() as x FROM generate_series(1, 3)

  1360.   )

  1361. SELECT * FROM t

  1362. UNION ALL

  1363. SELECT * FROM t

  1364. 

  1365.          x          

  1366. --------------------

  1367.   0.534150459803641

  1368.   0.520092216785997

  1369.  0.0735620250925422

  1370.   0.534150459803641

  1371.   0.520092216785997

  1372.  0.0735620250925422

  1373. </pre><p>

  1374. 

  1375.    Notice that the <code class="literal">WITH</code> query was evaluated only once,

  1376.    so that we got two sets of the same three random values.

  1377.   </p><p>

  1378.    This example uses <code class="literal">WITH RECURSIVE</code> to find all

  1379.    subordinates (direct or indirect) of the employee Mary, and their

  1380.    level of indirectness, from a table that shows only direct

  1381.    subordinates:

  1382. 

  1383. </p><pre class="programlisting">

  1384. WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (

  1385.     SELECT 1, employee_name, manager_name

  1386.     FROM employee

  1387.     WHERE manager_name = 'Mary'

  1388.   UNION ALL

  1389.     SELECT er.distance + 1, e.employee_name, e.manager_name

  1390.     FROM employee_recursive er, employee e

  1391.     WHERE er.employee_name = e.manager_name

  1392.   )

  1393. SELECT distance, employee_name FROM employee_recursive;

  1394. </pre><p>

  1395. 

  1396.    Notice the typical form of recursive queries:

  1397.    an initial condition, followed by <code class="literal">UNION</code>,

  1398.    followed by the recursive part of the query. Be sure that the

  1399.    recursive part of the query will eventually return no tuples, or

  1400.    else the query will loop indefinitely.  (See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a>

  1401.    for more examples.)

  1402.   </p><p>

  1403.    This example uses <code class="literal">LATERAL</code> to apply a set-returning function

  1404.    <code class="function">get_product_names()</code> for each row of the

  1405.    <code class="structname">manufacturers</code> table:

  1406. 

  1407. </p><pre class="programlisting">

  1408. SELECT m.name AS mname, pname

  1409. FROM manufacturers m, LATERAL get_product_names(m.id) pname;

  1410. </pre><p>

  1411. 

  1412.     Manufacturers not currently having any products would not appear in the

  1413.     result, since it is an inner join.  If we wished to include the names of

  1414.     such manufacturers in the result, we could do:

  1415. 

  1416. </p><pre class="programlisting">

  1417. SELECT m.name AS mname, pname

  1418. FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;

  1419. </pre></div><div class="refsect1" id="id-1.9.3.171.10"><h2>Compatibility</h2><p>

  1420.    Of course, the <code class="command">SELECT</code> statement is compatible

  1421.    with the SQL standard.  But there are some extensions and some

  1422.    missing features.

  1423.   </p><div class="refsect2" id="id-1.9.3.171.10.3"><h3>Omitted <code class="literal">FROM</code> Clauses</h3><p>

  1424.     <span class="productname">PostgreSQL</span> allows one to omit the

  1425.     <code class="literal">FROM</code> clause.  It has a straightforward use to

  1426.     compute the results of simple expressions:

  1427. </p><pre class="programlisting">

  1428. SELECT 2+2;

  1429. 

  1430.  ?column?

  1431. ----------

  1432.         4

  1433. </pre><p>

  1434.     Some other <acronym class="acronym">SQL</acronym> databases cannot do this except

  1435.     by introducing a dummy one-row table from which to do the

  1436.     <code class="command">SELECT</code>.

  1437.    </p><p>

  1438.     Note that if a <code class="literal">FROM</code> clause is not specified,

  1439.     the query cannot reference any database tables. For example, the

  1440.     following query is invalid:

  1441. </p><pre class="programlisting">

  1442. SELECT distributors.* WHERE distributors.name = 'Westward';

  1443. </pre><p>

  1444.     <span class="productname">PostgreSQL</span> releases prior to

  1445.     8.1 would accept queries of this form, and add an implicit entry

  1446.     to the query's <code class="literal">FROM</code> clause for each table

  1447.     referenced by the query. This is no longer allowed.

  1448.    </p></div><div class="refsect2" id="id-1.9.3.171.10.4"><h3>Empty <code class="literal">SELECT</code> Lists</h3><p>

  1449.     The list of output expressions after <code class="literal">SELECT</code> can be

  1450.     empty, producing a zero-column result table.

  1451.     This is not valid syntax according to the SQL standard.

  1452.     <span class="productname">PostgreSQL</span> allows it to be consistent with

  1453.     allowing zero-column tables.

  1454.     However, an empty list is not allowed when <code class="literal">DISTINCT</code> is used.

  1455.    </p></div><div class="refsect2" id="id-1.9.3.171.10.5"><h3>Omitting the <code class="literal">AS</code> Key Word</h3><p>

  1456.     In the SQL standard, the optional key word <code class="literal">AS</code> can be

  1457.     omitted before an output column name whenever the new column name

  1458.     is a valid column name (that is, not the same as any reserved

  1459.     keyword).  <span class="productname">PostgreSQL</span> is slightly more

  1460.     restrictive: <code class="literal">AS</code> is required if the new column name

  1461.     matches any keyword at all, reserved or not.  Recommended practice is

  1462.     to use <code class="literal">AS</code> or double-quote output column names, to prevent

  1463.     any possible conflict against future keyword additions.

  1464.    </p><p>

  1465.     In <code class="literal">FROM</code> items, both the standard and

  1466.     <span class="productname">PostgreSQL</span> allow <code class="literal">AS</code> to

  1467.     be omitted before an alias that is an unreserved keyword.  But

  1468.     this is impractical for output column names, because of syntactic

  1469.     ambiguities.

  1470.    </p></div><div class="refsect2" id="id-1.9.3.171.10.6"><h3><code class="literal">ONLY</code> and Inheritance</h3><p>

  1471.     The SQL standard requires parentheses around the table name when

  1472.     writing <code class="literal">ONLY</code>, for example <code class="literal">SELECT * FROM ONLY

  1473.     (tab1), ONLY (tab2) WHERE ...</code>.  <span class="productname">PostgreSQL</span>

  1474.     considers these parentheses to be optional.

  1475.    </p><p>

  1476.     <span class="productname">PostgreSQL</span> allows a trailing <code class="literal">*</code> to be written to

  1477.     explicitly specify the non-<code class="literal">ONLY</code> behavior of including

  1478.     child tables.  The standard does not allow this.

  1479.    </p><p>

  1480.     (These points apply equally to all SQL commands supporting the

  1481.     <code class="literal">ONLY</code> option.)

  1482.    </p></div><div class="refsect2" id="id-1.9.3.171.10.7"><h3><code class="literal">TABLESAMPLE</code> Clause Restrictions</h3><p>

  1483.     The <code class="literal">TABLESAMPLE</code> clause is currently accepted only on

  1484.     regular tables and materialized views.  According to the SQL standard

  1485.     it should be possible to apply it to any <code class="literal">FROM</code> item.

  1486.    </p></div><div class="refsect2" id="id-1.9.3.171.10.8"><h3>Function Calls in <code class="literal">FROM</code></h3><p>

  1487.     <span class="productname">PostgreSQL</span> allows a function call to be

  1488.     written directly as a member of the <code class="literal">FROM</code> list.  In the SQL

  1489.     standard it would be necessary to wrap such a function call in a

  1490.     sub-<code class="command">SELECT</code>; that is, the syntax

  1491.     <code class="literal">FROM <em class="replaceable"><code>func</code></em>(...) <em class="replaceable"><code>alias</code></em></code>

  1492.     is approximately equivalent to

  1493.     <code class="literal">FROM LATERAL (SELECT <em class="replaceable"><code>func</code></em>(...)) <em class="replaceable"><code>alias</code></em></code>.

  1494.     Note that <code class="literal">LATERAL</code> is considered to be implicit; this is

  1495.     because the standard requires <code class="literal">LATERAL</code> semantics for an

  1496.     <code class="literal">UNNEST()</code> item in <code class="literal">FROM</code>.

  1497.     <span class="productname">PostgreSQL</span> treats <code class="literal">UNNEST()</code> the

  1498.     same as other set-returning functions.

  1499.    </p></div><div class="refsect2" id="id-1.9.3.171.10.9"><h3>Namespace Available to <code class="literal">GROUP BY</code> and <code class="literal">ORDER BY</code></h3><p>

  1500.     In the SQL-92 standard, an <code class="literal">ORDER BY</code> clause can

  1501.     only use output column names or numbers, while a <code class="literal">GROUP

  1502.     BY</code> clause can only use expressions based on input column

  1503.     names.  <span class="productname">PostgreSQL</span> extends each of

  1504.     these clauses to allow the other choice as well (but it uses the

  1505.     standard's interpretation if there is ambiguity).

  1506.     <span class="productname">PostgreSQL</span> also allows both clauses to

  1507.     specify arbitrary expressions.  Note that names appearing in an

  1508.     expression will always be taken as input-column names, not as

  1509.     output-column names.

  1510.    </p><p>

  1511.     SQL:1999 and later use a slightly different definition which is not

  1512.     entirely upward compatible with SQL-92.

  1513.     In most cases, however, <span class="productname">PostgreSQL</span>

  1514.     will interpret an <code class="literal">ORDER BY</code> or <code class="literal">GROUP

  1515.     BY</code> expression the same way SQL:1999 does.

  1516.    </p></div><div class="refsect2" id="id-1.9.3.171.10.10"><h3>Functional Dependencies</h3><p>

  1517.     <span class="productname">PostgreSQL</span> recognizes functional dependency

  1518.     (allowing columns to be omitted from <code class="literal">GROUP BY</code>) only when

  1519.     a table's primary key is included in the <code class="literal">GROUP BY</code> list.

  1520.     The SQL standard specifies additional conditions that should be

  1521.     recognized.

  1522.    </p></div><div class="refsect2" id="id-1.9.3.171.10.11"><h3><code class="literal">LIMIT</code> and <code class="literal">OFFSET</code></h3><p>

  1523.     The clauses <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>

  1524.     are <span class="productname">PostgreSQL</span>-specific syntax, also

  1525.     used by <span class="productname">MySQL</span>.  The SQL:2008 standard

  1526.     has introduced the clauses <code class="literal">OFFSET ... FETCH {FIRST|NEXT}

  1527.     ...</code> for the same functionality, as shown above

  1528.     in <a class="xref" href="sql-select.html#SQL-LIMIT" title="LIMIT Clause"><code class="literal">LIMIT</code> Clause</a>.  This

  1529.     syntax is also used by <span class="productname">IBM DB2</span>.

  1530.     (Applications written for <span class="productname">Oracle</span>

  1531.     frequently use a workaround involving the automatically

  1532.     generated <code class="literal">rownum</code> column, which is not available in

  1533.     PostgreSQL, to implement the effects of these clauses.)

  1534.    </p></div><div class="refsect2" id="id-1.9.3.171.10.12"><h3><code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code>, <code class="literal">FOR KEY SHARE</code></h3><p>

  1535.     Although <code class="literal">FOR UPDATE</code> appears in the SQL standard, the

  1536.     standard allows it only as an option of <code class="command">DECLARE CURSOR</code>.

  1537.     <span class="productname">PostgreSQL</span> allows it in any <code class="command">SELECT</code>

  1538.     query as well as in sub-<code class="command">SELECT</code>s, but this is an extension.

  1539.     The <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code> and

  1540.     <code class="literal">FOR KEY SHARE</code> variants, as well as the <code class="literal">NOWAIT</code>

  1541.     and <code class="literal">SKIP LOCKED</code> options, do not appear in the

  1542.     standard.

  1543.    </p></div><div class="refsect2" id="id-1.9.3.171.10.13"><h3>Data-Modifying Statements in <code class="literal">WITH</code></h3><p>

  1544.     <span class="productname">PostgreSQL</span> allows <code class="command">INSERT</code>,

  1545.     <code class="command">UPDATE</code>, and <code class="command">DELETE</code> to be used as <code class="literal">WITH</code>

  1546.     queries.  This is not found in the SQL standard.

  1547.    </p></div><div class="refsect2" id="id-1.9.3.171.10.14"><h3>Nonstandard Clauses</h3><p>

  1548.     <code class="literal">DISTINCT ON ( ... )</code> is an extension of the

  1549.     SQL standard.

  1550.    </p><p>

  1551.     <code class="literal">ROWS FROM( ... )</code> is an extension of the SQL standard.

  1552.    </p><p>

  1553.     The <code class="literal">MATERIALIZED</code> and <code class="literal">NOT

  1554.     MATERIALIZED</code> options of <code class="literal">WITH</code> are extensions

  1555.     of the SQL standard.

  1556.    </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-security-label.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-selectinto.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SECURITY LABEL </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> SELECT INTO</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1