2 doc/src/sgml/ref/select.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-SELECT">
8 <refentrytitle>SELECT</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>SELECT</refname>
15 <refname>TABLE</refname>
16 <refname>WITH</refname>
17 <refpurpose>retrieve rows from a table or view</refpurpose>
20 <indexterm zone="sql-select">
21 <primary>SELECT</primary>
24 <indexterm zone="sql-select">
25 <primary>TABLE command</primary>
28 <indexterm zone="sql-select">
29 <primary>WITH</primary>
30 <secondary>in SELECT</secondary>
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 <replaceable class="parameter">expression</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 ]
48 [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
50 <phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
52 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
53 [ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
54 <replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
55 [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]
56 [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
57 <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <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> [, ...] ) ]
59 <phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
61 <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <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> )
63 TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
69 <title>Description</title>
72 <command>SELECT</command> retrieves rows from zero or more tables.
73 The general processing of <command>SELECT</command> is as follows:
78 All queries in the <literal>WITH</literal> list are computed.
79 These effectively serve as temporary tables that can be referenced
80 in the <literal>FROM</literal> list. A <literal>WITH</literal> query
81 that is referenced more than once in <literal>FROM</literal> is
83 (See <xref linkend="sql-with" endterm="sql-with-title"> below.)
89 All elements in the <literal>FROM</literal> list are computed.
90 (Each element in the <literal>FROM</literal> list is a real or
91 virtual table.) If more than one element is specified in the
92 <literal>FROM</literal> list, they are cross-joined together.
93 (See <xref linkend="sql-from" endterm="sql-from-title"> below.)
99 If the <literal>WHERE</literal> clause is specified, all rows
100 that do not satisfy the condition are eliminated from the
101 output. (See <xref linkend="sql-where"
102 endterm="sql-where-title"> below.)
108 If the <literal>GROUP BY</literal> clause is specified, the
109 output is combined into groups of rows that match on one or more
110 values. If the <literal>HAVING</literal> clause is present, it
111 eliminates groups that do not satisfy the given condition. (See
112 <xref linkend="sql-groupby" endterm="sql-groupby-title"> and
113 <xref linkend="sql-having" endterm="sql-having-title"> below.)
119 The actual output rows are computed using the
120 <command>SELECT</command> output expressions for each selected
121 row or row group. (See
122 <xref linkend="sql-select-list" endterm="sql-select-list-title">
128 <para><literal>SELECT DISTINCT</literal> eliminates duplicate rows from the
129 result. <literal>SELECT DISTINCT ON</literal> eliminates rows that
130 match on all the specified expressions. <literal>SELECT ALL</literal>
131 (the default) will return all candidate rows, including
132 duplicates. (See <xref linkend="sql-distinct"
133 endterm="sql-distinct-title"> below.)
139 Using the operators <literal>UNION</literal>,
140 <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
141 output of more than one <command>SELECT</command> statement can
142 be combined to form a single result set. The
143 <literal>UNION</literal> operator returns all rows that are in
144 one or both of the result sets. The
145 <literal>INTERSECT</literal> operator returns all rows that are
146 strictly in both result sets. The <literal>EXCEPT</literal>
147 operator returns the rows that are in the first result set but
148 not in the second. In all three cases, duplicate rows are
149 eliminated unless <literal>ALL</literal> is specified. The noise
150 word <literal>DISTINCT</> can be added to explicitly specify
151 eliminating duplicate rows. Notice that <literal>DISTINCT</> is
152 the default behavior here, even though <literal>ALL</literal> is
153 the default for <command>SELECT</> itself. (See
154 <xref linkend="sql-union" endterm="sql-union-title">, <xref
155 linkend="sql-intersect" endterm="sql-intersect-title">, and
156 <xref linkend="sql-except" endterm="sql-except-title"> below.)
162 If the <literal>ORDER BY</literal> clause is specified, the
163 returned rows are sorted in the specified order. If
164 <literal>ORDER BY</literal> is not given, the rows are returned
165 in whatever order the system finds fastest to produce. (See
166 <xref linkend="sql-orderby" endterm="sql-orderby-title"> below.)
172 If the <literal>LIMIT</literal> (or <literal>FETCH FIRST</literal>) or <literal>OFFSET</literal>
173 clause is specified, the <command>SELECT</command> statement
174 only returns a subset of the result rows. (See <xref
175 linkend="sql-limit" endterm="sql-limit-title"> below.)
181 If <literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</literal>, <literal>FOR SHARE</literal>
182 or <literal>FOR KEY SHARE</literal>
184 <command>SELECT</command> statement locks the selected rows
185 against concurrent updates. (See <xref linkend="sql-for-update-share"
186 endterm="sql-for-update-share-title"> below.)
193 You must have <literal>SELECT</literal> privilege on each column used
194 in a <command>SELECT</> command. The use of <literal>FOR NO KEY UPDATE</>,
195 <literal>FOR UPDATE</literal>,
196 <literal>FOR SHARE</literal> or <literal>FOR KEY SHARE</literal> requires
197 <literal>UPDATE</literal> privilege as well (for at least one column
198 of each table so selected).
203 <title>Parameters</title>
205 <refsect2 id="SQL-WITH">
206 <title id="sql-with-title"><literal>WITH</literal> Clause</title>
209 The <literal>WITH</literal> clause allows you to specify one or more
210 subqueries that can be referenced by name in the primary query.
211 The subqueries effectively act as temporary tables or views
212 for the duration of the primary query.
213 Each subquery can be a <command>SELECT</command>, <command>VALUES</command>,
214 <command>INSERT</command>, <command>UPDATE</command> or
215 <command>DELETE</command> statement.
216 When writing a data-modifying statement (<command>INSERT</command>,
217 <command>UPDATE</command> or <command>DELETE</command>) in
218 <literal>WITH</>, it is usual to include a <literal>RETURNING</> clause.
219 It is the output of <literal>RETURNING</>, <emphasis>not</> the underlying
220 table that the statement modifies, that forms the temporary table that is
221 read by the primary query. If <literal>RETURNING</> is omitted, the
222 statement is still executed, but it produces no output so it cannot be
223 referenced as a table by the primary query.
227 A name (without schema qualification) must be specified for each
228 <literal>WITH</literal> query. Optionally, a list of column names
229 can be specified; if this is omitted,
230 the column names are inferred from the subquery.
234 If <literal>RECURSIVE</literal> is specified, it allows a
235 <command>SELECT</command> subquery to reference itself by name. Such a
236 subquery must have the form
238 <replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
240 where the recursive self-reference must appear on the right-hand
241 side of the <literal>UNION</>. Only one recursive self-reference
242 is permitted per query. Recursive data-modifying statements are not
243 supported, but you can use the results of a recursive
244 <command>SELECT</command> query in
245 a data-modifying statement. See <xref linkend="queries-with"> for
250 Another effect of <literal>RECURSIVE</literal> is that
251 <literal>WITH</literal> queries need not be ordered: a query
252 can reference another one that is later in the list. (However,
253 circular references, or mutual recursion, are not implemented.)
254 Without <literal>RECURSIVE</literal>, <literal>WITH</literal> queries
255 can only reference sibling <literal>WITH</literal> queries
256 that are earlier in the <literal>WITH</literal> list.
260 A key property of <literal>WITH</literal> queries is that they
261 are evaluated only once per execution of the primary query,
262 even if the primary query refers to them more than once.
263 In particular, data-modifying statements are guaranteed to be
264 executed once and only once, regardless of whether the primary query
265 reads all or any of their output.
269 The primary query and the <literal>WITH</literal> queries are all
270 (notionally) executed at the same time. This implies that the effects of
271 a data-modifying statement in <literal>WITH</literal> cannot be seen from
272 other parts of the query, other than by reading its <literal>RETURNING</>
273 output. If two such data-modifying statements attempt to modify the same
274 row, the results are unspecified.
278 See <xref linkend="queries-with"> for additional information.
282 <refsect2 id="SQL-FROM">
283 <title id="sql-from-title"><literal>FROM</literal> Clause</title>
286 The <literal>FROM</literal> clause specifies one or more source
287 tables for the <command>SELECT</command>. If multiple sources are
288 specified, the result is the Cartesian product (cross join) of all
289 the sources. But usually qualification conditions are added (via
290 <literal>WHERE</>) to restrict the returned rows to a small subset of the
295 The <literal>FROM</literal> clause can contain the following
300 <term><replaceable class="parameter">table_name</replaceable></term>
303 The name (optionally schema-qualified) of an existing table or view.
304 If <literal>ONLY</> is specified before the table name, only that
305 table is scanned. If <literal>ONLY</> is not specified, the table
306 and all its descendant tables (if any) are scanned. Optionally,
307 <literal>*</> can be specified after the table name to explicitly
308 indicate that descendant tables are included.
314 <term><replaceable class="parameter">alias</replaceable></term>
317 A substitute name for the <literal>FROM</> item containing the
318 alias. An alias is used for brevity or to eliminate ambiguity
319 for self-joins (where the same table is scanned multiple
320 times). When an alias is provided, it completely hides the
321 actual name of the table or function; for example given
322 <literal>FROM foo AS f</>, the remainder of the
323 <command>SELECT</command> must refer to this <literal>FROM</>
324 item as <literal>f</> not <literal>foo</>. If an alias is
325 written, a column alias list can also be written to provide
326 substitute names for one or more columns of the table.
332 <term><replaceable class="parameter">select</replaceable></term>
335 A sub-<command>SELECT</command> can appear in the
336 <literal>FROM</literal> clause. This acts as though its
337 output were created as a temporary table for the duration of
338 this single <command>SELECT</command> command. Note that the
339 sub-<command>SELECT</command> must be surrounded by
340 parentheses, and an alias <emphasis>must</emphasis> be
342 <xref linkend="sql-values"> command
343 can also be used here.
349 <term><replaceable class="parameter">with_query_name</replaceable></term>
352 A <literal>WITH</> query is referenced by writing its name,
353 just as though the query's name were a table name. (In fact,
354 the <literal>WITH</> query hides any real table of the same name
355 for the purposes of the primary query. If necessary, you can
356 refer to a real table of the same name by schema-qualifying
358 An alias can be provided in the same way as for a table.
364 <term><replaceable class="parameter">function_name</replaceable></term>
367 Function calls can appear in the <literal>FROM</literal>
368 clause. (This is especially useful for functions that return
369 result sets, but any function can be used.) This acts as
370 though its output were created as a temporary table for the
371 duration of this single <command>SELECT</command> command. An
372 alias can also be used. If an alias is written, a column alias
373 list can also be written to provide substitute names for one
374 or more attributes of the function's composite return type. If
375 the function has been defined as returning the <type>record</>
376 data type, then an alias or the key word <literal>AS</> must
377 be present, followed by a column definition list in the form
378 <literal>( <replaceable
379 class="parameter">column_name</replaceable> <replaceable
380 class="parameter">data_type</replaceable> <optional>, ... </>
381 )</literal>. The column definition list must match the actual
382 number and types of columns returned by the function.
388 <term><replaceable class="parameter">join_type</replaceable></term>
394 <para><literal>[ INNER ] JOIN</literal></para>
397 <para><literal>LEFT [ OUTER ] JOIN</literal></para>
400 <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
403 <para><literal>FULL [ OUTER ] JOIN</literal></para>
406 <para><literal>CROSS JOIN</literal></para>
410 For the <literal>INNER</> and <literal>OUTER</> join types, a
411 join condition must be specified, namely exactly one of
412 <literal>NATURAL</>, <literal>ON <replaceable
413 class="parameter">join_condition</replaceable></literal>, or
414 <literal>USING (<replaceable
415 class="parameter">join_column</replaceable> [, ...])</literal>.
416 See below for the meaning. For <literal>CROSS JOIN</literal>,
417 none of these clauses can appear.
421 A <literal>JOIN</literal> clause combines two <literal>FROM</>
422 items, which for convenience we will refer to as <quote>tables</>,
423 though in reality they can be any type of <literal>FROM</> item.
424 Use parentheses if necessary to determine the order of nesting.
425 In the absence of parentheses, <literal>JOIN</literal>s nest
426 left-to-right. In any case <literal>JOIN</literal> binds more
427 tightly than the commas separating <literal>FROM</>-list items.
430 <para><literal>CROSS JOIN</> and <literal>INNER JOIN</literal>
431 produce a simple Cartesian product, the same result as you get from
432 listing the two tables at the top level of <literal>FROM</>,
433 but restricted by the join condition (if any).
434 <literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON
435 (TRUE)</>, that is, no rows are removed by qualification.
436 These join types are just a notational convenience, since they
437 do nothing you couldn't do with plain <literal>FROM</> and
441 <para><literal>LEFT OUTER JOIN</> returns all rows in the qualified
442 Cartesian product (i.e., all combined rows that pass its join
443 condition), plus one copy of each row in the left-hand table
444 for which there was no right-hand row that passed the join
445 condition. This left-hand row is extended to the full width
446 of the joined table by inserting null values for the
447 right-hand columns. Note that only the <literal>JOIN</>
448 clause's own condition is considered while deciding which rows
449 have matches. Outer conditions are applied afterwards.
453 Conversely, <literal>RIGHT OUTER JOIN</> returns all the
454 joined rows, plus one row for each unmatched right-hand row
455 (extended with nulls on the left). This is just a notational
456 convenience, since you could convert it to a <literal>LEFT
457 OUTER JOIN</> by switching the left and right tables.
460 <para><literal>FULL OUTER JOIN</> returns all the joined rows, plus
461 one row for each unmatched left-hand row (extended with nulls
462 on the right), plus one row for each unmatched right-hand row
463 (extended with nulls on the left).
469 <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>
471 <para><replaceable class="parameter">join_condition</replaceable> is
472 an expression resulting in a value of type
473 <type>boolean</type> (similar to a <literal>WHERE</literal>
474 clause) that specifies which rows in a join are considered to
481 <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] )</literal></term>
484 A clause of the form <literal>USING ( a, b, ... )</literal> is
485 shorthand for <literal>ON left_table.a = right_table.a AND
486 left_table.b = right_table.b ...</literal>. Also,
487 <literal>USING</> implies that only one of each pair of
488 equivalent columns will be included in the join output, not
495 <term><literal>NATURAL</literal></term>
497 <para><literal>NATURAL</literal> is shorthand for a
498 <literal>USING</> list that mentions all columns in the two
499 tables that have the same names.
505 <term><literal>LATERAL</literal></term>
507 <para>The <literal>LATERAL</literal> key word can precede a
508 sub-<command>SELECT</command> or function-call <literal>FROM</>
509 item. This allows the sub-<command>SELECT</command> or function
510 expression to refer to columns of <literal>FROM</> items that appear
511 before it in the <literal>FROM</> list. (Without
512 <literal>LATERAL</literal>, each <literal>FROM</> item is evaluated
513 independently and so cannot cross-reference any other
514 <literal>FROM</> item.) A <literal>LATERAL</literal> item can
515 appear at top level in the <literal>FROM</> list, or within a
516 <literal>JOIN</> tree; in the latter case it can also refer to any
517 items that are on the left-hand side of a <literal>JOIN</> that it is
518 on the right-hand side of.
522 When a <literal>FROM</> item contains <literal>LATERAL</literal>
523 cross-references, evaluation proceeds as follows: for each row of the
524 <literal>FROM</> item providing the cross-referenced column(s), or
525 set of rows of multiple <literal>FROM</> items providing the
526 columns, the <literal>LATERAL</literal> item is evaluated using that
527 row or row set's values of the columns. The resulting row(s) are
528 joined as usual with the rows they were computed from. This is
529 repeated for each row or set of rows from the column source table(s).
533 The column source table(s) must be <literal>INNER</> or
534 <literal>LEFT</> joined to the <literal>LATERAL</literal> item, else
535 there would not be a well-defined set of rows from which to compute
536 each set of rows for the <literal>LATERAL</literal> item. Thus,
537 although a construct such as <literal><replaceable>X</> RIGHT JOIN
538 LATERAL <replaceable>Y</></literal> is syntactically valid, it is
539 not actually allowed for <replaceable>Y</> to reference
548 <refsect2 id="SQL-WHERE">
549 <title id="sql-where-title"><literal>WHERE</literal> Clause</title>
552 The optional <literal>WHERE</literal> clause has the general form
554 WHERE <replaceable class="parameter">condition</replaceable>
556 where <replaceable class="parameter">condition</replaceable> is
557 any expression that evaluates to a result of type
558 <type>boolean</type>. Any row that does not satisfy this
559 condition will be eliminated from the output. A row satisfies the
560 condition if it returns true when the actual row values are
561 substituted for any variable references.
565 <refsect2 id="SQL-GROUPBY">
566 <title id="sql-groupby-title"><literal>GROUP BY</literal> Clause</title>
569 The optional <literal>GROUP BY</literal> clause has the general form
571 GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
576 <literal>GROUP BY</literal> will condense into a single row all
577 selected rows that share the same values for the grouped
578 expressions. <replaceable
579 class="parameter">expression</replaceable> can be an input column
580 name, or the name or ordinal number of an output column
581 (<command>SELECT</command> list item), or an arbitrary
582 expression formed from input-column values. In case of ambiguity,
583 a <literal>GROUP BY</literal> name will be interpreted as an
584 input-column name rather than an output column name.
588 Aggregate functions, if any are used, are computed across all rows
589 making up each group, producing a separate value for each group
590 (whereas without <literal>GROUP BY</literal>, an aggregate
591 produces a single value computed across all the selected rows).
592 When <literal>GROUP BY</literal> is present, it is not valid for
593 the <command>SELECT</command> list expressions to refer to
594 ungrouped columns except within aggregate functions or if the
595 ungrouped column is functionally dependent on the grouped columns,
596 since there would otherwise be more than one possible value to
597 return for an ungrouped column. A functional dependency exists if
598 the grouped columns (or a subset thereof) are the primary key of
599 the table containing the ungrouped column.
603 <refsect2 id="SQL-HAVING">
604 <title id="sql-having-title"><literal>HAVING</literal> Clause</title>
607 The optional <literal>HAVING</literal> clause has the general form
609 HAVING <replaceable class="parameter">condition</replaceable>
611 where <replaceable class="parameter">condition</replaceable> is
612 the same as specified for the <literal>WHERE</literal> clause.
616 <literal>HAVING</literal> eliminates group rows that do not
617 satisfy the condition. <literal>HAVING</literal> is different
618 from <literal>WHERE</literal>: <literal>WHERE</literal> filters
619 individual rows before the application of <literal>GROUP
620 BY</literal>, while <literal>HAVING</literal> filters group rows
621 created by <literal>GROUP BY</literal>. Each column referenced in
622 <replaceable class="parameter">condition</replaceable> must
623 unambiguously reference a grouping column, unless the reference
624 appears within an aggregate function.
628 The presence of <literal>HAVING</literal> turns a query into a grouped
629 query even if there is no <literal>GROUP BY</> clause. This is the
630 same as what happens when the query contains aggregate functions but
631 no <literal>GROUP BY</> clause. All the selected rows are considered to
632 form a single group, and the <command>SELECT</command> list and
633 <literal>HAVING</literal> clause can only reference table columns from
634 within aggregate functions. Such a query will emit a single row if the
635 <literal>HAVING</literal> condition is true, zero rows if it is not true.
639 <refsect2 id="SQL-WINDOW">
640 <title id="sql-window-title"><literal>WINDOW</literal> Clause</title>
643 The optional <literal>WINDOW</literal> clause has the general form
645 WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...]
647 where <replaceable class="parameter">window_name</replaceable> is
648 a name that can be referenced from subsequent window definitions or
649 <literal>OVER</> clauses, and
650 <replaceable class="parameter">window_definition</replaceable> is
652 [ <replaceable class="parameter">existing_window_name</replaceable> ]
653 [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
654 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
655 [ <replaceable class="parameter">frame_clause</replaceable> ]
660 If an <replaceable class="parameter">existing_window_name</replaceable>
661 is specified it must refer to an earlier entry in the <literal>WINDOW</>
662 list; the new window copies its partitioning clause from that entry,
663 as well as its ordering clause if any. In this case the new window cannot
664 specify its own <literal>PARTITION BY</> clause, and it can specify
665 <literal>ORDER BY</> only if the copied window does not have one.
666 The new window always uses its own frame clause; the copied window
667 must not specify a frame clause.
671 The elements of the <literal>PARTITION BY</> list are interpreted in
672 much the same fashion as elements of a
673 <xref linkend="sql-groupby" endterm="sql-groupby-title">, except that
674 they are always simple expressions and never the name or number of an
676 Another difference is that these expressions can contain aggregate
677 function calls, which are not allowed in a regular <literal>GROUP BY</>
678 clause. They are allowed here because windowing occurs after grouping
683 Similarly, the elements of the <literal>ORDER BY</> list are interpreted
684 in much the same fashion as elements of an
685 <xref linkend="sql-orderby" endterm="sql-orderby-title">, except that
686 the expressions are always taken as simple expressions and never the name
687 or number of an output column.
691 The optional <replaceable class="parameter">frame_clause</> defines
692 the <firstterm>window frame</> for window functions that depend on the
693 frame (not all do). The window frame is a set of related rows for
694 each row of the query (called the <firstterm>current row</>).
695 The <replaceable class="parameter">frame_clause</> can be one of
698 [ RANGE | ROWS ] <replaceable>frame_start</>
699 [ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
702 where <replaceable>frame_start</> and <replaceable>frame_end</> can be
707 <replaceable>value</replaceable> PRECEDING
709 <replaceable>value</replaceable> FOLLOWING
713 If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
714 ROW</>. Restrictions are that
715 <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
716 <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
717 and the <replaceable>frame_end</> choice cannot appear earlier in the
718 above list than the <replaceable>frame_start</> choice — for example
719 <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
720 PRECEDING</literal> is not allowed.
724 The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
725 which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
726 CURRENT ROW</>; it sets the frame to be all rows from the partition start
727 up through the current row's last peer in the <literal>ORDER BY</>
728 ordering (which means all rows if there is no <literal>ORDER BY</>).
729 In general, <literal>UNBOUNDED PRECEDING</> means that the frame
730 starts with the first row of the partition, and similarly
731 <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
732 row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
733 mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</>
734 means that the frame starts or ends with the current row; but in
735 <literal>RANGE</> mode it means that the frame starts or ends with
736 the current row's first or last peer in the <literal>ORDER BY</> ordering.
737 The <replaceable>value</> <literal>PRECEDING</> and
738 <replaceable>value</> <literal>FOLLOWING</> cases are currently only
739 allowed in <literal>ROWS</> mode. They indicate that the frame starts
740 or ends with the row that many rows before or after the current row.
741 <replaceable>value</replaceable> must be an integer expression not
742 containing any variables, aggregate functions, or window functions.
743 The value must not be null or negative; but it can be zero, which
744 selects the current row itself.
748 Beware that the <literal>ROWS</> options can produce unpredictable
749 results if the <literal>ORDER BY</> ordering does not order the rows
750 uniquely. The <literal>RANGE</> options are designed to ensure that
751 rows that are peers in the <literal>ORDER BY</> ordering are treated
752 alike; any two peer rows will be both in or both not in the frame.
756 The purpose of a <literal>WINDOW</literal> clause is to specify the
757 behavior of <firstterm>window functions</> appearing in the query's
758 <xref linkend="sql-select-list" endterm="sql-select-list-title"> or
759 <xref linkend="sql-orderby" endterm="sql-orderby-title">. These functions
760 can reference the <literal>WINDOW</literal> clause entries by name
761 in their <literal>OVER</> clauses. A <literal>WINDOW</literal> clause
762 entry does not have to be referenced anywhere, however; if it is not
763 used in the query it is simply ignored. It is possible to use window
764 functions without any <literal>WINDOW</literal> clause at all, since
765 a window function call can specify its window definition directly in
766 its <literal>OVER</> clause. However, the <literal>WINDOW</literal>
767 clause saves typing when the same window definition is needed for more
768 than one window function.
772 Window functions are described in detail in
773 <xref linkend="tutorial-window">,
774 <xref linkend="syntax-window-functions">, and
775 <xref linkend="queries-window">.
779 <refsect2 id="sql-select-list">
780 <title id="sql-select-list-title"><command>SELECT</command> List</title>
783 The <command>SELECT</command> list (between the key words
784 <literal>SELECT</> and <literal>FROM</>) specifies expressions
785 that form the output rows of the <command>SELECT</command>
786 statement. The expressions can (and usually do) refer to columns
787 computed in the <literal>FROM</> clause.
791 Just as in a table, every output column of a <command>SELECT</command>
792 has a name. In a simple <command>SELECT</command> this name is just
793 used to label the column for display, but when the <command>SELECT</>
794 is a sub-query of a larger query, the name is seen by the larger query
795 as the column name of the virtual table produced by the sub-query.
796 To specify the name to use for an output column, write
797 <literal>AS</> <replaceable class="parameter">output_name</replaceable>
798 after the column's expression. (You can omit <literal>AS</literal>,
799 but only if the desired output name does not match any
800 <productname>PostgreSQL</productname> keyword (see <xref
801 linkend="sql-keywords-appendix">). For protection against possible
802 future keyword additions, it is recommended that you always either
803 write <literal>AS</literal> or double-quote the output name.)
804 If you do not specify a column name, a name is chosen automatically
805 by <productname>PostgreSQL</productname>. If the column's expression
806 is a simple column reference then the chosen name is the same as that
807 column's name. In more complex cases a function or type name may be
808 used, or the system may fall back on a generated name such as
809 <literal>?column?</literal>.
813 An output column's name can be used to refer to the column's value in
814 <literal>ORDER BY</> and <literal>GROUP BY</> clauses, but not in the
815 <literal>WHERE</> or <literal>HAVING</> clauses; there you must write
816 out the expression instead.
820 Instead of an expression, <literal>*</literal> can be written in
821 the output list as a shorthand for all the columns of the selected
822 rows. Also, you can write <literal><replaceable
823 class="parameter">table_name</replaceable>.*</literal> as a
824 shorthand for the columns coming from just that table. In these
825 cases it is not possible to specify new names with <literal>AS</>;
826 the output column names will be the same as the table columns' names.
830 <refsect2 id="sql-distinct">
831 <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
834 If <literal>SELECT DISTINCT</> is specified, all duplicate rows are
835 removed from the result set (one row is kept from each group of
836 duplicates). <literal>SELECT ALL</> specifies the opposite: all rows are
837 kept; that is the default.
841 <literal>SELECT DISTINCT ON ( <replaceable
842 class="parameter">expression</replaceable> [, ...] )</literal>
843 keeps only the first row of each set of rows where the given
844 expressions evaluate to equal. The <literal>DISTINCT ON</literal>
845 expressions are interpreted using the same rules as for
846 <literal>ORDER BY</> (see above). Note that the <quote>first
847 row</quote> of each set is unpredictable unless <literal>ORDER
848 BY</> is used to ensure that the desired row appears first. For
851 SELECT DISTINCT ON (location) location, time, report
853 ORDER BY location, time DESC;
855 retrieves the most recent weather report for each location. But
856 if we had not used <literal>ORDER BY</> to force descending order
857 of time values for each location, we'd have gotten a report from
858 an unpredictable time for each location.
862 The <literal>DISTINCT ON</> expression(s) must match the leftmost
863 <literal>ORDER BY</> expression(s). The <literal>ORDER BY</> clause
864 will normally contain additional expression(s) that determine the
865 desired precedence of rows within each <literal>DISTINCT ON</> group.
869 <refsect2 id="SQL-UNION">
870 <title id="sql-union-title"><literal>UNION</literal> Clause</title>
873 The <literal>UNION</literal> clause has this general form:
875 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
876 </synopsis><replaceable class="parameter">select_statement</replaceable> is
877 any <command>SELECT</command> statement without an <literal>ORDER
878 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
879 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
880 (<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
881 subexpression if it is enclosed in parentheses. Without
882 parentheses, these clauses will be taken to apply to the result of
883 the <literal>UNION</literal>, not to its right-hand input
888 The <literal>UNION</literal> operator computes the set union of
889 the rows returned by the involved <command>SELECT</command>
890 statements. A row is in the set union of two result sets if it
891 appears in at least one of the result sets. The two
892 <command>SELECT</command> statements that represent the direct
893 operands of the <literal>UNION</literal> must produce the same
894 number of columns, and corresponding columns must be of compatible
899 The result of <literal>UNION</> does not contain any duplicate
900 rows unless the <literal>ALL</> option is specified.
901 <literal>ALL</> prevents elimination of duplicates. (Therefore,
902 <literal>UNION ALL</> is usually significantly quicker than
903 <literal>UNION</>; use <literal>ALL</> when you can.)
904 <literal>DISTINCT</> can be written to explicitly specify the
905 default behavior of eliminating duplicate rows.
909 Multiple <literal>UNION</> operators in the same
910 <command>SELECT</command> statement are evaluated left to right,
911 unless otherwise indicated by parentheses.
915 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
916 <literal>FOR KEY SHARE</> cannot be
917 specified either for a <literal>UNION</> result or for any input of a
922 <refsect2 id="SQL-INTERSECT">
923 <title id="sql-intersect-title"><literal>INTERSECT</literal> Clause</title>
926 The <literal>INTERSECT</literal> clause has this general form:
928 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
929 </synopsis><replaceable class="parameter">select_statement</replaceable> is
930 any <command>SELECT</command> statement without an <literal>ORDER
931 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
932 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</> clause.
936 The <literal>INTERSECT</literal> operator computes the set
937 intersection of the rows returned by the involved
938 <command>SELECT</command> statements. A row is in the
939 intersection of two result sets if it appears in both result sets.
943 The result of <literal>INTERSECT</literal> does not contain any
944 duplicate rows unless the <literal>ALL</> option is specified.
945 With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
946 left table and <replaceable>n</> duplicates in the right table will appear
947 min(<replaceable>m</>,<replaceable>n</>) times in the result set.
948 <literal>DISTINCT</> can be written to explicitly specify the
949 default behavior of eliminating duplicate rows.
953 Multiple <literal>INTERSECT</literal> operators in the same
954 <command>SELECT</command> statement are evaluated left to right,
955 unless parentheses dictate otherwise.
956 <literal>INTERSECT</literal> binds more tightly than
957 <literal>UNION</literal>. That is, <literal>A UNION B INTERSECT
958 C</literal> will be read as <literal>A UNION (B INTERSECT
963 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
964 <literal>FOR KEY SHARE</> cannot be
965 specified either for an <literal>INTERSECT</> result or for any input of
966 an <literal>INTERSECT</>.
970 <refsect2 id="SQL-EXCEPT">
971 <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>
974 The <literal>EXCEPT</literal> clause has this general form:
976 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
977 </synopsis><replaceable class="parameter">select_statement</replaceable> is
978 any <command>SELECT</command> statement without an <literal>ORDER
979 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
980 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</> clause.
984 The <literal>EXCEPT</literal> operator computes the set of rows
985 that are in the result of the left <command>SELECT</command>
986 statement but not in the result of the right one.
990 The result of <literal>EXCEPT</literal> does not contain any
991 duplicate rows unless the <literal>ALL</> option is specified.
992 With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
993 left table and <replaceable>n</> duplicates in the right table will appear
994 max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
995 <literal>DISTINCT</> can be written to explicitly specify the
996 default behavior of eliminating duplicate rows.
1000 Multiple <literal>EXCEPT</literal> operators in the same
1001 <command>SELECT</command> statement are evaluated left to right,
1002 unless parentheses dictate otherwise. <literal>EXCEPT</> binds at
1003 the same level as <literal>UNION</>.
1007 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
1008 <literal>FOR KEY SHARE</> cannot be
1009 specified either for an <literal>EXCEPT</> result or for any input of
1010 an <literal>EXCEPT</>.
1014 <refsect2 id="SQL-ORDERBY">
1015 <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>
1018 The optional <literal>ORDER BY</literal> clause has this general form:
1020 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
1022 The <literal>ORDER BY</literal> clause causes the result rows to
1023 be sorted according to the specified expression(s). If two rows are
1024 equal according to the leftmost expression, they are compared
1025 according to the next expression and so on. If they are equal
1026 according to all specified expressions, they are returned in
1027 an implementation-dependent order.
1031 Each <replaceable class="parameter">expression</replaceable> can be the
1032 name or ordinal number of an output column
1033 (<command>SELECT</command> list item), or it can be an arbitrary
1034 expression formed from input-column values.
1038 The ordinal number refers to the ordinal (left-to-right) position
1039 of the output column. This feature makes it possible to define an
1040 ordering on the basis of a column that does not have a unique
1041 name. This is never absolutely necessary because it is always
1042 possible to assign a name to an output column using the
1043 <literal>AS</> clause.
1047 It is also possible to use arbitrary expressions in the
1048 <literal>ORDER BY</literal> clause, including columns that do not
1049 appear in the <command>SELECT</command> output list. Thus the
1050 following statement is valid:
1052 SELECT name FROM distributors ORDER BY code;
1054 A limitation of this feature is that an <literal>ORDER BY</>
1055 clause applying to the result of a <literal>UNION</>,
1056 <literal>INTERSECT</>, or <literal>EXCEPT</> clause can only
1057 specify an output column name or number, not an expression.
1061 If an <literal>ORDER BY</> expression is a simple name that
1062 matches both an output column name and an input column name,
1063 <literal>ORDER BY</> will interpret it as the output column name.
1064 This is the opposite of the choice that <literal>GROUP BY</> will
1065 make in the same situation. This inconsistency is made to be
1066 compatible with the SQL standard.
1070 Optionally one can add the key word <literal>ASC</> (ascending) or
1071 <literal>DESC</> (descending) after any expression in the
1072 <literal>ORDER BY</> clause. If not specified, <literal>ASC</> is
1073 assumed by default. Alternatively, a specific ordering operator
1074 name can be specified in the <literal>USING</> clause.
1075 An ordering operator must be a less-than or greater-than
1076 member of some B-tree operator family.
1077 <literal>ASC</> is usually equivalent to <literal>USING <</> and
1078 <literal>DESC</> is usually equivalent to <literal>USING ></>.
1079 (But the creator of a user-defined data type can define exactly what the
1080 default sort ordering is, and it might correspond to operators with other
1085 If <literal>NULLS LAST</> is specified, null values sort after all
1086 non-null values; if <literal>NULLS FIRST</> is specified, null values
1087 sort before all non-null values. If neither is specified, the default
1088 behavior is <literal>NULLS LAST</> when <literal>ASC</> is specified
1089 or implied, and <literal>NULLS FIRST</> when <literal>DESC</> is specified
1090 (thus, the default is to act as though nulls are larger than non-nulls).
1091 When <literal>USING</> is specified, the default nulls ordering depends
1092 on whether the operator is a less-than or greater-than operator.
1096 Note that ordering options apply only to the expression they follow;
1097 for example <literal>ORDER BY x, y DESC</> does not mean
1098 the same thing as <literal>ORDER BY x DESC, y DESC</>.
1102 Character-string data is sorted according to the collation that applies
1103 to the column being sorted. That can be overridden at need by including
1104 a <literal>COLLATE</> clause in the
1105 <replaceable class="parameter">expression</replaceable>, for example
1106 <literal>ORDER BY mycolumn COLLATE "en_US"</>.
1107 For more information see <xref linkend="sql-syntax-collate-exprs"> and
1108 <xref linkend="collation">.
1112 <refsect2 id="SQL-LIMIT">
1113 <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
1116 The <literal>LIMIT</literal> clause consists of two independent
1119 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
1120 OFFSET <replaceable class="parameter">start</replaceable>
1122 <replaceable class="parameter">count</replaceable> specifies the
1123 maximum number of rows to return, while <replaceable
1124 class="parameter">start</replaceable> specifies the number of rows
1125 to skip before starting to return rows. When both are specified,
1126 <replaceable class="parameter">start</replaceable> rows are skipped
1127 before starting to count the <replaceable
1128 class="parameter">count</replaceable> rows to be returned.
1132 If the <replaceable class="parameter">count</replaceable> expression
1133 evaluates to NULL, it is treated as <literal>LIMIT ALL</>, i.e., no
1134 limit. If <replaceable class="parameter">start</replaceable> evaluates
1135 to NULL, it is treated the same as <literal>OFFSET 0</>.
1139 SQL:2008 introduced a different syntax to achieve the same result,
1140 which <productname>PostgreSQL</> also supports. It is:
1142 OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
1143 FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
1145 In this syntax, to write anything except a simple integer constant for
1146 <replaceable class="parameter">start</> or <replaceable
1147 class="parameter">count</replaceable>, you must write parentheses
1149 If <replaceable class="parameter">count</> is
1150 omitted in a <literal>FETCH</> clause, it defaults to 1.
1151 <literal>ROW</literal>
1152 and <literal>ROWS</literal> as well as <literal>FIRST</literal>
1153 and <literal>NEXT</literal> are noise words that don't influence
1154 the effects of these clauses.
1155 According to the standard, the <literal>OFFSET</literal> clause must come
1156 before the <literal>FETCH</literal> clause if both are present; but
1157 <productname>PostgreSQL</> is laxer and allows either order.
1161 When using <literal>LIMIT</>, it is a good idea to use an
1162 <literal>ORDER BY</> clause that constrains the result rows into a
1163 unique order. Otherwise you will get an unpredictable subset of
1164 the query's rows — you might be asking for the tenth through
1165 twentieth rows, but tenth through twentieth in what ordering? You
1166 don't know what ordering unless you specify <literal>ORDER BY</>.
1170 The query planner takes <literal>LIMIT</> into account when
1171 generating a query plan, so you are very likely to get different
1172 plans (yielding different row orders) depending on what you use
1173 for <literal>LIMIT</> and <literal>OFFSET</>. Thus, using
1174 different <literal>LIMIT</>/<literal>OFFSET</> values to select
1175 different subsets of a query result <emphasis>will give
1176 inconsistent results</emphasis> unless you enforce a predictable
1177 result ordering with <literal>ORDER BY</>. This is not a bug; it
1178 is an inherent consequence of the fact that SQL does not promise
1179 to deliver the results of a query in any particular order unless
1180 <literal>ORDER BY</> is used to constrain the order.
1184 It is even possible for repeated executions of the same <literal>LIMIT</>
1185 query to return different subsets of the rows of a table, if there
1186 is not an <literal>ORDER BY</> to enforce selection of a deterministic
1187 subset. Again, this is not a bug; determinism of the results is
1188 simply not guaranteed in such a case.
1192 <refsect2 id="SQL-FOR-UPDATE-SHARE">
1193 <title id="sql-for-update-share-title"><literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</>/<literal>FOR SHARE</>/<literal>FOR KEY SHARE</> Clauses</title>
1196 <literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</>
1197 and <literal>FOR KEY SHARE</>
1198 are <firstterm>locking clauses</>; they affect how <literal>SELECT</>
1199 locks rows as they are obtained from the table.
1203 The <literal>FOR UPDATE</literal> clause has this form:
1205 FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1210 The <literal>FOR NO KEY UPDATE</literal> clause has this form:
1212 FOR NO KEY UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1217 The closely related <literal>FOR SHARE</literal> clause has this form:
1219 FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1224 Similarly, the <literal>FOR KEY SHARE</> clause has this form:
1226 FOR KEY SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1231 <literal>FOR UPDATE</literal> causes the rows retrieved by the
1232 <command>SELECT</command> statement to be locked as though for
1233 update. This prevents them from being modified or deleted by
1234 other transactions until the current transaction ends. That is,
1235 other transactions that attempt <command>UPDATE</command>,
1236 <command>DELETE</command>,
1237 <command>SELECT FOR UPDATE</command>,
1238 <command>SELECT FOR SHARE</command> or
1239 <command>SELECT FOR KEY SHARE</command>
1240 of these rows will be blocked until the current transaction ends.
1241 The <literal>FOR UPDATE</> lock mode
1242 is also acquired by any <command>DELETE</> on a row, and also by an
1243 <command>UPDATE</> that modifies the values on certain columns. Currently,
1244 the set of columns considered for the <command>UPDATE</> case are those that
1245 have an unique index on them that can be used in a foreign key (so partial
1246 indexes and expressional indexes are not considered), but this may change
1248 Also, if an <command>UPDATE</command>, <command>DELETE</command>,
1249 or <command>SELECT FOR UPDATE</command> from another transaction
1250 has already locked a selected row or rows, <command>SELECT FOR
1251 UPDATE</command> will wait for the other transaction to complete,
1252 and will then lock and return the updated row (or no row, if the
1253 row was deleted). Within a <literal>REPEATABLE READ</> or <literal>SERIALIZABLE</> transaction,
1254 however, an error will be thrown if a row to be locked has changed
1255 since the transaction started. For further discussion see <xref
1260 <literal>FOR NO KEY UPDATE</> behaves similarly, except that the lock
1261 acquired is weaker: this lock will not block
1262 <literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
1263 a lock on the same rows.
1267 <literal>FOR SHARE</literal> behaves similarly, except that it
1268 acquires a shared rather than exclusive lock on each retrieved
1269 row. A shared lock blocks other transactions from performing
1270 <command>UPDATE</command>, <command>DELETE</command>, or <command>SELECT
1271 FOR UPDATE</command> on these rows, but it does not prevent them
1272 from performing <command>SELECT FOR SHARE</command> or
1273 <command>SELECT FOR KEY SHARE</command>.
1277 <literal>FOR KEY SHARE</> behaves similarly to <literal>FOR SHARE</literal>,
1278 except that the lock
1279 is weaker: <literal>SELECT FOR UPDATE</> is blocked, but
1280 not <literal>SELECT FOR NO KEY UPDATE</>. A key-shared
1281 lock blocks other transactions from performing <command>DELETE</command>
1282 or any <command>UPDATE</command> that changes the key values, but not
1283 other <command>UPDATE</>, and neither it does prevent
1284 <command>SELECT FOR UPDATE</>, <command>SELECT FOR SHARE</>, or
1285 <command>SELECT FOR KEY SHARE</>.
1289 To prevent the operation from waiting for other transactions to commit,
1290 use the <literal>NOWAIT</> option. With <literal>NOWAIT</>, the statement
1291 reports an error, rather than waiting, if a selected row
1292 cannot be locked immediately. Note that <literal>NOWAIT</> applies only
1293 to the row-level lock(s) — the required <literal>ROW SHARE</literal>
1294 table-level lock is still taken in the ordinary way (see
1295 <xref linkend="mvcc">). You can use
1296 <xref linkend="sql-lock">
1297 with the <literal>NOWAIT</> option first,
1298 if you need to acquire the table-level lock without waiting.
1302 If specific tables are named in a locking clause,
1303 then only rows coming from those tables are locked; any other
1304 tables used in the <command>SELECT</command> are simply read as
1306 clause without a table list affects all tables used in the statement.
1307 If a locking clause is
1308 applied to a view or sub-query, it affects all tables used in
1309 the view or sub-query.
1310 However, these clauses
1311 do not apply to <literal>WITH</> queries referenced by the primary query.
1312 If you want row locking to occur within a <literal>WITH</> query, specify
1313 a locking clause within the <literal>WITH</> query.
1318 clauses can be written if it is necessary to specify different locking
1319 behavior for different tables. If the same table is mentioned (or
1320 implicitly affected) by more than one locking clause,
1321 then it is processed as if it was only specified by the strongest one.
1322 Similarly, a table is processed
1323 as <literal>NOWAIT</> if that is specified in any of the clauses
1328 The locking clauses cannot be
1329 used in contexts where returned rows cannot be clearly identified with
1330 individual table rows; for example they cannot be used with aggregation.
1334 When a locking clause
1335 appears at the top level of a <command>SELECT</> query, the rows that
1336 are locked are exactly those that are returned by the query; in the
1337 case of a join query, the rows locked are those that contribute to
1338 returned join rows. In addition, rows that satisfied the query
1339 conditions as of the query snapshot will be locked, although they
1340 will not be returned if they were updated after the snapshot
1341 and no longer satisfy the query conditions. If a
1342 <literal>LIMIT</> is used, locking stops
1343 once enough rows have been returned to satisfy the limit (but note that
1344 rows skipped over by <literal>OFFSET</> will get locked). Similarly,
1346 is used in a cursor's query, only rows actually fetched or stepped past
1347 by the cursor will be locked.
1351 When a locking clause
1352 appears in a sub-<command>SELECT</>, the rows locked are those
1353 returned to the outer query by the sub-query. This might involve
1354 fewer rows than inspection of the sub-query alone would suggest,
1355 since conditions from the outer query might be used to optimize
1356 execution of the sub-query. For example,
1358 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
1360 will lock only rows having <literal>col1 = 5</>, even though that
1361 condition is not textually within the sub-query.
1365 Previous releases failed to preserve a lock which is upgraded by a later
1366 savepoint. For example, this code:
1369 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
1371 UPDATE mytable SET ... WHERE key = 1;
1374 would fail to preserve the <literal>FOR UPDATE</> lock after the
1375 <command>ROLLBACK</>. This has been fixed in release 9.2.
1380 It is possible for a <command>SELECT</> command running at the <literal>READ
1381 COMMITTED</literal> transaction isolation level and using <literal>ORDER
1382 BY</literal> and a locking clause to return rows out of
1383 order. This is because <literal>ORDER BY</> is applied first.
1384 The command sorts the result, but might then block trying to obtain a lock
1385 on one or more of the rows. Once the <literal>SELECT</> unblocks, some
1386 of the ordering column values might have been modified, leading to those
1387 rows appearing to be out of order (though they are in order in terms
1388 of the original column values). This can be worked around at need by
1389 placing the <literal>FOR UPDATE/SHARE</literal> clause in a sub-query,
1392 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
1394 Note that this will result in locking all rows of <structname>mytable</>,
1395 whereas <literal>FOR UPDATE</> at the top level would lock only the
1396 actually returned rows. This can make for a significant performance
1397 difference, particularly if the <literal>ORDER BY</> is combined with
1398 <literal>LIMIT</> or other restrictions. So this technique is recommended
1399 only if concurrent updates of the ordering columns are expected and a
1400 strictly sorted result is required.
1404 At the <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
1405 transaction isolation level this would cause a serialization failure (with
1406 a <literal>SQLSTATE</literal> of <literal>'40001'</literal>), so there is
1407 no possibility of receiving rows out of order under these isolation levels.
1412 <refsect2 id="SQL-TABLE">
1413 <title><literal>TABLE</literal> Command</title>
1418 TABLE <replaceable class="parameter">name</replaceable>
1420 is completely equivalent to
1422 SELECT * FROM <replaceable class="parameter">name</replaceable>
1424 It can be used as a top-level command or as a space-saving syntax
1425 variant in parts of complex queries.
1431 <title>Examples</title>
1434 To join the table <literal>films</literal> with the table
1435 <literal>distributors</literal>:
1438 SELECT f.title, f.did, d.name, f.date_prod, f.kind
1439 FROM distributors d, films f
1442 title | did | name | date_prod | kind
1443 -------------------+-----+--------------+------------+----------
1444 The Third Man | 101 | British Lion | 1949-12-23 | Drama
1445 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
1451 To sum the column <literal>len</literal> of all films and group
1452 the results by <literal>kind</literal>:
1455 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
1468 To sum the column <literal>len</literal> of all films, group
1469 the results by <literal>kind</literal> and show those group totals
1470 that are less than 5 hours:
1473 SELECT kind, sum(len) AS total
1476 HAVING sum(len) < interval '5 hours';
1486 The following two examples are identical ways of sorting the individual
1487 results according to the contents of the second column
1488 (<literal>name</literal>):
1491 SELECT * FROM distributors ORDER BY name;
1492 SELECT * FROM distributors ORDER BY 2;
1495 -----+------------------
1496 109 | 20th Century Fox
1497 110 | Bavaria Atelier
1500 102 | Jean Luc Godard
1505 105 | United Artists
1513 The next example shows how to obtain the union of the tables
1514 <literal>distributors</literal> and
1515 <literal>actors</literal>, restricting the results to those that begin
1516 with the letter W in each table. Only distinct rows are wanted, so the
1517 key word <literal>ALL</literal> is omitted.
1520 distributors: actors:
1521 did | name id | name
1522 -----+-------------- ----+----------------
1523 108 | Westward 1 | Woody Allen
1524 111 | Walt Disney 2 | Warren Beatty
1525 112 | Warner Bros. 3 | Walter Matthau
1528 SELECT distributors.name
1530 WHERE distributors.name LIKE 'W%'
1534 WHERE actors.name LIKE 'W%';
1548 This example shows how to use a function in the <literal>FROM</>
1549 clause, both with and without a column definition list:
1552 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
1553 SELECT * FROM distributors WHERE did = $1;
1556 SELECT * FROM distributors(111);
1561 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
1562 SELECT * FROM distributors WHERE did = $1;
1565 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
1573 This example shows how to use a simple <literal>WITH</> clause:
1577 SELECT random() as x FROM generate_series(1, 3)
1584 --------------------
1593 Notice that the <literal>WITH</> query was evaluated only once,
1594 so that we got two sets of the same three random values.
1598 This example uses <literal>WITH RECURSIVE</literal> to find all
1599 subordinates (direct or indirect) of the employee Mary, and their
1600 level of indirectness, from a table that shows only direct
1604 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
1605 SELECT 1, employee_name, manager_name
1607 WHERE manager_name = 'Mary'
1609 SELECT er.distance + 1, e.employee_name, e.manager_name
1610 FROM employee_recursive er, employee e
1611 WHERE er.employee_name = e.manager_name
1613 SELECT distance, employee_name FROM employee_recursive;
1616 Notice the typical form of recursive queries:
1617 an initial condition, followed by <literal>UNION</literal>,
1618 followed by the recursive part of the query. Be sure that the
1619 recursive part of the query will eventually return no tuples, or
1620 else the query will loop indefinitely. (See <xref linkend="queries-with">
1625 This example uses <literal>LATERAL</> to apply a set-returning function
1626 <function>get_product_names()</> for each row of the
1627 <structname>manufacturers</> table:
1630 SELECT m.name AS mname, pname
1631 FROM manufacturers m, LATERAL get_product_names(m.id) pname;
1634 Manufacturers not currently having any products would not appear in the
1635 result, since it is an inner join. If we wished to include the names of
1636 such manufacturers in the result, we could do:
1639 SELECT m.name AS mname, pname
1640 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
1646 <title>Compatibility</title>
1649 Of course, the <command>SELECT</command> statement is compatible
1650 with the SQL standard. But there are some extensions and some
1655 <title>Omitted <literal>FROM</literal> Clauses</title>
1658 <productname>PostgreSQL</productname> allows one to omit the
1659 <literal>FROM</literal> clause. It has a straightforward use to
1660 compute the results of simple expressions:
1668 Some other <acronym>SQL</acronym> databases cannot do this except
1669 by introducing a dummy one-row table from which to do the
1670 <command>SELECT</command>.
1674 Note that if a <literal>FROM</literal> clause is not specified,
1675 the query cannot reference any database tables. For example, the
1676 following query is invalid:
1678 SELECT distributors.* WHERE distributors.name = 'Westward';
1679 </programlisting><productname>PostgreSQL</productname> releases prior to
1680 8.1 would accept queries of this form, and add an implicit entry
1681 to the query's <literal>FROM</literal> clause for each table
1682 referenced by the query. This is no longer allowed.
1687 <title>Omitting the <literal>AS</literal> Key Word</title>
1690 In the SQL standard, the optional key word <literal>AS</> can be
1691 omitted before an output column name whenever the new column name
1692 is a valid column name (that is, not the same as any reserved
1693 keyword). <productname>PostgreSQL</productname> is slightly more
1694 restrictive: <literal>AS</> is required if the new column name
1695 matches any keyword at all, reserved or not. Recommended practice is
1696 to use <literal>AS</> or double-quote output column names, to prevent
1697 any possible conflict against future keyword additions.
1701 In <literal>FROM</literal> items, both the standard and
1702 <productname>PostgreSQL</productname> allow <literal>AS</> to
1703 be omitted before an alias that is an unreserved keyword. But
1704 this is impractical for output column names, because of syntactic
1710 <title><literal>ONLY</literal> and Inheritance</title>
1713 The SQL standard requires parentheses around the table name when
1714 writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY
1715 (tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</>
1716 considers these parentheses to be optional.
1720 <productname>PostgreSQL</> allows a trailing <literal>*</> to be written to
1721 explicitly specify the non-<literal>ONLY</literal> behavior of including
1722 child tables. The standard does not allow this.
1726 (These points apply equally to all SQL commands supporting the
1727 <literal>ONLY</literal> option.)
1732 <title>Function Calls in <literal>FROM</literal></title>
1735 <productname>PostgreSQL</productname> allows a function call to be
1736 written directly as a member of the <literal>FROM</> list. In the SQL
1737 standard it would be necessary to wrap such a function call in a
1738 sub-<command>SELECT</command>; that is, the syntax
1739 <literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
1740 is approximately equivalent to
1741 <literal>FROM (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
1746 <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
1749 In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
1750 only use output column names or numbers, while a <literal>GROUP
1751 BY</literal> clause can only use expressions based on input column
1752 names. <productname>PostgreSQL</productname> extends each of
1753 these clauses to allow the other choice as well (but it uses the
1754 standard's interpretation if there is ambiguity).
1755 <productname>PostgreSQL</productname> also allows both clauses to
1756 specify arbitrary expressions. Note that names appearing in an
1757 expression will always be taken as input-column names, not as
1758 output-column names.
1762 SQL:1999 and later use a slightly different definition which is not
1763 entirely upward compatible with SQL-92.
1764 In most cases, however, <productname>PostgreSQL</productname>
1765 will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1766 BY</literal> expression the same way SQL:1999 does.
1771 <title>Functional Dependencies</title>
1774 <productname>PostgreSQL</productname> recognizes functional dependency
1775 (allowing columns to be omitted from <literal>GROUP BY</>) only when
1776 a table's primary key is included in the <literal>GROUP BY</> list.
1777 The SQL standard specifies additional conditions that should be
1783 <title><literal>WINDOW</literal> Clause Restrictions</title>
1786 The SQL standard provides additional options for the window
1787 <replaceable class="parameter">frame_clause</>.
1788 <productname>PostgreSQL</productname> currently supports only the
1789 options listed above.
1794 <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1797 The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
1798 are <productname>PostgreSQL</productname>-specific syntax, also
1799 used by <productname>MySQL</productname>. The SQL:2008 standard
1800 has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
1801 ...</literal> for the same functionality, as shown above
1802 in <xref linkend="sql-limit" endterm="sql-limit-title">. This
1803 syntax is also used by <productname>IBM DB2</productname>.
1804 (Applications written for <productname>Oracle</productname>
1805 frequently use a workaround involving the automatically
1806 generated <literal>rownum</literal> column, which is not available in
1807 PostgreSQL, to implement the effects of these clauses.)
1812 <title><literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</>, <literal>FOR KEY SHARE</></title>
1815 Although <literal>FOR UPDATE</> appears in the SQL standard, the
1816 standard allows it only as an option of <command>DECLARE CURSOR</>.
1817 <productname>PostgreSQL</productname> allows it in any <command>SELECT</>
1818 query as well as in sub-<command>SELECT</>s, but this is an extension.
1819 The <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</> and
1820 <literal>FOR KEY SHARE</> variants,
1821 as well as the <literal>NOWAIT</> option,
1822 do not appear in the standard.
1827 <title>Data-Modifying Statements in <literal>WITH</></title>
1830 <productname>PostgreSQL</productname> allows <command>INSERT</>,
1831 <command>UPDATE</>, and <command>DELETE</> to be used as <literal>WITH</>
1832 queries. This is not found in the SQL standard.
1837 <title>Nonstandard Clauses</title>
1840 The clause <literal>DISTINCT ON</literal> is not defined in the
projects / postgresql / blob