1 <!-- doc/src/sgml/queries.sgml -->
6 <indexterm zone="queries">
7 <primary>query</primary>
10 <indexterm zone="queries">
11 <primary>SELECT</primary>
15 The previous chapters explained how to create tables, how to fill
16 them with data, and how to manipulate that data. Now we finally
17 discuss how to retrieve the data from the database.
21 <sect1 id="queries-overview">
22 <title>Overview</title>
25 The process of retrieving or the command to retrieve data from a
26 database is called a <firstterm>query</firstterm>. In SQL the
27 <xref linkend="sql-select"/> command is
28 used to specify queries. The general syntax of the
29 <command>SELECT</command> command is
31 <optional>WITH <replaceable>with_queries</replaceable></optional> SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>
33 The following sections describe the details of the select list, the
34 table expression, and the sort specification. <literal>WITH</literal>
35 queries are treated last since they are an advanced feature.
39 A simple kind of query has the form:
43 Assuming that there is a table called <literal>table1</literal>,
44 this command would retrieve all rows and all user-defined columns from
45 <literal>table1</literal>. (The method of retrieval depends on the
46 client application. For example, the
47 <application>psql</application> program will display an ASCII-art
48 table on the screen, while client libraries will offer functions to
49 extract individual values from the query result.) The select list
50 specification <literal>*</literal> means all columns that the table
51 expression happens to provide. A select list can also select a
52 subset of the available columns or make calculations using the
53 columns. For example, if
54 <literal>table1</literal> has columns named <literal>a</literal>,
55 <literal>b</literal>, and <literal>c</literal> (and perhaps others) you can make
58 SELECT a, b + c FROM table1;
60 (assuming that <literal>b</literal> and <literal>c</literal> are of a numerical
62 See <xref linkend="queries-select-lists"/> for more details.
66 <literal>FROM table1</literal> is a simple kind of
67 table expression: it reads just one table. In general, table
68 expressions can be complex constructs of base tables, joins, and
69 subqueries. But you can also omit the table expression entirely and
70 use the <command>SELECT</command> command as a calculator:
74 This is more useful if the expressions in the select list return
75 varying results. For example, you could call a function this way:
83 <sect1 id="queries-table-expressions">
84 <title>Table Expressions</title>
86 <indexterm zone="queries-table-expressions">
87 <primary>table expression</primary>
91 A <firstterm>table expression</firstterm> computes a table. The
92 table expression contains a <literal>FROM</literal> clause that is
93 optionally followed by <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
94 <literal>HAVING</literal> clauses. Trivial table expressions simply refer
95 to a table on disk, a so-called base table, but more complex
96 expressions can be used to modify or combine base tables in various
101 The optional <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
102 <literal>HAVING</literal> clauses in the table expression specify a
103 pipeline of successive transformations performed on the table
104 derived in the <literal>FROM</literal> clause. All these transformations
105 produce a virtual table that provides the rows that are passed to
106 the select list to compute the output rows of the query.
109 <sect2 id="queries-from">
110 <title>The <literal>FROM</literal> Clause</title>
113 The <xref linkend="sql-from" endterm="sql-from-title"/> derives a
114 table from one or more other tables given in a comma-separated
115 table reference list.
117 FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional>
120 A table reference can be a table name (possibly schema-qualified),
121 or a derived table such as a subquery, a <literal>JOIN</literal> construct, or
122 complex combinations of these. If more than one table reference is
123 listed in the <literal>FROM</literal> clause, the tables are cross-joined
124 (that is, the Cartesian product of their rows is formed; see below).
125 The result of the <literal>FROM</literal> list is an intermediate virtual
126 table that can then be subject to
127 transformations by the <literal>WHERE</literal>, <literal>GROUP BY</literal>,
128 and <literal>HAVING</literal> clauses and is finally the result of the
129 overall table expression.
133 <primary>ONLY</primary>
137 When a table reference names a table that is the parent of a
138 table inheritance hierarchy, the table reference produces rows of
139 not only that table but all of its descendant tables, unless the
140 key word <literal>ONLY</literal> precedes the table name. However, the
141 reference produces only the columns that appear in the named table
142 — any columns added in subtables are ignored.
146 Instead of writing <literal>ONLY</literal> before the table name, you can write
147 <literal>*</literal> after the table name to explicitly specify that descendant
148 tables are included. There is no real reason to use this syntax any more,
149 because searching descendant tables is now always the default behavior.
150 However, it is supported for compatibility with older releases.
153 <sect3 id="queries-join">
154 <title>Joined Tables</title>
156 <indexterm zone="queries-join">
157 <primary>join</primary>
161 A joined table is a table derived from two other (real or
162 derived) tables according to the rules of the particular join
163 type. Inner, outer, and cross-joins are available.
164 The general syntax of a joined table is
166 <replaceable>T1</replaceable> <replaceable>join_type</replaceable> <replaceable>T2</replaceable> <optional> <replaceable>join_condition</replaceable> </optional>
168 Joins of all types can be chained together, or nested: either or
169 both <replaceable>T1</replaceable> and
170 <replaceable>T2</replaceable> can be joined tables. Parentheses
171 can be used around <literal>JOIN</literal> clauses to control the join
172 order. In the absence of parentheses, <literal>JOIN</literal> clauses
177 <title>Join Types</title>
182 <primary>join</primary>
183 <secondary>cross</secondary>
187 <primary>cross join</primary>
193 <replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
197 For every possible combination of rows from
198 <replaceable>T1</replaceable> and
199 <replaceable>T2</replaceable> (i.e., a Cartesian product),
200 the joined table will contain a
201 row consisting of all columns in <replaceable>T1</replaceable>
202 followed by all columns in <replaceable>T2</replaceable>. If
203 the tables have N and M rows respectively, the joined
204 table will have N * M rows.
208 <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
209 <replaceable>T2</replaceable></literal> is equivalent to
210 <literal>FROM <replaceable>T1</replaceable> INNER JOIN
211 <replaceable>T2</replaceable> ON TRUE</literal> (see below).
212 It is also equivalent to
213 <literal>FROM <replaceable>T1</replaceable>,
214 <replaceable>T2</replaceable></literal>.
217 This latter equivalence does not hold exactly when more than two
218 tables appear, because <literal>JOIN</literal> binds more tightly than
220 <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
221 <replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
222 ON <replaceable>condition</replaceable></literal>
224 <literal>FROM <replaceable>T1</replaceable>,
225 <replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
226 ON <replaceable>condition</replaceable></literal>
227 because the <replaceable>condition</replaceable> can
228 reference <replaceable>T1</replaceable> in the first case but not
237 <term>Qualified joins
239 <primary>join</primary>
240 <secondary>outer</secondary>
244 <primary>outer join</primary>
250 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
251 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
252 <replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
256 The words <literal>INNER</literal> and
257 <literal>OUTER</literal> are optional in all forms.
258 <literal>INNER</literal> is the default;
259 <literal>LEFT</literal>, <literal>RIGHT</literal>, and
260 <literal>FULL</literal> imply an outer join.
264 The <firstterm>join condition</firstterm> is specified in the
265 <literal>ON</literal> or <literal>USING</literal> clause, or implicitly by
266 the word <literal>NATURAL</literal>. The join condition determines
267 which rows from the two source tables are considered to
268 <quote>match</quote>, as explained in detail below.
272 The possible types of qualified join are:
276 <term><literal>INNER JOIN</literal></term>
280 For each row R1 of T1, the joined table has a row for each
281 row in T2 that satisfies the join condition with R1.
287 <term><literal>LEFT OUTER JOIN</literal>
289 <primary>join</primary>
290 <secondary>left</secondary>
294 <primary>left join</primary>
300 First, an inner join is performed. Then, for each row in
301 T1 that does not satisfy the join condition with any row in
302 T2, a joined row is added with null values in columns of
303 T2. Thus, the joined table always has at least
304 one row for each row in T1.
310 <term><literal>RIGHT OUTER JOIN</literal>
312 <primary>join</primary>
313 <secondary>right</secondary>
317 <primary>right join</primary>
323 First, an inner join is performed. Then, for each row in
324 T2 that does not satisfy the join condition with any row in
325 T1, a joined row is added with null values in columns of
326 T1. This is the converse of a left join: the result table
327 will always have a row for each row in T2.
333 <term><literal>FULL OUTER JOIN</literal></term>
337 First, an inner join is performed. Then, for each row in
338 T1 that does not satisfy the join condition with any row in
339 T2, a joined row is added with null values in columns of
340 T2. Also, for each row of T2 that does not satisfy the
341 join condition with any row in T1, a joined row with null
342 values in the columns of T1 is added.
350 The <literal>ON</literal> clause is the most general kind of join
351 condition: it takes a Boolean value expression of the same
352 kind as is used in a <literal>WHERE</literal> clause. A pair of rows
353 from <replaceable>T1</replaceable> and <replaceable>T2</replaceable> match if the
354 <literal>ON</literal> expression evaluates to true.
358 The <literal>USING</literal> clause is a shorthand that allows you to take
359 advantage of the specific situation where both sides of the join use
360 the same name for the joining column(s). It takes a
361 comma-separated list of the shared column names
362 and forms a join condition that includes an equality comparison
363 for each one. For example, joining <replaceable>T1</replaceable>
364 and <replaceable>T2</replaceable> with <literal>USING (a, b)</literal> produces
365 the join condition <literal>ON <replaceable>T1</replaceable>.a
366 = <replaceable>T2</replaceable>.a AND <replaceable>T1</replaceable>.b
367 = <replaceable>T2</replaceable>.b</literal>.
371 Furthermore, the output of <literal>JOIN USING</literal> suppresses
372 redundant columns: there is no need to print both of the matched
373 columns, since they must have equal values. While <literal>JOIN
374 ON</literal> produces all columns from <replaceable>T1</replaceable> followed by all
375 columns from <replaceable>T2</replaceable>, <literal>JOIN USING</literal> produces one
376 output column for each of the listed column pairs (in the listed
377 order), followed by any remaining columns from <replaceable>T1</replaceable>,
378 followed by any remaining columns from <replaceable>T2</replaceable>.
383 <primary>join</primary>
384 <secondary>natural</secondary>
387 <primary>natural join</primary>
389 Finally, <literal>NATURAL</literal> is a shorthand form of
390 <literal>USING</literal>: it forms a <literal>USING</literal> list
391 consisting of all column names that appear in both
392 input tables. As with <literal>USING</literal>, these columns appear
393 only once in the output table. If there are no common
394 column names, <literal>NATURAL JOIN</literal> behaves like
395 <literal>JOIN ... ON TRUE</literal>, producing a cross-product join.
400 <literal>USING</literal> is reasonably safe from column changes
401 in the joined relations since only the listed columns
402 are combined. <literal>NATURAL</literal> is considerably more risky since
403 any schema changes to either relation that cause a new matching
404 column name to be present will cause the join to combine that new
413 To put this together, assume we have tables <literal>t1</literal>:
421 and <literal>t2</literal>:
429 then we get the following results for the various joins:
431 <prompt>=></prompt> <userinput>SELECT * FROM t1 CROSS JOIN t2;</userinput>
432 num | name | num | value
433 -----+------+-----+-------
445 <prompt>=></prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</userinput>
446 num | name | num | value
447 -----+------+-----+-------
452 <prompt>=></prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 USING (num);</userinput>
459 <prompt>=></prompt> <userinput>SELECT * FROM t1 NATURAL INNER JOIN t2;</userinput>
466 <prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</userinput>
467 num | name | num | value
468 -----+------+-----+-------
474 <prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 USING (num);</userinput>
482 <prompt>=></prompt> <userinput>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</userinput>
483 num | name | num | value
484 -----+------+-----+-------
490 <prompt>=></prompt> <userinput>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</userinput>
491 num | name | num | value
492 -----+------+-----+-------
502 The join condition specified with <literal>ON</literal> can also contain
503 conditions that do not relate directly to the join. This can
504 prove useful for some queries but needs to be thought out
505 carefully. For example:
507 <prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</userinput>
508 num | name | num | value
509 -----+------+-----+-------
515 Notice that placing the restriction in the <literal>WHERE</literal> clause
516 produces a different result:
518 <prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</userinput>
519 num | name | num | value
520 -----+------+-----+-------
524 This is because a restriction placed in the <literal>ON</literal>
525 clause is processed <emphasis>before</emphasis> the join, while
526 a restriction placed in the <literal>WHERE</literal> clause is processed
527 <emphasis>after</emphasis> the join.
528 That does not matter with inner joins, but it matters a lot with outer
533 <sect3 id="queries-table-aliases">
534 <title>Table and Column Aliases</title>
536 <indexterm zone="queries-table-aliases">
537 <primary>alias</primary>
538 <secondary>in the FROM clause</secondary>
542 <primary>label</primary>
547 A temporary name can be given to tables and complex table
548 references to be used for references to the derived table in
549 the rest of the query. This is called a <firstterm>table
554 To create a table alias, write
556 FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable>
560 FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
562 The <literal>AS</literal> key word is optional noise.
563 <replaceable>alias</replaceable> can be any identifier.
567 A typical application of table aliases is to assign short
568 identifiers to long table names to keep the join clauses
569 readable. For example:
571 SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
576 The alias becomes the new name of the table reference so far as the
577 current query is concerned — it is not allowed to refer to the
578 table by the original name elsewhere in the query. Thus, this is not
581 SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
586 Table aliases are mainly for notational convenience, but it is
587 necessary to use them when joining a table to itself, e.g.:
589 SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
591 Additionally, an alias is required if the table reference is a
592 subquery (see <xref linkend="queries-subqueries"/>).
596 Parentheses are used to resolve ambiguities. In the following example,
597 the first statement assigns the alias <literal>b</literal> to the second
598 instance of <literal>my_table</literal>, but the second statement assigns the
599 alias to the result of the join:
601 SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
602 SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
607 Another form of table aliasing gives temporary names to the columns of
608 the table, as well as the table itself:
610 FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> )
612 If fewer column aliases are specified than the actual table has
613 columns, the remaining columns are not renamed. This syntax is
614 especially useful for self-joins or subqueries.
618 When an alias is applied to the output of a <literal>JOIN</literal>
619 clause, the alias hides the original
620 name(s) within the <literal>JOIN</literal>. For example:
622 SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
626 SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
628 is not valid; the table alias <literal>a</literal> is not visible
629 outside the alias <literal>c</literal>.
633 <sect3 id="queries-subqueries">
634 <title>Subqueries</title>
636 <indexterm zone="queries-subqueries">
637 <primary>subquery</primary>
641 Subqueries specifying a derived table must be enclosed in
642 parentheses and <emphasis>must</emphasis> be assigned a table
643 alias name (as in <xref linkend="queries-table-aliases"/>). For
646 FROM (SELECT * FROM table1) AS alias_name
651 This example is equivalent to <literal>FROM table1 AS
652 alias_name</literal>. More interesting cases, which cannot be
653 reduced to a plain join, arise when the subquery involves
654 grouping or aggregation.
658 A subquery can also be a <command>VALUES</command> list:
660 FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
661 AS names(first, last)
663 Again, a table alias is required. Assigning alias names to the columns
664 of the <command>VALUES</command> list is optional, but is good practice.
665 For more information see <xref linkend="queries-values"/>.
669 <sect3 id="queries-tablefunctions">
670 <title>Table Functions</title>
672 <indexterm zone="queries-tablefunctions"><primary>table function</primary></indexterm>
674 <indexterm zone="queries-tablefunctions">
675 <primary>function</primary>
676 <secondary>in the FROM clause</secondary>
680 Table functions are functions that produce a set of rows, made up
681 of either base data types (scalar types) or composite data types
682 (table rows). They are used like a table, view, or subquery in
683 the <literal>FROM</literal> clause of a query. Columns returned by table
684 functions can be included in <literal>SELECT</literal>,
685 <literal>JOIN</literal>, or <literal>WHERE</literal> clauses in the same manner
686 as columns of a table, view, or subquery.
690 Table functions may also be combined using the <literal>ROWS FROM</literal>
691 syntax, with the results returned in parallel columns; the number of
692 result rows in this case is that of the largest function result, with
693 smaller results padded with null values to match.
697 <replaceable>function_call</replaceable> <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
698 ROWS FROM( <replaceable>function_call</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
702 If the <literal>WITH ORDINALITY</literal> clause is specified, an
703 additional column of type <type>bigint</type> will be added to the
704 function result columns. This column numbers the rows of the function
705 result set, starting from 1. (This is a generalization of the
706 SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.)
707 By default, the ordinal column is called <literal>ordinality</literal>, but
708 a different column name can be assigned to it using
709 an <literal>AS</literal> clause.
713 The special table function <literal>UNNEST</literal> may be called with
714 any number of array parameters, and it returns a corresponding number of
715 columns, as if <literal>UNNEST</literal>
716 (<xref linkend="functions-array"/>) had been called on each parameter
717 separately and combined using the <literal>ROWS FROM</literal> construct.
721 UNNEST( <replaceable>array_expression</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
725 If no <replaceable>table_alias</replaceable> is specified, the function
726 name is used as the table name; in the case of a <literal>ROWS FROM()</literal>
727 construct, the first function's name is used.
731 If column aliases are not supplied, then for a function returning a base
732 data type, the column name is also the same as the function name. For a
733 function returning a composite type, the result columns get the names
734 of the individual attributes of the type.
740 CREATE TABLE foo (fooid int, foosubid int, fooname text);
742 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
743 SELECT * FROM foo WHERE fooid = $1;
746 SELECT * FROM getfoo(1) AS t1;
751 FROM getfoo(foo.fooid) z
752 WHERE z.fooid = foo.fooid
755 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
757 SELECT * FROM vw_getfoo;
762 In some cases it is useful to define table functions that can
763 return different column sets depending on how they are invoked.
764 To support this, the table function can be declared as returning
765 the pseudo-type <type>record</type>. When such a function is used in
766 a query, the expected row structure must be specified in the
767 query itself, so that the system can know how to parse and plan
768 the query. This syntax looks like:
772 <replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
773 <replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
774 ROWS FROM( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
778 When not using the <literal>ROWS FROM()</literal> syntax,
779 the <replaceable>column_definition</replaceable> list replaces the column
780 alias list that could otherwise be attached to the <literal>FROM</literal>
781 item; the names in the column definitions serve as column aliases.
782 When using the <literal>ROWS FROM()</literal> syntax,
783 a <replaceable>column_definition</replaceable> list can be attached to
784 each member function separately; or if there is only one member function
785 and no <literal>WITH ORDINALITY</literal> clause,
786 a <replaceable>column_definition</replaceable> list can be written in
787 place of a column alias list following <literal>ROWS FROM()</literal>.
791 Consider this example:
794 FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
795 AS t1(proname name, prosrc text)
796 WHERE proname LIKE 'bytea%';
798 The <xref linkend="contrib-dblink-function"/> function
799 (part of the <xref linkend="dblink"/> module) executes
800 a remote query. It is declared to return
801 <type>record</type> since it might be used for any kind of query.
802 The actual column set must be specified in the calling query so
803 that the parser knows, for example, what <literal>*</literal> should
808 <sect3 id="queries-lateral">
809 <title><literal>LATERAL</literal> Subqueries</title>
811 <indexterm zone="queries-lateral">
812 <primary>LATERAL</primary>
813 <secondary>in the FROM clause</secondary>
817 Subqueries appearing in <literal>FROM</literal> can be
818 preceded by the key word <literal>LATERAL</literal>. This allows them to
819 reference columns provided by preceding <literal>FROM</literal> items.
820 (Without <literal>LATERAL</literal>, each subquery is
821 evaluated independently and so cannot cross-reference any other
822 <literal>FROM</literal> item.)
826 Table functions appearing in <literal>FROM</literal> can also be
827 preceded by the key word <literal>LATERAL</literal>, but for functions the
828 key word is optional; the function's arguments can contain references
829 to columns provided by preceding <literal>FROM</literal> items in any case.
833 A <literal>LATERAL</literal> item can appear at top level in the
834 <literal>FROM</literal> list, or within a <literal>JOIN</literal> tree. In the latter
835 case it can also refer to any items that are on the left-hand side of a
836 <literal>JOIN</literal> that it is on the right-hand side of.
840 When a <literal>FROM</literal> item contains <literal>LATERAL</literal>
841 cross-references, evaluation proceeds as follows: for each row of the
842 <literal>FROM</literal> item providing the cross-referenced column(s), or
843 set of rows of multiple <literal>FROM</literal> items providing the
844 columns, the <literal>LATERAL</literal> item is evaluated using that
845 row or row set's values of the columns. The resulting row(s) are
846 joined as usual with the rows they were computed from. This is
847 repeated for each row or set of rows from the column source table(s).
851 A trivial example of <literal>LATERAL</literal> is
853 SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
855 This is not especially useful since it has exactly the same result as
856 the more conventional
858 SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
860 <literal>LATERAL</literal> is primarily useful when the cross-referenced
861 column is necessary for computing the row(s) to be joined. A common
862 application is providing an argument value for a set-returning function.
863 For example, supposing that <function>vertices(polygon)</function> returns the
864 set of vertices of a polygon, we could identify close-together vertices
865 of polygons stored in a table with:
867 SELECT p1.id, p2.id, v1, v2
868 FROM polygons p1, polygons p2,
869 LATERAL vertices(p1.poly) v1,
870 LATERAL vertices(p2.poly) v2
871 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
873 This query could also be written
875 SELECT p1.id, p2.id, v1, v2
876 FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
877 polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
878 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
880 or in several other equivalent formulations. (As already mentioned,
881 the <literal>LATERAL</literal> key word is unnecessary in this example, but
882 we use it for clarity.)
886 It is often particularly handy to <literal>LEFT JOIN</literal> to a
887 <literal>LATERAL</literal> subquery, so that source rows will appear in
888 the result even if the <literal>LATERAL</literal> subquery produces no
889 rows for them. For example, if <function>get_product_names()</function> returns
890 the names of products made by a manufacturer, but some manufacturers in
891 our table currently produce no products, we could find out which ones
895 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
902 <sect2 id="queries-where">
903 <title>The <literal>WHERE</literal> Clause</title>
905 <indexterm zone="queries-where">
906 <primary>WHERE</primary>
910 The syntax of the <xref linkend="sql-where"
911 endterm="sql-where-title"/> is
913 WHERE <replaceable>search_condition</replaceable>
915 where <replaceable>search_condition</replaceable> is any value
916 expression (see <xref linkend="sql-expressions"/>) that
917 returns a value of type <type>boolean</type>.
921 After the processing of the <literal>FROM</literal> clause is done, each
922 row of the derived virtual table is checked against the search
923 condition. If the result of the condition is true, the row is
924 kept in the output table, otherwise (i.e., if the result is
925 false or null) it is discarded. The search condition typically
926 references at least one column of the table generated in the
927 <literal>FROM</literal> clause; this is not required, but otherwise the
928 <literal>WHERE</literal> clause will be fairly useless.
933 The join condition of an inner join can be written either in
934 the <literal>WHERE</literal> clause or in the <literal>JOIN</literal> clause.
935 For example, these table expressions are equivalent:
937 FROM a, b WHERE a.id = b.id AND b.val > 5
941 FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
945 FROM a NATURAL JOIN b WHERE b.val > 5
947 Which one of these you use is mainly a matter of style. The
948 <literal>JOIN</literal> syntax in the <literal>FROM</literal> clause is
949 probably not as portable to other SQL database management systems,
950 even though it is in the SQL standard. For
951 outer joins there is no choice: they must be done in
952 the <literal>FROM</literal> clause. The <literal>ON</literal> or <literal>USING</literal>
953 clause of an outer join is <emphasis>not</emphasis> equivalent to a
954 <literal>WHERE</literal> condition, because it results in the addition
955 of rows (for unmatched input rows) as well as the removal of rows
961 Here are some examples of <literal>WHERE</literal> clauses:
963 SELECT ... FROM fdt WHERE c1 > 5
965 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
967 SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
969 SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
971 SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
973 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
975 <literal>fdt</literal> is the table derived in the
976 <literal>FROM</literal> clause. Rows that do not meet the search
977 condition of the <literal>WHERE</literal> clause are eliminated from
978 <literal>fdt</literal>. Notice the use of scalar subqueries as
979 value expressions. Just like any other query, the subqueries can
980 employ complex table expressions. Notice also how
981 <literal>fdt</literal> is referenced in the subqueries.
982 Qualifying <literal>c1</literal> as <literal>fdt.c1</literal> is only necessary
983 if <literal>c1</literal> is also the name of a column in the derived
984 input table of the subquery. But qualifying the column name adds
985 clarity even when it is not needed. This example shows how the column
986 naming scope of an outer query extends into its inner queries.
991 <sect2 id="queries-group">
992 <title>The <literal>GROUP BY</literal> and <literal>HAVING</literal> Clauses</title>
994 <indexterm zone="queries-group">
995 <primary>GROUP BY</primary>
998 <indexterm zone="queries-group">
999 <primary>grouping</primary>
1003 After passing the <literal>WHERE</literal> filter, the derived input
1004 table might be subject to grouping, using the <literal>GROUP BY</literal>
1005 clause, and elimination of group rows using the <literal>HAVING</literal>
1010 SELECT <replaceable>select_list</replaceable>
1012 <optional>WHERE ...</optional>
1013 GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
1017 The <xref linkend="sql-groupby" endterm="sql-groupby-title"/> is
1018 used to group together those rows in a table that have the same
1019 values in all the columns listed. The order in which the columns
1020 are listed does not matter. The effect is to combine each set
1021 of rows having common values into one group row that
1022 represents all rows in the group. This is done to
1023 eliminate redundancy in the output and/or compute aggregates that
1024 apply to these groups. For instance:
1026 <prompt>=></prompt> <userinput>SELECT * FROM test1;</userinput>
1035 <prompt>=></prompt> <userinput>SELECT x FROM test1 GROUP BY x;</userinput>
1046 In the second query, we could not have written <literal>SELECT *
1047 FROM test1 GROUP BY x</literal>, because there is no single value
1048 for the column <literal>y</literal> that could be associated with each
1049 group. The grouped-by columns can be referenced in the select list since
1050 they have a single value in each group.
1054 In general, if a table is grouped, columns that are not
1055 listed in <literal>GROUP BY</literal> cannot be referenced except in aggregate
1056 expressions. An example with aggregate expressions is:
1058 <prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x;</userinput>
1066 Here <literal>sum</literal> is an aggregate function that
1067 computes a single value over the entire group. More information
1068 about the available aggregate functions can be found in <xref
1069 linkend="functions-aggregate"/>.
1074 Grouping without aggregate expressions effectively calculates the
1075 set of distinct values in a column. This can also be achieved
1076 using the <literal>DISTINCT</literal> clause (see <xref
1077 linkend="queries-distinct"/>).
1082 Here is another example: it calculates the total sales for each
1083 product (rather than the total sales of all products):
1085 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
1086 FROM products p LEFT JOIN sales s USING (product_id)
1087 GROUP BY product_id, p.name, p.price;
1089 In this example, the columns <literal>product_id</literal>,
1090 <literal>p.name</literal>, and <literal>p.price</literal> must be
1091 in the <literal>GROUP BY</literal> clause since they are referenced in
1092 the query select list (but see below). The column
1093 <literal>s.units</literal> does not have to be in the <literal>GROUP
1094 BY</literal> list since it is only used in an aggregate expression
1095 (<literal>sum(...)</literal>), which represents the sales
1096 of a product. For each product, the query returns a summary row about
1097 all sales of the product.
1100 <indexterm><primary>functional dependency</primary></indexterm>
1103 If the products table is set up so that, say,
1104 <literal>product_id</literal> is the primary key, then it would be
1105 enough to group by <literal>product_id</literal> in the above example,
1106 since name and price would be <firstterm>functionally
1107 dependent</firstterm> on the product ID, and so there would be no
1108 ambiguity about which name and price value to return for each product
1113 In strict SQL, <literal>GROUP BY</literal> can only group by columns of
1114 the source table but <productname>PostgreSQL</productname> extends
1115 this to also allow <literal>GROUP BY</literal> to group by columns in the
1116 select list. Grouping by value expressions instead of simple
1117 column names is also allowed.
1121 <primary>HAVING</primary>
1125 If a table has been grouped using <literal>GROUP BY</literal>,
1126 but only certain groups are of interest, the
1127 <literal>HAVING</literal> clause can be used, much like a
1128 <literal>WHERE</literal> clause, to eliminate groups from the result.
1131 SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable>
1133 Expressions in the <literal>HAVING</literal> clause can refer both to
1134 grouped expressions and to ungrouped expressions (which necessarily
1135 involve an aggregate function).
1141 <prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</userinput>
1148 <prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';</userinput>
1158 Again, a more realistic example:
1160 SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
1161 FROM products p LEFT JOIN sales s USING (product_id)
1162 WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
1163 GROUP BY product_id, p.name, p.price, p.cost
1164 HAVING sum(p.price * s.units) > 5000;
1166 In the example above, the <literal>WHERE</literal> clause is selecting
1167 rows by a column that is not grouped (the expression is only true for
1168 sales during the last four weeks), while the <literal>HAVING</literal>
1169 clause restricts the output to groups with total gross sales over
1170 5000. Note that the aggregate expressions do not necessarily need
1171 to be the same in all parts of the query.
1175 If a query contains aggregate function calls, but no <literal>GROUP BY</literal>
1176 clause, grouping still occurs: the result is a single group row (or
1177 perhaps no rows at all, if the single row is then eliminated by
1178 <literal>HAVING</literal>).
1179 The same is true if it contains a <literal>HAVING</literal> clause, even
1180 without any aggregate function calls or <literal>GROUP BY</literal> clause.
1184 <sect2 id="queries-grouping-sets">
1185 <title><literal>GROUPING SETS</literal>, <literal>CUBE</literal>, and <literal>ROLLUP</literal></title>
1187 <indexterm zone="queries-grouping-sets">
1188 <primary>GROUPING SETS</primary>
1190 <indexterm zone="queries-grouping-sets">
1191 <primary>CUBE</primary>
1193 <indexterm zone="queries-grouping-sets">
1194 <primary>ROLLUP</primary>
1198 More complex grouping operations than those described above are possible
1199 using the concept of <firstterm>grouping sets</firstterm>. The data selected by
1200 the <literal>FROM</literal> and <literal>WHERE</literal> clauses is grouped separately
1201 by each specified grouping set, aggregates computed for each group just as
1202 for simple <literal>GROUP BY</literal> clauses, and then the results returned.
1205 <prompt>=></prompt> <userinput>SELECT * FROM items_sold;</userinput>
1206 brand | size | sales
1207 -------+------+-------
1214 <prompt>=></prompt> <userinput>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</userinput>
1216 -------+------+-----
1227 Each sublist of <literal>GROUPING SETS</literal> may specify zero or more columns
1228 or expressions and is interpreted the same way as though it were directly
1229 in the <literal>GROUP BY</literal> clause. An empty grouping set means that all
1230 rows are aggregated down to a single group (which is output even if no
1231 input rows were present), as described above for the case of aggregate
1232 functions with no <literal>GROUP BY</literal> clause.
1236 References to the grouping columns or expressions are replaced
1237 by null values in result rows for grouping sets in which those
1238 columns do not appear. To distinguish which grouping a particular output
1239 row resulted from, see <xref linkend="functions-grouping-table"/>.
1243 A shorthand notation is provided for specifying two common types of grouping set.
1244 A clause of the form
1246 ROLLUP ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... )
1248 represents the given list of expressions and all prefixes of the list including
1249 the empty list; thus it is equivalent to
1252 ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... ),
1254 ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable> ),
1255 ( <replaceable>e1</replaceable> ),
1259 This is commonly used for analysis over hierarchical data; e.g. total
1260 salary by department, division, and company-wide total.
1264 A clause of the form
1266 CUBE ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, ... )
1268 represents the given list and all of its possible subsets (i.e. the power
1289 The individual elements of a <literal>CUBE</literal> or <literal>ROLLUP</literal>
1290 clause may be either individual expressions, or sublists of elements in
1291 parentheses. In the latter case, the sublists are treated as single
1292 units for the purposes of generating the individual grouping sets.
1295 CUBE ( (a, b), (c, d) )
1308 ROLLUP ( a, (b, c), d )
1322 The <literal>CUBE</literal> and <literal>ROLLUP</literal> constructs can be used either
1323 directly in the <literal>GROUP BY</literal> clause, or nested inside a
1324 <literal>GROUPING SETS</literal> clause. If one <literal>GROUPING SETS</literal> clause
1325 is nested inside another, the effect is the same as if all the elements of
1326 the inner clause had been written directly in the outer clause.
1330 If multiple grouping items are specified in a single <literal>GROUP BY</literal>
1331 clause, then the final list of grouping sets is the cross product of the
1332 individual items. For example:
1334 GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
1338 GROUP BY GROUPING SETS (
1339 (a, b, c, d), (a, b, c, e),
1340 (a, b, d), (a, b, e),
1341 (a, c, d), (a, c, e),
1349 The construct <literal>(a, b)</literal> is normally recognized in expressions as
1350 a <link linkend="sql-syntax-row-constructors">row constructor</link>.
1351 Within the <literal>GROUP BY</literal> clause, this does not apply at the top
1352 levels of expressions, and <literal>(a, b)</literal> is parsed as a list of
1353 expressions as described above. If for some reason you <emphasis>need</emphasis>
1354 a row constructor in a grouping expression, use <literal>ROW(a, b)</literal>.
1359 <sect2 id="queries-window">
1360 <title>Window Function Processing</title>
1362 <indexterm zone="queries-window">
1363 <primary>window function</primary>
1364 <secondary>order of execution</secondary>
1368 If the query contains any window functions (see
1369 <xref linkend="tutorial-window"/>,
1370 <xref linkend="functions-window"/> and
1371 <xref linkend="syntax-window-functions"/>), these functions are evaluated
1372 after any grouping, aggregation, and <literal>HAVING</literal> filtering is
1373 performed. That is, if the query uses any aggregates, <literal>GROUP
1374 BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions
1375 are the group rows instead of the original table rows from
1376 <literal>FROM</literal>/<literal>WHERE</literal>.
1380 When multiple window functions are used, all the window functions having
1381 syntactically equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
1382 clauses in their window definitions are guaranteed to be evaluated in a
1383 single pass over the data. Therefore they will see the same sort ordering,
1384 even if the <literal>ORDER BY</literal> does not uniquely determine an ordering.
1385 However, no guarantees are made about the evaluation of functions having
1386 different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
1387 (In such cases a sort step is typically required between the passes of
1388 window function evaluations, and the sort is not guaranteed to preserve
1389 ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.)
1393 Currently, window functions always require presorted data, and so the
1394 query output will be ordered according to one or another of the window
1395 functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses.
1396 It is not recommended to rely on this, however. Use an explicit
1397 top-level <literal>ORDER BY</literal> clause if you want to be sure the
1398 results are sorted in a particular way.
1404 <sect1 id="queries-select-lists">
1405 <title>Select Lists</title>
1408 <primary>SELECT</primary>
1409 <secondary>select list</secondary>
1413 As shown in the previous section,
1414 the table expression in the <command>SELECT</command> command
1415 constructs an intermediate virtual table by possibly combining
1416 tables, views, eliminating rows, grouping, etc. This table is
1417 finally passed on to processing by the <firstterm>select list</firstterm>. The select
1418 list determines which <emphasis>columns</emphasis> of the
1419 intermediate table are actually output.
1422 <sect2 id="queries-select-list-items">
1423 <title>Select-List Items</title>
1426 <primary>*</primary>
1430 The simplest kind of select list is <literal>*</literal> which
1431 emits all columns that the table expression produces. Otherwise,
1432 a select list is a comma-separated list of value expressions (as
1433 defined in <xref linkend="sql-expressions"/>). For instance, it
1434 could be a list of column names:
1436 SELECT a, b, c FROM ...
1438 The columns names <literal>a</literal>, <literal>b</literal>, and <literal>c</literal>
1439 are either the actual names of the columns of tables referenced
1440 in the <literal>FROM</literal> clause, or the aliases given to them as
1441 explained in <xref linkend="queries-table-aliases"/>. The name
1442 space available in the select list is the same as in the
1443 <literal>WHERE</literal> clause, unless grouping is used, in which case
1444 it is the same as in the <literal>HAVING</literal> clause.
1448 If more than one table has a column of the same name, the table
1449 name must also be given, as in:
1451 SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
1453 When working with multiple tables, it can also be useful to ask for
1454 all the columns of a particular table:
1456 SELECT tbl1.*, tbl2.a FROM ...
1458 See <xref linkend="rowtypes-usage"/> for more about
1459 the <replaceable>table_name</replaceable><literal>.*</literal> notation.
1463 If an arbitrary value expression is used in the select list, it
1464 conceptually adds a new virtual column to the returned table. The
1465 value expression is evaluated once for each result row, with
1466 the row's values substituted for any column references. But the
1467 expressions in the select list do not have to reference any
1468 columns in the table expression of the <literal>FROM</literal> clause;
1469 they can be constant arithmetic expressions, for instance.
1473 <sect2 id="queries-column-labels">
1474 <title>Column Labels</title>
1476 <indexterm zone="queries-column-labels">
1477 <primary>alias</primary>
1478 <secondary>in the select list</secondary>
1482 The entries in the select list can be assigned names for subsequent
1483 processing, such as for use in an <literal>ORDER BY</literal> clause
1484 or for display by the client application. For example:
1486 SELECT a AS value, b + c AS sum FROM ...
1491 If no output column name is specified using <literal>AS</literal>,
1492 the system assigns a default column name. For simple column references,
1493 this is the name of the referenced column. For function
1494 calls, this is the name of the function. For complex expressions,
1495 the system will generate a generic name.
1499 The <literal>AS</literal> keyword is optional, but only if the new column
1500 name does not match any
1501 <productname>PostgreSQL</productname> keyword (see <xref
1502 linkend="sql-keywords-appendix"/>). To avoid an accidental match to
1503 a keyword, you can double-quote the column name. For example,
1504 <literal>VALUE</literal> is a keyword, so this does not work:
1506 SELECT a value, b + c AS sum FROM ...
1510 SELECT a "value", b + c AS sum FROM ...
1512 For protection against possible
1513 future keyword additions, it is recommended that you always either
1514 write <literal>AS</literal> or double-quote the output column name.
1519 The naming of output columns here is different from that done in
1520 the <literal>FROM</literal> clause (see <xref
1521 linkend="queries-table-aliases"/>). It is possible
1522 to rename the same column twice, but the name assigned in
1523 the select list is the one that will be passed on.
1528 <sect2 id="queries-distinct">
1529 <title><literal>DISTINCT</literal></title>
1531 <indexterm zone="queries-distinct">
1532 <primary>DISTINCT</primary>
1535 <indexterm zone="queries-distinct">
1536 <primary>duplicates</primary>
1540 After the select list has been processed, the result table can
1541 optionally be subject to the elimination of duplicate rows. The
1542 <literal>DISTINCT</literal> key word is written directly after
1543 <literal>SELECT</literal> to specify this:
1545 SELECT DISTINCT <replaceable>select_list</replaceable> ...
1547 (Instead of <literal>DISTINCT</literal> the key word <literal>ALL</literal>
1548 can be used to specify the default behavior of retaining all rows.)
1552 <primary>null value</primary>
1553 <secondary sortas="DISTINCT">in DISTINCT</secondary>
1557 Obviously, two rows are considered distinct if they differ in at
1558 least one column value. Null values are considered equal in this
1563 Alternatively, an arbitrary expression can determine what rows are
1564 to be considered distinct:
1566 SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ...
1568 Here <replaceable>expression</replaceable> is an arbitrary value
1569 expression that is evaluated for all rows. A set of rows for
1570 which all the expressions are equal are considered duplicates, and
1571 only the first row of the set is kept in the output. Note that
1572 the <quote>first row</quote> of a set is unpredictable unless the
1573 query is sorted on enough columns to guarantee a unique ordering
1574 of the rows arriving at the <literal>DISTINCT</literal> filter.
1575 (<literal>DISTINCT ON</literal> processing occurs after <literal>ORDER
1576 BY</literal> sorting.)
1580 The <literal>DISTINCT ON</literal> clause is not part of the SQL standard
1581 and is sometimes considered bad style because of the potentially
1582 indeterminate nature of its results. With judicious use of
1583 <literal>GROUP BY</literal> and subqueries in <literal>FROM</literal>, this
1584 construct can be avoided, but it is often the most convenient
1591 <sect1 id="queries-union">
1592 <title>Combining Queries</title>
1594 <indexterm zone="queries-union">
1595 <primary>UNION</primary>
1597 <indexterm zone="queries-union">
1598 <primary>INTERSECT</primary>
1600 <indexterm zone="queries-union">
1601 <primary>EXCEPT</primary>
1603 <indexterm zone="queries-union">
1604 <primary>set union</primary>
1606 <indexterm zone="queries-union">
1607 <primary>set intersection</primary>
1609 <indexterm zone="queries-union">
1610 <primary>set difference</primary>
1612 <indexterm zone="queries-union">
1613 <primary>set operation</primary>
1617 The results of two queries can be combined using the set operations
1618 union, intersection, and difference. The syntax is
1620 <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
1621 <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
1622 <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
1624 <replaceable>query1</replaceable> and
1625 <replaceable>query2</replaceable> are queries that can use any of
1626 the features discussed up to this point. Set operations can also
1627 be nested and chained, for example
1629 <replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> UNION <replaceable>query3</replaceable>
1631 which is executed as:
1633 (<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) UNION <replaceable>query3</replaceable>
1638 <literal>UNION</literal> effectively appends the result of
1639 <replaceable>query2</replaceable> to the result of
1640 <replaceable>query1</replaceable> (although there is no guarantee
1641 that this is the order in which the rows are actually returned).
1642 Furthermore, it eliminates duplicate rows from its result, in the same
1643 way as <literal>DISTINCT</literal>, unless <literal>UNION ALL</literal> is used.
1647 <literal>INTERSECT</literal> returns all rows that are both in the result
1648 of <replaceable>query1</replaceable> and in the result of
1649 <replaceable>query2</replaceable>. Duplicate rows are eliminated
1650 unless <literal>INTERSECT ALL</literal> is used.
1654 <literal>EXCEPT</literal> returns all rows that are in the result of
1655 <replaceable>query1</replaceable> but not in the result of
1656 <replaceable>query2</replaceable>. (This is sometimes called the
1657 <firstterm>difference</firstterm> between two queries.) Again, duplicates
1658 are eliminated unless <literal>EXCEPT ALL</literal> is used.
1662 In order to calculate the union, intersection, or difference of two
1663 queries, the two queries must be <quote>union compatible</quote>,
1664 which means that they return the same number of columns and
1665 the corresponding columns have compatible data types, as
1666 described in <xref linkend="typeconv-union-case"/>.
1671 <sect1 id="queries-order">
1672 <title>Sorting Rows</title>
1674 <indexterm zone="queries-order">
1675 <primary>sorting</primary>
1678 <indexterm zone="queries-order">
1679 <primary>ORDER BY</primary>
1683 After a query has produced an output table (after the select list
1684 has been processed) it can optionally be sorted. If sorting is not
1685 chosen, the rows will be returned in an unspecified order. The actual
1686 order in that case will depend on the scan and join plan types and
1687 the order on disk, but it must not be relied on. A particular
1688 output ordering can only be guaranteed if the sort step is explicitly
1693 The <literal>ORDER BY</literal> clause specifies the sort order:
1695 SELECT <replaceable>select_list</replaceable>
1696 FROM <replaceable>table_expression</replaceable>
1697 ORDER BY <replaceable>sort_expression1</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional>
1698 <optional>, <replaceable>sort_expression2</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional> ...</optional>
1700 The sort expression(s) can be any expression that would be valid in the
1701 query's select list. An example is:
1703 SELECT a, b FROM table1 ORDER BY a + b, c;
1705 When more than one expression is specified,
1706 the later values are used to sort rows that are equal according to the
1707 earlier values. Each expression can be followed by an optional
1708 <literal>ASC</literal> or <literal>DESC</literal> keyword to set the sort direction to
1709 ascending or descending. <literal>ASC</literal> order is the default.
1710 Ascending order puts smaller values first, where
1711 <quote>smaller</quote> is defined in terms of the
1712 <literal><</literal> operator. Similarly, descending order is
1713 determined with the <literal>></literal> operator.
1716 Actually, <productname>PostgreSQL</productname> uses the <firstterm>default B-tree
1717 operator class</firstterm> for the expression's data type to determine the sort
1718 ordering for <literal>ASC</literal> and <literal>DESC</literal>. Conventionally,
1719 data types will be set up so that the <literal><</literal> and
1720 <literal>></literal> operators correspond to this sort ordering,
1721 but a user-defined data type's designer could choose to do something
1728 The <literal>NULLS FIRST</literal> and <literal>NULLS LAST</literal> options can be
1729 used to determine whether nulls appear before or after non-null values
1730 in the sort ordering. By default, null values sort as if larger than any
1731 non-null value; that is, <literal>NULLS FIRST</literal> is the default for
1732 <literal>DESC</literal> order, and <literal>NULLS LAST</literal> otherwise.
1736 Note that the ordering options are considered independently for each
1737 sort column. For example <literal>ORDER BY x, y DESC</literal> means
1738 <literal>ORDER BY x ASC, y DESC</literal>, which is not the same as
1739 <literal>ORDER BY x DESC, y DESC</literal>.
1743 A <replaceable>sort_expression</replaceable> can also be the column label or number
1744 of an output column, as in:
1746 SELECT a + b AS sum, c FROM table1 ORDER BY sum;
1747 SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
1749 both of which sort by the first output column. Note that an output
1750 column name has to stand alone, that is, it cannot be used in an expression
1751 — for example, this is <emphasis>not</emphasis> correct:
1753 SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
1755 This restriction is made to reduce ambiguity. There is still
1756 ambiguity if an <literal>ORDER BY</literal> item is a simple name that
1757 could match either an output column name or a column from the table
1758 expression. The output column is used in such cases. This would
1759 only cause confusion if you use <literal>AS</literal> to rename an output
1760 column to match some other table column's name.
1764 <literal>ORDER BY</literal> can be applied to the result of a
1765 <literal>UNION</literal>, <literal>INTERSECT</literal>, or <literal>EXCEPT</literal>
1766 combination, but in this case it is only permitted to sort by
1767 output column names or numbers, not by expressions.
1772 <sect1 id="queries-limit">
1773 <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1775 <indexterm zone="queries-limit">
1776 <primary>LIMIT</primary>
1779 <indexterm zone="queries-limit">
1780 <primary>OFFSET</primary>
1784 <literal>LIMIT</literal> and <literal>OFFSET</literal> allow you to retrieve just
1785 a portion of the rows that are generated by the rest of the query:
1787 SELECT <replaceable>select_list</replaceable>
1788 FROM <replaceable>table_expression</replaceable>
1789 <optional> ORDER BY ... </optional>
1790 <optional> LIMIT { <replaceable>number</replaceable> | ALL } </optional> <optional> OFFSET <replaceable>number</replaceable> </optional>
1795 If a limit count is given, no more than that many rows will be
1796 returned (but possibly fewer, if the query itself yields fewer rows).
1797 <literal>LIMIT ALL</literal> is the same as omitting the <literal>LIMIT</literal>
1798 clause, as is <literal>LIMIT</literal> with a NULL argument.
1802 <literal>OFFSET</literal> says to skip that many rows before beginning to
1803 return rows. <literal>OFFSET 0</literal> is the same as omitting the
1804 <literal>OFFSET</literal> clause, as is <literal>OFFSET</literal> with a NULL argument.
1808 If both <literal>OFFSET</literal>
1809 and <literal>LIMIT</literal> appear, then <literal>OFFSET</literal> rows are
1810 skipped before starting to count the <literal>LIMIT</literal> rows that
1815 When using <literal>LIMIT</literal>, it is important to use an
1816 <literal>ORDER BY</literal> clause that constrains the result rows into a
1817 unique order. Otherwise you will get an unpredictable subset of
1818 the query's rows. You might be asking for the tenth through
1819 twentieth rows, but tenth through twentieth in what ordering? The
1820 ordering is unknown, unless you specified <literal>ORDER BY</literal>.
1824 The query optimizer takes <literal>LIMIT</literal> into account when
1825 generating query plans, so you are very likely to get different
1826 plans (yielding different row orders) depending on what you give
1827 for <literal>LIMIT</literal> and <literal>OFFSET</literal>. Thus, using
1828 different <literal>LIMIT</literal>/<literal>OFFSET</literal> values to select
1829 different subsets of a query result <emphasis>will give
1830 inconsistent results</emphasis> unless you enforce a predictable
1831 result ordering with <literal>ORDER BY</literal>. This is not a bug; it
1832 is an inherent consequence of the fact that SQL does not promise to
1833 deliver the results of a query in any particular order unless
1834 <literal>ORDER BY</literal> is used to constrain the order.
1838 The rows skipped by an <literal>OFFSET</literal> clause still have to be
1839 computed inside the server; therefore a large <literal>OFFSET</literal>
1840 might be inefficient.
1845 <sect1 id="queries-values">
1846 <title><literal>VALUES</literal> Lists</title>
1848 <indexterm zone="queries-values">
1849 <primary>VALUES</primary>
1853 <literal>VALUES</literal> provides a way to generate a <quote>constant table</quote>
1854 that can be used in a query without having to actually create and populate
1855 a table on-disk. The syntax is
1857 VALUES ( <replaceable class="parameter">expression</replaceable> [, ...] ) [, ...]
1859 Each parenthesized list of expressions generates a row in the table.
1860 The lists must all have the same number of elements (i.e., the number
1861 of columns in the table), and corresponding entries in each list must
1862 have compatible data types. The actual data type assigned to each column
1863 of the result is determined using the same rules as for <literal>UNION</literal>
1864 (see <xref linkend="typeconv-union-case"/>).
1870 VALUES (1, 'one'), (2, 'two'), (3, 'three');
1873 will return a table of two columns and three rows. It's effectively
1876 SELECT 1 AS column1, 'one' AS column2
1883 By default, <productname>PostgreSQL</productname> assigns the names
1884 <literal>column1</literal>, <literal>column2</literal>, etc. to the columns of a
1885 <literal>VALUES</literal> table. The column names are not specified by the
1886 SQL standard and different database systems do it differently, so
1887 it's usually better to override the default names with a table alias
1890 => SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
1901 Syntactically, <literal>VALUES</literal> followed by expression lists is
1902 treated as equivalent to:
1904 SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
1906 and can appear anywhere a <literal>SELECT</literal> can. For example, you can
1907 use it as part of a <literal>UNION</literal>, or attach a
1908 <replaceable>sort_specification</replaceable> (<literal>ORDER BY</literal>,
1909 <literal>LIMIT</literal>, and/or <literal>OFFSET</literal>) to it. <literal>VALUES</literal>
1910 is most commonly used as the data source in an <command>INSERT</command> command,
1911 and next most commonly as a subquery.
1915 For more information see <xref linkend="sql-values"/>.
1921 <sect1 id="queries-with">
1922 <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
1924 <indexterm zone="queries-with">
1925 <primary>WITH</primary>
1926 <secondary>in SELECT</secondary>
1930 <primary>common table expression</primary>
1935 <literal>WITH</literal> provides a way to write auxiliary statements for use in a
1936 larger query. These statements, which are often referred to as Common
1937 Table Expressions or <acronym>CTE</acronym>s, can be thought of as defining
1938 temporary tables that exist just for one query. Each auxiliary statement
1939 in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
1940 <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>; and the
1941 <literal>WITH</literal> clause itself is attached to a primary statement that can
1942 also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, or
1943 <command>DELETE</command>.
1946 <sect2 id="queries-with-select">
1947 <title><command>SELECT</command> in <literal>WITH</literal></title>
1950 The basic value of <command>SELECT</command> in <literal>WITH</literal> is to
1951 break down complicated queries into simpler parts. An example is:
1954 WITH regional_sales AS (
1955 SELECT region, SUM(amount) AS total_sales
1961 WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
1965 SUM(quantity) AS product_units,
1966 SUM(amount) AS product_sales
1968 WHERE region IN (SELECT region FROM top_regions)
1969 GROUP BY region, product;
1972 which displays per-product sales totals in only the top sales regions.
1973 The <literal>WITH</literal> clause defines two auxiliary statements named
1974 <structname>regional_sales</structname> and <structname>top_regions</structname>,
1975 where the output of <structname>regional_sales</structname> is used in
1976 <structname>top_regions</structname> and the output of <structname>top_regions</structname>
1977 is used in the primary <command>SELECT</command> query.
1978 This example could have been written without <literal>WITH</literal>,
1979 but we'd have needed two levels of nested sub-<command>SELECT</command>s. It's a bit
1980 easier to follow this way.
1985 <primary>RECURSIVE</primary>
1986 <secondary>in common table expressions</secondary>
1988 The optional <literal>RECURSIVE</literal> modifier changes <literal>WITH</literal>
1989 from a mere syntactic convenience into a feature that accomplishes
1990 things not otherwise possible in standard SQL. Using
1991 <literal>RECURSIVE</literal>, a <literal>WITH</literal> query can refer to its own
1992 output. A very simple example is this query to sum the integers from 1
1996 WITH RECURSIVE t(n) AS (
1999 SELECT n+1 FROM t WHERE n < 100
2001 SELECT sum(n) FROM t;
2004 The general form of a recursive <literal>WITH</literal> query is always a
2005 <firstterm>non-recursive term</firstterm>, then <literal>UNION</literal> (or
2006 <literal>UNION ALL</literal>), then a
2007 <firstterm>recursive term</firstterm>, where only the recursive term can contain
2008 a reference to the query's own output. Such a query is executed as
2013 <title>Recursive Query Evaluation</title>
2015 <step performance="required">
2017 Evaluate the non-recursive term. For <literal>UNION</literal> (but not
2018 <literal>UNION ALL</literal>), discard duplicate rows. Include all remaining
2019 rows in the result of the recursive query, and also place them in a
2020 temporary <firstterm>working table</firstterm>.
2024 <step performance="required">
2026 So long as the working table is not empty, repeat these steps:
2029 <step performance="required">
2031 Evaluate the recursive term, substituting the current contents of
2032 the working table for the recursive self-reference.
2033 For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard
2034 duplicate rows and rows that duplicate any previous result row.
2035 Include all remaining rows in the result of the recursive query, and
2036 also place them in a temporary <firstterm>intermediate table</firstterm>.
2040 <step performance="required">
2042 Replace the contents of the working table with the contents of the
2043 intermediate table, then empty the intermediate table.
2052 Strictly speaking, this process is iteration not recursion, but
2053 <literal>RECURSIVE</literal> is the terminology chosen by the SQL standards
2059 In the example above, the working table has just a single row in each step,
2060 and it takes on the values from 1 through 100 in successive steps. In
2061 the 100th step, there is no output because of the <literal>WHERE</literal>
2062 clause, and so the query terminates.
2066 Recursive queries are typically used to deal with hierarchical or
2067 tree-structured data. A useful example is this query to find all the
2068 direct and indirect sub-parts of a product, given only a table that
2069 shows immediate inclusions:
2072 WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
2073 SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
2075 SELECT p.sub_part, p.part, p.quantity
2076 FROM included_parts pr, parts p
2077 WHERE p.part = pr.sub_part
2079 SELECT sub_part, SUM(quantity) as total_quantity
2086 When working with recursive queries it is important to be sure that
2087 the recursive part of the query will eventually return no tuples,
2088 or else the query will loop indefinitely. Sometimes, using
2089 <literal>UNION</literal> instead of <literal>UNION ALL</literal> can accomplish this
2090 by discarding rows that duplicate previous output rows. However, often a
2091 cycle does not involve output rows that are completely duplicate: it may be
2092 necessary to check just one or a few fields to see if the same point has
2093 been reached before. The standard method for handling such situations is
2094 to compute an array of the already-visited values. For example, consider
2095 the following query that searches a table <structname>graph</structname> using a
2096 <structfield>link</structfield> field:
2099 WITH RECURSIVE search_graph(id, link, data, depth) AS (
2100 SELECT g.id, g.link, g.data, 1
2103 SELECT g.id, g.link, g.data, sg.depth + 1
2104 FROM graph g, search_graph sg
2105 WHERE g.id = sg.link
2107 SELECT * FROM search_graph;
2110 This query will loop if the <structfield>link</structfield> relationships contain
2111 cycles. Because we require a <quote>depth</quote> output, just changing
2112 <literal>UNION ALL</literal> to <literal>UNION</literal> would not eliminate the looping.
2113 Instead we need to recognize whether we have reached the same row again
2114 while following a particular path of links. We add two columns
2115 <structfield>path</structfield> and <structfield>cycle</structfield> to the loop-prone query:
2118 WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
2119 SELECT g.id, g.link, g.data, 1,
2124 SELECT g.id, g.link, g.data, sg.depth + 1,
2127 FROM graph g, search_graph sg
2128 WHERE g.id = sg.link AND NOT cycle
2130 SELECT * FROM search_graph;
2133 Aside from preventing cycles, the array value is often useful in its own
2134 right as representing the <quote>path</quote> taken to reach any particular row.
2138 In the general case where more than one field needs to be checked to
2139 recognize a cycle, use an array of rows. For example, if we needed to
2140 compare fields <structfield>f1</structfield> and <structfield>f2</structfield>:
2143 WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
2144 SELECT g.id, g.link, g.data, 1,
2145 ARRAY[ROW(g.f1, g.f2)],
2149 SELECT g.id, g.link, g.data, sg.depth + 1,
2150 path || ROW(g.f1, g.f2),
2151 ROW(g.f1, g.f2) = ANY(path)
2152 FROM graph g, search_graph sg
2153 WHERE g.id = sg.link AND NOT cycle
2155 SELECT * FROM search_graph;
2161 Omit the <literal>ROW()</literal> syntax in the common case where only one field
2162 needs to be checked to recognize a cycle. This allows a simple array
2163 rather than a composite-type array to be used, gaining efficiency.
2169 The recursive query evaluation algorithm produces its output in
2170 breadth-first search order. You can display the results in depth-first
2171 search order by making the outer query <literal>ORDER BY</literal> a
2172 <quote>path</quote> column constructed in this way.
2177 A helpful trick for testing queries
2178 when you are not certain if they might loop is to place a <literal>LIMIT</literal>
2179 in the parent query. For example, this query would loop forever without
2180 the <literal>LIMIT</literal>:
2183 WITH RECURSIVE t(n) AS (
2188 SELECT n FROM t LIMIT 100;
2191 This works because <productname>PostgreSQL</productname>'s implementation
2192 evaluates only as many rows of a <literal>WITH</literal> query as are actually
2193 fetched by the parent query. Using this trick in production is not
2194 recommended, because other systems might work differently. Also, it
2195 usually won't work if you make the outer query sort the recursive query's
2196 results or join them to some other table, because in such cases the
2197 outer query will usually try to fetch all of the <literal>WITH</literal> query's
2202 A useful property of <literal>WITH</literal> queries is that they are
2203 normally evaluated only once per execution of the parent query, even if
2204 they are referred to more than once by the parent query or
2205 sibling <literal>WITH</literal> queries.
2206 Thus, expensive calculations that are needed in multiple places can be
2207 placed within a <literal>WITH</literal> query to avoid redundant work. Another
2208 possible application is to prevent unwanted multiple evaluations of
2209 functions with side-effects.
2210 However, the other side of this coin is that the optimizer is not able to
2211 push restrictions from the parent query down into a multiply-referenced
2212 <literal>WITH</literal> query, since that might affect all uses of the
2213 <literal>WITH</literal> query's output when it should affect only one.
2214 The multiply-referenced <literal>WITH</literal> query will be
2215 evaluated as written, without suppression of rows that the parent query
2216 might discard afterwards. (But, as mentioned above, evaluation might stop
2217 early if the reference(s) to the query demand only a limited number of
2222 However, if a <literal>WITH</literal> query is non-recursive and
2223 side-effect-free (that is, it is a <literal>SELECT</literal> containing
2224 no volatile functions) then it can be folded into the parent query,
2225 allowing joint optimization of the two query levels. By default, this
2226 happens if the parent query references the <literal>WITH</literal> query
2227 just once, but not if it references the <literal>WITH</literal> query
2228 more than once. You can override that decision by
2229 specifying <literal>MATERIALIZED</literal> to force separate calculation
2230 of the <literal>WITH</literal> query, or by specifying <literal>NOT
2231 MATERIALIZED</literal> to force it to be merged into the parent query.
2232 The latter choice risks duplicate computation of
2233 the <literal>WITH</literal> query, but it can still give a net savings if
2234 each usage of the <literal>WITH</literal> query needs only a small part
2235 of the <literal>WITH</literal> query's full output.
2239 A simple example of these rules is
2242 SELECT * FROM big_table
2244 SELECT * FROM w WHERE key = 123;
2246 This <literal>WITH</literal> query will be folded, producing the same
2249 SELECT * FROM big_table WHERE key = 123;
2251 In particular, if there's an index on <structfield>key</structfield>,
2252 it will probably be used to fetch just the rows having <literal>key =
2253 123</literal>. On the other hand, in
2256 SELECT * FROM big_table
2258 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2261 the <literal>WITH</literal> query will be materialized, producing a
2262 temporary copy of <structname>big_table</structname> that is then
2263 joined with itself — without benefit of any index. This query
2264 will be executed much more efficiently if written as
2266 WITH w AS NOT MATERIALIZED (
2267 SELECT * FROM big_table
2269 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2272 so that the parent query's restrictions can be applied directly
2273 to scans of <structname>big_table</structname>.
2277 An example where <literal>NOT MATERIALIZED</literal> could be
2281 SELECT key, very_expensive_function(val) as f FROM some_table
2283 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
2285 Here, materialization of the <literal>WITH</literal> query ensures
2286 that <function>very_expensive_function</function> is evaluated only
2287 once per table row, not twice.
2291 The examples above only show <literal>WITH</literal> being used with
2292 <command>SELECT</command>, but it can be attached in the same way to
2293 <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>.
2294 In each case it effectively provides temporary table(s) that can
2295 be referred to in the main command.
2299 <sect2 id="queries-with-modifying">
2300 <title>Data-Modifying Statements in <literal>WITH</literal></title>
2303 You can use data-modifying statements (<command>INSERT</command>,
2304 <command>UPDATE</command>, or <command>DELETE</command>) in <literal>WITH</literal>. This
2305 allows you to perform several different operations in the same query.
2309 WITH moved_rows AS (
2310 DELETE FROM products
2312 "date" >= '2010-10-01' AND
2313 "date" < '2010-11-01'
2316 INSERT INTO products_log
2317 SELECT * FROM moved_rows;
2320 This query effectively moves rows from <structname>products</structname> to
2321 <structname>products_log</structname>. The <command>DELETE</command> in <literal>WITH</literal>
2322 deletes the specified rows from <structname>products</structname>, returning their
2323 contents by means of its <literal>RETURNING</literal> clause; and then the
2324 primary query reads that output and inserts it into
2325 <structname>products_log</structname>.
2329 A fine point of the above example is that the <literal>WITH</literal> clause is
2330 attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within
2331 the <command>INSERT</command>. This is necessary because data-modifying
2332 statements are only allowed in <literal>WITH</literal> clauses that are attached
2333 to the top-level statement. However, normal <literal>WITH</literal> visibility
2334 rules apply, so it is possible to refer to the <literal>WITH</literal>
2335 statement's output from the sub-<command>SELECT</command>.
2339 Data-modifying statements in <literal>WITH</literal> usually have
2340 <literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>),
2341 as shown in the example above.
2342 It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> the
2343 target table of the data-modifying statement, that forms the temporary
2344 table that can be referred to by the rest of the query. If a
2345 data-modifying statement in <literal>WITH</literal> lacks a <literal>RETURNING</literal>
2346 clause, then it forms no temporary table and cannot be referred to in
2347 the rest of the query. Such a statement will be executed nonetheless.
2348 A not-particularly-useful example is:
2357 This example would remove all rows from tables <structname>foo</structname> and
2358 <structname>bar</structname>. The number of affected rows reported to the client
2359 would only include rows removed from <structname>bar</structname>.
2363 Recursive self-references in data-modifying statements are not
2364 allowed. In some cases it is possible to work around this limitation by
2365 referring to the output of a recursive <literal>WITH</literal>, for example:
2368 WITH RECURSIVE included_parts(sub_part, part) AS (
2369 SELECT sub_part, part FROM parts WHERE part = 'our_product'
2371 SELECT p.sub_part, p.part
2372 FROM included_parts pr, parts p
2373 WHERE p.part = pr.sub_part
2376 WHERE part IN (SELECT part FROM included_parts);
2379 This query would remove all direct and indirect subparts of a product.
2383 Data-modifying statements in <literal>WITH</literal> are executed exactly once,
2384 and always to completion, independently of whether the primary query
2385 reads all (or indeed any) of their output. Notice that this is different
2386 from the rule for <command>SELECT</command> in <literal>WITH</literal>: as stated in the
2387 previous section, execution of a <command>SELECT</command> is carried only as far
2388 as the primary query demands its output.
2392 The sub-statements in <literal>WITH</literal> are executed concurrently with
2393 each other and with the main query. Therefore, when using data-modifying
2394 statements in <literal>WITH</literal>, the order in which the specified updates
2395 actually happen is unpredictable. All the statements are executed with
2396 the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they
2397 cannot <quote>see</quote> one another's effects on the target tables. This
2398 alleviates the effects of the unpredictability of the actual order of row
2399 updates, and means that <literal>RETURNING</literal> data is the only way to
2400 communicate changes between different <literal>WITH</literal> sub-statements and
2401 the main query. An example of this is that in
2405 UPDATE products SET price = price * 1.05
2408 SELECT * FROM products;
2411 the outer <command>SELECT</command> would return the original prices before the
2412 action of the <command>UPDATE</command>, while in
2416 UPDATE products SET price = price * 1.05
2422 the outer <command>SELECT</command> would return the updated data.
2426 Trying to update the same row twice in a single statement is not
2427 supported. Only one of the modifications takes place, but it is not easy
2428 (and sometimes not possible) to reliably predict which one. This also
2429 applies to deleting a row that was already updated in the same statement:
2430 only the update is performed. Therefore you should generally avoid trying
2431 to modify a single row twice in a single statement. In particular avoid
2432 writing <literal>WITH</literal> sub-statements that could affect the same rows
2433 changed by the main statement or a sibling sub-statement. The effects
2434 of such a statement will not be predictable.
2438 At present, any table used as the target of a data-modifying statement in
2439 <literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal>
2440 rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements.
projects / postgresql / blob