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>
508 The <literal>LATERAL</literal> key word can precede a
509 sub-<command>SELECT</command> <literal>FROM</> item. This allows the
510 sub-<command>SELECT</command> to refer to columns of <literal>FROM</>
511 items that appear before it in the <literal>FROM</> list. (Without
512 <literal>LATERAL</literal>, each sub-<command>SELECT</command> is
513 evaluated independently and so cannot cross-reference any other
514 <literal>FROM</> item.)
518 <literal>LATERAL</literal> can also precede a function-call
519 <literal>FROM</> item, but in this case it is a noise word, because
520 the function expression can refer to earlier <literal>FROM</> items
525 A <literal>LATERAL</literal> item can appear at top level in the
526 <literal>FROM</> list, or within a <literal>JOIN</> tree. In the
527 latter case it can also refer to any items that are on the left-hand
528 side of a <literal>JOIN</> that it is on the right-hand side of.
532 When a <literal>FROM</> item contains <literal>LATERAL</literal>
533 cross-references, evaluation proceeds as follows: for each row of the
534 <literal>FROM</> item providing the cross-referenced column(s), or
535 set of rows of multiple <literal>FROM</> items providing the
536 columns, the <literal>LATERAL</literal> item is evaluated using that
537 row or row set's values of the columns. The resulting row(s) are
538 joined as usual with the rows they were computed from. This is
539 repeated for each row or set of rows from the column source table(s).
543 The column source table(s) must be <literal>INNER</> or
544 <literal>LEFT</> joined to the <literal>LATERAL</literal> item, else
545 there would not be a well-defined set of rows from which to compute
546 each set of rows for the <literal>LATERAL</literal> item. Thus,
547 although a construct such as <literal><replaceable>X</> RIGHT JOIN
548 LATERAL <replaceable>Y</></literal> is syntactically valid, it is
549 not actually allowed for <replaceable>Y</> to reference
558 <refsect2 id="SQL-WHERE">
559 <title id="sql-where-title"><literal>WHERE</literal> Clause</title>
562 The optional <literal>WHERE</literal> clause has the general form
564 WHERE <replaceable class="parameter">condition</replaceable>
566 where <replaceable class="parameter">condition</replaceable> is
567 any expression that evaluates to a result of type
568 <type>boolean</type>. Any row that does not satisfy this
569 condition will be eliminated from the output. A row satisfies the
570 condition if it returns true when the actual row values are
571 substituted for any variable references.
575 <refsect2 id="SQL-GROUPBY">
576 <title id="sql-groupby-title"><literal>GROUP BY</literal> Clause</title>
579 The optional <literal>GROUP BY</literal> clause has the general form
581 GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
586 <literal>GROUP BY</literal> will condense into a single row all
587 selected rows that share the same values for the grouped
588 expressions. <replaceable
589 class="parameter">expression</replaceable> can be an input column
590 name, or the name or ordinal number of an output column
591 (<command>SELECT</command> list item), or an arbitrary
592 expression formed from input-column values. In case of ambiguity,
593 a <literal>GROUP BY</literal> name will be interpreted as an
594 input-column name rather than an output column name.
598 Aggregate functions, if any are used, are computed across all rows
599 making up each group, producing a separate value for each group
600 (whereas without <literal>GROUP BY</literal>, an aggregate
601 produces a single value computed across all the selected rows).
602 When <literal>GROUP BY</literal> is present, it is not valid for
603 the <command>SELECT</command> list expressions to refer to
604 ungrouped columns except within aggregate functions or if the
605 ungrouped column is functionally dependent on the grouped columns,
606 since there would otherwise be more than one possible value to
607 return for an ungrouped column. A functional dependency exists if
608 the grouped columns (or a subset thereof) are the primary key of
609 the table containing the ungrouped column.
613 <refsect2 id="SQL-HAVING">
614 <title id="sql-having-title"><literal>HAVING</literal> Clause</title>
617 The optional <literal>HAVING</literal> clause has the general form
619 HAVING <replaceable class="parameter">condition</replaceable>
621 where <replaceable class="parameter">condition</replaceable> is
622 the same as specified for the <literal>WHERE</literal> clause.
626 <literal>HAVING</literal> eliminates group rows that do not
627 satisfy the condition. <literal>HAVING</literal> is different
628 from <literal>WHERE</literal>: <literal>WHERE</literal> filters
629 individual rows before the application of <literal>GROUP
630 BY</literal>, while <literal>HAVING</literal> filters group rows
631 created by <literal>GROUP BY</literal>. Each column referenced in
632 <replaceable class="parameter">condition</replaceable> must
633 unambiguously reference a grouping column, unless the reference
634 appears within an aggregate function.
638 The presence of <literal>HAVING</literal> turns a query into a grouped
639 query even if there is no <literal>GROUP BY</> clause. This is the
640 same as what happens when the query contains aggregate functions but
641 no <literal>GROUP BY</> clause. All the selected rows are considered to
642 form a single group, and the <command>SELECT</command> list and
643 <literal>HAVING</literal> clause can only reference table columns from
644 within aggregate functions. Such a query will emit a single row if the
645 <literal>HAVING</literal> condition is true, zero rows if it is not true.
649 <refsect2 id="SQL-WINDOW">
650 <title id="sql-window-title"><literal>WINDOW</literal> Clause</title>
653 The optional <literal>WINDOW</literal> clause has the general form
655 WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...]
657 where <replaceable class="parameter">window_name</replaceable> is
658 a name that can be referenced from subsequent window definitions or
659 <literal>OVER</> clauses, and
660 <replaceable class="parameter">window_definition</replaceable> is
662 [ <replaceable class="parameter">existing_window_name</replaceable> ]
663 [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
664 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
665 [ <replaceable class="parameter">frame_clause</replaceable> ]
670 If an <replaceable class="parameter">existing_window_name</replaceable>
671 is specified it must refer to an earlier entry in the <literal>WINDOW</>
672 list; the new window copies its partitioning clause from that entry,
673 as well as its ordering clause if any. In this case the new window cannot
674 specify its own <literal>PARTITION BY</> clause, and it can specify
675 <literal>ORDER BY</> only if the copied window does not have one.
676 The new window always uses its own frame clause; the copied window
677 must not specify a frame clause.
681 The elements of the <literal>PARTITION BY</> list are interpreted in
682 much the same fashion as elements of a
683 <xref linkend="sql-groupby" endterm="sql-groupby-title">, except that
684 they are always simple expressions and never the name or number of an
686 Another difference is that these expressions can contain aggregate
687 function calls, which are not allowed in a regular <literal>GROUP BY</>
688 clause. They are allowed here because windowing occurs after grouping
693 Similarly, the elements of the <literal>ORDER BY</> list are interpreted
694 in much the same fashion as elements of an
695 <xref linkend="sql-orderby" endterm="sql-orderby-title">, except that
696 the expressions are always taken as simple expressions and never the name
697 or number of an output column.
701 The optional <replaceable class="parameter">frame_clause</> defines
702 the <firstterm>window frame</> for window functions that depend on the
703 frame (not all do). The window frame is a set of related rows for
704 each row of the query (called the <firstterm>current row</>).
705 The <replaceable class="parameter">frame_clause</> can be one of
708 [ RANGE | ROWS ] <replaceable>frame_start</>
709 [ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
712 where <replaceable>frame_start</> and <replaceable>frame_end</> can be
717 <replaceable>value</replaceable> PRECEDING
719 <replaceable>value</replaceable> FOLLOWING
723 If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
724 ROW</>. Restrictions are that
725 <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
726 <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
727 and the <replaceable>frame_end</> choice cannot appear earlier in the
728 above list than the <replaceable>frame_start</> choice — for example
729 <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
730 PRECEDING</literal> is not allowed.
734 The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
735 which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
736 CURRENT ROW</>; it sets the frame to be all rows from the partition start
737 up through the current row's last peer in the <literal>ORDER BY</>
738 ordering (which means all rows if there is no <literal>ORDER BY</>).
739 In general, <literal>UNBOUNDED PRECEDING</> means that the frame
740 starts with the first row of the partition, and similarly
741 <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
742 row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
743 mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</>
744 means that the frame starts or ends with the current row; but in
745 <literal>RANGE</> mode it means that the frame starts or ends with
746 the current row's first or last peer in the <literal>ORDER BY</> ordering.
747 The <replaceable>value</> <literal>PRECEDING</> and
748 <replaceable>value</> <literal>FOLLOWING</> cases are currently only
749 allowed in <literal>ROWS</> mode. They indicate that the frame starts
750 or ends with the row that many rows before or after the current row.
751 <replaceable>value</replaceable> must be an integer expression not
752 containing any variables, aggregate functions, or window functions.
753 The value must not be null or negative; but it can be zero, which
754 selects the current row itself.
758 Beware that the <literal>ROWS</> options can produce unpredictable
759 results if the <literal>ORDER BY</> ordering does not order the rows
760 uniquely. The <literal>RANGE</> options are designed to ensure that
761 rows that are peers in the <literal>ORDER BY</> ordering are treated
762 alike; any two peer rows will be both in or both not in the frame.
766 The purpose of a <literal>WINDOW</literal> clause is to specify the
767 behavior of <firstterm>window functions</> appearing in the query's
768 <xref linkend="sql-select-list" endterm="sql-select-list-title"> or
769 <xref linkend="sql-orderby" endterm="sql-orderby-title">. These functions
770 can reference the <literal>WINDOW</literal> clause entries by name
771 in their <literal>OVER</> clauses. A <literal>WINDOW</literal> clause
772 entry does not have to be referenced anywhere, however; if it is not
773 used in the query it is simply ignored. It is possible to use window
774 functions without any <literal>WINDOW</literal> clause at all, since
775 a window function call can specify its window definition directly in
776 its <literal>OVER</> clause. However, the <literal>WINDOW</literal>
777 clause saves typing when the same window definition is needed for more
778 than one window function.
782 Window functions are described in detail in
783 <xref linkend="tutorial-window">,
784 <xref linkend="syntax-window-functions">, and
785 <xref linkend="queries-window">.
789 <refsect2 id="sql-select-list">
790 <title id="sql-select-list-title"><command>SELECT</command> List</title>
793 The <command>SELECT</command> list (between the key words
794 <literal>SELECT</> and <literal>FROM</>) specifies expressions
795 that form the output rows of the <command>SELECT</command>
796 statement. The expressions can (and usually do) refer to columns
797 computed in the <literal>FROM</> clause.
801 Just as in a table, every output column of a <command>SELECT</command>
802 has a name. In a simple <command>SELECT</command> this name is just
803 used to label the column for display, but when the <command>SELECT</>
804 is a sub-query of a larger query, the name is seen by the larger query
805 as the column name of the virtual table produced by the sub-query.
806 To specify the name to use for an output column, write
807 <literal>AS</> <replaceable class="parameter">output_name</replaceable>
808 after the column's expression. (You can omit <literal>AS</literal>,
809 but only if the desired output name does not match any
810 <productname>PostgreSQL</productname> keyword (see <xref
811 linkend="sql-keywords-appendix">). For protection against possible
812 future keyword additions, it is recommended that you always either
813 write <literal>AS</literal> or double-quote the output name.)
814 If you do not specify a column name, a name is chosen automatically
815 by <productname>PostgreSQL</productname>. If the column's expression
816 is a simple column reference then the chosen name is the same as that
817 column's name. In more complex cases a function or type name may be
818 used, or the system may fall back on a generated name such as
819 <literal>?column?</literal>.
823 An output column's name can be used to refer to the column's value in
824 <literal>ORDER BY</> and <literal>GROUP BY</> clauses, but not in the
825 <literal>WHERE</> or <literal>HAVING</> clauses; there you must write
826 out the expression instead.
830 Instead of an expression, <literal>*</literal> can be written in
831 the output list as a shorthand for all the columns of the selected
832 rows. Also, you can write <literal><replaceable
833 class="parameter">table_name</replaceable>.*</literal> as a
834 shorthand for the columns coming from just that table. In these
835 cases it is not possible to specify new names with <literal>AS</>;
836 the output column names will be the same as the table columns' names.
840 <refsect2 id="sql-distinct">
841 <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
844 If <literal>SELECT DISTINCT</> is specified, all duplicate rows are
845 removed from the result set (one row is kept from each group of
846 duplicates). <literal>SELECT ALL</> specifies the opposite: all rows are
847 kept; that is the default.
851 <literal>SELECT DISTINCT ON ( <replaceable
852 class="parameter">expression</replaceable> [, ...] )</literal>
853 keeps only the first row of each set of rows where the given
854 expressions evaluate to equal. The <literal>DISTINCT ON</literal>
855 expressions are interpreted using the same rules as for
856 <literal>ORDER BY</> (see above). Note that the <quote>first
857 row</quote> of each set is unpredictable unless <literal>ORDER
858 BY</> is used to ensure that the desired row appears first. For
861 SELECT DISTINCT ON (location) location, time, report
863 ORDER BY location, time DESC;
865 retrieves the most recent weather report for each location. But
866 if we had not used <literal>ORDER BY</> to force descending order
867 of time values for each location, we'd have gotten a report from
868 an unpredictable time for each location.
872 The <literal>DISTINCT ON</> expression(s) must match the leftmost
873 <literal>ORDER BY</> expression(s). The <literal>ORDER BY</> clause
874 will normally contain additional expression(s) that determine the
875 desired precedence of rows within each <literal>DISTINCT ON</> group.
879 <refsect2 id="SQL-UNION">
880 <title id="sql-union-title"><literal>UNION</literal> Clause</title>
883 The <literal>UNION</literal> clause has this general form:
885 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
886 </synopsis><replaceable class="parameter">select_statement</replaceable> is
887 any <command>SELECT</command> statement without an <literal>ORDER
888 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
889 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal> clause.
890 (<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
891 subexpression if it is enclosed in parentheses. Without
892 parentheses, these clauses will be taken to apply to the result of
893 the <literal>UNION</literal>, not to its right-hand input
898 The <literal>UNION</literal> operator computes the set union of
899 the rows returned by the involved <command>SELECT</command>
900 statements. A row is in the set union of two result sets if it
901 appears in at least one of the result sets. The two
902 <command>SELECT</command> statements that represent the direct
903 operands of the <literal>UNION</literal> must produce the same
904 number of columns, and corresponding columns must be of compatible
909 The result of <literal>UNION</> does not contain any duplicate
910 rows unless the <literal>ALL</> option is specified.
911 <literal>ALL</> prevents elimination of duplicates. (Therefore,
912 <literal>UNION ALL</> is usually significantly quicker than
913 <literal>UNION</>; use <literal>ALL</> when you can.)
914 <literal>DISTINCT</> can be written to explicitly specify the
915 default behavior of eliminating duplicate rows.
919 Multiple <literal>UNION</> operators in the same
920 <command>SELECT</command> statement are evaluated left to right,
921 unless otherwise indicated by parentheses.
925 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
926 <literal>FOR KEY SHARE</> cannot be
927 specified either for a <literal>UNION</> result or for any input of a
932 <refsect2 id="SQL-INTERSECT">
933 <title id="sql-intersect-title"><literal>INTERSECT</literal> Clause</title>
936 The <literal>INTERSECT</literal> clause has this general form:
938 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
939 </synopsis><replaceable class="parameter">select_statement</replaceable> is
940 any <command>SELECT</command> statement without an <literal>ORDER
941 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
942 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</> clause.
946 The <literal>INTERSECT</literal> operator computes the set
947 intersection of the rows returned by the involved
948 <command>SELECT</command> statements. A row is in the
949 intersection of two result sets if it appears in both result sets.
953 The result of <literal>INTERSECT</literal> does not contain any
954 duplicate rows unless the <literal>ALL</> option is specified.
955 With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
956 left table and <replaceable>n</> duplicates in the right table will appear
957 min(<replaceable>m</>,<replaceable>n</>) times in the result set.
958 <literal>DISTINCT</> can be written to explicitly specify the
959 default behavior of eliminating duplicate rows.
963 Multiple <literal>INTERSECT</literal> operators in the same
964 <command>SELECT</command> statement are evaluated left to right,
965 unless parentheses dictate otherwise.
966 <literal>INTERSECT</literal> binds more tightly than
967 <literal>UNION</literal>. That is, <literal>A UNION B INTERSECT
968 C</literal> will be read as <literal>A UNION (B INTERSECT
973 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
974 <literal>FOR KEY SHARE</> cannot be
975 specified either for an <literal>INTERSECT</> result or for any input of
976 an <literal>INTERSECT</>.
980 <refsect2 id="SQL-EXCEPT">
981 <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>
984 The <literal>EXCEPT</literal> clause has this general form:
986 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
987 </synopsis><replaceable class="parameter">select_statement</replaceable> is
988 any <command>SELECT</command> statement without an <literal>ORDER
989 BY</>, <literal>LIMIT</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</literal>,
990 <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</> clause.
994 The <literal>EXCEPT</literal> operator computes the set of rows
995 that are in the result of the left <command>SELECT</command>
996 statement but not in the result of the right one.
1000 The result of <literal>EXCEPT</literal> does not contain any
1001 duplicate rows unless the <literal>ALL</> option is specified.
1002 With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
1003 left table and <replaceable>n</> duplicates in the right table will appear
1004 max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
1005 <literal>DISTINCT</> can be written to explicitly specify the
1006 default behavior of eliminating duplicate rows.
1010 Multiple <literal>EXCEPT</literal> operators in the same
1011 <command>SELECT</command> statement are evaluated left to right,
1012 unless parentheses dictate otherwise. <literal>EXCEPT</> binds at
1013 the same level as <literal>UNION</>.
1017 Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</> and
1018 <literal>FOR KEY SHARE</> cannot be
1019 specified either for an <literal>EXCEPT</> result or for any input of
1020 an <literal>EXCEPT</>.
1024 <refsect2 id="SQL-ORDERBY">
1025 <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>
1028 The optional <literal>ORDER BY</literal> clause has this general form:
1030 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...]
1032 The <literal>ORDER BY</literal> clause causes the result rows to
1033 be sorted according to the specified expression(s). If two rows are
1034 equal according to the leftmost expression, they are compared
1035 according to the next expression and so on. If they are equal
1036 according to all specified expressions, they are returned in
1037 an implementation-dependent order.
1041 Each <replaceable class="parameter">expression</replaceable> can be the
1042 name or ordinal number of an output column
1043 (<command>SELECT</command> list item), or it can be an arbitrary
1044 expression formed from input-column values.
1048 The ordinal number refers to the ordinal (left-to-right) position
1049 of the output column. This feature makes it possible to define an
1050 ordering on the basis of a column that does not have a unique
1051 name. This is never absolutely necessary because it is always
1052 possible to assign a name to an output column using the
1053 <literal>AS</> clause.
1057 It is also possible to use arbitrary expressions in the
1058 <literal>ORDER BY</literal> clause, including columns that do not
1059 appear in the <command>SELECT</command> output list. Thus the
1060 following statement is valid:
1062 SELECT name FROM distributors ORDER BY code;
1064 A limitation of this feature is that an <literal>ORDER BY</>
1065 clause applying to the result of a <literal>UNION</>,
1066 <literal>INTERSECT</>, or <literal>EXCEPT</> clause can only
1067 specify an output column name or number, not an expression.
1071 If an <literal>ORDER BY</> expression is a simple name that
1072 matches both an output column name and an input column name,
1073 <literal>ORDER BY</> will interpret it as the output column name.
1074 This is the opposite of the choice that <literal>GROUP BY</> will
1075 make in the same situation. This inconsistency is made to be
1076 compatible with the SQL standard.
1080 Optionally one can add the key word <literal>ASC</> (ascending) or
1081 <literal>DESC</> (descending) after any expression in the
1082 <literal>ORDER BY</> clause. If not specified, <literal>ASC</> is
1083 assumed by default. Alternatively, a specific ordering operator
1084 name can be specified in the <literal>USING</> clause.
1085 An ordering operator must be a less-than or greater-than
1086 member of some B-tree operator family.
1087 <literal>ASC</> is usually equivalent to <literal>USING <</> and
1088 <literal>DESC</> is usually equivalent to <literal>USING ></>.
1089 (But the creator of a user-defined data type can define exactly what the
1090 default sort ordering is, and it might correspond to operators with other
1095 If <literal>NULLS LAST</> is specified, null values sort after all
1096 non-null values; if <literal>NULLS FIRST</> is specified, null values
1097 sort before all non-null values. If neither is specified, the default
1098 behavior is <literal>NULLS LAST</> when <literal>ASC</> is specified
1099 or implied, and <literal>NULLS FIRST</> when <literal>DESC</> is specified
1100 (thus, the default is to act as though nulls are larger than non-nulls).
1101 When <literal>USING</> is specified, the default nulls ordering depends
1102 on whether the operator is a less-than or greater-than operator.
1106 Note that ordering options apply only to the expression they follow;
1107 for example <literal>ORDER BY x, y DESC</> does not mean
1108 the same thing as <literal>ORDER BY x DESC, y DESC</>.
1112 Character-string data is sorted according to the collation that applies
1113 to the column being sorted. That can be overridden at need by including
1114 a <literal>COLLATE</> clause in the
1115 <replaceable class="parameter">expression</replaceable>, for example
1116 <literal>ORDER BY mycolumn COLLATE "en_US"</>.
1117 For more information see <xref linkend="sql-syntax-collate-exprs"> and
1118 <xref linkend="collation">.
1122 <refsect2 id="SQL-LIMIT">
1123 <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
1126 The <literal>LIMIT</literal> clause consists of two independent
1129 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
1130 OFFSET <replaceable class="parameter">start</replaceable>
1132 <replaceable class="parameter">count</replaceable> specifies the
1133 maximum number of rows to return, while <replaceable
1134 class="parameter">start</replaceable> specifies the number of rows
1135 to skip before starting to return rows. When both are specified,
1136 <replaceable class="parameter">start</replaceable> rows are skipped
1137 before starting to count the <replaceable
1138 class="parameter">count</replaceable> rows to be returned.
1142 If the <replaceable class="parameter">count</replaceable> expression
1143 evaluates to NULL, it is treated as <literal>LIMIT ALL</>, i.e., no
1144 limit. If <replaceable class="parameter">start</replaceable> evaluates
1145 to NULL, it is treated the same as <literal>OFFSET 0</>.
1149 SQL:2008 introduced a different syntax to achieve the same result,
1150 which <productname>PostgreSQL</> also supports. It is:
1152 OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
1153 FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
1155 In this syntax, to write anything except a simple integer constant for
1156 <replaceable class="parameter">start</> or <replaceable
1157 class="parameter">count</replaceable>, you must write parentheses
1159 If <replaceable class="parameter">count</> is
1160 omitted in a <literal>FETCH</> clause, it defaults to 1.
1161 <literal>ROW</literal>
1162 and <literal>ROWS</literal> as well as <literal>FIRST</literal>
1163 and <literal>NEXT</literal> are noise words that don't influence
1164 the effects of these clauses.
1165 According to the standard, the <literal>OFFSET</literal> clause must come
1166 before the <literal>FETCH</literal> clause if both are present; but
1167 <productname>PostgreSQL</> is laxer and allows either order.
1171 When using <literal>LIMIT</>, it is a good idea to use an
1172 <literal>ORDER BY</> clause that constrains the result rows into a
1173 unique order. Otherwise you will get an unpredictable subset of
1174 the query's rows — you might be asking for the tenth through
1175 twentieth rows, but tenth through twentieth in what ordering? You
1176 don't know what ordering unless you specify <literal>ORDER BY</>.
1180 The query planner takes <literal>LIMIT</> into account when
1181 generating a query plan, so you are very likely to get different
1182 plans (yielding different row orders) depending on what you use
1183 for <literal>LIMIT</> and <literal>OFFSET</>. Thus, using
1184 different <literal>LIMIT</>/<literal>OFFSET</> values to select
1185 different subsets of a query result <emphasis>will give
1186 inconsistent results</emphasis> unless you enforce a predictable
1187 result ordering with <literal>ORDER BY</>. This is not a bug; it
1188 is an inherent consequence of the fact that SQL does not promise
1189 to deliver the results of a query in any particular order unless
1190 <literal>ORDER BY</> is used to constrain the order.
1194 It is even possible for repeated executions of the same <literal>LIMIT</>
1195 query to return different subsets of the rows of a table, if there
1196 is not an <literal>ORDER BY</> to enforce selection of a deterministic
1197 subset. Again, this is not a bug; determinism of the results is
1198 simply not guaranteed in such a case.
1202 <refsect2 id="SQL-FOR-UPDATE-SHARE">
1203 <title id="sql-for-update-share-title"><literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</>/<literal>FOR SHARE</>/<literal>FOR KEY SHARE</> Clauses</title>
1206 <literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</>
1207 and <literal>FOR KEY SHARE</>
1208 are <firstterm>locking clauses</>; they affect how <literal>SELECT</>
1209 locks rows as they are obtained from the table.
1213 The <literal>FOR UPDATE</literal> clause has this form:
1215 FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1220 The <literal>FOR NO KEY UPDATE</literal> clause has this form:
1222 FOR NO KEY UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1227 The closely related <literal>FOR SHARE</literal> clause has this form:
1229 FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1234 Similarly, the <literal>FOR KEY SHARE</> clause has this form:
1236 FOR KEY SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
1241 <literal>FOR UPDATE</literal> causes the rows retrieved by the
1242 <command>SELECT</command> statement to be locked as though for
1243 update. This prevents them from being modified or deleted by
1244 other transactions until the current transaction ends. That is,
1245 other transactions that attempt <command>UPDATE</command>,
1246 <command>DELETE</command>,
1247 <command>SELECT FOR UPDATE</command>,
1248 <command>SELECT FOR SHARE</command> or
1249 <command>SELECT FOR KEY SHARE</command>
1250 of these rows will be blocked until the current transaction ends.
1251 The <literal>FOR UPDATE</> lock mode
1252 is also acquired by any <command>DELETE</> on a row, and also by an
1253 <command>UPDATE</> that modifies the values on certain columns. Currently,
1254 the set of columns considered for the <command>UPDATE</> case are those that
1255 have an unique index on them that can be used in a foreign key (so partial
1256 indexes and expressional indexes are not considered), but this may change
1258 Also, if an <command>UPDATE</command>, <command>DELETE</command>,
1259 or <command>SELECT FOR UPDATE</command> from another transaction
1260 has already locked a selected row or rows, <command>SELECT FOR
1261 UPDATE</command> will wait for the other transaction to complete,
1262 and will then lock and return the updated row (or no row, if the
1263 row was deleted). Within a <literal>REPEATABLE READ</> or <literal>SERIALIZABLE</> transaction,
1264 however, an error will be thrown if a row to be locked has changed
1265 since the transaction started. For further discussion see <xref
1270 <literal>FOR NO KEY UPDATE</> behaves similarly, except that the lock
1271 acquired is weaker: this lock will not block
1272 <literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
1273 a lock on the same rows.
1277 <literal>FOR SHARE</literal> behaves similarly, except that it
1278 acquires a shared rather than exclusive lock on each retrieved
1279 row. A shared lock blocks other transactions from performing
1280 <command>UPDATE</command>, <command>DELETE</command>, or <command>SELECT
1281 FOR UPDATE</command> on these rows, but it does not prevent them
1282 from performing <command>SELECT FOR SHARE</command> or
1283 <command>SELECT FOR KEY SHARE</command>.
1287 <literal>FOR KEY SHARE</> behaves similarly to <literal>FOR SHARE</literal>,
1288 except that the lock
1289 is weaker: <literal>SELECT FOR UPDATE</> is blocked, but
1290 not <literal>SELECT FOR NO KEY UPDATE</>. A key-shared
1291 lock blocks other transactions from performing <command>DELETE</command>
1292 or any <command>UPDATE</command> that changes the key values, but not
1293 other <command>UPDATE</>, and neither it does prevent
1294 <command>SELECT FOR UPDATE</>, <command>SELECT FOR SHARE</>, or
1295 <command>SELECT FOR KEY SHARE</>.
1299 To prevent the operation from waiting for other transactions to commit,
1300 use the <literal>NOWAIT</> option. With <literal>NOWAIT</>, the statement
1301 reports an error, rather than waiting, if a selected row
1302 cannot be locked immediately. Note that <literal>NOWAIT</> applies only
1303 to the row-level lock(s) — the required <literal>ROW SHARE</literal>
1304 table-level lock is still taken in the ordinary way (see
1305 <xref linkend="mvcc">). You can use
1306 <xref linkend="sql-lock">
1307 with the <literal>NOWAIT</> option first,
1308 if you need to acquire the table-level lock without waiting.
1312 If specific tables are named in a locking clause,
1313 then only rows coming from those tables are locked; any other
1314 tables used in the <command>SELECT</command> are simply read as
1316 clause without a table list affects all tables used in the statement.
1317 If a locking clause is
1318 applied to a view or sub-query, it affects all tables used in
1319 the view or sub-query.
1320 However, these clauses
1321 do not apply to <literal>WITH</> queries referenced by the primary query.
1322 If you want row locking to occur within a <literal>WITH</> query, specify
1323 a locking clause within the <literal>WITH</> query.
1328 clauses can be written if it is necessary to specify different locking
1329 behavior for different tables. If the same table is mentioned (or
1330 implicitly affected) by more than one locking clause,
1331 then it is processed as if it was only specified by the strongest one.
1332 Similarly, a table is processed
1333 as <literal>NOWAIT</> if that is specified in any of the clauses
1338 The locking clauses cannot be
1339 used in contexts where returned rows cannot be clearly identified with
1340 individual table rows; for example they cannot be used with aggregation.
1344 When a locking clause
1345 appears at the top level of a <command>SELECT</> query, the rows that
1346 are locked are exactly those that are returned by the query; in the
1347 case of a join query, the rows locked are those that contribute to
1348 returned join rows. In addition, rows that satisfied the query
1349 conditions as of the query snapshot will be locked, although they
1350 will not be returned if they were updated after the snapshot
1351 and no longer satisfy the query conditions. If a
1352 <literal>LIMIT</> is used, locking stops
1353 once enough rows have been returned to satisfy the limit (but note that
1354 rows skipped over by <literal>OFFSET</> will get locked). Similarly,
1356 is used in a cursor's query, only rows actually fetched or stepped past
1357 by the cursor will be locked.
1361 When a locking clause
1362 appears in a sub-<command>SELECT</>, the rows locked are those
1363 returned to the outer query by the sub-query. This might involve
1364 fewer rows than inspection of the sub-query alone would suggest,
1365 since conditions from the outer query might be used to optimize
1366 execution of the sub-query. For example,
1368 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;
1370 will lock only rows having <literal>col1 = 5</>, even though that
1371 condition is not textually within the sub-query.
1375 Previous releases failed to preserve a lock which is upgraded by a later
1376 savepoint. For example, this code:
1379 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
1381 UPDATE mytable SET ... WHERE key = 1;
1384 would fail to preserve the <literal>FOR UPDATE</> lock after the
1385 <command>ROLLBACK</>. This has been fixed in release 9.2.
1390 It is possible for a <command>SELECT</> command running at the <literal>READ
1391 COMMITTED</literal> transaction isolation level and using <literal>ORDER
1392 BY</literal> and a locking clause to return rows out of
1393 order. This is because <literal>ORDER BY</> is applied first.
1394 The command sorts the result, but might then block trying to obtain a lock
1395 on one or more of the rows. Once the <literal>SELECT</> unblocks, some
1396 of the ordering column values might have been modified, leading to those
1397 rows appearing to be out of order (though they are in order in terms
1398 of the original column values). This can be worked around at need by
1399 placing the <literal>FOR UPDATE/SHARE</literal> clause in a sub-query,
1402 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
1404 Note that this will result in locking all rows of <structname>mytable</>,
1405 whereas <literal>FOR UPDATE</> at the top level would lock only the
1406 actually returned rows. This can make for a significant performance
1407 difference, particularly if the <literal>ORDER BY</> is combined with
1408 <literal>LIMIT</> or other restrictions. So this technique is recommended
1409 only if concurrent updates of the ordering columns are expected and a
1410 strictly sorted result is required.
1414 At the <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
1415 transaction isolation level this would cause a serialization failure (with
1416 a <literal>SQLSTATE</literal> of <literal>'40001'</literal>), so there is
1417 no possibility of receiving rows out of order under these isolation levels.
1422 <refsect2 id="SQL-TABLE">
1423 <title><literal>TABLE</literal> Command</title>
1428 TABLE <replaceable class="parameter">name</replaceable>
1430 is completely equivalent to
1432 SELECT * FROM <replaceable class="parameter">name</replaceable>
1434 It can be used as a top-level command or as a space-saving syntax
1435 variant in parts of complex queries.
1441 <title>Examples</title>
1444 To join the table <literal>films</literal> with the table
1445 <literal>distributors</literal>:
1448 SELECT f.title, f.did, d.name, f.date_prod, f.kind
1449 FROM distributors d, films f
1452 title | did | name | date_prod | kind
1453 -------------------+-----+--------------+------------+----------
1454 The Third Man | 101 | British Lion | 1949-12-23 | Drama
1455 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
1461 To sum the column <literal>len</literal> of all films and group
1462 the results by <literal>kind</literal>:
1465 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
1478 To sum the column <literal>len</literal> of all films, group
1479 the results by <literal>kind</literal> and show those group totals
1480 that are less than 5 hours:
1483 SELECT kind, sum(len) AS total
1486 HAVING sum(len) < interval '5 hours';
1496 The following two examples are identical ways of sorting the individual
1497 results according to the contents of the second column
1498 (<literal>name</literal>):
1501 SELECT * FROM distributors ORDER BY name;
1502 SELECT * FROM distributors ORDER BY 2;
1505 -----+------------------
1506 109 | 20th Century Fox
1507 110 | Bavaria Atelier
1510 102 | Jean Luc Godard
1515 105 | United Artists
1523 The next example shows how to obtain the union of the tables
1524 <literal>distributors</literal> and
1525 <literal>actors</literal>, restricting the results to those that begin
1526 with the letter W in each table. Only distinct rows are wanted, so the
1527 key word <literal>ALL</literal> is omitted.
1530 distributors: actors:
1531 did | name id | name
1532 -----+-------------- ----+----------------
1533 108 | Westward 1 | Woody Allen
1534 111 | Walt Disney 2 | Warren Beatty
1535 112 | Warner Bros. 3 | Walter Matthau
1538 SELECT distributors.name
1540 WHERE distributors.name LIKE 'W%'
1544 WHERE actors.name LIKE 'W%';
1558 This example shows how to use a function in the <literal>FROM</>
1559 clause, both with and without a column definition list:
1562 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
1563 SELECT * FROM distributors WHERE did = $1;
1566 SELECT * FROM distributors(111);
1571 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
1572 SELECT * FROM distributors WHERE did = $1;
1575 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
1583 This example shows how to use a simple <literal>WITH</> clause:
1587 SELECT random() as x FROM generate_series(1, 3)
1594 --------------------
1603 Notice that the <literal>WITH</> query was evaluated only once,
1604 so that we got two sets of the same three random values.
1608 This example uses <literal>WITH RECURSIVE</literal> to find all
1609 subordinates (direct or indirect) of the employee Mary, and their
1610 level of indirectness, from a table that shows only direct
1614 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
1615 SELECT 1, employee_name, manager_name
1617 WHERE manager_name = 'Mary'
1619 SELECT er.distance + 1, e.employee_name, e.manager_name
1620 FROM employee_recursive er, employee e
1621 WHERE er.employee_name = e.manager_name
1623 SELECT distance, employee_name FROM employee_recursive;
1626 Notice the typical form of recursive queries:
1627 an initial condition, followed by <literal>UNION</literal>,
1628 followed by the recursive part of the query. Be sure that the
1629 recursive part of the query will eventually return no tuples, or
1630 else the query will loop indefinitely. (See <xref linkend="queries-with">
1635 This example uses <literal>LATERAL</> to apply a set-returning function
1636 <function>get_product_names()</> for each row of the
1637 <structname>manufacturers</> table:
1640 SELECT m.name AS mname, pname
1641 FROM manufacturers m, LATERAL get_product_names(m.id) pname;
1644 Manufacturers not currently having any products would not appear in the
1645 result, since it is an inner join. If we wished to include the names of
1646 such manufacturers in the result, we could do:
1649 SELECT m.name AS mname, pname
1650 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;
1656 <title>Compatibility</title>
1659 Of course, the <command>SELECT</command> statement is compatible
1660 with the SQL standard. But there are some extensions and some
1665 <title>Omitted <literal>FROM</literal> Clauses</title>
1668 <productname>PostgreSQL</productname> allows one to omit the
1669 <literal>FROM</literal> clause. It has a straightforward use to
1670 compute the results of simple expressions:
1678 Some other <acronym>SQL</acronym> databases cannot do this except
1679 by introducing a dummy one-row table from which to do the
1680 <command>SELECT</command>.
1684 Note that if a <literal>FROM</literal> clause is not specified,
1685 the query cannot reference any database tables. For example, the
1686 following query is invalid:
1688 SELECT distributors.* WHERE distributors.name = 'Westward';
1689 </programlisting><productname>PostgreSQL</productname> releases prior to
1690 8.1 would accept queries of this form, and add an implicit entry
1691 to the query's <literal>FROM</literal> clause for each table
1692 referenced by the query. This is no longer allowed.
1697 <title>Omitting the <literal>AS</literal> Key Word</title>
1700 In the SQL standard, the optional key word <literal>AS</> can be
1701 omitted before an output column name whenever the new column name
1702 is a valid column name (that is, not the same as any reserved
1703 keyword). <productname>PostgreSQL</productname> is slightly more
1704 restrictive: <literal>AS</> is required if the new column name
1705 matches any keyword at all, reserved or not. Recommended practice is
1706 to use <literal>AS</> or double-quote output column names, to prevent
1707 any possible conflict against future keyword additions.
1711 In <literal>FROM</literal> items, both the standard and
1712 <productname>PostgreSQL</productname> allow <literal>AS</> to
1713 be omitted before an alias that is an unreserved keyword. But
1714 this is impractical for output column names, because of syntactic
1720 <title><literal>ONLY</literal> and Inheritance</title>
1723 The SQL standard requires parentheses around the table name when
1724 writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY
1725 (tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</>
1726 considers these parentheses to be optional.
1730 <productname>PostgreSQL</> allows a trailing <literal>*</> to be written to
1731 explicitly specify the non-<literal>ONLY</literal> behavior of including
1732 child tables. The standard does not allow this.
1736 (These points apply equally to all SQL commands supporting the
1737 <literal>ONLY</literal> option.)
1742 <title>Function Calls in <literal>FROM</literal></title>
1745 <productname>PostgreSQL</productname> allows a function call to be
1746 written directly as a member of the <literal>FROM</> list. In the SQL
1747 standard it would be necessary to wrap such a function call in a
1748 sub-<command>SELECT</command>; that is, the syntax
1749 <literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
1750 is approximately equivalent to
1751 <literal>FROM LATERAL (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
1752 Note that <literal>LATERAL</> is considered to be implicit; this is
1753 because the standard requires <literal>LATERAL</> semantics for an
1754 <literal>UNNEST()</> item in <literal>FROM</>.
1755 <productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
1756 same as other set-returning functions.
1761 <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
1764 In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
1765 only use output column names or numbers, while a <literal>GROUP
1766 BY</literal> clause can only use expressions based on input column
1767 names. <productname>PostgreSQL</productname> extends each of
1768 these clauses to allow the other choice as well (but it uses the
1769 standard's interpretation if there is ambiguity).
1770 <productname>PostgreSQL</productname> also allows both clauses to
1771 specify arbitrary expressions. Note that names appearing in an
1772 expression will always be taken as input-column names, not as
1773 output-column names.
1777 SQL:1999 and later use a slightly different definition which is not
1778 entirely upward compatible with SQL-92.
1779 In most cases, however, <productname>PostgreSQL</productname>
1780 will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1781 BY</literal> expression the same way SQL:1999 does.
1786 <title>Functional Dependencies</title>
1789 <productname>PostgreSQL</productname> recognizes functional dependency
1790 (allowing columns to be omitted from <literal>GROUP BY</>) only when
1791 a table's primary key is included in the <literal>GROUP BY</> list.
1792 The SQL standard specifies additional conditions that should be
1798 <title><literal>WINDOW</literal> Clause Restrictions</title>
1801 The SQL standard provides additional options for the window
1802 <replaceable class="parameter">frame_clause</>.
1803 <productname>PostgreSQL</productname> currently supports only the
1804 options listed above.
1809 <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1812 The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
1813 are <productname>PostgreSQL</productname>-specific syntax, also
1814 used by <productname>MySQL</productname>. The SQL:2008 standard
1815 has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
1816 ...</literal> for the same functionality, as shown above
1817 in <xref linkend="sql-limit" endterm="sql-limit-title">. This
1818 syntax is also used by <productname>IBM DB2</productname>.
1819 (Applications written for <productname>Oracle</productname>
1820 frequently use a workaround involving the automatically
1821 generated <literal>rownum</literal> column, which is not available in
1822 PostgreSQL, to implement the effects of these clauses.)
1827 <title><literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</>, <literal>FOR KEY SHARE</></title>
1830 Although <literal>FOR UPDATE</> appears in the SQL standard, the
1831 standard allows it only as an option of <command>DECLARE CURSOR</>.
1832 <productname>PostgreSQL</productname> allows it in any <command>SELECT</>
1833 query as well as in sub-<command>SELECT</>s, but this is an extension.
1834 The <literal>FOR NO KEY UPDATE</>, <literal>FOR SHARE</> and
1835 <literal>FOR KEY SHARE</> variants,
1836 as well as the <literal>NOWAIT</> option,
1837 do not appear in the standard.
1842 <title>Data-Modifying Statements in <literal>WITH</></title>
1845 <productname>PostgreSQL</productname> allows <command>INSERT</>,
1846 <command>UPDATE</>, and <command>DELETE</> to be used as <literal>WITH</>
1847 queries. This is not found in the SQL standard.
1852 <title>Nonstandard Clauses</title>
1855 The clause <literal>DISTINCT ON</literal> is not defined in the
projects / postgresql / blob