Debian Codegraph @

doc/src/sgml/ref/select.sgml

raw
1 <!--
2 doc/src/sgml/ref/select.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-select">
7 <indexterm zone="sql-select">
8 <primary>SELECT</primary>
9 </indexterm>
10
11 <indexterm zone="sql-select">
12 <primary>TABLE command</primary>
13 </indexterm>
14
15 <indexterm zone="sql-select">
16 <primary>WITH</primary>
17 <secondary>in SELECT</secondary>
18 </indexterm>
19
20 <refmeta>
21 <refentrytitle>SELECT</refentrytitle>
22 <manvolnum>7</manvolnum>
23 <refmiscinfo>SQL - Language Statements</refmiscinfo>
24 </refmeta>
25
26 <refnamediv>
27 <refname>SELECT</refname>
28 <refname>TABLE</refname>
29 <refname>WITH</refname>
30 <refpurpose>retrieve rows from a table or view</refpurpose>
31 </refnamediv>
32
33 <refsynopsisdiv>
34 <synopsis>
35 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
36 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
37 [ { * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]
38 [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
39 [ WHERE <replaceable class="parameter">condition</replaceable> ]
40 [ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
41 [ HAVING <replaceable class="parameter">condition</replaceable> ]
42 [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
43 [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
44 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
45 [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
46 [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
47 [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ]
48 [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
49
50 <phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
51
52 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
53 [ TABLESAMPLE <replaceable class="parameter">sampling_method</replaceable> ( <replaceable class="parameter">argument</replaceable> [, ...] ) [ REPEATABLE ( <replaceable class="parameter">seed</replaceable> ) ] ]
54 [ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
55 <replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
56 [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
57 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
58 [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
59 [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
60 [ LATERAL ] ROWS FROM( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
61 [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
62 <replaceable class="parameter">from_item</replaceable> <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> { ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ] }
63 <replaceable class="parameter">from_item</replaceable> NATURAL <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable>
64 <replaceable class="parameter">from_item</replaceable> CROSS JOIN <replaceable class="parameter">from_item</replaceable>
65
66 <phrase>and <replaceable class="parameter">grouping_element</replaceable> can be one of:</phrase>
67
68 ( )
69 <replaceable class="parameter">expression</replaceable>
70 ( <replaceable class="parameter">expression</replaceable> [, ...] )
71 ROLLUP ( { <replaceable class="parameter">expression</replaceable> | ( <replaceable class="parameter">expression</replaceable> [, ...] ) } [, ...] )
72 CUBE ( { <replaceable class="parameter">expression</replaceable> | ( <replaceable class="parameter">expression</replaceable> [, ...] ) } [, ...] )
73 GROUPING SETS ( <replaceable class="parameter">grouping_element</replaceable> [, ...] )
74
75 <phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
76
77 <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> | <replaceable class="parameter">merge</replaceable> )
78 [ SEARCH { BREADTH | DEPTH } FIRST BY <replaceable>column_name</replaceable> [, ...] SET <replaceable>search_seq_col_name</replaceable> ]
79 [ CYCLE <replaceable>column_name</replaceable> [, ...] SET <replaceable>cycle_mark_col_name</replaceable> [ TO <replaceable>cycle_mark_value</replaceable> DEFAULT <replaceable>cycle_mark_default</replaceable> ] USING <replaceable>cycle_path_col_name</replaceable> ]
80
81 TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
82 </synopsis>
83
84 </refsynopsisdiv>
85
86 <refsect1>
87 <title>Description</title>
88
89 <para>
90 <command>SELECT</command> retrieves rows from zero or more tables.
91 The general processing of <command>SELECT</command> is as follows:
92
93 <orderedlist>
94 <listitem>
95 <para>
96 All queries in the <literal>WITH</literal> list are computed.
97 These effectively serve as temporary tables that can be referenced
98 in the <literal>FROM</literal> list. A <literal>WITH</literal> query
99 that is referenced more than once in <literal>FROM</literal> is
100 computed only once,
101 unless specified otherwise with <literal>NOT MATERIALIZED</literal>.
102 (See <xref linkend="sql-with"/> below.)
103 </para>
104 </listitem>
105
106 <listitem>
107 <para>
108 All elements in the <literal>FROM</literal> list are computed.
109 (Each element in the <literal>FROM</literal> list is a real or
110 virtual table.) If more than one element is specified in the
111 <literal>FROM</literal> list, they are cross-joined together.
112 (See <xref linkend="sql-from"/> below.)
113 </para>
114 </listitem>
115
116 <listitem>
117 <para>
118 If the <literal>WHERE</literal> clause is specified, all rows
119 that do not satisfy the condition are eliminated from the
120 output. (See <xref linkend="sql-where"/> below.)
121 </para>
122 </listitem>
123
124 <listitem>
125 <para>
126 If the <literal>GROUP BY</literal> clause is specified,
127 or if there are aggregate function calls, the
128 output is combined into groups of rows that match on one or more
129 values, and the results of aggregate functions are computed.
130 If the <literal>HAVING</literal> clause is present, it
131 eliminates groups that do not satisfy the given condition. (See
132 <xref linkend="sql-groupby"/> and
133 <xref linkend="sql-having"/> below.)
134 Although query output columns are nominally computed in the next
135 step, they can also be referenced (by name or ordinal number)
136 in the <literal>GROUP BY</literal> clause.
137 </para>
138 </listitem>
139
140 <listitem>
141 <para>
142 The actual output rows are computed using the
143 <command>SELECT</command> output expressions for each selected
144 row or row group. (See <xref linkend="sql-select-list"/> below.)
145 </para>
146 </listitem>
147
148 <listitem>
149 <para><literal>SELECT DISTINCT</literal> eliminates duplicate rows from the
150 result. <literal>SELECT DISTINCT ON</literal> eliminates rows that
151 match on all the specified expressions. <literal>SELECT ALL</literal>
152 (the default) will return all candidate rows, including
153 duplicates. (See <xref linkend="sql-distinct"/> below.)
154 </para>
155 </listitem>
156
157 <listitem>
158 <para>
159 Using the operators <literal>UNION</literal>,
160 <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
161 output of more than one <command>SELECT</command> statement can
162 be combined to form a single result set. The
163 <literal>UNION</literal> operator returns all rows that are in
164 one or both of the result sets. The
165 <literal>INTERSECT</literal> operator returns all rows that are
166 strictly in both result sets. The <literal>EXCEPT</literal>
167 operator returns the rows that are in the first result set but
168 not in the second. In all three cases, duplicate rows are
169 eliminated unless <literal>ALL</literal> is specified. The noise
170 word <literal>DISTINCT</literal> can be added to explicitly specify
171 eliminating duplicate rows. Notice that <literal>DISTINCT</literal> is
172 the default behavior here, even though <literal>ALL</literal> is
173 the default for <command>SELECT</command> itself. (See
174 <xref linkend="sql-union"/>, <xref linkend="sql-intersect"/>, and
175 <xref linkend="sql-except"/> below.)
176 </para>
177 </listitem>
178
179 <listitem>
180 <para>
181 If the <literal>ORDER BY</literal> clause is specified, the
182 returned rows are sorted in the specified order. If
183 <literal>ORDER BY</literal> is not given, the rows are returned
184 in whatever order the system finds fastest to produce. (See
185 <xref linkend="sql-orderby"/> below.)
186 </para>
187 </listitem>
188
189 <listitem>
190 <para>
191 If the <literal>LIMIT</literal> (or <literal>FETCH FIRST</literal>) or <literal>OFFSET</literal>
192 clause is specified, the <command>SELECT</command> statement
193 only returns a subset of the result rows. (See <xref
194 linkend="sql-limit"/> below.)
195 </para>
196 </listitem>
197
198 <listitem>
199 <para>
200 If <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR SHARE</literal>
201 or <literal>FOR KEY SHARE</literal>
202 is specified, the
203 <command>SELECT</command> statement locks the selected rows
204 against concurrent updates. (See <xref linkend="sql-for-update-share"/>
205 below.)
206 </para>
207 </listitem>
208 </orderedlist>
209 </para>
210
211 <para>
212 You must have <literal>SELECT</literal> privilege on each column used
213 in a <command>SELECT</command> command. The use of <literal>FOR NO KEY UPDATE</literal>,
214 <literal>FOR UPDATE</literal>,
215 <literal>FOR SHARE</literal> or <literal>FOR KEY SHARE</literal> requires
216 <literal>UPDATE</literal> privilege as well (for at least one column
217 of each table so selected).
218 </para>
219 </refsect1>
220
221 <refsect1>
222 <title>Parameters</title>
223
224 <refsect2 id="sql-with" xreflabel="WITH Clause">
225 <title><literal>WITH</literal> Clause</title>
226
227 <para>
228 The <literal>WITH</literal> clause allows you to specify one or more
229 subqueries that can be referenced by name in the primary query.
230 The subqueries effectively act as temporary tables or views
231 for the duration of the primary query.
232 Each subquery can be a <command>SELECT</command>, <command>TABLE</command>, <command>VALUES</command>,
233 <command>INSERT</command>, <command>UPDATE</command>,
234 <command>DELETE</command>, or <command>MERGE</command> statement.
235 When writing a data-modifying statement (<command>INSERT</command>,
236 <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>) in
237 <literal>WITH</literal>, it is usual to include a <literal>RETURNING</literal> clause.
238 It is the output of <literal>RETURNING</literal>, <emphasis>not</emphasis> the underlying
239 table that the statement modifies, that forms the temporary table that is
240 read by the primary query. If <literal>RETURNING</literal> is omitted, the
241 statement is still executed, but it produces no output so it cannot be
242 referenced as a table by the primary query.
243 </para>
244
245 <para>
246 A name (without schema qualification) must be specified for each
247 <literal>WITH</literal> query. Optionally, a list of column names
248 can be specified; if this is omitted,
249 the column names are inferred from the subquery.
250 </para>
251
252 <para>
253 If <literal>RECURSIVE</literal> is specified, it allows a
254 <command>SELECT</command> subquery to reference itself by name. Such a
255 subquery must have the form
256 <synopsis>
257 <replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
258 </synopsis>
259 where the recursive self-reference must appear on the right-hand
260 side of the <literal>UNION</literal>. Only one recursive self-reference
261 is permitted per query. Recursive data-modifying statements are not
262 supported, but you can use the results of a recursive
263 <command>SELECT</command> query in
264 a data-modifying statement. See <xref linkend="queries-with"/> for
265 an example.
266 </para>
267
268 <para>
269 Another effect of <literal>RECURSIVE</literal> is that
270 <literal>WITH</literal> queries need not be ordered: a query
271 can reference another one that is later in the list. (However,
272 circular references, or mutual recursion, are not implemented.)
273 Without <literal>RECURSIVE</literal>, <literal>WITH</literal> queries
274 can only reference sibling <literal>WITH</literal> queries
275 that are earlier in the <literal>WITH</literal> list.
276 </para>
277
278 <para>
279 When there are multiple queries in the <literal>WITH</literal>
280 clause, <literal>RECURSIVE</literal> should be written only once,
281 immediately after <literal>WITH</literal>. It applies to all queries
282 in the <literal>WITH</literal> clause, though it has no effect on
283 queries that do not use recursion or forward references.
284 </para>
285
286 <para>
287 The optional <literal>SEARCH</literal> clause computes a <firstterm>search
288 sequence column</firstterm> that can be used for ordering the results of a
289 recursive query in either breadth-first or depth-first order. The
290 supplied column name list specifies the row key that is to be used for
291 keeping track of visited rows. A column named
292 <replaceable>search_seq_col_name</replaceable> will be added to the result
293 column list of the <literal>WITH</literal> query. This column can be
294 ordered by in the outer query to achieve the respective ordering. See
295 <xref linkend="queries-with-search"/> for examples.
296 </para>
297
298 <para>
299 The optional <literal>CYCLE</literal> clause is used to detect cycles in
300 recursive queries. The supplied column name list specifies the row key
301 that is to be used for keeping track of visited rows. A column named
302 <replaceable>cycle_mark_col_name</replaceable> will be added to the result
303 column list of the <literal>WITH</literal> query. This column will be set
304 to <replaceable>cycle_mark_value</replaceable> when a cycle has been
305 detected, else to <replaceable>cycle_mark_default</replaceable>.
306 Furthermore, processing of the recursive union will stop when a cycle has
307 been detected. <replaceable>cycle_mark_value</replaceable> and
308 <replaceable>cycle_mark_default</replaceable> must be constants and they
309 must be coercible to a common data type, and the data type must have an
310 inequality operator. (The SQL standard requires that they be Boolean
311 constants or character strings, but PostgreSQL does not require that.) By
312 default, <literal>TRUE</literal> and <literal>FALSE</literal> (of type
313 <type>boolean</type>) are used. Furthermore, a column
314 named <replaceable>cycle_path_col_name</replaceable> will be added to the
315 result column list of the <literal>WITH</literal> query. This column is
316 used internally for tracking visited rows. See <xref
317 linkend="queries-with-cycle"/> for examples.
318 </para>
319
320 <para>
321 Both the <literal>SEARCH</literal> and the <literal>CYCLE</literal> clause
322 are only valid for recursive <literal>WITH</literal> queries. The
323 <replaceable>with_query</replaceable> must be a <literal>UNION</literal>
324 (or <literal>UNION ALL</literal>) of two <literal>SELECT</literal> (or
325 equivalent) commands (no nested <literal>UNION</literal>s). If both
326 clauses are used, the column added by the <literal>SEARCH</literal> clause
327 appears before the columns added by the <literal>CYCLE</literal> clause.
328 </para>
329
330 <para>
331 The primary query and the <literal>WITH</literal> queries are all
332 (notionally) executed at the same time. This implies that the effects of
333 a data-modifying statement in <literal>WITH</literal> cannot be seen from
334 other parts of the query, other than by reading its <literal>RETURNING</literal>
335 output. If two such data-modifying statements attempt to modify the same
336 row, the results are unspecified.
337 </para>
338
339 <para>
340 A key property of <literal>WITH</literal> queries is that they
341 are normally evaluated only once per execution of the primary query,
342 even if the primary query refers to them more than once.
343 In particular, data-modifying statements are guaranteed to be
344 executed once and only once, regardless of whether the primary query
345 reads all or any of their output.
346 </para>
347
348 <para>
349 However, a <literal>WITH</literal> query can be marked
350 <literal>NOT MATERIALIZED</literal> to remove this guarantee. In that
351 case, the <literal>WITH</literal> query can be folded into the primary
352 query much as though it were a simple sub-<literal>SELECT</literal> in
353 the primary query's <literal>FROM</literal> clause. This results in
354 duplicate computations if the primary query refers to
355 that <literal>WITH</literal> query more than once; but if each such use
356 requires only a few rows of the <literal>WITH</literal> query's total
357 output, <literal>NOT MATERIALIZED</literal> can provide a net savings by
358 allowing the queries to be optimized jointly.
359 <literal>NOT MATERIALIZED</literal> is ignored if it is attached to
360 a <literal>WITH</literal> query that is recursive or is not
361 side-effect-free (i.e., is not a plain <literal>SELECT</literal>
362 containing no volatile functions).
363 </para>
364
365 <para>
366 By default, a side-effect-free <literal>WITH</literal> query is folded
367 into the primary query if it is used exactly once in the primary
368 query's <literal>FROM</literal> clause. This allows joint optimization
369 of the two query levels in situations where that should be semantically
370 invisible. However, such folding can be prevented by marking the
371 <literal>WITH</literal> query as <literal>MATERIALIZED</literal>.
372 That might be useful, for example, if the <literal>WITH</literal> query
373 is being used as an optimization fence to prevent the planner from
374 choosing a bad plan.
375 <productname>PostgreSQL</productname> versions before v12 never did
376 such folding, so queries written for older versions might rely on
377 <literal>WITH</literal> to act as an optimization fence.
378 </para>
379
380 <para>
381 See <xref linkend="queries-with"/> for additional information.
382 </para>
383 </refsect2>
384
385 <refsect2 id="sql-from" xreflabel="FROM Clause">
386 <title><literal>FROM</literal> Clause</title>
387
388 <para>
389 The <literal>FROM</literal> clause specifies one or more source
390 tables for the <command>SELECT</command>. If multiple sources are
391 specified, the result is the Cartesian product (cross join) of all
392 the sources. But usually qualification conditions are added (via
393 <literal>WHERE</literal>) to restrict the returned rows to a small subset of the
394 Cartesian product.
395 </para>
396
397 <para>
398 The <literal>FROM</literal> clause can contain the following
399 elements:
400
401 <variablelist>
402 <varlistentry>
403 <term><replaceable class="parameter">table_name</replaceable></term>
404 <listitem>
405 <para>
406 The name (optionally schema-qualified) of an existing table or view.
407 If <literal>ONLY</literal> is specified before the table name, only that
408 table is scanned. If <literal>ONLY</literal> is not specified, the table
409 and all its descendant tables (if any) are scanned. Optionally,
410 <literal>*</literal> can be specified after the table name to explicitly
411 indicate that descendant tables are included.
412 </para>
413 </listitem>
414 </varlistentry>
415
416 <varlistentry>
417 <term><replaceable class="parameter">alias</replaceable></term>
418 <listitem>
419 <para>
420 A substitute name for the <literal>FROM</literal> item containing the
421 alias. An alias is used for brevity or to eliminate ambiguity
422 for self-joins (where the same table is scanned multiple
423 times). When an alias is provided, it completely hides the
424 actual name of the table or function; for example given
425 <literal>FROM foo AS f</literal>, the remainder of the
426 <command>SELECT</command> must refer to this <literal>FROM</literal>
427 item as <literal>f</literal> not <literal>foo</literal>. If an alias is
428 written, a column alias list can also be written to provide
429 substitute names for one or more columns of the table.
430 </para>
431 </listitem>
432 </varlistentry>
433
434 <varlistentry>
435 <term><literal>TABLESAMPLE <replaceable class="parameter">sampling_method</replaceable> ( <replaceable class="parameter">argument</replaceable> [, ...] ) [ REPEATABLE ( <replaceable class="parameter">seed</replaceable> ) ]</literal></term>
436 <listitem>
437 <para>
438 A <literal>TABLESAMPLE</literal> clause after
439 a <replaceable class="parameter">table_name</replaceable> indicates that the
440 specified <replaceable class="parameter">sampling_method</replaceable>
441 should be used to retrieve a subset of the rows in that table.
442 This sampling precedes the application of any other filters such
443 as <literal>WHERE</literal> clauses.
444 The standard <productname>PostgreSQL</productname> distribution
445 includes two sampling methods, <literal>BERNOULLI</literal>
446 and <literal>SYSTEM</literal>, and other sampling methods can be
447 installed in the database via extensions.
448 </para>
449
450 <para>
451 The <literal>BERNOULLI</literal> and <literal>SYSTEM</literal> sampling methods
452 each accept a single <replaceable class="parameter">argument</replaceable>
453 which is the fraction of the table to sample, expressed as a
454 percentage between 0 and 100. This argument can be
455 any <type>real</type>-valued expression. (Other sampling methods might
456 accept more or different arguments.) These two methods each return
457 a randomly-chosen sample of the table that will contain
458 approximately the specified percentage of the table's rows.
459 The <literal>BERNOULLI</literal> method scans the whole table and
460 selects or ignores individual rows independently with the specified
461 probability.
462 The <literal>SYSTEM</literal> method does block-level sampling with
463 each block having the specified chance of being selected; all rows
464 in each selected block are returned.
465 The <literal>SYSTEM</literal> method is significantly faster than
466 the <literal>BERNOULLI</literal> method when small sampling
467 percentages are specified, but it may return a less-random sample of
468 the table as a result of clustering effects.
469 </para>
470
471 <para>
472 The optional <literal>REPEATABLE</literal> clause specifies
473 a <replaceable class="parameter">seed</replaceable> number or expression to use
474 for generating random numbers within the sampling method. The seed
475 value can be any non-null floating-point value. Two queries that
476 specify the same seed and <replaceable class="parameter">argument</replaceable>
477 values will select the same sample of the table, if the table has
478 not been changed meanwhile. But different seed values will usually
479 produce different samples.
480 If <literal>REPEATABLE</literal> is not given then a new random
481 sample is selected for each query, based upon a system-generated seed.
482 Note that some add-on sampling methods do not
483 accept <literal>REPEATABLE</literal>, and will always produce new
484 samples on each use.
485 </para>
486 </listitem>
487 </varlistentry>
488
489 <varlistentry>
490 <term><replaceable class="parameter">select</replaceable></term>
491 <listitem>
492 <para>
493 A sub-<command>SELECT</command> can appear in the
494 <literal>FROM</literal> clause. This acts as though its
495 output were created as a temporary table for the duration of
496 this single <command>SELECT</command> command. Note that the
497 sub-<command>SELECT</command> must be surrounded by
498 parentheses, and an alias can be provided in the same way as for a
499 table. A
500 <link linkend="sql-values"><command>VALUES</command></link> command
501 can also be used here.
502 </para>
503 </listitem>
504 </varlistentry>
505
506 <varlistentry>
507 <term><replaceable class="parameter">with_query_name</replaceable></term>
508 <listitem>
509 <para>
510 A <literal>WITH</literal> query is referenced by writing its name,
511 just as though the query's name were a table name. (In fact,
512 the <literal>WITH</literal> query hides any real table of the same name
513 for the purposes of the primary query. If necessary, you can
514 refer to a real table of the same name by schema-qualifying
515 the table's name.)
516 An alias can be provided in the same way as for a table.
517 </para>
518 </listitem>
519 </varlistentry>
520
521 <varlistentry>
522 <term><replaceable class="parameter">function_name</replaceable></term>
523 <listitem>
524 <para>
525 Function calls can appear in the <literal>FROM</literal>
526 clause. (This is especially useful for functions that return
527 result sets, but any function can be used.) This acts as
528 though the function's output were created as a temporary table for the
529 duration of this single <command>SELECT</command> command.
530 If the function's result type is composite (including the case of a
531 function with multiple <literal>OUT</literal> parameters), each
532 attribute becomes a separate column in the implicit table.
533 </para>
534
535 <para>
536 When the optional <command>WITH ORDINALITY</command> clause is added
537 to the function call, an additional column of type <type>bigint</type>
538 will be appended to the function's result column(s). This column
539 numbers the rows of the function's result set, starting from 1.
540 By default, this column is named <literal>ordinality</literal>.
541 </para>
542
543 <para>
544 An alias can be provided in the same way as for a table.
545 If an alias is written, a column
546 alias list can also be written to provide substitute names for
547 one or more attributes of the function's composite return
548 type, including the ordinality column if present.
549 </para>
550
551 <para>
552 Multiple function calls can be combined into a
553 single <literal>FROM</literal>-clause item by surrounding them
554 with <literal>ROWS FROM( ... )</literal>. The output of such an item is the
555 concatenation of the first row from each function, then the second
556 row from each function, etc. If some of the functions produce fewer
557 rows than others, null values are substituted for the missing data, so
558 that the total number of rows returned is always the same as for the
559 function that produced the most rows.
560 </para>
561
562 <para>
563 If the function has been defined as returning the
564 <type>record</type> data type, then an alias or the key word
565 <literal>AS</literal> must be present, followed by a column
566 definition list in the form <literal>( <replaceable
567 class="parameter">column_name</replaceable> <replaceable
568 class="parameter">data_type</replaceable> <optional>, ...
569 </optional>)</literal>. The column definition list must match the
570 actual number and types of columns returned by the function.
571 </para>
572
573 <para>
574 When using the <literal>ROWS FROM( ... )</literal> syntax, if one of the
575 functions requires a column definition list, it's preferred to put
576 the column definition list after the function call inside
577 <literal>ROWS FROM( ... )</literal>. A column definition list can be placed
578 after the <literal>ROWS FROM( ... )</literal> construct only if there's just
579 a single function and no <literal>WITH ORDINALITY</literal> clause.
580 </para>
581
582 <para>
583 To use <literal>ORDINALITY</literal> together with a column definition
584 list, you must use the <literal>ROWS FROM( ... )</literal> syntax and put the
585 column definition list inside <literal>ROWS FROM( ... )</literal>.
586 </para>
587 </listitem>
588 </varlistentry>
589
590 <varlistentry>
591 <term><replaceable class="parameter">join_type</replaceable></term>
592 <listitem>
593 <para>
594 One of
595 <itemizedlist>
596 <listitem>
597 <para><literal>[ INNER ] JOIN</literal></para>
598 </listitem>
599 <listitem>
600 <para><literal>LEFT [ OUTER ] JOIN</literal></para>
601 </listitem>
602 <listitem>
603 <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
604 </listitem>
605 <listitem>
606 <para><literal>FULL [ OUTER ] JOIN</literal></para>
607 </listitem>
608 </itemizedlist>
609
610 For the <literal>INNER</literal> and <literal>OUTER</literal> join types, a
611 join condition must be specified, namely exactly one of
612 <literal>ON <replaceable
613 class="parameter">join_condition</replaceable></literal>,
614 <literal>USING (<replaceable
615 class="parameter">join_column</replaceable> [, ...])</literal>,
616 or <literal>NATURAL</literal>. See below for the meaning.
617 </para>
618
619 <para>
620 A <literal>JOIN</literal> clause combines two <literal>FROM</literal>
621 items, which for convenience we will refer to as <quote>tables</quote>,
622 though in reality they can be any type of <literal>FROM</literal> item.
623 Use parentheses if necessary to determine the order of nesting.
624 In the absence of parentheses, <literal>JOIN</literal>s nest
625 left-to-right. In any case <literal>JOIN</literal> binds more
626 tightly than the commas separating <literal>FROM</literal>-list items.
627 All the <literal>JOIN</literal> options are just a notational
628 convenience, since they do nothing you couldn't do with plain
629 <literal>FROM</literal> and <literal>WHERE</literal>.
630 </para>
631
632 <para><literal>LEFT OUTER JOIN</literal> returns all rows in the qualified
633 Cartesian product (i.e., all combined rows that pass its join
634 condition), plus one copy of each row in the left-hand table
635 for which there was no right-hand row that passed the join
636 condition. This left-hand row is extended to the full width
637 of the joined table by inserting null values for the
638 right-hand columns. Note that only the <literal>JOIN</literal>
639 clause's own condition is considered while deciding which rows
640 have matches. Outer conditions are applied afterwards.
641 </para>
642
643 <para>
644 Conversely, <literal>RIGHT OUTER JOIN</literal> returns all the
645 joined rows, plus one row for each unmatched right-hand row
646 (extended with nulls on the left). This is just a notational
647 convenience, since you could convert it to a <literal>LEFT
648 OUTER JOIN</literal> by switching the left and right tables.
649 </para>
650
651 <para><literal>FULL OUTER JOIN</literal> returns all the joined rows, plus
652 one row for each unmatched left-hand row (extended with nulls
653 on the right), plus one row for each unmatched right-hand row
654 (extended with nulls on the left).
655 </para>
656 </listitem>
657 </varlistentry>
658
659 <varlistentry>
660 <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>
661 <listitem>
662 <para><replaceable class="parameter">join_condition</replaceable> is
663 an expression resulting in a value of type
664 <type>boolean</type> (similar to a <literal>WHERE</literal>
665 clause) that specifies which rows in a join are considered to
666 match.
667 </para>
668 </listitem>
669 </varlistentry>
670
671 <varlistentry>
672 <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable class="parameter">join_using_alias</replaceable> ]</literal></term>
673 <listitem>
674 <para>
675 A clause of the form <literal>USING ( a, b, ... )</literal> is
676 shorthand for <literal>ON left_table.a = right_table.a AND
677 left_table.b = right_table.b ...</literal>. Also,
678 <literal>USING</literal> implies that only one of each pair of
679 equivalent columns will be included in the join output, not
680 both.
681 </para>
682
683 <para>
684 If a <replaceable class="parameter">join_using_alias</replaceable>
685 name is specified, it provides a table alias for the join columns.
686 Only the join columns listed in the <literal>USING</literal> clause
687 are addressable by this name. Unlike a regular <replaceable
688 class="parameter">alias</replaceable>, this does not hide the names of
689 the joined tables from the rest of the query. Also unlike a regular
690 <replaceable class="parameter">alias</replaceable>, you cannot write a
691 column alias list &mdash; the output names of the join columns are the
692 same as they appear in the <literal>USING</literal> list.
693 </para>
694 </listitem>
695 </varlistentry>
696
697 <varlistentry>
698 <term><literal>NATURAL</literal></term>
699 <listitem>
700 <para>
701 <literal>NATURAL</literal> is shorthand for a
702 <literal>USING</literal> list that mentions all columns in the two
703 tables that have matching names. If there are no common
704 column names, <literal>NATURAL</literal> is equivalent
705 to <literal>ON TRUE</literal>.
706 </para>
707 </listitem>
708 </varlistentry>
709
710 <varlistentry>
711 <term><literal>CROSS JOIN</literal></term>
712 <listitem>
713 <para>
714 <literal>CROSS JOIN</literal> is equivalent to <literal>INNER JOIN ON
715 (TRUE)</literal>, that is, no rows are removed by qualification.
716 They produce a simple Cartesian product, the same result as you get from
717 listing the two tables at the top level of <literal>FROM</literal>,
718 but restricted by the join condition (if any).
719 </para>
720 </listitem>
721 </varlistentry>
722
723 <varlistentry>
724 <term><literal>LATERAL</literal></term>
725 <listitem>
726 <para>
727 The <literal>LATERAL</literal> key word can precede a
728 sub-<command>SELECT</command> <literal>FROM</literal> item. This allows the
729 sub-<command>SELECT</command> to refer to columns of <literal>FROM</literal>
730 items that appear before it in the <literal>FROM</literal> list. (Without
731 <literal>LATERAL</literal>, each sub-<command>SELECT</command> is
732 evaluated independently and so cannot cross-reference any other
733 <literal>FROM</literal> item.)
734 </para>
735
736 <para><literal>LATERAL</literal> can also precede a function-call
737 <literal>FROM</literal> item, but in this case it is a noise word, because
738 the function expression can refer to earlier <literal>FROM</literal> items
739 in any case.
740 </para>
741
742 <para>
743 A <literal>LATERAL</literal> item can appear at top level in the
744 <literal>FROM</literal> list, or within a <literal>JOIN</literal> tree. In the
745 latter case it can also refer to any items that are on the left-hand
746 side of a <literal>JOIN</literal> that it is on the right-hand side of.
747 </para>
748
749 <para>
750 When a <literal>FROM</literal> item contains <literal>LATERAL</literal>
751 cross-references, evaluation proceeds as follows: for each row of the
752 <literal>FROM</literal> item providing the cross-referenced column(s), or
753 set of rows of multiple <literal>FROM</literal> items providing the
754 columns, the <literal>LATERAL</literal> item is evaluated using that
755 row or row set's values of the columns. The resulting row(s) are
756 joined as usual with the rows they were computed from. This is
757 repeated for each row or set of rows from the column source table(s).
758 </para>
759
760 <para>
761 The column source table(s) must be <literal>INNER</literal> or
762 <literal>LEFT</literal> joined to the <literal>LATERAL</literal> item, else
763 there would not be a well-defined set of rows from which to compute
764 each set of rows for the <literal>LATERAL</literal> item. Thus,
765 although a construct such as <literal><replaceable>X</replaceable> RIGHT JOIN
766 LATERAL <replaceable>Y</replaceable></literal> is syntactically valid, it is
767 not actually allowed for <replaceable>Y</replaceable> to reference
768 <replaceable>X</replaceable>.
769 </para>
770 </listitem>
771 </varlistentry>
772 </variablelist>
773 </para>
774 </refsect2>
775
776 <refsect2 id="sql-where" xreflabel="WHERE Clause">
777 <title><literal>WHERE</literal> Clause</title>
778
779 <para>
780 The optional <literal>WHERE</literal> clause has the general form
781 <synopsis>
782 WHERE <replaceable class="parameter">condition</replaceable>
783 </synopsis>
784 where <replaceable class="parameter">condition</replaceable> is
785 any expression that evaluates to a result of type
786 <type>boolean</type>. Any row that does not satisfy this
787 condition will be eliminated from the output. A row satisfies the
788 condition if it returns true when the actual row values are
789 substituted for any variable references.
790 </para>
791 </refsect2>
792
793 <refsect2 id="sql-groupby" xreflabel="GROUP BY Clause">
794 <title><literal>GROUP BY</literal> Clause</title>
795
796 <para>
797 The optional <literal>GROUP BY</literal> clause has the general form
798 <synopsis>
799 GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...]
800 </synopsis>
801 </para>
802
803 <para>
804 <literal>GROUP BY</literal> will condense into a single row all
805 selected rows that share the same values for the grouped
806 expressions. An <replaceable
807 class="parameter">expression</replaceable> used inside a
808 <replaceable class="parameter">grouping_element</replaceable>
809 can be an input column name, or the name or ordinal number of an
810 output column (<command>SELECT</command> list item), or an arbitrary
811 expression formed from input-column values. In case of ambiguity,
812 a <literal>GROUP BY</literal> name will be interpreted as an
813 input-column name rather than an output column name.
814 </para>
815
816 <para>
817 If any of <literal>GROUPING SETS</literal>, <literal>ROLLUP</literal> or
818 <literal>CUBE</literal> are present as grouping elements, then the
819 <literal>GROUP BY</literal> clause as a whole defines some number of
820 independent <replaceable>grouping sets</replaceable>. The effect of this is
821 equivalent to constructing a <literal>UNION ALL</literal> between
822 subqueries with the individual grouping sets as their
823 <literal>GROUP BY</literal> clauses. The optional <literal>DISTINCT</literal>
824 clause removes duplicate sets before processing; it does <emphasis>not</emphasis>
825 transform the <literal>UNION ALL</literal> into a <literal>UNION DISTINCT</literal>.
826 For further details on the handling
827 of grouping sets see <xref linkend="queries-grouping-sets"/>.
828 </para>
829
830 <para>
831 Aggregate functions, if any are used, are computed across all rows
832 making up each group, producing a separate value for each group.
833 (If there are aggregate functions but no <literal>GROUP BY</literal>
834 clause, the query is treated as having a single group comprising all
835 the selected rows.)
836 The set of rows fed to each aggregate function can be further filtered by
837 attaching a <literal>FILTER</literal> clause to the aggregate function
838 call; see <xref linkend="syntax-aggregates"/> for more information. When
839 a <literal>FILTER</literal> clause is present, only those rows matching it
840 are included in the input to that aggregate function.
841 </para>
842
843 <para>
844 When <literal>GROUP BY</literal> is present,
845 or any aggregate functions are present, it is not valid for
846 the <command>SELECT</command> list expressions to refer to
847 ungrouped columns except within aggregate functions or when the
848 ungrouped column is functionally dependent on the grouped columns,
849 since there would otherwise be more than one possible value to
850 return for an ungrouped column. A functional dependency exists if
851 the grouped columns (or a subset thereof) are the primary key of
852 the table containing the ungrouped column.
853 </para>
854
855 <para>
856 Keep in mind that all aggregate functions are evaluated before
857 evaluating any <quote>scalar</quote> expressions in the <literal>HAVING</literal>
858 clause or <literal>SELECT</literal> list. This means that, for example,
859 a <literal>CASE</literal> expression cannot be used to skip evaluation of
860 an aggregate function; see <xref linkend="syntax-express-eval"/>.
861 </para>
862
863 <para>
864 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
865 <literal>FOR SHARE</literal> and <literal>FOR KEY SHARE</literal> cannot be
866 specified with <literal>GROUP BY</literal>.
867 </para>
868 </refsect2>
869
870 <refsect2 id="sql-having" xreflabel="HAVING Clause">
871 <title><literal>HAVING</literal> Clause</title>
872
873 <para>
874 The optional <literal>HAVING</literal> clause has the general form
875 <synopsis>
876 HAVING <replaceable class="parameter">condition</replaceable>
877 </synopsis>
878 where <replaceable class="parameter">condition</replaceable> is
879 the same as specified for the <literal>WHERE</literal> clause.
880 </para>
881
882 <para>
883 <literal>HAVING</literal> eliminates group rows that do not
884 satisfy the condition. <literal>HAVING</literal> is different
885 from <literal>WHERE</literal>: <literal>WHERE</literal> filters
886 individual rows before the application of <literal>GROUP
887 BY</literal>, while <literal>HAVING</literal> filters group rows
888 created by <literal>GROUP BY</literal>. Each column referenced in
889 <replaceable class="parameter">condition</replaceable> must
890 unambiguously reference a grouping column, unless the reference
891 appears within an aggregate function or the ungrouped column is
892 functionally dependent on the grouping columns.
893 </para>
894
895 <para>
896 The presence of <literal>HAVING</literal> turns a query into a grouped
897 query even if there is no <literal>GROUP BY</literal> clause. This is the
898 same as what happens when the query contains aggregate functions but
899 no <literal>GROUP BY</literal> clause. All the selected rows are considered to
900 form a single group, and the <command>SELECT</command> list and
901 <literal>HAVING</literal> clause can only reference table columns from
902 within aggregate functions. Such a query will emit a single row if the
903 <literal>HAVING</literal> condition is true, zero rows if it is not true.
904 </para>
905
906 <para>
907 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
908 <literal>FOR SHARE</literal> and <literal>FOR KEY SHARE</literal> cannot be
909 specified with <literal>HAVING</literal>.
910 </para>
911 </refsect2>
912
913 <refsect2 id="sql-window" xreflabel="WINDOW Clause">
914 <title><literal>WINDOW</literal> Clause</title>
915
916 <para>
917 The optional <literal>WINDOW</literal> clause has the general form
918 <synopsis>
919 WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...]
920 </synopsis>
921 where <replaceable class="parameter">window_name</replaceable> is
922 a name that can be referenced from <literal>OVER</literal> clauses or
923 subsequent window definitions, and
924 <replaceable class="parameter">window_definition</replaceable> is
925 <synopsis>
926 [ <replaceable class="parameter">existing_window_name</replaceable> ]
927 [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
928 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
929 [ <replaceable class="parameter">frame_clause</replaceable> ]
930 </synopsis>
931 </para>
932
933 <para>
934 If an <replaceable class="parameter">existing_window_name</replaceable>
935 is specified it must refer to an earlier entry in the <literal>WINDOW</literal>
936 list; the new window copies its partitioning clause from that entry,
937 as well as its ordering clause if any. In this case the new window cannot
938 specify its own <literal>PARTITION BY</literal> clause, and it can specify
939 <literal>ORDER BY</literal> only if the copied window does not have one.
940 The new window always uses its own frame clause; the copied window
941 must not specify a frame clause.
942 </para>
943
944 <para>
945 The elements of the <literal>PARTITION BY</literal> list are interpreted in
946 much the same fashion as elements of a <link
947 linkend="sql-groupby"><literal>GROUP BY</literal></link> clause, except that
948 they are always simple expressions and never the name or number of an
949 output column.
950 Another difference is that these expressions can contain aggregate
951 function calls, which are not allowed in a regular <literal>GROUP BY</literal>
952 clause. They are allowed here because windowing occurs after grouping
953 and aggregation.
954 </para>
955
956 <para>
957 Similarly, the elements of the <literal>ORDER BY</literal> list are interpreted
958 in much the same fashion as elements of a statement-level <link
959 linkend="sql-orderby"><literal>ORDER BY</literal></link> clause, except that
960 the expressions are always taken as simple expressions and never the name
961 or number of an output column.
962 </para>
963
964 <para>
965 The optional <replaceable class="parameter">frame_clause</replaceable> defines
966 the <firstterm>window frame</firstterm> for window functions that depend on the
967 frame (not all do). The window frame is a set of related rows for
968 each row of the query (called the <firstterm>current row</firstterm>).
969 The <replaceable class="parameter">frame_clause</replaceable> can be one of
970
971 <synopsis>
972 { RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
973 { RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
974 </synopsis>
975
976 where <replaceable>frame_start</replaceable>
977 and <replaceable>frame_end</replaceable> can be one of
978
979 <synopsis>
980 UNBOUNDED PRECEDING
981 <replaceable>offset</replaceable> PRECEDING
982 CURRENT ROW
983 <replaceable>offset</replaceable> FOLLOWING
984 UNBOUNDED FOLLOWING
985 </synopsis>
986
987 and <replaceable>frame_exclusion</replaceable> can be one of
988
989 <synopsis>
990 EXCLUDE CURRENT ROW
991 EXCLUDE GROUP
992 EXCLUDE TIES
993 EXCLUDE NO OTHERS
994 </synopsis>
995
996 If <replaceable>frame_end</replaceable> is omitted it defaults to <literal>CURRENT
997 ROW</literal>. Restrictions are that
998 <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>,
999 <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>,
1000 and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the
1001 above list of <replaceable>frame_start</replaceable>
1002 and <replaceable>frame_end</replaceable> options than
1003 the <replaceable>frame_start</replaceable> choice does &mdash; for example
1004 <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable>
1005 PRECEDING</literal> is not allowed.
1006 </para>
1007
1008 <para>
1009 The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>,
1010 which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
1011 CURRENT ROW</literal>; it sets the frame to be all rows from the partition start
1012 up through the current row's last <firstterm>peer</firstterm> (a row
1013 that the window's <literal>ORDER BY</literal> clause considers
1014 equivalent to the current row; all rows are peers if there
1015 is no <literal>ORDER BY</literal>).
1016 In general, <literal>UNBOUNDED PRECEDING</literal> means that the frame
1017 starts with the first row of the partition, and similarly
1018 <literal>UNBOUNDED FOLLOWING</literal> means that the frame ends with the last
1019 row of the partition, regardless
1020 of <literal>RANGE</literal>, <literal>ROWS</literal>
1021 or <literal>GROUPS</literal> mode.
1022 In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> means
1023 that the frame starts or ends with the current row; but
1024 in <literal>RANGE</literal> or <literal>GROUPS</literal> mode it means
1025 that the frame starts or ends with the current row's first or last peer
1026 in the <literal>ORDER BY</literal> ordering.
1027 The <replaceable>offset</replaceable> <literal>PRECEDING</literal> and
1028 <replaceable>offset</replaceable> <literal>FOLLOWING</literal> options
1029 vary in meaning depending on the frame mode.
1030 In <literal>ROWS</literal> mode, the <replaceable>offset</replaceable>
1031 is an integer indicating that the frame starts or ends that many rows
1032 before or after the current row.
1033 In <literal>GROUPS</literal> mode, the <replaceable>offset</replaceable>
1034 is an integer indicating that the frame starts or ends that many peer
1035 groups before or after the current row's peer group, where
1036 a <firstterm>peer group</firstterm> is a group of rows that are
1037 equivalent according to the window's <literal>ORDER BY</literal> clause.
1038 In <literal>RANGE</literal> mode, use of
1039 an <replaceable>offset</replaceable> option requires that there be
1040 exactly one <literal>ORDER BY</literal> column in the window definition.
1041 Then the frame contains those rows whose ordering column value is no
1042 more than <replaceable>offset</replaceable> less than
1043 (for <literal>PRECEDING</literal>) or more than
1044 (for <literal>FOLLOWING</literal>) the current row's ordering column
1045 value. In these cases the data type of
1046 the <replaceable>offset</replaceable> expression depends on the data
1047 type of the ordering column. For numeric ordering columns it is
1048 typically of the same type as the ordering column, but for datetime
1049 ordering columns it is an <type>interval</type>.
1050 In all these cases, the value of the <replaceable>offset</replaceable>
1051 must be non-null and non-negative. Also, while
1052 the <replaceable>offset</replaceable> does not have to be a simple
1053 constant, it cannot contain variables, aggregate functions, or window
1054 functions.
1055 </para>
1056
1057 <para>
1058 The <replaceable>frame_exclusion</replaceable> option allows rows around
1059 the current row to be excluded from the frame, even if they would be
1060 included according to the frame start and frame end options.
1061 <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the
1062 frame.
1063 <literal>EXCLUDE GROUP</literal> excludes the current row and its
1064 ordering peers from the frame.
1065 <literal>EXCLUDE TIES</literal> excludes any peers of the current
1066 row from the frame, but not the current row itself.
1067 <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the
1068 default behavior of not excluding the current row or its peers.
1069 </para>
1070
1071 <para>
1072 Beware that the <literal>ROWS</literal> mode can produce unpredictable
1073 results if the <literal>ORDER BY</literal> ordering does not order the rows
1074 uniquely. The <literal>RANGE</literal> and <literal>GROUPS</literal>
1075 modes are designed to ensure that rows that are peers in
1076 the <literal>ORDER BY</literal> ordering are treated alike: all rows of
1077 a given peer group will be in the frame or excluded from it.
1078 </para>
1079
1080 <para>
1081 The purpose of a <literal>WINDOW</literal> clause is to specify the
1082 behavior of <firstterm>window functions</firstterm> appearing in the query's
1083 <link linkend="sql-select-list"><command>SELECT</command> list</link> or
1084 <link linkend="sql-orderby"><literal>ORDER BY</literal></link> clause.
1085 These functions
1086 can reference the <literal>WINDOW</literal> clause entries by name
1087 in their <literal>OVER</literal> clauses. A <literal>WINDOW</literal> clause
1088 entry does not have to be referenced anywhere, however; if it is not
1089 used in the query it is simply ignored. It is possible to use window
1090 functions without any <literal>WINDOW</literal> clause at all, since
1091 a window function call can specify its window definition directly in
1092 its <literal>OVER</literal> clause. However, the <literal>WINDOW</literal>
1093 clause saves typing when the same window definition is needed for more
1094 than one window function.
1095 </para>
1096
1097 <para>
1098 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
1099 <literal>FOR SHARE</literal> and <literal>FOR KEY SHARE</literal> cannot be
1100 specified with <literal>WINDOW</literal>.
1101 </para>
1102
1103 <para>
1104 Window functions are described in detail in
1105 <xref linkend="tutorial-window"/>,
1106 <xref linkend="syntax-window-functions"/>, and
1107 <xref linkend="queries-window"/>.
1108 </para>
1109 </refsect2>
1110
1111 <refsect2 id="sql-select-list" xreflabel="SELECT List">
1112 <title><command>SELECT</command> List</title>
1113
1114 <para>
1115 The <command>SELECT</command> list (between the key words
1116 <literal>SELECT</literal> and <literal>FROM</literal>) specifies expressions
1117 that form the output rows of the <command>SELECT</command>
1118 statement. The expressions can (and usually do) refer to columns
1119 computed in the <literal>FROM</literal> clause.
1120 </para>
1121
1122 <para>
1123 Just as in a table, every output column of a <command>SELECT</command>
1124 has a name. In a simple <command>SELECT</command> this name is just
1125 used to label the column for display, but when the <command>SELECT</command>
1126 is a sub-query of a larger query, the name is seen by the larger query
1127 as the column name of the virtual table produced by the sub-query.
1128 To specify the name to use for an output column, write
1129 <literal>AS</literal> <replaceable class="parameter">output_name</replaceable>
1130 after the column's expression. (You can omit <literal>AS</literal>,
1131 but only if the desired output name does not match any
1132 <productname>PostgreSQL</productname> keyword (see <xref
1133 linkend="sql-keywords-appendix"/>). For protection against possible
1134 future keyword additions, it is recommended that you always either
1135 write <literal>AS</literal> or double-quote the output name.)
1136 If you do not specify a column name, a name is chosen automatically
1137 by <productname>PostgreSQL</productname>. If the column's expression
1138 is a simple column reference then the chosen name is the same as that
1139 column's name. In more complex cases a function or type name may be
1140 used, or the system may fall back on a generated name such as
1141 <literal>?column?</literal>.
1142 </para>
1143
1144 <para>
1145 An output column's name can be used to refer to the column's value in
1146 <literal>ORDER BY</literal> and <literal>GROUP BY</literal> clauses, but not in the
1147 <literal>WHERE</literal> or <literal>HAVING</literal> clauses; there you must write
1148 out the expression instead.
1149 </para>
1150
1151 <para>
1152 Instead of an expression, <literal>*</literal> can be written in
1153 the output list as a shorthand for all the columns of the selected
1154 rows. Also, you can write <literal><replaceable
1155 class="parameter">table_name</replaceable>.*</literal> as a
1156 shorthand for the columns coming from just that table. In these
1157 cases it is not possible to specify new names with <literal>AS</literal>;
1158 the output column names will be the same as the table columns' names.
1159 </para>
1160
1161 <para>
1162 According to the SQL standard, the expressions in the output list should
1163 be computed before applying <literal>DISTINCT</literal>, <literal>ORDER
1164 BY</literal>, or <literal>LIMIT</literal>. This is obviously necessary
1165 when using <literal>DISTINCT</literal>, since otherwise it's not clear
1166 what values are being made distinct. However, in many cases it is
1167 convenient if output expressions are computed after <literal>ORDER
1168 BY</literal> and <literal>LIMIT</literal>; particularly if the output list
1169 contains any volatile or expensive functions. With that behavior, the
1170 order of function evaluations is more intuitive and there will not be
1171 evaluations corresponding to rows that never appear in the output.
1172 <productname>PostgreSQL</productname> will effectively evaluate output expressions
1173 after sorting and limiting, so long as those expressions are not
1174 referenced in <literal>DISTINCT</literal>, <literal>ORDER BY</literal>
1175 or <literal>GROUP BY</literal>. (As a counterexample, <literal>SELECT
1176 f(x) FROM tab ORDER BY 1</literal> clearly must evaluate <function>f(x)</function>
1177 before sorting.) Output expressions that contain set-returning functions
1178 are effectively evaluated after sorting and before limiting, so
1179 that <literal>LIMIT</literal> will act to cut off the output from a
1180 set-returning function.
1181 </para>
1182
1183 <note>
1184 <para>
1185 <productname>PostgreSQL</productname> versions before 9.6 did not provide any
1186 guarantees about the timing of evaluation of output expressions versus
1187 sorting and limiting; it depended on the form of the chosen query plan.
1188 </para>
1189 </note>
1190 </refsect2>
1191
1192 <refsect2 id="sql-distinct" xreflabel="DISTINCT Clause">
1193 <title><literal>DISTINCT</literal> Clause</title>
1194
1195 <para>
1196 If <literal>SELECT DISTINCT</literal> is specified, all duplicate rows are
1197 removed from the result set (one row is kept from each group of
1198 duplicates). <literal>SELECT ALL</literal> specifies the opposite: all rows are
1199 kept; that is the default.
1200 </para>
1201
1202 <para>
1203 <literal>SELECT DISTINCT ON ( <replaceable
1204 class="parameter">expression</replaceable> [, ...] )</literal>
1205 keeps only the first row of each set of rows where the given
1206 expressions evaluate to equal. The <literal>DISTINCT ON</literal>
1207 expressions are interpreted using the same rules as for
1208 <literal>ORDER BY</literal> (see above). Note that the <quote>first
1209 row</quote> of each set is unpredictable unless <literal>ORDER
1210 BY</literal> is used to ensure that the desired row appears first. For
1211 example:
1212 <programlisting>
1213 SELECT DISTINCT ON (location) location, time, report
1214 FROM weather_reports
1215 ORDER BY location, time DESC;
1216 </programlisting>
1217 retrieves the most recent weather report for each location. But
1218 if we had not used <literal>ORDER BY</literal> to force descending order
1219 of time values for each location, we'd have gotten a report from
1220 an unpredictable time for each location.
1221 </para>
1222
1223 <para>
1224 The <literal>DISTINCT ON</literal> expression(s) must match the leftmost
1225 <literal>ORDER BY</literal> expression(s). The <literal>ORDER BY</literal> clause
1226 will normally contain additional expression(s) that determine the
1227 desired precedence of rows within each <literal>DISTINCT ON</literal> group.
1228 </para>
1229
1230 <para>
1231 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
1232 <literal>FOR SHARE</literal> and <literal>FOR KEY SHARE</literal> cannot be
1233 specified with <literal>DISTINCT</literal>.
1234 </para>
1235 </refsect2>
1236
1237 <refsect2 id="sql-union" xreflabel="UNION Clause">
1238 <title><literal>UNION</literal> Clause</title>
1239
1240 <para>
1241 The <literal>UNION</literal> clause has this general form:
1242 <synopsis>
1243 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
1244 </synopsis><replaceable class="parameter">select_statement</replaceable> is
1245 any <command>SELECT</command> statement without an <literal>ORDER
1246 BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
1247 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
1248 (<literal>ORDER BY</literal> and <literal>LIMIT</literal> can be attached to a
1249 subexpression if it is enclosed in parentheses. Without
1250 parentheses, these clauses will be taken to apply to the result of
1251 the <literal>UNION</literal>, not to its right-hand input
1252 expression.)
1253 </para>
1254
1255 <para>
1256 The <literal>UNION</literal> operator computes the set union of
1257 the rows returned by the involved <command>SELECT</command>
1258 statements. A row is in the set union of two result sets if it
1259 appears in at least one of the result sets. The two
1260 <command>SELECT</command> statements that represent the direct
1261 operands of the <literal>UNION</literal> must produce the same
1262 number of columns, and corresponding columns must be of compatible
1263 data types.
1264 </para>
1265
1266 <para>
1267 The result of <literal>UNION</literal> does not contain any duplicate
1268 rows unless the <literal>ALL</literal> option is specified.
1269 <literal>ALL</literal> prevents elimination of duplicates. (Therefore,
1270 <literal>UNION ALL</literal> is usually significantly quicker than
1271 <literal>UNION</literal>; use <literal>ALL</literal> when you can.)
1272 <literal>DISTINCT</literal> can be written to explicitly specify the
1273 default behavior of eliminating duplicate rows.
1274 </para>
1275
1276 <para>
1277 Multiple <literal>UNION</literal> operators in the same
1278 <command>SELECT</command> statement are evaluated left to right,
1279 unless otherwise indicated by parentheses.
1280 </para>
1281
1282 <para>
1283 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>, <literal>FOR SHARE</literal> and
1284 <literal>FOR KEY SHARE</literal> cannot be
1285 specified either for a <literal>UNION</literal> result or for any input of a
1286 <literal>UNION</literal>.
1287 </para>
1288 </refsect2>
1289
1290 <refsect2 id="sql-intersect" xreflabel="INTERSECT Clause">
1291 <title><literal>INTERSECT</literal> Clause</title>
1292
1293 <para>
1294 The <literal>INTERSECT</literal> clause has this general form:
1295 <synopsis>
1296 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
1297 </synopsis><replaceable class="parameter">select_statement</replaceable> is
1298 any <command>SELECT</command> statement without an <literal>ORDER
1299 BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
1300 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
1301 </para>
1302
1303 <para>
1304 The <literal>INTERSECT</literal> operator computes the set
1305 intersection of the rows returned by the involved
1306 <command>SELECT</command> statements. A row is in the
1307 intersection of two result sets if it appears in both result sets.
1308 </para>
1309
1310 <para>
1311 The result of <literal>INTERSECT</literal> does not contain any
1312 duplicate rows unless the <literal>ALL</literal> option is specified.
1313 With <literal>ALL</literal>, a row that has <replaceable>m</replaceable> duplicates in the
1314 left table and <replaceable>n</replaceable> duplicates in the right table will appear
1315 min(<replaceable>m</replaceable>,<replaceable>n</replaceable>) times in the result set.
1316 <literal>DISTINCT</literal> can be written to explicitly specify the
1317 default behavior of eliminating duplicate rows.
1318 </para>
1319
1320 <para>
1321 Multiple <literal>INTERSECT</literal> operators in the same
1322 <command>SELECT</command> statement are evaluated left to right,
1323 unless parentheses dictate otherwise.
1324 <literal>INTERSECT</literal> binds more tightly than
1325 <literal>UNION</literal>. That is, <literal>A UNION B INTERSECT
1326 C</literal> will be read as <literal>A UNION (B INTERSECT
1327 C)</literal>.
1328 </para>
1329
1330 <para>
1331 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>, <literal>FOR SHARE</literal> and
1332 <literal>FOR KEY SHARE</literal> cannot be
1333 specified either for an <literal>INTERSECT</literal> result or for any input of
1334 an <literal>INTERSECT</literal>.
1335 </para>
1336 </refsect2>
1337
1338 <refsect2 id="sql-except" xreflabel="EXCEPT Clause">
1339 <title><literal>EXCEPT</literal> Clause</title>
1340
1341 <para>
1342 The <literal>EXCEPT</literal> clause has this general form:
1343 <synopsis>
1344 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
1345 </synopsis><replaceable class="parameter">select_statement</replaceable> is
1346 any <command>SELECT</command> statement without an <literal>ORDER
1347 BY</literal>, <literal>LIMIT</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>,
1348 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
1349 </para>
1350
1351 <para>
1352 The <literal>EXCEPT</literal> operator computes the set of rows
1353 that are in the result of the left <command>SELECT</command>
1354 statement but not in the result of the right one.
1355 </para>
1356
1357 <para>
1358 The result of <literal>EXCEPT</literal> does not contain any
1359 duplicate rows unless the <literal>ALL</literal> option is specified.
1360 With <literal>ALL</literal>, a row that has <replaceable>m</replaceable> duplicates in the
1361 left table and <replaceable>n</replaceable> duplicates in the right table will appear
1362 max(<replaceable>m</replaceable>-<replaceable>n</replaceable>,0) times in the result set.
1363 <literal>DISTINCT</literal> can be written to explicitly specify the
1364 default behavior of eliminating duplicate rows.
1365 </para>
1366
1367 <para>
1368 Multiple <literal>EXCEPT</literal> operators in the same
1369 <command>SELECT</command> statement are evaluated left to right,
1370 unless parentheses dictate otherwise. <literal>EXCEPT</literal> binds at
1371 the same level as <literal>UNION</literal>.
1372 </para>
1373
1374 <para>
1375 Currently, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>, <literal>FOR SHARE</literal> and
1376 <literal>FOR KEY SHARE</literal> cannot be
1377 specified either for an <literal>EXCEPT</literal> result or for any input of
1378 an <literal>EXCEPT</literal>.
1379 </para>
1380 </refsect2>
1381
1382 <refsect2 id="sql-orderby" xreflabel="ORDER BY Clause">
1383 <title><literal>ORDER BY</literal> Clause</title>
1384
1385 <para>
1386 The optional <literal>ORDER BY</literal> clause has this general form:
1387 <synopsis>
1388 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
1389 </synopsis>
1390 The <literal>ORDER BY</literal> clause causes the result rows to
1391 be sorted according to the specified expression(s). If two rows are
1392 equal according to the leftmost expression, they are compared
1393 according to the next expression and so on. If they are equal
1394 according to all specified expressions, they are returned in
1395 an implementation-dependent order.
1396 </para>
1397
1398 <para>
1399 Each <replaceable class="parameter">expression</replaceable> can be the
1400 name or ordinal number of an output column
1401 (<command>SELECT</command> list item), or it can be an arbitrary
1402 expression formed from input-column values.
1403 </para>
1404
1405 <para>
1406 The ordinal number refers to the ordinal (left-to-right) position
1407 of the output column. This feature makes it possible to define an
1408 ordering on the basis of a column that does not have a unique
1409 name. This is never absolutely necessary because it is always
1410 possible to assign a name to an output column using the
1411 <literal>AS</literal> clause.
1412 </para>
1413
1414 <para>
1415 It is also possible to use arbitrary expressions in the
1416 <literal>ORDER BY</literal> clause, including columns that do not
1417 appear in the <command>SELECT</command> output list. Thus the
1418 following statement is valid:
1419 <programlisting>
1420 SELECT name FROM distributors ORDER BY code;
1421 </programlisting>
1422 A limitation of this feature is that an <literal>ORDER BY</literal>
1423 clause applying to the result of a <literal>UNION</literal>,
1424 <literal>INTERSECT</literal>, or <literal>EXCEPT</literal> clause can only
1425 specify an output column name or number, not an expression.
1426 </para>
1427
1428 <para>
1429 If an <literal>ORDER BY</literal> expression is a simple name that
1430 matches both an output column name and an input column name,
1431 <literal>ORDER BY</literal> will interpret it as the output column name.
1432 This is the opposite of the choice that <literal>GROUP BY</literal> will
1433 make in the same situation. This inconsistency is made to be
1434 compatible with the SQL standard.
1435 </para>
1436
1437 <para>
1438 Optionally one can add the key word <literal>ASC</literal> (ascending) or
1439 <literal>DESC</literal> (descending) after any expression in the
1440 <literal>ORDER BY</literal> clause. If not specified, <literal>ASC</literal> is
1441 assumed by default. Alternatively, a specific ordering operator
1442 name can be specified in the <literal>USING</literal> clause.
1443 An ordering operator must be a less-than or greater-than
1444 member of some B-tree operator family.
1445 <literal>ASC</literal> is usually equivalent to <literal>USING &lt;</literal> and
1446 <literal>DESC</literal> is usually equivalent to <literal>USING &gt;</literal>.
1447 (But the creator of a user-defined data type can define exactly what the
1448 default sort ordering is, and it might correspond to operators with other
1449 names.)
1450 </para>
1451
1452 <para>
1453 If <literal>NULLS LAST</literal> is specified, null values sort after all
1454 non-null values; if <literal>NULLS FIRST</literal> is specified, null values
1455 sort before all non-null values. If neither is specified, the default
1456 behavior is <literal>NULLS LAST</literal> when <literal>ASC</literal> is specified
1457 or implied, and <literal>NULLS FIRST</literal> when <literal>DESC</literal> is specified
1458 (thus, the default is to act as though nulls are larger than non-nulls).
1459 When <literal>USING</literal> is specified, the default nulls ordering depends
1460 on whether the operator is a less-than or greater-than operator.
1461 </para>
1462
1463 <para>
1464 Note that ordering options apply only to the expression they follow;
1465 for example <literal>ORDER BY x, y DESC</literal> does not mean
1466 the same thing as <literal>ORDER BY x DESC, y DESC</literal>.
1467 </para>
1468
1469 <para>
1470 Character-string data is sorted according to the collation that applies
1471 to the column being sorted. That can be overridden at need by including
1472 a <literal>COLLATE</literal> clause in the
1473 <replaceable class="parameter">expression</replaceable>, for example
1474 <literal>ORDER BY mycolumn COLLATE "en_US"</literal>.
1475 For more information see <xref linkend="sql-syntax-collate-exprs"/> and
1476 <xref linkend="collation"/>.
1477 </para>
1478 </refsect2>
1479
1480 <refsect2 id="sql-limit" xreflabel="LIMIT Clause">
1481 <title><literal>LIMIT</literal> Clause</title>
1482
1483 <para>
1484 The <literal>LIMIT</literal> clause consists of two independent
1485 sub-clauses:
1486 <synopsis>
1487 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
1488 OFFSET <replaceable class="parameter">start</replaceable>
1489 </synopsis>
1490 The parameter <replaceable class="parameter">count</replaceable> specifies the
1491 maximum number of rows to return, while <replaceable
1492 class="parameter">start</replaceable> specifies the number of rows
1493 to skip before starting to return rows. When both are specified,
1494 <replaceable class="parameter">start</replaceable> rows are skipped
1495 before starting to count the <replaceable
1496 class="parameter">count</replaceable> rows to be returned.
1497 </para>
1498
1499 <para>
1500 If the <replaceable class="parameter">count</replaceable> expression
1501 evaluates to NULL, it is treated as <literal>LIMIT ALL</literal>, i.e., no
1502 limit. If <replaceable class="parameter">start</replaceable> evaluates
1503 to NULL, it is treated the same as <literal>OFFSET 0</literal>.
1504 </para>
1505
1506 <para>
1507 SQL:2008 introduced a different syntax to achieve the same result,
1508 which <productname>PostgreSQL</productname> also supports. It is:
1509 <synopsis>
1510 OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
1511 FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES }
1512 </synopsis>
1513 In this syntax, the <replaceable class="parameter">start</replaceable>
1514 or <replaceable class="parameter">count</replaceable> value is required by
1515 the standard to be a literal constant, a parameter, or a variable name;
1516 as a <productname>PostgreSQL</productname> extension, other expressions
1517 are allowed, but will generally need to be enclosed in parentheses to avoid
1518 ambiguity.
1519 If <replaceable class="parameter">count</replaceable> is
1520 omitted in a <literal>FETCH</literal> clause, it defaults to 1.
1521 The <literal>WITH TIES</literal> option is used to return any additional
1522 rows that tie for the last place in the result set according to
1523 the <literal>ORDER BY</literal> clause; <literal>ORDER BY</literal>
1524 is mandatory in this case, and <literal>SKIP LOCKED</literal> is
1525 not allowed.
1526 <literal>ROW</literal> and <literal>ROWS</literal> as well as
1527 <literal>FIRST</literal> and <literal>NEXT</literal> are noise
1528 words that don't influence the effects of these clauses.
1529 According to the standard, the <literal>OFFSET</literal> clause must come
1530 before the <literal>FETCH</literal> clause if both are present; but
1531 <productname>PostgreSQL</productname> is laxer and allows either order.
1532 </para>
1533
1534 <para>
1535 When using <literal>LIMIT</literal>, it is a good idea to use an
1536 <literal>ORDER BY</literal> clause that constrains the result rows into a
1537 unique order. Otherwise you will get an unpredictable subset of
1538 the query's rows &mdash; you might be asking for the tenth through
1539 twentieth rows, but tenth through twentieth in what ordering? You
1540 don't know what ordering unless you specify <literal>ORDER BY</literal>.
1541 </para>
1542
1543 <para>
1544 The query planner takes <literal>LIMIT</literal> into account when
1545 generating a query plan, so you are very likely to get different
1546 plans (yielding different row orders) depending on what you use
1547 for <literal>LIMIT</literal> and <literal>OFFSET</literal>. Thus, using
1548 different <literal>LIMIT</literal>/<literal>OFFSET</literal> values to select
1549 different subsets of a query result <emphasis>will give
1550 inconsistent results</emphasis> unless you enforce a predictable
1551 result ordering with <literal>ORDER BY</literal>. This is not a bug; it
1552 is an inherent consequence of the fact that SQL does not promise
1553 to deliver the results of a query in any particular order unless
1554 <literal>ORDER BY</literal> is used to constrain the order.
1555 </para>
1556
1557 <para>
1558 It is even possible for repeated executions of the same <literal>LIMIT</literal>
1559 query to return different subsets of the rows of a table, if there
1560 is not an <literal>ORDER BY</literal> to enforce selection of a deterministic
1561 subset. Again, this is not a bug; determinism of the results is
1562 simply not guaranteed in such a case.
1563 </para>
1564 </refsect2>
1565
1566 <refsect2 id="sql-for-update-share" xreflabel="The Locking Clause">
1567 <title>The Locking Clause</title>
1568
1569 <para>
1570 <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR SHARE</literal>
1571 and <literal>FOR KEY SHARE</literal>
1572 are <firstterm>locking clauses</firstterm>; they affect how <literal>SELECT</literal>
1573 locks rows as they are obtained from the table.
1574 </para>
1575
1576 <para>
1577 The locking clause has the general form
1578
1579 <synopsis>
1580 FOR <replaceable>lock_strength</replaceable> [ OF <replaceable class="parameter">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ]
1581 </synopsis>
1582
1583 where <replaceable>lock_strength</replaceable> can be one of
1584
1585 <synopsis>
1586 UPDATE
1587 NO KEY UPDATE
1588 SHARE
1589 KEY SHARE
1590 </synopsis>
1591 </para>
1592
1593 <para>
1594 <replaceable class="parameter">from_reference</replaceable> must be a
1595 table <replaceable class="parameter">alias</replaceable> or non-hidden
1596 <replaceable class="parameter">table_name</replaceable> referenced
1597 in the <literal>FROM</literal> clause. For more information on each
1598 row-level lock mode, refer to <xref linkend="locking-rows"/>.
1599 </para>
1600
1601 <para>
1602 To prevent the operation from waiting for other transactions to commit,
1603 use either the <literal>NOWAIT</literal> or <literal>SKIP LOCKED</literal>
1604 option. With <literal>NOWAIT</literal>, the statement reports an error, rather
1605 than waiting, if a selected row cannot be locked immediately.
1606 With <literal>SKIP LOCKED</literal>, any selected rows that cannot be
1607 immediately locked are skipped. Skipping locked rows provides an
1608 inconsistent view of the data, so this is not suitable for general purpose
1609 work, but can be used to avoid lock contention with multiple consumers
1610 accessing a queue-like table.
1611 Note that <literal>NOWAIT</literal> and <literal>SKIP LOCKED</literal> apply only
1612 to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
1613 table-level lock is still taken in the ordinary way (see
1614 <xref linkend="mvcc"/>). You can use
1615 <link linkend="sql-lock"><command>LOCK</command></link>
1616 with the <literal>NOWAIT</literal> option first,
1617 if you need to acquire the table-level lock without waiting.
1618 </para>
1619
1620 <para>
1621 If specific tables are named in a locking clause,
1622 then only rows coming from those tables are locked; any other
1623 tables used in the <command>SELECT</command> are simply read as
1624 usual. A locking
1625 clause without a table list affects all tables used in the statement.
1626 If a locking clause is
1627 applied to a view or sub-query, it affects all tables used in
1628 the view or sub-query.
1629 However, these clauses
1630 do not apply to <literal>WITH</literal> queries referenced by the primary query.
1631 If you want row locking to occur within a <literal>WITH</literal> query, specify
1632 a locking clause within the <literal>WITH</literal> query.
1633 </para>
1634
1635 <para>
1636 Multiple locking
1637 clauses can be written if it is necessary to specify different locking
1638 behavior for different tables. If the same table is mentioned (or
1639 implicitly affected) by more than one locking clause,
1640 then it is processed as if it was only specified by the strongest one.
1641 Similarly, a table is processed
1642 as <literal>NOWAIT</literal> if that is specified in any of the clauses
1643 affecting it. Otherwise, it is processed
1644 as <literal>SKIP LOCKED</literal> if that is specified in any of the
1645 clauses affecting it.
1646 </para>
1647
1648 <para>
1649 The locking clauses cannot be
1650 used in contexts where returned rows cannot be clearly identified with
1651 individual table rows; for example they cannot be used with aggregation.
1652 </para>
1653
1654 <para>
1655 When a locking clause
1656 appears at the top level of a <command>SELECT</command> query, the rows that
1657 are locked are exactly those that are returned by the query; in the
1658 case of a join query, the rows locked are those that contribute to
1659 returned join rows. In addition, rows that satisfied the query
1660 conditions as of the query snapshot will be locked, although they
1661 will not be returned if they were updated after the snapshot
1662 and no longer satisfy the query conditions. If a
1663 <literal>LIMIT</literal> is used, locking stops
1664 once enough rows have been returned to satisfy the limit (but note that
1665 rows skipped over by <literal>OFFSET</literal> will get locked). Similarly,
1666 if a locking clause
1667 is used in a cursor's query, only rows actually fetched or stepped past
1668 by the cursor will be locked.
1669 </para>
1670
1671 <para>
1672 When a locking clause
1673 appears in a sub-<command>SELECT</command>, the rows locked are those
1674 returned to the outer query by the sub-query. This might involve
1675 fewer rows than inspection of the sub-query alone would suggest,
1676 since conditions from the outer query might be used to optimize
1677 execution of the sub-query. For example,
1678 <programlisting>
1679 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
1680 </programlisting>
1681 will lock only rows having <literal>col1 = 5</literal>, even though that
1682 condition is not textually within the sub-query.
1683 </para>
1684
1685 <para>
1686 Previous releases failed to preserve a lock which is upgraded by a later
1687 savepoint. For example, this code:
1688 <programlisting>
1689 BEGIN;
1690 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
1691 SAVEPOINT s;
1692 UPDATE mytable SET ... WHERE key = 1;
1693 ROLLBACK TO s;
1694 </programlisting>
1695 would fail to preserve the <literal>FOR UPDATE</literal> lock after the
1696 <command>ROLLBACK TO</command>. This has been fixed in release 9.3.
1697 </para>
1698
1699 <caution>
1700 <para>
1701 It is possible for a <command>SELECT</command> command running at the <literal>READ
1702 COMMITTED</literal> transaction isolation level and using <literal>ORDER
1703 BY</literal> and a locking clause to return rows out of
1704 order. This is because <literal>ORDER BY</literal> is applied first.
1705 The command sorts the result, but might then block trying to obtain a lock
1706 on one or more of the rows. Once the <literal>SELECT</literal> unblocks, some
1707 of the ordering column values might have been modified, leading to those
1708 rows appearing to be out of order (though they are in order in terms
1709 of the original column values). This can be worked around at need by
1710 placing the <literal>FOR UPDATE/SHARE</literal> clause in a sub-query,
1711 for example
1712 <programlisting>
1713 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
1714 </programlisting>
1715 Note that this will result in locking all rows of <structname>mytable</structname>,
1716 whereas <literal>FOR UPDATE</literal> at the top level would lock only the
1717 actually returned rows. This can make for a significant performance
1718 difference, particularly if the <literal>ORDER BY</literal> is combined with
1719 <literal>LIMIT</literal> or other restrictions. So this technique is recommended
1720 only if concurrent updates of the ordering columns are expected and a
1721 strictly sorted result is required.
1722 </para>
1723
1724 <para>
1725 At the <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
1726 transaction isolation level this would cause a serialization failure (with
1727 an <literal>SQLSTATE</literal> of <literal>'40001'</literal>), so there is
1728 no possibility of receiving rows out of order under these isolation levels.
1729 </para>
1730 </caution>
1731 </refsect2>
1732
1733 <refsect2 id="sql-table">
1734 <title><literal>TABLE</literal> Command</title>
1735
1736 <para>
1737 The command
1738 <programlisting>
1739 TABLE <replaceable class="parameter">name</replaceable>
1740 </programlisting>
1741 is equivalent to
1742 <programlisting>
1743 SELECT * FROM <replaceable class="parameter">name</replaceable>
1744 </programlisting>
1745 It can be used as a top-level command or as a space-saving syntax
1746 variant in parts of complex queries. Only the <literal>WITH</literal>,
1747 <literal>UNION</literal>, <literal>INTERSECT</literal>, <literal>EXCEPT</literal>,
1748 <literal>ORDER BY</literal>, <literal>LIMIT</literal>, <literal>OFFSET</literal>,
1749 <literal>FETCH</literal> and <literal>FOR</literal> locking clauses can be used
1750 with <command>TABLE</command>; the <literal>WHERE</literal> clause and any form of
1751 aggregation cannot
1752 be used.
1753 </para>
1754 </refsect2>
1755 </refsect1>
1756
1757 <refsect1>
1758 <title>Examples</title>
1759
1760 <para>
1761 To join the table <literal>films</literal> with the table
1762 <literal>distributors</literal>:
1763
1764 <programlisting>
1765 SELECT f.title, f.did, d.name, f.date_prod, f.kind
1766 FROM distributors d JOIN films f USING (did);
1767
1768 title | did | name | date_prod | kind
1769 -------------------+-----+--------------+------------+----------
1770 The Third Man | 101 | British Lion | 1949-12-23 | Drama
1771 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
1772 ...
1773 </programlisting>
1774 </para>
1775
1776 <para>
1777 To sum the column <literal>len</literal> of all films and group
1778 the results by <literal>kind</literal>:
1779
1780 <programlisting>
1781 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
1782
1783 kind | total
1784 ----------+-------
1785 Action | 07:34
1786 Comedy | 02:58
1787 Drama | 14:28
1788 Musical | 06:42
1789 Romantic | 04:38
1790 </programlisting>
1791 </para>
1792
1793 <para>
1794 To sum the column <literal>len</literal> of all films, group
1795 the results by <literal>kind</literal> and show those group totals
1796 that are less than 5 hours:
1797
1798 <programlisting>
1799 SELECT kind, sum(len) AS total
1800 FROM films
1801 GROUP BY kind
1802 HAVING sum(len) &lt; interval '5 hours';
1803
1804 kind | total
1805 ----------+-------
1806 Comedy | 02:58
1807 Romantic | 04:38
1808 </programlisting>
1809 </para>
1810
1811 <para>
1812 The following two examples are identical ways of sorting the individual
1813 results according to the contents of the second column
1814 (<literal>name</literal>):
1815
1816 <programlisting>
1817 SELECT * FROM distributors ORDER BY name;
1818 SELECT * FROM distributors ORDER BY 2;
1819
1820 did | name
1821 -----+------------------
1822 109 | 20th Century Fox
1823 110 | Bavaria Atelier
1824 101 | British Lion
1825 107 | Columbia
1826 102 | Jean Luc Godard
1827 113 | Luso films
1828 104 | Mosfilm
1829 103 | Paramount
1830 106 | Toho
1831 105 | United Artists
1832 111 | Walt Disney
1833 112 | Warner Bros.
1834 108 | Westward
1835 </programlisting>
1836 </para>
1837
1838 <para>
1839 The next example shows how to obtain the union of the tables
1840 <literal>distributors</literal> and
1841 <literal>actors</literal>, restricting the results to those that begin
1842 with the letter W in each table. Only distinct rows are wanted, so the
1843 key word <literal>ALL</literal> is omitted.
1844
1845 <programlisting>
1846 distributors: actors:
1847 did | name id | name
1848 -----+-------------- ----+----------------
1849 108 | Westward 1 | Woody Allen
1850 111 | Walt Disney 2 | Warren Beatty
1851 112 | Warner Bros. 3 | Walter Matthau
1852 ... ...
1853
1854 SELECT distributors.name
1855 FROM distributors
1856 WHERE distributors.name LIKE 'W%'
1857 UNION
1858 SELECT actors.name
1859 FROM actors
1860 WHERE actors.name LIKE 'W%';
1861
1862 name
1863 ----------------
1864 Walt Disney
1865 Walter Matthau
1866 Warner Bros.
1867 Warren Beatty
1868 Westward
1869 Woody Allen
1870 </programlisting>
1871 </para>
1872
1873 <para>
1874 This example shows how to use a function in the <literal>FROM</literal>
1875 clause, both with and without a column definition list:
1876
1877 <programlisting>
1878 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
1879 SELECT * FROM distributors WHERE did = $1;
1880 $$ LANGUAGE SQL;
1881
1882 SELECT * FROM distributors(111);
1883 did | name
1884 -----+-------------
1885 111 | Walt Disney
1886
1887 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
1888 SELECT * FROM distributors WHERE did = $1;
1889 $$ LANGUAGE SQL;
1890
1891 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
1892 f1 | f2
1893 -----+-------------
1894 111 | Walt Disney
1895 </programlisting>
1896 </para>
1897
1898 <para>
1899 Here is an example of a function with an ordinality column added:
1900
1901 <programlisting>
1902 SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
1903 unnest | ordinality
1904 --------+----------
1905 a | 1
1906 b | 2
1907 c | 3
1908 d | 4
1909 e | 5
1910 f | 6
1911 (6 rows)
1912 </programlisting>
1913 </para>
1914
1915 <para>
1916 This example shows how to use a simple <literal>WITH</literal> clause:
1917
1918 <programlisting>
1919 WITH t AS (
1920 SELECT random() as x FROM generate_series(1, 3)
1921 )
1922 SELECT * FROM t
1923 UNION ALL
1924 SELECT * FROM t;
1925 x
1926 --------------------
1927 0.534150459803641
1928 0.520092216785997
1929 0.0735620250925422
1930 0.534150459803641
1931 0.520092216785997
1932 0.0735620250925422
1933 </programlisting>
1934
1935 Notice that the <literal>WITH</literal> query was evaluated only once,
1936 so that we got two sets of the same three random values.
1937 </para>
1938
1939 <para>
1940 This example uses <literal>WITH RECURSIVE</literal> to find all
1941 subordinates (direct or indirect) of the employee Mary, and their
1942 level of indirectness, from a table that shows only direct
1943 subordinates:
1944
1945 <programlisting>
1946 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
1947 SELECT 1, employee_name, manager_name
1948 FROM employee
1949 WHERE manager_name = 'Mary'
1950 UNION ALL
1951 SELECT er.distance + 1, e.employee_name, e.manager_name
1952 FROM employee_recursive er, employee e
1953 WHERE er.employee_name = e.manager_name
1954 )
1955 SELECT distance, employee_name FROM employee_recursive;
1956 </programlisting>
1957
1958 Notice the typical form of recursive queries:
1959 an initial condition, followed by <literal>UNION</literal>,
1960 followed by the recursive part of the query. Be sure that the
1961 recursive part of the query will eventually return no tuples, or
1962 else the query will loop indefinitely. (See <xref linkend="queries-with"/>
1963 for more examples.)
1964 </para>
1965
1966 <para>
1967 This example uses <literal>LATERAL</literal> to apply a set-returning function
1968 <function>get_product_names()</function> for each row of the
1969 <structname>manufacturers</structname> table:
1970
1971 <programlisting>
1972 SELECT m.name AS mname, pname
1973 FROM manufacturers m, LATERAL get_product_names(m.id) pname;
1974 </programlisting>
1975
1976 Manufacturers not currently having any products would not appear in the
1977 result, since it is an inner join. If we wished to include the names of
1978 such manufacturers in the result, we could do:
1979
1980 <programlisting>
1981 SELECT m.name AS mname, pname
1982 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
1983 </programlisting></para>
1984 </refsect1>
1985
1986 <refsect1>
1987 <title>Compatibility</title>
1988
1989 <para>
1990 Of course, the <command>SELECT</command> statement is compatible
1991 with the SQL standard. But there are some extensions and some
1992 missing features.
1993 </para>
1994
1995 <refsect2>
1996 <title>Omitted <literal>FROM</literal> Clauses</title>
1997
1998 <para>
1999 <productname>PostgreSQL</productname> allows one to omit the
2000 <literal>FROM</literal> clause. It has a straightforward use to
2001 compute the results of simple expressions:
2002 <programlisting>
2003 SELECT 2+2;
2004
2005 ?column?
2006 ----------
2007 4
2008 </programlisting>
2009 Some other <acronym>SQL</acronym> databases cannot do this except
2010 by introducing a dummy one-row table from which to do the
2011 <command>SELECT</command>.
2012 </para>
2013 </refsect2>
2014
2015 <refsect2>
2016 <title>Empty <literal>SELECT</literal> Lists</title>
2017
2018 <para>
2019 The list of output expressions after <literal>SELECT</literal> can be
2020 empty, producing a zero-column result table.
2021 This is not valid syntax according to the SQL standard.
2022 <productname>PostgreSQL</productname> allows it to be consistent with
2023 allowing zero-column tables.
2024 However, an empty list is not allowed when <literal>DISTINCT</literal> is used.
2025 </para>
2026 </refsect2>
2027
2028 <refsect2>
2029 <title>Omitting the <literal>AS</literal> Key Word</title>
2030
2031 <para>
2032 In the SQL standard, the optional key word <literal>AS</literal> can be
2033 omitted before an output column name whenever the new column name
2034 is a valid column name (that is, not the same as any reserved
2035 keyword). <productname>PostgreSQL</productname> is slightly more
2036 restrictive: <literal>AS</literal> is required if the new column name
2037 matches any keyword at all, reserved or not. Recommended practice is
2038 to use <literal>AS</literal> or double-quote output column names, to prevent
2039 any possible conflict against future keyword additions.
2040 </para>
2041
2042 <para>
2043 In <literal>FROM</literal> items, both the standard and
2044 <productname>PostgreSQL</productname> allow <literal>AS</literal> to
2045 be omitted before an alias that is an unreserved keyword. But
2046 this is impractical for output column names, because of syntactic
2047 ambiguities.
2048 </para>
2049 </refsect2>
2050
2051 <refsect2>
2052 <title>Omitting Sub-<command>SELECT</command> Aliases in <literal>FROM</literal></title>
2053
2054 <para>
2055 According to the SQL standard, a sub-<command>SELECT</command> in the
2056 <literal>FROM</literal> list must have an alias. In
2057 <productname>PostgreSQL</productname>, this alias may be omitted.
2058 </para>
2059 </refsect2>
2060
2061 <refsect2>
2062 <title><literal>ONLY</literal> and Inheritance</title>
2063
2064 <para>
2065 The SQL standard requires parentheses around the table name when
2066 writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY
2067 (tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</productname>
2068 considers these parentheses to be optional.
2069 </para>
2070
2071 <para>
2072 <productname>PostgreSQL</productname> allows a trailing <literal>*</literal> to be written to
2073 explicitly specify the non-<literal>ONLY</literal> behavior of including
2074 child tables. The standard does not allow this.
2075 </para>
2076
2077 <para>
2078 (These points apply equally to all SQL commands supporting the
2079 <literal>ONLY</literal> option.)
2080 </para>
2081 </refsect2>
2082
2083 <refsect2>
2084 <title><literal>TABLESAMPLE</literal> Clause Restrictions</title>
2085
2086 <para>
2087 The <literal>TABLESAMPLE</literal> clause is currently accepted only on
2088 regular tables and materialized views. According to the SQL standard
2089 it should be possible to apply it to any <literal>FROM</literal> item.
2090 </para>
2091 </refsect2>
2092
2093 <refsect2>
2094 <title>Function Calls in <literal>FROM</literal></title>
2095
2096 <para>
2097 <productname>PostgreSQL</productname> allows a function call to be
2098 written directly as a member of the <literal>FROM</literal> list. In the SQL
2099 standard it would be necessary to wrap such a function call in a
2100 sub-<command>SELECT</command>; that is, the syntax
2101 <literal>FROM <replaceable>func</replaceable>(...) <replaceable>alias</replaceable></literal>
2102 is approximately equivalent to
2103 <literal>FROM LATERAL (SELECT <replaceable>func</replaceable>(...)) <replaceable>alias</replaceable></literal>.
2104 Note that <literal>LATERAL</literal> is considered to be implicit; this is
2105 because the standard requires <literal>LATERAL</literal> semantics for an
2106 <literal>UNNEST()</literal> item in <literal>FROM</literal>.
2107 <productname>PostgreSQL</productname> treats <literal>UNNEST()</literal> the
2108 same as other set-returning functions.
2109 </para>
2110 </refsect2>
2111
2112 <refsect2>
2113 <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
2114
2115 <para>
2116 In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
2117 only use output column names or numbers, while a <literal>GROUP
2118 BY</literal> clause can only use expressions based on input column
2119 names. <productname>PostgreSQL</productname> extends each of
2120 these clauses to allow the other choice as well (but it uses the
2121 standard's interpretation if there is ambiguity).
2122 <productname>PostgreSQL</productname> also allows both clauses to
2123 specify arbitrary expressions. Note that names appearing in an
2124 expression will always be taken as input-column names, not as
2125 output-column names.
2126 </para>
2127
2128 <para>
2129 SQL:1999 and later use a slightly different definition which is not
2130 entirely upward compatible with SQL-92.
2131 In most cases, however, <productname>PostgreSQL</productname>
2132 will interpret an <literal>ORDER BY</literal> or <literal>GROUP
2133 BY</literal> expression the same way SQL:1999 does.
2134 </para>
2135 </refsect2>
2136
2137 <refsect2>
2138 <title>Functional Dependencies</title>
2139
2140 <para>
2141 <productname>PostgreSQL</productname> recognizes functional dependency
2142 (allowing columns to be omitted from <literal>GROUP BY</literal>) only when
2143 a table's primary key is included in the <literal>GROUP BY</literal> list.
2144 The SQL standard specifies additional conditions that should be
2145 recognized.
2146 </para>
2147 </refsect2>
2148
2149 <refsect2>
2150 <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
2151
2152 <para>
2153 The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
2154 are <productname>PostgreSQL</productname>-specific syntax, also
2155 used by <productname>MySQL</productname>. The SQL:2008 standard
2156 has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
2157 ...</literal> for the same functionality, as shown above
2158 in <xref linkend="sql-limit"/>. This
2159 syntax is also used by <productname>IBM DB2</productname>.
2160 (Applications written for <productname>Oracle</productname>
2161 frequently use a workaround involving the automatically
2162 generated <literal>rownum</literal> column, which is not available in
2163 PostgreSQL, to implement the effects of these clauses.)
2164 </para>
2165 </refsect2>
2166
2167 <refsect2>
2168 <title><literal>FOR NO KEY UPDATE</literal>, <literal>FOR UPDATE</literal>, <literal>FOR SHARE</literal>, <literal>FOR KEY SHARE</literal></title>
2169
2170 <para>
2171 Although <literal>FOR UPDATE</literal> appears in the SQL standard, the
2172 standard allows it only as an option of <command>DECLARE CURSOR</command>.
2173 <productname>PostgreSQL</productname> allows it in any <command>SELECT</command>
2174 query as well as in sub-<command>SELECT</command>s, but this is an extension.
2175 The <literal>FOR NO KEY UPDATE</literal>, <literal>FOR SHARE</literal> and
2176 <literal>FOR KEY SHARE</literal> variants, as well as the <literal>NOWAIT</literal>
2177 and <literal>SKIP LOCKED</literal> options, do not appear in the
2178 standard.
2179 </para>
2180 </refsect2>
2181
2182 <refsect2>
2183 <title>Data-Modifying Statements in <literal>WITH</literal></title>
2184
2185 <para>
2186 <productname>PostgreSQL</productname> allows <command>INSERT</command>,
2187 <command>UPDATE</command>, <command>DELETE</command>, and
2188 <command>MERGE</command> to be used as <literal>WITH</literal>
2189 queries. This is not found in the SQL standard.
2190 </para>
2191 </refsect2>
2192
2193 <refsect2>
2194 <title>Nonstandard Clauses</title>
2195
2196 <para>
2197 <literal>DISTINCT ON ( ... )</literal> is an extension of the
2198 SQL standard.
2199 </para>
2200
2201 <para>
2202 <literal>ROWS FROM( ... )</literal> is an extension of the SQL standard.
2203 </para>
2204
2205 <para>
2206 The <literal>MATERIALIZED</literal> and <literal>NOT
2207 MATERIALIZED</literal> options of <literal>WITH</literal> are extensions
2208 of the SQL standard.
2209 </para>
2210 </refsect2>
2211
2212 </refsect1>
2213 </refentry>