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