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 18.0 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> [, ...] ]
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 [ 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>from_reference</code></em> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
18 <span class="phrase">where <em class="replaceable"><code>from_item</code></em> can be one of:</span>
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>
34 <span class="phrase">and <em class="replaceable"><code>grouping_element</code></em> can be one of:</span>
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> [, ...] )
43 <span class="phrase">and <em class="replaceable"><code>with_query</code></em> is:</span>
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> | <em class="replaceable"><code>merge</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> ]
49 TABLE [ 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:
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 referenced
57 in the <code class="literal">FROM</code> list. A <code class="literal">WITH</code> query
58 that is referenced more than once in <code class="literal">FROM</code> is
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 or
65 virtual table.) If more than one element is specified in the
66 <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 rows
70 that do not satisfy the condition are eliminated from the
71 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, the
75 output is combined into groups of rows that match on one or more
76 values, and the results of aggregate functions are computed.
77 If the <code class="literal">HAVING</code> clause is present, it
78 eliminates groups that do not satisfy the given condition. (See
79 <a class="xref" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause">GROUP BY Clause</a> and
80 <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 next
82 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 the
86 <code class="command">SELECT</code> output expressions for each selected
87 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 the
89 result. <code class="literal">SELECT DISTINCT ON</code> eliminates rows that
90 match on all the specified expressions. <code class="literal">SELECT ALL</code>
91 (the default) will return all candidate rows, including
92 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>, the
96 output of more than one <code class="command">SELECT</code> statement can
97 be combined to form a single result set. The
98 <code class="literal">UNION</code> operator returns all rows that are in
99 one or both of the result sets. The
100 <code class="literal">INTERSECT</code> operator returns all rows that are
101 strictly in both result sets. The <code class="literal">EXCEPT</code>
102 operator returns the rows that are in the first result set but
103 not in the second. In all three cases, duplicate rows are
104 eliminated unless <code class="literal">ALL</code> is specified. The noise
105 word <code class="literal">DISTINCT</code> can be added to explicitly specify
106 eliminating duplicate rows. Notice that <code class="literal">DISTINCT</code> is
107 the default behavior here, even though <code class="literal">ALL</code> is
108 the default for <code class="command">SELECT</code> itself. (See
109 <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>, and
110 <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, the
113 returned rows are sorted in the specified order. If
114 <code class="literal">ORDER BY</code> is not given, the rows are returned
115 in whatever order the system finds fastest to produce. (See
116 <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> statement
120 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>
125 <code class="command">SELECT</code> statement locks the selected rows
126 against concurrent updates. (See <a class="xref" href="sql-select.html#SQL-FOR-UPDATE-SHARE" title="The Locking Clause">The Locking Clause</a>
128 </p></li></ol></div><p>
130 You must have <code class="literal">SELECT</code> privilege on each column used
131 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> requires
134 <code class="literal">UPDATE</code> privilege as well (for at least one column
135 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 more
138 subqueries that can be referenced by name in the primary query.
139 The subqueries effectively act as temporary tables or views
140 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>,
143 <code class="command">DELETE</code>, or <code class="command">MERGE</code> statement.
144 When writing a data-modifying statement (<code class="command">INSERT</code>,
145 <code class="command">UPDATE</code>, <code class="command">DELETE</code>, or <code class="command">MERGE</code>) in
146 <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 underlying
148 table that the statement modifies, that forms the temporary table that is
149 read by the primary query. If <code class="literal">RETURNING</code> is omitted, the
150 statement is still executed, but it produces no output so it cannot be
151 referenced as a table by the primary query.
153 A name (without schema qualification) must be specified for each
154 <code class="literal">WITH</code> query. Optionally, a list of column names
155 can be specified; if this is omitted,
156 the column names are inferred from the subquery.
158 If <code class="literal">RECURSIVE</code> is specified, it allows a
159 <code class="command">SELECT</code> subquery to reference itself by name. Such a
160 subquery must have the form
161 </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>
164 where the recursive self-reference must appear on the right-hand
165 side of the <code class="literal">UNION</code>. Only one recursive self-reference
166 is permitted per query. Recursive data-modifying statements are not
167 supported, but you can use the results of a recursive
168 <code class="command">SELECT</code> query in
169 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
172 Another effect of <code class="literal">RECURSIVE</code> is that
173 <code class="literal">WITH</code> queries need not be ordered: a query
174 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> queries
177 can only reference sibling <code class="literal">WITH</code> queries
178 that are earlier in the <code class="literal">WITH</code> list.
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 queries
183 in the <code class="literal">WITH</code> clause, though it has no effect on
184 queries that do not use recursion or forward references.
186 The optional <code class="literal">SEARCH</code> clause computes a <em class="firstterm">search
187 sequence column</em> that can be used for ordering the results of a
188 recursive query in either breadth-first or depth-first order. The
189 supplied column name list specifies the row key that is to be used for
190 keeping track of visited rows. A column named
191 <em class="replaceable"><code>search_seq_col_name</code></em> will be added to the result
192 column list of the <code class="literal">WITH</code> query. This column can be
193 ordered by in the outer query to achieve the respective ordering. See
194 <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.
196 The optional <code class="literal">CYCLE</code> clause is used to detect cycles in
197 recursive queries. The supplied column name list specifies the row key
198 that is to be used for keeping track of visited rows. A column named
199 <em class="replaceable"><code>cycle_mark_col_name</code></em> will be added to the result
200 column list of the <code class="literal">WITH</code> query. This column will be set
201 to <em class="replaceable"><code>cycle_mark_value</code></em> when a cycle has been
202 detected, else to <em class="replaceable"><code>cycle_mark_default</code></em>.
203 Furthermore, processing of the recursive union will stop when a cycle has
204 been detected. <em class="replaceable"><code>cycle_mark_value</code></em> and
205 <em class="replaceable"><code>cycle_mark_default</code></em> must be constants and they
206 must be coercible to a common data type, and the data type must have an
207 inequality operator. (The SQL standard requires that they be Boolean
208 constants or character strings, but PostgreSQL does not require that.) By
209 default, <code class="literal">TRUE</code> and <code class="literal">FALSE</code> (of type
210 <code class="type">boolean</code>) are used. Furthermore, a column
211 named <em class="replaceable"><code>cycle_path_col_name</code></em> will be added to the
212 result column list of the <code class="literal">WITH</code> query. This column is
213 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.
215 Both the <code class="literal">SEARCH</code> and the <code class="literal">CYCLE</code> clause
216 are only valid for recursive <code class="literal">WITH</code> queries. The
217 <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> (or
219 equivalent) commands (no nested <code class="literal">UNION</code>s). If both
220 clauses are used, the column added by the <code class="literal">SEARCH</code> clause
221 appears before the columns added by the <code class="literal">CYCLE</code> clause.
223 The primary query and the <code class="literal">WITH</code> queries are all
224 (notionally) executed at the same time. This implies that the effects of
225 a data-modifying statement in <code class="literal">WITH</code> cannot be seen from
226 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 same
228 row, the results are unspecified.
230 A key property of <code class="literal">WITH</code> queries is that they
231 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 be
234 executed once and only once, regardless of whether the primary query
235 reads all or any of their output.
237 However, a <code class="literal">WITH</code> query can be marked
238 <code class="literal">NOT MATERIALIZED</code> to remove this guarantee. In that
239 case, the <code class="literal">WITH</code> query can be folded into the primary
240 query much as though it were a simple sub-<code class="literal">SELECT</code> in
241 the primary query's <code class="literal">FROM</code> clause. This results in
242 duplicate computations if the primary query refers to
243 that <code class="literal">WITH</code> query more than once; but if each such use
244 requires only a few rows of the <code class="literal">WITH</code> query's total
245 output, <code class="literal">NOT MATERIALIZED</code> can provide a net savings by
246 allowing the queries to be optimized jointly.
247 <code class="literal">NOT MATERIALIZED</code> is ignored if it is attached to
248 a <code class="literal">WITH</code> query that is recursive or is not
249 side-effect-free (i.e., is not a plain <code class="literal">SELECT</code>
250 containing no volatile functions).
252 By default, a side-effect-free <code class="literal">WITH</code> query is folded
253 into the primary query if it is used exactly once in the primary
254 query's <code class="literal">FROM</code> clause. This allows joint optimization
255 of the two query levels in situations where that should be semantically
256 invisible. However, such folding can be prevented by marking the
257 <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> query
259 is being used as an optimization fence to prevent the planner from
261 <span class="productname">PostgreSQL</span> versions before v12 never did
262 such folding, so queries written for older versions might rely on
263 <code class="literal">WITH</code> to act as an optimization fence.
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 source
268 tables for the <code class="command">SELECT</code>. If multiple sources are
269 specified, the result is the Cartesian product (cross join) of all
270 the sources. But usually qualification conditions are added (via
271 <code class="literal">WHERE</code>) to restrict the returned rows to a small subset of the
274 The <code class="literal">FROM</code> clause can contain the following
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 that
280 table is scanned. If <code class="literal">ONLY</code> is not specified, the table
281 and all its descendant tables (if any) are scanned. Optionally,
282 <code class="literal">*</code> can be specified after the table name to explicitly
283 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 the
286 alias. An alias is used for brevity or to eliminate ambiguity
287 for self-joins (where the same table is scanned multiple
288 times). When an alias is provided, it completely hides the
289 actual name of the table or function; for example given
290 <code class="literal">FROM foo AS f</code>, the remainder of the
291 <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 is
293 written, a column alias list can also be written to provide
294 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 after
297 a <em class="replaceable"><code>table_name</code></em> indicates that the
298 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 such
301 as <code class="literal">WHERE</code> clauses.
302 The standard <span class="productname">PostgreSQL</span> distribution
303 includes two sampling methods, <code class="literal">BERNOULLI</code>
304 and <code class="literal">SYSTEM</code>, and other sampling methods can be
305 installed in the database via extensions.
307 The <code class="literal">BERNOULLI</code> and <code class="literal">SYSTEM</code> sampling methods
308 each accept a single <em class="replaceable"><code>argument</code></em>
309 which is the fraction of the table to sample, expressed as a
310 percentage between 0 and 100. This argument can be
311 any <code class="type">real</code>-valued expression. (Other sampling methods might
312 accept more or different arguments.) These two methods each return
313 a randomly-chosen sample of the table that will contain
314 approximately the specified percentage of the table's rows.
315 The <code class="literal">BERNOULLI</code> method scans the whole table and
316 selects or ignores individual rows independently with the specified
318 The <code class="literal">SYSTEM</code> method does block-level sampling with
319 each block having the specified chance of being selected; all rows
320 in each selected block are returned.
321 The <code class="literal">SYSTEM</code> method is significantly faster than
322 the <code class="literal">BERNOULLI</code> method when small sampling
323 percentages are specified, but it may return a less-random sample of
324 the table as a result of clustering effects.
326 The optional <code class="literal">REPEATABLE</code> clause specifies
327 a <em class="replaceable"><code>seed</code></em> number or expression to use
328 for generating random numbers within the sampling method. The seed
329 value can be any non-null floating-point value. Two queries that
330 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 has
332 not been changed meanwhile. But different seed values will usually
333 produce different samples.
334 If <code class="literal">REPEATABLE</code> is not given then a new random
335 sample is selected for each query, based upon a system-generated seed.
336 Note that some add-on sampling methods do not
337 accept <code class="literal">REPEATABLE</code>, and will always produce new
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 the
341 <code class="literal">FROM</code> clause. This acts as though its
342 output were created as a temporary table for the duration of
343 this single <code class="command">SELECT</code> command. Note that the
344 sub-<code class="command">SELECT</code> must be surrounded by
345 parentheses, and an alias can be provided in the same way as for a
347 <a class="link" href="sql-values.html" title="VALUES"><code class="command">VALUES</code></a> command
348 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 name
353 for the purposes of the primary query. If necessary, you can
354 refer to a real table of the same name by schema-qualifying
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 return
360 result sets, but any function can be used.) This acts as
361 though the function's output were created as a temporary table for the
362 duration of this single <code class="command">SELECT</code> command.
363 If the function's result type is composite (including the case of a
364 function with multiple <code class="literal">OUT</code> parameters), each
365 attribute becomes a separate column in the implicit table.
367 When the optional <code class="command">WITH ORDINALITY</code> clause is added
368 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 column
370 numbers the rows of the function's result set, starting from 1.
371 By default, this column is named <code class="literal">ordinality</code>.
373 An alias can be provided in the same way as for a table.
374 If an alias is written, a column
375 alias list can also be written to provide substitute names for
376 one or more attributes of the function's composite return
377 type, including the ordinality column if present.
379 Multiple function calls can be combined into a
380 single <code class="literal">FROM</code>-clause item by surrounding them
381 with <code class="literal">ROWS FROM( ... )</code>. The output of such an item is the
382 concatenation of the first row from each function, then the second
383 row from each function, etc. If some of the functions produce fewer
384 rows than others, null values are substituted for the missing data, so
385 that the total number of rows returned is always the same as for the
386 function that produced the most rows.
388 If the function has been defined as returning the
389 <code class="type">record</code> data type, then an alias or the key word
390 <code class="literal">AS</code> must be present, followed by a column
391 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 the
393 actual number and types of columns returned by the function.
395 When using the <code class="literal">ROWS FROM( ... )</code> syntax, if one of the
396 functions requires a column definition list, it's preferred to put
397 the column definition list after the function call inside
398 <code class="literal">ROWS FROM( ... )</code>. A column definition list can be placed
399 after the <code class="literal">ROWS FROM( ... )</code> construct only if there's just
400 a single function and no <code class="literal">WITH ORDINALITY</code> clause.
402 To use <code class="literal">ORDINALITY</code> together with a column definition
403 list, you must use the <code class="literal">ROWS FROM( ... )</code> syntax and put the
404 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>
407 </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>
409 For the <code class="literal">INNER</code> and <code class="literal">OUTER</code> join types, a
410 join condition must be specified, namely exactly one of
411 <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.
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 nest
420 left-to-right. In any case <code class="literal">JOIN</code> binds more
421 tightly than the commas separating <code class="literal">FROM</code>-list items.
422 All the <code class="literal">JOIN</code> options are just a notational
423 convenience, since they do nothing you couldn't do with plain
424 <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 qualified
426 Cartesian product (i.e., all combined rows that pass its join
427 condition), plus one copy of each row in the left-hand table
428 for which there was no right-hand row that passed the join
429 condition. This left-hand row is extended to the full width
430 of the joined table by inserting null values for the
431 right-hand columns. Note that only the <code class="literal">JOIN</code>
432 clause's own condition is considered while deciding which rows
433 have matches. Outer conditions are applied afterwards.
435 Conversely, <code class="literal">RIGHT OUTER JOIN</code> returns all the
436 joined rows, plus one row for each unmatched right-hand row
437 (extended with nulls on the left). This is just a notational
438 convenience, since you could convert it to a <code class="literal">LEFT
439 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, plus
441 one row for each unmatched left-hand row (extended with nulls
442 on the right), plus one row for each unmatched right-hand row
443 (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> is
445 an expression resulting in a value of type
446 <code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>
447 clause) that specifies which rows in a join are considered to
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> is
451 shorthand for <code class="literal">ON left_table.a = right_table.a AND
452 left_table.b = right_table.b ...</code>. Also,
453 <code class="literal">USING</code> implies that only one of each pair of
454 equivalent columns will be included in the join output, not
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> clause
460 are addressable by this name. Unlike a regular <em class="replaceable"><code>alias</code></em>, this does not hide the names of
461 the joined tables from the rest of the query. Also unlike a regular
462 <em class="replaceable"><code>alias</code></em>, you cannot write a
463 column alias list — the output names of the join columns are the
464 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 a
467 <code class="literal">USING</code> list that mentions all columns in the two
468 tables that have matching names. If there are no common
469 column names, <code class="literal">NATURAL</code> is equivalent
470 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 ON
473 (TRUE)</code>, that is, no rows are removed by qualification.
474 They produce a simple Cartesian product, the same result as you get from
475 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 a
479 sub-<code class="command">SELECT</code> <code class="literal">FROM</code> item. This allows the
480 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. (Without
482 <code class="literal">LATERAL</code>, each sub-<code class="command">SELECT</code> is
483 evaluated independently and so cannot cross-reference any other
484 <code class="literal">FROM</code> item.)
485 </p><p><code class="literal">LATERAL</code> can also precede a function-call
486 <code class="literal">FROM</code> item, but in this case it is a noise word, because
487 the function expression can refer to earlier <code class="literal">FROM</code> items
490 A <code class="literal">LATERAL</code> item can appear at top level in the
491 <code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree. In the
492 latter case it can also refer to any items that are on the left-hand
493 side of a <code class="literal">JOIN</code> that it is on the right-hand side of.
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 the
497 <code class="literal">FROM</code> item providing the cross-referenced column(s), or
498 set of rows of multiple <code class="literal">FROM</code> items providing the
499 columns, the <code class="literal">LATERAL</code> item is evaluated using that
500 row or row set's values of the columns. The resulting row(s) are
501 joined as usual with the rows they were computed from. This is
502 repeated for each row or set of rows from the column source table(s).
504 The column source table(s) must be <code class="literal">INNER</code> or
505 <code class="literal">LEFT</code> joined to the <code class="literal">LATERAL</code> item, else
506 there would not be a well-defined set of rows from which to compute
507 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 JOIN
509 LATERAL <em class="replaceable"><code>Y</code></em></code> is syntactically valid, it is
510 not actually allowed for <em class="replaceable"><code>Y</code></em> to reference
511 <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 form
515 </p><pre class="synopsis">
516 WHERE <em class="replaceable"><code>condition</code></em>
518 where <em class="replaceable"><code>condition</code></em> is
519 any expression that evaluates to a result of type
520 <code class="type">boolean</code>. Any row that does not satisfy this
521 condition will be eliminated from the output. A row satisfies the
522 condition if it returns true when the actual row values are
523 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 form
526 </p><pre class="synopsis">
527 GROUP BY [ ALL | DISTINCT ] <em class="replaceable"><code>grouping_element</code></em> [, ...]
530 <code class="literal">GROUP BY</code> will condense into a single row all
531 selected rows that share the same values for the grouped
532 expressions. An <em class="replaceable"><code>expression</code></em> used inside a
533 <em class="replaceable"><code>grouping_element</code></em>
534 can be an input column name, or the name or ordinal number of an
535 output column (<code class="command">SELECT</code> list item), or an arbitrary
536 expression formed from input-column values. In case of ambiguity,
537 a <code class="literal">GROUP BY</code> name will be interpreted as an
538 input-column name rather than an output column name.
540 If any of <code class="literal">GROUPING SETS</code>, <code class="literal">ROLLUP</code> or
541 <code class="literal">CUBE</code> are present as grouping elements, then the
542 <code class="literal">GROUP BY</code> clause as a whole defines some number of
543 independent <em class="replaceable"><code>grouping sets</code></em>. The effect of this is
544 equivalent to constructing a <code class="literal">UNION ALL</code> between
545 subqueries with the individual grouping sets as their
546 <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 handling
550 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>.
552 Aggregate functions, if any are used, are computed across all rows
553 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 all
557 The set of rows fed to each aggregate function can be further filtered by
558 attaching a <code class="literal">FILTER</code> clause to the aggregate function
559 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
560 a <code class="literal">FILTER</code> clause is present, only those rows matching it
561 are included in the input to that aggregate function.
563 When <code class="literal">GROUP BY</code> is present,
564 or any aggregate functions are present, it is not valid for
565 the <code class="command">SELECT</code> list expressions to refer to
566 ungrouped columns except within aggregate functions or when the
567 ungrouped column is functionally dependent on the grouped columns,
568 since there would otherwise be more than one possible value to
569 return for an ungrouped column. A functional dependency exists if
570 the grouped columns (or a subset thereof) are the primary key of
571 the table containing the ungrouped column.
573 Keep in mind that all aggregate functions are evaluated before
574 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 of
577 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>.
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 be
581 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 form
584 </p><pre class="synopsis">
585 HAVING <em class="replaceable"><code>condition</code></em>
587 where <em class="replaceable"><code>condition</code></em> is
588 the same as specified for the <code class="literal">WHERE</code> clause.
590 <code class="literal">HAVING</code> eliminates group rows that do not
591 satisfy the condition. <code class="literal">HAVING</code> is different
592 from <code class="literal">WHERE</code>: <code class="literal">WHERE</code> filters
593 individual rows before the application of <code class="literal">GROUP
594 BY</code>, while <code class="literal">HAVING</code> filters group rows
595 created by <code class="literal">GROUP BY</code>. Each column referenced in
596 <em class="replaceable"><code>condition</code></em> must
597 unambiguously reference a grouping column, unless the reference
598 appears within an aggregate function or the ungrouped column is
599 functionally dependent on the grouping columns.
601 The presence of <code class="literal">HAVING</code> turns a query into a grouped
602 query even if there is no <code class="literal">GROUP BY</code> clause. This is the
603 same as what happens when the query contains aggregate functions but
604 no <code class="literal">GROUP BY</code> clause. All the selected rows are considered to
605 form a single group, and the <code class="command">SELECT</code> list and
606 <code class="literal">HAVING</code> clause can only reference table columns from
607 within aggregate functions. Such a query will emit a single row if the
608 <code class="literal">HAVING</code> condition is true, zero rows if it is not true.
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 be
612 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 form
615 </p><pre class="synopsis">
616 WINDOW <em class="replaceable"><code>window_name</code></em> AS ( <em class="replaceable"><code>window_definition</code></em> ) [, ...]
618 where <em class="replaceable"><code>window_name</code></em> is
619 a name that can be referenced from <code class="literal">OVER</code> clauses or
620 subsequent window definitions, and
621 <em class="replaceable"><code>window_definition</code></em> is
622 </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> ]
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 cannot
633 specify its own <code class="literal">PARTITION BY</code> clause, and it can specify
634 <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 window
636 must not specify a frame clause.
638 The elements of the <code class="literal">PARTITION BY</code> list are interpreted in
639 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 that
640 they are always simple expressions and never the name or number of an
642 Another difference is that these expressions can contain aggregate
643 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 grouping
647 Similarly, the elements of the <code class="literal">ORDER BY</code> list are interpreted
648 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 that
649 the expressions are always taken as simple expressions and never the name
650 or number of an output column.
652 The optional <em class="replaceable"><code>frame_clause</code></em> defines
653 the <em class="firstterm">window frame</em> for window functions that depend on the
654 frame (not all do). The window frame is a set of related rows for
655 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 of
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> ]
663 where <em class="replaceable"><code>frame_start</code></em>
664 and <em class="replaceable"><code>frame_end</code></em> can be one of
666 </p><pre class="synopsis">
668 <em class="replaceable"><code>offset</code></em> PRECEDING
670 <em class="replaceable"><code>offset</code></em> FOLLOWING
674 and <em class="replaceable"><code>frame_exclusion</code></em> can be one of
676 </p><pre class="synopsis">
683 If <em class="replaceable"><code>frame_end</code></em> is omitted it defaults to <code class="literal">CURRENT
684 ROW</code>. Restrictions are that
685 <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 the
688 above list of <em class="replaceable"><code>frame_start</code></em>
689 and <em class="replaceable"><code>frame_end</code></em> options than
690 the <em class="replaceable"><code>frame_start</code></em> choice does — for example
691 <code class="literal">RANGE BETWEEN CURRENT ROW AND <em class="replaceable"><code>offset</code></em>
692 PRECEDING</code> is not allowed.
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 AND
696 CURRENT ROW</code>; it sets the frame to be all rows from the partition start
697 up through the current row's last <em class="firstterm">peer</em> (a row
698 that the window's <code class="literal">ORDER BY</code> clause considers
699 equivalent to the current row; all rows are peers if there
700 is no <code class="literal">ORDER BY</code>).
701 In general, <code class="literal">UNBOUNDED PRECEDING</code> means that the frame
702 starts with the first row of the partition, and similarly
703 <code class="literal">UNBOUNDED FOLLOWING</code> means that the frame ends with the last
704 row of the partition, regardless
705 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> means
708 that the frame starts or ends with the current row; but
709 in <code class="literal">RANGE</code> or <code class="literal">GROUPS</code> mode it means
710 that the frame starts or ends with the current row's first or last peer
711 in the <code class="literal">ORDER BY</code> ordering.
712 The <em class="replaceable"><code>offset</code></em> <code class="literal">PRECEDING</code> and
713 <em class="replaceable"><code>offset</code></em> <code class="literal">FOLLOWING</code> options
714 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 rows
717 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 peer
720 groups before or after the current row's peer group, where
721 a <em class="firstterm">peer group</em> is a group of rows that are
722 equivalent according to the window's <code class="literal">ORDER BY</code> clause.
723 In <code class="literal">RANGE</code> mode, use of
724 an <em class="replaceable"><code>offset</code></em> option requires that there be
725 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 no
727 more than <em class="replaceable"><code>offset</code></em> less than
728 (for <code class="literal">PRECEDING</code>) or more than
729 (for <code class="literal">FOLLOWING</code>) the current row's ordering column
730 value. In these cases the data type of
731 the <em class="replaceable"><code>offset</code></em> expression depends on the data
732 type of the ordering column. For numeric ordering columns it is
733 typically of the same type as the ordering column, but for datetime
734 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, while
737 the <em class="replaceable"><code>offset</code></em> does not have to be a simple
738 constant, it cannot contain variables, aggregate functions, or window
741 The <em class="replaceable"><code>frame_exclusion</code></em> option allows rows around
742 the current row to be excluded from the frame, even if they would be
743 included according to the frame start and frame end options.
744 <code class="literal">EXCLUDE CURRENT ROW</code> excludes the current row from the
746 <code class="literal">EXCLUDE GROUP</code> excludes the current row and its
747 ordering peers from the frame.
748 <code class="literal">EXCLUDE TIES</code> excludes any peers of the current
749 row from the frame, but not the current row itself.
750 <code class="literal">EXCLUDE NO OTHERS</code> simply specifies explicitly the
751 default behavior of not excluding the current row or its peers.
753 Beware that the <code class="literal">ROWS</code> mode can produce unpredictable
754 results if the <code class="literal">ORDER BY</code> ordering does not order the rows
755 uniquely. The <code class="literal">RANGE</code> and <code class="literal">GROUPS</code>
756 modes are designed to ensure that rows that are peers in
757 the <code class="literal">ORDER BY</code> ordering are treated alike: all rows of
758 a given peer group will be in the frame or excluded from it.
760 The purpose of a <code class="literal">WINDOW</code> clause is to specify the
761 behavior of <em class="firstterm">window functions</em> appearing in the query's
762 <a class="link" href="sql-select.html#SQL-SELECT-LIST" title="SELECT List"><code class="command">SELECT</code> list</a> or
763 <a class="link" href="sql-select.html#SQL-ORDERBY" title="ORDER BY Clause"><code class="literal">ORDER BY</code></a> clause.
765 can reference the <code class="literal">WINDOW</code> clause entries by name
766 in their <code class="literal">OVER</code> clauses. A <code class="literal">WINDOW</code> clause
767 entry does not have to be referenced anywhere, however; if it is not
768 used in the query it is simply ignored. It is possible to use window
769 functions without any <code class="literal">WINDOW</code> clause at all, since
770 a window function call can specify its window definition directly in
771 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 more
773 than one window function.
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 be
777 specified with <code class="literal">WINDOW</code>.
779 Window functions are described in detail in
780 <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>, and
782 <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 words
785 <code class="literal">SELECT</code> and <code class="literal">FROM</code>) specifies expressions
786 that form the output rows of the <code class="command">SELECT</code>
787 statement. The expressions can (and usually do) refer to columns
788 computed in the <code class="literal">FROM</code> clause.
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 just
792 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 query
794 as the column name of the virtual table produced by the sub-query.
795 To specify the name to use for an output column, write
796 <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 any
799 <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
800 future keyword additions, it is recommended that you always either
801 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 automatically
803 by <span class="productname">PostgreSQL</span>. If the column's expression
804 is a simple column reference then the chosen name is the same as that
805 column's name. In more complex cases a function or type name may be
806 used, or the system may fall back on a generated name such as
807 <code class="literal">?column?</code>.
809 An output column's name can be used to refer to the column's value in
810 <code class="literal">ORDER BY</code> and <code class="literal">GROUP BY</code> clauses, but not in the
811 <code class="literal">WHERE</code> or <code class="literal">HAVING</code> clauses; there you must write
812 out the expression instead.
814 Instead of an expression, <code class="literal">*</code> can be written in
815 the output list as a shorthand for all the columns of the selected
816 rows. Also, you can write <code class="literal"><em class="replaceable"><code>table_name</code></em>.*</code> as a
817 shorthand for the columns coming from just that table. In these
818 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.
821 According to the SQL standard, the expressions in the output list should
822 be computed before applying <code class="literal">DISTINCT</code>, <code class="literal">ORDER
823 BY</code>, or <code class="literal">LIMIT</code>. This is obviously necessary
824 when using <code class="literal">DISTINCT</code>, since otherwise it's not clear
825 what values are being made distinct. However, in many cases it is
826 convenient if output expressions are computed after <code class="literal">ORDER
827 BY</code> and <code class="literal">LIMIT</code>; particularly if the output list
828 contains any volatile or expensive functions. With that behavior, the
829 order of function evaluations is more intuitive and there will not be
830 evaluations corresponding to rows that never appear in the output.
831 <span class="productname">PostgreSQL</span> will effectively evaluate output expressions
832 after sorting and limiting, so long as those expressions are not
833 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">SELECT
835 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 functions
837 are effectively evaluated after sorting and before limiting, so
838 that <code class="literal">LIMIT</code> will act to cut off the output from a
839 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 any
842 guarantees about the timing of evaluation of output expressions versus
843 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 are
846 removed from the result set (one row is kept from each group of
847 duplicates). <code class="literal">SELECT ALL</code> specifies the opposite: all rows are
848 kept; that is the default.
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 given
852 expressions evaluate to equal. The <code class="literal">DISTINCT ON</code>
853 expressions are interpreted using the same rules as for
854 <code class="literal">ORDER BY</code> (see above). Note that the <span class="quote">“<span class="quote">first
855 row</span>”</span> of each set is unpredictable unless <code class="literal">ORDER
856 BY</code> is used to ensure that the desired row appears first. For
858 </p><pre class="programlisting">
859 SELECT DISTINCT ON (location) location, time, report
861 ORDER BY location, time DESC;
863 retrieves the most recent weather report for each location. But
864 if we had not used <code class="literal">ORDER BY</code> to force descending order
865 of time values for each location, we'd have gotten a report from
866 an unpredictable time for each location.
868 The <code class="literal">DISTINCT ON</code> expression(s) must match the leftmost
869 <code class="literal">ORDER BY</code> expression(s). The <code class="literal">ORDER BY</code> clause
870 will normally contain additional expression(s) that determine the
871 desired precedence of rows within each <code class="literal">DISTINCT ON</code> group.
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 be
875 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> is
881 any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
882 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 a
885 subexpression if it is enclosed in parentheses. Without
886 parentheses, these clauses will be taken to apply to the result of
887 the <code class="literal">UNION</code>, not to its right-hand input
890 The <code class="literal">UNION</code> operator computes the set union of
891 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 it
893 appears in at least one of the result sets. The two
894 <code class="command">SELECT</code> statements that represent the direct
895 operands of the <code class="literal">UNION</code> must produce the same
896 number of columns, and corresponding columns must be of compatible
899 The result of <code class="literal">UNION</code> does not contain any duplicate
900 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 than
903 <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 the
905 default behavior of eliminating duplicate rows.
907 Multiple <code class="literal">UNION</code> operators in the same
908 <code class="command">SELECT</code> statement are evaluated left to right,
909 unless otherwise indicated by parentheses.
911 Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and
912 <code class="literal">FOR KEY SHARE</code> cannot be
913 specified either for a <code class="literal">UNION</code> result or for any input of a
914 <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> is
920 any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
921 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.
924 The <code class="literal">INTERSECT</code> operator computes the set
925 intersection of the rows returned by the involved
926 <code class="command">SELECT</code> statements. A row is in the
927 intersection of two result sets if it appears in both result sets.
929 The result of <code class="literal">INTERSECT</code> does not contain any
930 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 the
932 left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear
933 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 the
935 default behavior of eliminating duplicate rows.
937 Multiple <code class="literal">INTERSECT</code> operators in the same
938 <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 than
941 <code class="literal">UNION</code>. That is, <code class="literal">A UNION B INTERSECT
942 C</code> will be read as <code class="literal">A UNION (B INTERSECT
945 Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and
946 <code class="literal">FOR KEY SHARE</code> cannot be
947 specified either for an <code class="literal">INTERSECT</code> result or for any input of
948 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> is
954 any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
955 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.
958 The <code class="literal">EXCEPT</code> operator computes the set of rows
959 that are in the result of the left <code class="command">SELECT</code>
960 statement but not in the result of the right one.
962 The result of <code class="literal">EXCEPT</code> does not contain any
963 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 the
965 left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear
966 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 the
968 default behavior of eliminating duplicate rows.
970 Multiple <code class="literal">EXCEPT</code> operators in the same
971 <code class="command">SELECT</code> statement are evaluated left to right,
972 unless parentheses dictate otherwise. <code class="literal">EXCEPT</code> binds at
973 the same level as <code class="literal">UNION</code>.
975 Currently, <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR UPDATE</code>, <code class="literal">FOR SHARE</code> and
976 <code class="literal">FOR KEY SHARE</code> cannot be
977 specified either for an <code class="literal">EXCEPT</code> result or for any input of
978 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">
982 ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [ NULLS { FIRST | LAST } ] [, ...]
984 The <code class="literal">ORDER BY</code> clause causes the result rows to
985 be sorted according to the specified expression(s). If two rows are
986 equal according to the leftmost expression, they are compared
987 according to the next expression and so on. If they are equal
988 according to all specified expressions, they are returned in
989 an implementation-dependent order.
991 Each <em class="replaceable"><code>expression</code></em> can be the
992 name or ordinal number of an output column
993 (<code class="command">SELECT</code> list item), or it can be an arbitrary
994 expression formed from input-column values.
996 The ordinal number refers to the ordinal (left-to-right) position
997 of the output column. This feature makes it possible to define an
998 ordering on the basis of a column that does not have a unique
999 name. This is never absolutely necessary because it is always
1000 possible to assign a name to an output column using the
1001 <code class="literal">AS</code> clause.
1003 It is also possible to use arbitrary expressions in the
1004 <code class="literal">ORDER BY</code> clause, including columns that do not
1005 appear in the <code class="command">SELECT</code> output list. Thus the
1006 following statement is valid:
1007 </p><pre class="programlisting">
1008 SELECT name FROM distributors ORDER BY code;
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 only
1013 specify an output column name or number, not an expression.
1015 If an <code class="literal">ORDER BY</code> expression is a simple name that
1016 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> will
1019 make in the same situation. This inconsistency is made to be
1020 compatible with the SQL standard.
1022 Optionally one can add the key word <code class="literal">ASC</code> (ascending) or
1023 <code class="literal">DESC</code> (descending) after any expression in the
1024 <code class="literal">ORDER BY</code> clause. If not specified, <code class="literal">ASC</code> is
1025 assumed by default. Alternatively, a specific ordering operator
1026 name can be specified in the <code class="literal">USING</code> clause.
1027 An ordering operator must be a less-than or greater-than
1028 member of some B-tree operator family.
1029 <code class="literal">ASC</code> is usually equivalent to <code class="literal">USING <</code> and
1030 <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 the
1032 default sort ordering is, and it might correspond to operators with other
1035 If <code class="literal">NULLS LAST</code> is specified, null values sort after all
1036 non-null values; if <code class="literal">NULLS FIRST</code> is specified, null values
1037 sort before all non-null values. If neither is specified, the default
1038 behavior is <code class="literal">NULLS LAST</code> when <code class="literal">ASC</code> is specified
1039 or implied, and <code class="literal">NULLS FIRST</code> when <code class="literal">DESC</code> is specified
1040 (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 depends
1042 on whether the operator is a less-than or greater-than operator.
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 mean
1046 the same thing as <code class="literal">ORDER BY x DESC, y DESC</code>.
1048 Character-string data is sorted according to the collation that applies
1049 to the column being sorted. That can be overridden at need by including
1050 a <code class="literal">COLLATE</code> clause in the
1051 <em class="replaceable"><code>expression</code></em>, for example
1052 <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> and
1054 <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.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 independent
1058 </p><pre class="synopsis">
1059 LIMIT { <em class="replaceable"><code>count</code></em> | ALL }
1060 OFFSET <em class="replaceable"><code>start</code></em>
1062 The parameter <em class="replaceable"><code>count</code></em> specifies the
1063 maximum number of rows to return, while <em class="replaceable"><code>start</code></em> specifies the number of rows
1064 to skip before starting to return rows. When both are specified,
1065 <em class="replaceable"><code>start</code></em> rows are skipped
1066 before starting to count the <em class="replaceable"><code>count</code></em> rows to be returned.
1068 If the <em class="replaceable"><code>count</code></em> expression
1069 evaluates to NULL, it is treated as <code class="literal">LIMIT ALL</code>, i.e., no
1070 limit. If <em class="replaceable"><code>start</code></em> evaluates
1071 to NULL, it is treated the same as <code class="literal">OFFSET 0</code>.
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">
1076 OFFSET <em class="replaceable"><code>start</code></em> { ROW | ROWS }
1077 FETCH { FIRST | NEXT } [ <em class="replaceable"><code>count</code></em> ] { ROW | ROWS } { ONLY | WITH TIES }
1079 In this syntax, the <em class="replaceable"><code>start</code></em>
1080 or <em class="replaceable"><code>count</code></em> value is required by
1081 the standard to be a literal constant, a parameter, or a variable name;
1082 as a <span class="productname">PostgreSQL</span> extension, other expressions
1083 are allowed, but will generally need to be enclosed in parentheses to avoid
1085 If <em class="replaceable"><code>count</code></em> is
1086 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 additional
1088 rows that tie for the last place in the result set according to
1089 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> is
1092 <code class="literal">ROW</code> and <code class="literal">ROWS</code> as well as
1093 <code class="literal">FIRST</code> and <code class="literal">NEXT</code> are noise
1094 words that don't influence the effects of these clauses.
1095 According to the standard, the <code class="literal">OFFSET</code> clause must come
1096 before the <code class="literal">FETCH</code> clause if both are present; but
1097 <span class="productname">PostgreSQL</span> is laxer and allows either order.
1099 When using <code class="literal">LIMIT</code>, it is a good idea to use an
1100 <code class="literal">ORDER BY</code> clause that constrains the result rows into a
1101 unique order. Otherwise you will get an unpredictable subset of
1102 the query's rows — you might be asking for the tenth through
1103 twentieth rows, but tenth through twentieth in what ordering? You
1104 don't know what ordering unless you specify <code class="literal">ORDER BY</code>.
1106 The query planner takes <code class="literal">LIMIT</code> into account when
1107 generating a query plan, so you are very likely to get different
1108 plans (yielding different row orders) depending on what you use
1109 for <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>. Thus, using
1110 different <code class="literal">LIMIT</code>/<code class="literal">OFFSET</code> values to select
1111 different subsets of a query result <span class="emphasis"><em>will give
1112 inconsistent results</em></span> unless you enforce a predictable
1113 result ordering with <code class="literal">ORDER BY</code>. This is not a bug; it
1114 is an inherent consequence of the fact that SQL does not promise
1115 to deliver the results of a query in any particular order unless
1116 <code class="literal">ORDER BY</code> is used to constrain the order.
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 there
1120 is not an <code class="literal">ORDER BY</code> to enforce selection of a deterministic
1121 subset. Again, this is not a bug; determinism of the results is
1122 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.
1129 The locking clause has the general form
1131 </p><pre class="synopsis">
1132 FOR <em class="replaceable"><code>lock_strength</code></em> [ OF <em class="replaceable"><code>from_reference</code></em> [, ...] ] [ NOWAIT | SKIP LOCKED ]
1135 where <em class="replaceable"><code>lock_strength</code></em> can be one of
1137 </p><pre class="synopsis">
1144 <em class="replaceable"><code>from_reference</code></em> must be a
1145 table <em class="replaceable"><code>alias</code></em> or non-hidden
1146 <em class="replaceable"><code>table_name</code></em> referenced
1147 in the <code class="literal">FROM</code> clause. For more information on each
1148 row-level lock mode, refer to <a class="xref" href="explicit-locking.html#LOCKING-ROWS" title="13.3.2. Row-Level Locks">Section 13.3.2</a>.
1150 To prevent the operation from waiting for other transactions to commit,
1151 use either the <code class="literal">NOWAIT</code> or <code class="literal">SKIP LOCKED</code>
1152 option. With <code class="literal">NOWAIT</code>, the statement reports an error, rather
1153 than waiting, if a selected row cannot be locked immediately.
1154 With <code class="literal">SKIP LOCKED</code>, any selected rows that cannot be
1155 immediately locked are skipped. Skipping locked rows provides an
1156 inconsistent view of the data, so this is not suitable for general purpose
1157 work, but can be used to avoid lock contention with multiple consumers
1158 accessing a queue-like table.
1159 Note that <code class="literal">NOWAIT</code> and <code class="literal">SKIP LOCKED</code> apply only
1160 to the row-level lock(s) — the required <code class="literal">ROW SHARE</code>
1161 table-level lock is still taken in the ordinary way (see
1162 <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>). You can use
1163 <a class="link" href="sql-lock.html" title="LOCK"><code class="command">LOCK</code></a>
1164 with the <code class="literal">NOWAIT</code> option first,
1165 if you need to acquire the table-level lock without waiting.
1167 If specific tables are named in a locking clause,
1168 then only rows coming from those tables are locked; any other
1169 tables used in the <code class="command">SELECT</code> are simply read as
1171 clause without a table list affects all tables used in the statement.
1172 If a locking clause is
1173 applied to a view or sub-query, it affects all tables used in
1174 the view or sub-query.
1175 However, these clauses
1176 do not apply to <code class="literal">WITH</code> queries referenced by the primary query.
1177 If you want row locking to occur within a <code class="literal">WITH</code> query, specify
1178 a locking clause within the <code class="literal">WITH</code> query.
1181 clauses can be written if it is necessary to specify different locking
1182 behavior for different tables. If the same table is mentioned (or
1183 implicitly affected) by more than one locking clause,
1184 then it is processed as if it was only specified by the strongest one.
1185 Similarly, a table is processed
1186 as <code class="literal">NOWAIT</code> if that is specified in any of the clauses
1187 affecting it. Otherwise, it is processed
1188 as <code class="literal">SKIP LOCKED</code> if that is specified in any of the
1189 clauses affecting it.
1191 The locking clauses cannot be
1192 used in contexts where returned rows cannot be clearly identified with
1193 individual table rows; for example they cannot be used with aggregation.
1195 When a locking clause
1196 appears at the top level of a <code class="command">SELECT</code> query, the rows that
1197 are locked are exactly those that are returned by the query; in the
1198 case of a join query, the rows locked are those that contribute to
1199 returned join rows. In addition, rows that satisfied the query
1200 conditions as of the query snapshot will be locked, although they
1201 will not be returned if they were updated after the snapshot
1202 and no longer satisfy the query conditions. If a
1203 <code class="literal">LIMIT</code> is used, locking stops
1204 once enough rows have been returned to satisfy the limit (but note that
1205 rows skipped over by <code class="literal">OFFSET</code> will get locked). Similarly,
1207 is used in a cursor's query, only rows actually fetched or stepped past
1208 by the cursor will be locked.
1210 When a locking clause
1211 appears in a sub-<code class="command">SELECT</code>, the rows locked are those
1212 returned to the outer query by the sub-query. This might involve
1213 fewer rows than inspection of the sub-query alone would suggest,
1214 since conditions from the outer query might be used to optimize
1215 execution of the sub-query. For example,
1216 </p><pre class="programlisting">
1217 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
1219 will lock only rows having <code class="literal">col1 = 5</code>, even though that
1220 condition is not textually within the sub-query.
1222 Previous releases failed to preserve a lock which is upgraded by a later
1223 savepoint. For example, this code:
1224 </p><pre class="programlisting">
1226 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
1228 UPDATE mytable SET ... WHERE key = 1;
1231 would fail to preserve the <code class="literal">FOR UPDATE</code> lock after the
1232 <code class="command">ROLLBACK TO</code>. This has been fixed in release 9.3.
1233 </p><div class="caution"><h3 class="title">Caution</h3><p>
1234 It is possible for a <code class="command">SELECT</code> command running at the <code class="literal">READ
1235 COMMITTED</code> transaction isolation level and using <code class="literal">ORDER
1236 BY</code> and a locking clause to return rows out of
1237 order. This is because <code class="literal">ORDER BY</code> is applied first.
1238 The command sorts the result, but might then block trying to obtain a lock
1239 on one or more of the rows. Once the <code class="literal">SELECT</code> unblocks, some
1240 of the ordering column values might have been modified, leading to those
1241 rows appearing to be out of order (though they are in order in terms
1242 of the original column values). This can be worked around at need by
1243 placing the <code class="literal">FOR UPDATE/SHARE</code> clause in a sub-query,
1245 </p><pre class="programlisting">
1246 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
1248 Note that this will result in locking all rows of <code class="structname">mytable</code>,
1249 whereas <code class="literal">FOR UPDATE</code> at the top level would lock only the
1250 actually returned rows. This can make for a significant performance
1251 difference, particularly if the <code class="literal">ORDER BY</code> is combined with
1252 <code class="literal">LIMIT</code> or other restrictions. So this technique is recommended
1253 only if concurrent updates of the ordering columns are expected and a
1254 strictly sorted result is required.
1256 At the <code class="literal">REPEATABLE READ</code> or <code class="literal">SERIALIZABLE</code>
1257 transaction isolation level this would cause a serialization failure (with
1258 an <code class="literal">SQLSTATE</code> of <code class="literal">'40001'</code>), so there is
1259 no possibility of receiving rows out of order under these isolation levels.
1260 </p></div></div><div class="refsect2" id="SQL-TABLE"><h3><code class="literal">TABLE</code> Command</h3><p>
1262 </p><pre class="programlisting">
1263 TABLE <em class="replaceable"><code>name</code></em>
1266 </p><pre class="programlisting">
1267 SELECT * FROM <em class="replaceable"><code>name</code></em>
1269 It can be used as a top-level command or as a space-saving syntax
1270 variant in parts of complex queries. Only the <code class="literal">WITH</code>,
1271 <code class="literal">UNION</code>, <code class="literal">INTERSECT</code>, <code class="literal">EXCEPT</code>,
1272 <code class="literal">ORDER BY</code>, <code class="literal">LIMIT</code>, <code class="literal">OFFSET</code>,
1273 <code class="literal">FETCH</code> and <code class="literal">FOR</code> locking clauses can be used
1274 with <code class="command">TABLE</code>; the <code class="literal">WHERE</code> clause and any form of
1277 </p></div></div><div class="refsect1" id="id-1.9.3.172.9"><h2>Examples</h2><p>
1278 To join the table <code class="literal">films</code> with the table
1279 <code class="literal">distributors</code>:
1281 </p><pre class="programlisting">
1282 SELECT f.title, f.did, d.name, f.date_prod, f.kind
1283 FROM distributors d JOIN films f USING (did);
1285 title | did | name | date_prod | kind
1286 -------------------+-----+--------------+------------+----------
1287 The Third Man | 101 | British Lion | 1949-12-23 | Drama
1288 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
1292 To sum the column <code class="literal">len</code> of all films and group
1293 the results by <code class="literal">kind</code>:
1295 </p><pre class="programlisting">
1296 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
1307 To sum the column <code class="literal">len</code> of all films, group
1308 the results by <code class="literal">kind</code> and show those group totals
1309 that are less than 5 hours:
1311 </p><pre class="programlisting">
1312 SELECT kind, sum(len) AS total
1315 HAVING sum(len) < interval '5 hours';
1323 The following two examples are identical ways of sorting the individual
1324 results according to the contents of the second column
1325 (<code class="literal">name</code>):
1327 </p><pre class="programlisting">
1328 SELECT * FROM distributors ORDER BY name;
1329 SELECT * FROM distributors ORDER BY 2;
1332 -----+------------------
1333 109 | 20th Century Fox
1334 110 | Bavaria Atelier
1337 102 | Jean Luc Godard
1342 105 | United Artists
1348 The next example shows how to obtain the union of the tables
1349 <code class="literal">distributors</code> and
1350 <code class="literal">actors</code>, restricting the results to those that begin
1351 with the letter W in each table. Only distinct rows are wanted, so the
1352 key word <code class="literal">ALL</code> is omitted.
1354 </p><pre class="programlisting">
1355 distributors: actors:
1356 did | name id | name
1357 -----+-------------- ----+----------------
1358 108 | Westward 1 | Woody Allen
1359 111 | Walt Disney 2 | Warren Beatty
1360 112 | Warner Bros. 3 | Walter Matthau
1363 SELECT distributors.name
1365 WHERE distributors.name LIKE 'W%'
1369 WHERE actors.name LIKE 'W%';
1381 This example shows how to use a function in the <code class="literal">FROM</code>
1382 clause, both with and without a column definition list:
1384 </p><pre class="programlisting">
1385 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
1386 SELECT * FROM distributors WHERE did = $1;
1389 SELECT * FROM distributors(111);
1394 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
1395 SELECT * FROM distributors WHERE did = $1;
1398 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
1404 Here is an example of a function with an ordinality column added:
1406 </p><pre class="programlisting">
1407 SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
1419 This example shows how to use a simple <code class="literal">WITH</code> clause:
1421 </p><pre class="programlisting">
1423 SELECT random() as x FROM generate_series(1, 3)
1429 --------------------
1438 Notice that the <code class="literal">WITH</code> query was evaluated only once,
1439 so that we got two sets of the same three random values.
1441 This example uses <code class="literal">WITH RECURSIVE</code> to find all
1442 subordinates (direct or indirect) of the employee Mary, and their
1443 level of indirectness, from a table that shows only direct
1446 </p><pre class="programlisting">
1447 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
1448 SELECT 1, employee_name, manager_name
1450 WHERE manager_name = 'Mary'
1452 SELECT er.distance + 1, e.employee_name, e.manager_name
1453 FROM employee_recursive er, employee e
1454 WHERE er.employee_name = e.manager_name
1456 SELECT distance, employee_name FROM employee_recursive;
1459 Notice the typical form of recursive queries:
1460 an initial condition, followed by <code class="literal">UNION</code>,
1461 followed by the recursive part of the query. Be sure that the
1462 recursive part of the query will eventually return no tuples, or
1463 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>
1466 This example uses <code class="literal">LATERAL</code> to apply a set-returning function
1467 <code class="function">get_product_names()</code> for each row of the
1468 <code class="structname">manufacturers</code> table:
1470 </p><pre class="programlisting">
1471 SELECT m.name AS mname, pname
1472 FROM manufacturers m, LATERAL get_product_names(m.id) pname;
1475 Manufacturers not currently having any products would not appear in the
1476 result, since it is an inner join. If we wished to include the names of
1477 such manufacturers in the result, we could do:
1479 </p><pre class="programlisting">
1480 SELECT m.name AS mname, pname
1481 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
1482 </pre></div><div class="refsect1" id="id-1.9.3.172.10"><h2>Compatibility</h2><p>
1483 Of course, the <code class="command">SELECT</code> statement is compatible
1484 with the SQL standard. But there are some extensions and some
1486 </p><div class="refsect2" id="id-1.9.3.172.10.3"><h3>Omitted <code class="literal">FROM</code> Clauses</h3><p>
1487 <span class="productname">PostgreSQL</span> allows one to omit the
1488 <code class="literal">FROM</code> clause. It has a straightforward use to
1489 compute the results of simple expressions:
1490 </p><pre class="programlisting">
1497 Some other <acronym class="acronym">SQL</acronym> databases cannot do this except
1498 by introducing a dummy one-row table from which to do the
1499 <code class="command">SELECT</code>.
1500 </p></div><div class="refsect2" id="id-1.9.3.172.10.4"><h3>Empty <code class="literal">SELECT</code> Lists</h3><p>
1501 The list of output expressions after <code class="literal">SELECT</code> can be
1502 empty, producing a zero-column result table.
1503 This is not valid syntax according to the SQL standard.
1504 <span class="productname">PostgreSQL</span> allows it to be consistent with
1505 allowing zero-column tables.
1506 However, an empty list is not allowed when <code class="literal">DISTINCT</code> is used.
1507 </p></div><div class="refsect2" id="id-1.9.3.172.10.5"><h3>Omitting the <code class="literal">AS</code> Key Word</h3><p>
1508 In the SQL standard, the optional key word <code class="literal">AS</code> can be
1509 omitted before an output column name whenever the new column name
1510 is a valid column name (that is, not the same as any reserved
1511 keyword). <span class="productname">PostgreSQL</span> is slightly more
1512 restrictive: <code class="literal">AS</code> is required if the new column name
1513 matches any keyword at all, reserved or not. Recommended practice is
1514 to use <code class="literal">AS</code> or double-quote output column names, to prevent
1515 any possible conflict against future keyword additions.
1517 In <code class="literal">FROM</code> items, both the standard and
1518 <span class="productname">PostgreSQL</span> allow <code class="literal">AS</code> to
1519 be omitted before an alias that is an unreserved keyword. But
1520 this is impractical for output column names, because of syntactic
1522 </p></div><div class="refsect2" id="id-1.9.3.172.10.6"><h3>Omitting Sub-<code class="command">SELECT</code> Aliases in <code class="literal">FROM</code></h3><p>
1523 According to the SQL standard, a sub-<code class="command">SELECT</code> in the
1524 <code class="literal">FROM</code> list must have an alias. In
1525 <span class="productname">PostgreSQL</span>, this alias may be omitted.
1526 </p></div><div class="refsect2" id="id-1.9.3.172.10.7"><h3><code class="literal">ONLY</code> and Inheritance</h3><p>
1527 The SQL standard requires parentheses around the table name when
1528 writing <code class="literal">ONLY</code>, for example <code class="literal">SELECT * FROM ONLY
1529 (tab1), ONLY (tab2) WHERE ...</code>. <span class="productname">PostgreSQL</span>
1530 considers these parentheses to be optional.
1532 <span class="productname">PostgreSQL</span> allows a trailing <code class="literal">*</code> to be written to
1533 explicitly specify the non-<code class="literal">ONLY</code> behavior of including
1534 child tables. The standard does not allow this.
1536 (These points apply equally to all SQL commands supporting the
1537 <code class="literal">ONLY</code> option.)
1538 </p></div><div class="refsect2" id="id-1.9.3.172.10.8"><h3><code class="literal">TABLESAMPLE</code> Clause Restrictions</h3><p>
1539 The <code class="literal">TABLESAMPLE</code> clause is currently accepted only on
1540 regular tables and materialized views. According to the SQL standard
1541 it should be possible to apply it to any <code class="literal">FROM</code> item.
1542 </p></div><div class="refsect2" id="id-1.9.3.172.10.9"><h3>Function Calls in <code class="literal">FROM</code></h3><p>
1543 <span class="productname">PostgreSQL</span> allows a function call to be
1544 written directly as a member of the <code class="literal">FROM</code> list. In the SQL
1545 standard it would be necessary to wrap such a function call in a
1546 sub-<code class="command">SELECT</code>; that is, the syntax
1547 <code class="literal">FROM <em class="replaceable"><code>func</code></em>(...) <em class="replaceable"><code>alias</code></em></code>
1548 is approximately equivalent to
1549 <code class="literal">FROM LATERAL (SELECT <em class="replaceable"><code>func</code></em>(...)) <em class="replaceable"><code>alias</code></em></code>.
1550 Note that <code class="literal">LATERAL</code> is considered to be implicit; this is
1551 because the standard requires <code class="literal">LATERAL</code> semantics for an
1552 <code class="literal">UNNEST()</code> item in <code class="literal">FROM</code>.
1553 <span class="productname">PostgreSQL</span> treats <code class="literal">UNNEST()</code> the
1554 same as other set-returning functions.
1555 </p></div><div class="refsect2" id="id-1.9.3.172.10.10"><h3>Namespace Available to <code class="literal">GROUP BY</code> and <code class="literal">ORDER BY</code></h3><p>
1556 In the SQL-92 standard, an <code class="literal">ORDER BY</code> clause can
1557 only use output column names or numbers, while a <code class="literal">GROUP
1558 BY</code> clause can only use expressions based on input column
1559 names. <span class="productname">PostgreSQL</span> extends each of
1560 these clauses to allow the other choice as well (but it uses the
1561 standard's interpretation if there is ambiguity).
1562 <span class="productname">PostgreSQL</span> also allows both clauses to
1563 specify arbitrary expressions. Note that names appearing in an
1564 expression will always be taken as input-column names, not as
1565 output-column names.
1567 SQL:1999 and later use a slightly different definition which is not
1568 entirely upward compatible with SQL-92.
1569 In most cases, however, <span class="productname">PostgreSQL</span>
1570 will interpret an <code class="literal">ORDER BY</code> or <code class="literal">GROUP
1571 BY</code> expression the same way SQL:1999 does.
1572 </p></div><div class="refsect2" id="id-1.9.3.172.10.11"><h3>Functional Dependencies</h3><p>
1573 <span class="productname">PostgreSQL</span> recognizes functional dependency
1574 (allowing columns to be omitted from <code class="literal">GROUP BY</code>) only when
1575 a table's primary key is included in the <code class="literal">GROUP BY</code> list.
1576 The SQL standard specifies additional conditions that should be
1578 </p></div><div class="refsect2" id="id-1.9.3.172.10.12"><h3><code class="literal">LIMIT</code> and <code class="literal">OFFSET</code></h3><p>
1579 The clauses <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>
1580 are <span class="productname">PostgreSQL</span>-specific syntax, also
1581 used by <span class="productname">MySQL</span>. The SQL:2008 standard
1582 has introduced the clauses <code class="literal">OFFSET ... FETCH {FIRST|NEXT}
1583 ...</code> for the same functionality, as shown above
1584 in <a class="xref" href="sql-select.html#SQL-LIMIT" title="LIMIT Clause">LIMIT Clause</a>. This
1585 syntax is also used by <span class="productname">IBM DB2</span>.
1586 (Applications written for <span class="productname">Oracle</span>
1587 frequently use a workaround involving the automatically
1588 generated <code class="literal">rownum</code> column, which is not available in
1589 PostgreSQL, to implement the effects of these clauses.)
1590 </p></div><div class="refsect2" id="id-1.9.3.172.10.13"><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>
1591 Although <code class="literal">FOR UPDATE</code> appears in the SQL standard, the
1592 standard allows it only as an option of <code class="command">DECLARE CURSOR</code>.
1593 <span class="productname">PostgreSQL</span> allows it in any <code class="command">SELECT</code>
1594 query as well as in sub-<code class="command">SELECT</code>s, but this is an extension.
1595 The <code class="literal">FOR NO KEY UPDATE</code>, <code class="literal">FOR SHARE</code> and
1596 <code class="literal">FOR KEY SHARE</code> variants, as well as the <code class="literal">NOWAIT</code>
1597 and <code class="literal">SKIP LOCKED</code> options, do not appear in the
1599 </p></div><div class="refsect2" id="id-1.9.3.172.10.14"><h3>Data-Modifying Statements in <code class="literal">WITH</code></h3><p>
1600 <span class="productname">PostgreSQL</span> allows <code class="command">INSERT</code>,
1601 <code class="command">UPDATE</code>, <code class="command">DELETE</code>, and
1602 <code class="command">MERGE</code> to be used as <code class="literal">WITH</code>
1603 queries. This is not found in the SQL standard.
1604 </p></div><div class="refsect2" id="id-1.9.3.172.10.15"><h3>Nonstandard Clauses</h3><p>
1605 <code class="literal">DISTINCT ON ( ... )</code> is an extension of the
1608 <code class="literal">ROWS FROM( ... )</code> is an extension of the SQL standard.
1610 The <code class="literal">MATERIALIZED</code> and <code class="literal">NOT
1611 MATERIALIZED</code> options of <code class="literal">WITH</code> are extensions
1612 of the SQL standard.
1613 </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" title="SECURITY LABEL">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-selectinto.html" title="SELECT INTO">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" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SELECT INTO</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob