Debian Codegraph @

doc/src/sgml/queries.sgml

raw
1 <!-- doc/src/sgml/queries.sgml -->
2
3 <chapter id="queries">
4 <title>Queries</title>
5
6 <indexterm zone="queries">
7 <primary>query</primary>
8 </indexterm>
9
10 <indexterm zone="queries">
11 <primary>SELECT</primary>
12 </indexterm>
13
14 <para>
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.
18 </para>
19
20
21 <sect1 id="queries-overview">
22 <title>Overview</title>
23
24 <para>
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 <link linkend="sql-select"><command>SELECT</command></link> command is
28 used to specify queries. The general syntax of the
29 <command>SELECT</command> command is
30 <synopsis>
31 <optional>WITH <replaceable>with_queries</replaceable></optional> SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>
32 </synopsis>
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.
36 </para>
37
38 <para>
39 A simple kind of query has the form:
40 <programlisting>
41 SELECT * FROM table1;
42 </programlisting>
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
56 the following query:
57 <programlisting>
58 SELECT a, b + c FROM table1;
59 </programlisting>
60 (assuming that <literal>b</literal> and <literal>c</literal> are of a numerical
61 data type).
62 See <xref linkend="queries-select-lists"/> for more details.
63 </para>
64
65 <para>
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:
71 <programlisting>
72 SELECT 3 * 4;
73 </programlisting>
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:
76 <programlisting>
77 SELECT random();
78 </programlisting>
79 </para>
80 </sect1>
81
82
83 <sect1 id="queries-table-expressions">
84 <title>Table Expressions</title>
85
86 <indexterm zone="queries-table-expressions">
87 <primary>table expression</primary>
88 </indexterm>
89
90 <para>
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
97 ways.
98 </para>
99
100 <para>
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.
107 </para>
108
109 <sect2 id="queries-from">
110 <title>The <literal>FROM</literal> Clause</title>
111
112 <para>
113 The <link linkend="sql-from"><literal>FROM</literal></link> clause derives a
114 table from one or more other tables given in a comma-separated
115 table reference list.
116 <synopsis>
117 FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional>
118 </synopsis>
119
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.
130 </para>
131
132 <indexterm>
133 <primary>ONLY</primary>
134 </indexterm>
135
136 <para>
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 &mdash; any columns added in subtables are ignored.
143 </para>
144
145 <para>
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.
151 </para>
152
153 <sect3 id="queries-join">
154 <title>Joined Tables</title>
155
156 <indexterm zone="queries-join">
157 <primary>join</primary>
158 </indexterm>
159
160 <para>
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
165 <synopsis>
166 <replaceable>T1</replaceable> <replaceable>join_type</replaceable> <replaceable>T2</replaceable> <optional> <replaceable>join_condition</replaceable> </optional>
167 </synopsis>
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
173 nest left-to-right.
174 </para>
175
176 <variablelist>
177 <title>Join Types</title>
178
179 <varlistentry>
180 <term>Cross join
181 <indexterm>
182 <primary>join</primary>
183 <secondary>cross</secondary>
184 </indexterm>
185
186 <indexterm>
187 <primary>cross join</primary>
188 </indexterm>
189 </term>
190
191 <listitem>
192 <synopsis>
193 <replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
194 </synopsis>
195
196 <para>
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.
205 </para>
206
207 <para>
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>.
215 <note>
216 <para>
217 This latter equivalence does not hold exactly when more than two
218 tables appear, because <literal>JOIN</literal> binds more tightly than
219 comma. For example
220 <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
221 <replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
222 ON <replaceable>condition</replaceable></literal>
223 is not the same as
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
229 the second.
230 </para>
231 </note>
232 </para>
233 </listitem>
234 </varlistentry>
235
236 <varlistentry>
237 <term>Qualified joins
238 <indexterm>
239 <primary>join</primary>
240 <secondary>outer</secondary>
241 </indexterm>
242
243 <indexterm>
244 <primary>outer join</primary>
245 </indexterm>
246 </term>
247
248 <listitem>
249 <synopsis>
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>
253 </synopsis>
254
255 <para>
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.
261 </para>
262
263 <para>
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.
269 </para>
270
271 <para>
272 The possible types of qualified join are:
273
274 <variablelist>
275 <varlistentry>
276 <term><literal>INNER JOIN</literal></term>
277
278 <listitem>
279 <para>
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.
282 </para>
283 </listitem>
284 </varlistentry>
285
286 <varlistentry>
287 <term><literal>LEFT OUTER JOIN</literal>
288 <indexterm>
289 <primary>join</primary>
290 <secondary>left</secondary>
291 </indexterm>
292
293 <indexterm>
294 <primary>left join</primary>
295 </indexterm>
296 </term>
297
298 <listitem>
299 <para>
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.
305 </para>
306 </listitem>
307 </varlistentry>
308
309 <varlistentry>
310 <term><literal>RIGHT OUTER JOIN</literal>
311 <indexterm>
312 <primary>join</primary>
313 <secondary>right</secondary>
314 </indexterm>
315
316 <indexterm>
317 <primary>right join</primary>
318 </indexterm>
319 </term>
320
321 <listitem>
322 <para>
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.
328 </para>
329 </listitem>
330 </varlistentry>
331
332 <varlistentry>
333 <term><literal>FULL OUTER JOIN</literal></term>
334
335 <listitem>
336 <para>
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.
343 </para>
344 </listitem>
345 </varlistentry>
346 </variablelist>
347 </para>
348
349 <para>
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.
355 </para>
356
357 <para>
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>.
368 </para>
369
370 <para>
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>.
379 </para>
380
381 <para>
382 <indexterm>
383 <primary>join</primary>
384 <secondary>natural</secondary>
385 </indexterm>
386 <indexterm>
387 <primary>natural join</primary>
388 </indexterm>
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>CROSS JOIN</literal>.
396 </para>
397
398 <note>
399 <para>
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
405 column as well.
406 </para>
407 </note>
408 </listitem>
409 </varlistentry>
410 </variablelist>
411
412 <para>
413 To put this together, assume we have tables <literal>t1</literal>:
414 <programlisting>
415 num | name
416 -----+------
417 1 | a
418 2 | b
419 3 | c
420 </programlisting>
421 and <literal>t2</literal>:
422 <programlisting>
423 num | value
424 -----+-------
425 1 | xxx
426 3 | yyy
427 5 | zzz
428 </programlisting>
429 then we get the following results for the various joins:
430 <screen>
431 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 CROSS JOIN t2;</userinput>
432 num | name | num | value
433 -----+------+-----+-------
434 1 | a | 1 | xxx
435 1 | a | 3 | yyy
436 1 | a | 5 | zzz
437 2 | b | 1 | xxx
438 2 | b | 3 | yyy
439 2 | b | 5 | zzz
440 3 | c | 1 | xxx
441 3 | c | 3 | yyy
442 3 | c | 5 | zzz
443 (9 rows)
444
445 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</userinput>
446 num | name | num | value
447 -----+------+-----+-------
448 1 | a | 1 | xxx
449 3 | c | 3 | yyy
450 (2 rows)
451
452 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 USING (num);</userinput>
453 num | name | value
454 -----+------+-------
455 1 | a | xxx
456 3 | c | yyy
457 (2 rows)
458
459 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 NATURAL INNER JOIN t2;</userinput>
460 num | name | value
461 -----+------+-------
462 1 | a | xxx
463 3 | c | yyy
464 (2 rows)
465
466 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</userinput>
467 num | name | num | value
468 -----+------+-----+-------
469 1 | a | 1 | xxx
470 2 | b | |
471 3 | c | 3 | yyy
472 (3 rows)
473
474 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 USING (num);</userinput>
475 num | name | value
476 -----+------+-------
477 1 | a | xxx
478 2 | b |
479 3 | c | yyy
480 (3 rows)
481
482 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</userinput>
483 num | name | num | value
484 -----+------+-----+-------
485 1 | a | 1 | xxx
486 3 | c | 3 | yyy
487 | | 5 | zzz
488 (3 rows)
489
490 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</userinput>
491 num | name | num | value
492 -----+------+-----+-------
493 1 | a | 1 | xxx
494 2 | b | |
495 3 | c | 3 | yyy
496 | | 5 | zzz
497 (4 rows)
498 </screen>
499 </para>
500
501 <para>
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:
506 <screen>
507 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</userinput>
508 num | name | num | value
509 -----+------+-----+-------
510 1 | a | 1 | xxx
511 2 | b | |
512 3 | c | |
513 (3 rows)
514 </screen>
515 Notice that placing the restriction in the <literal>WHERE</literal> clause
516 produces a different result:
517 <screen>
518 <prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</userinput>
519 num | name | num | value
520 -----+------+-----+-------
521 1 | a | 1 | xxx
522 (1 row)
523 </screen>
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
529 joins.
530 </para>
531 </sect3>
532
533 <sect3 id="queries-table-aliases">
534 <title>Table and Column Aliases</title>
535
536 <indexterm zone="queries-table-aliases">
537 <primary>alias</primary>
538 <secondary>in the FROM clause</secondary>
539 </indexterm>
540
541 <indexterm>
542 <primary>label</primary>
543 <see>alias</see>
544 </indexterm>
545
546 <para>
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
550 alias</firstterm>.
551 </para>
552
553 <para>
554 To create a table alias, write
555 <synopsis>
556 FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable>
557 </synopsis>
558 or
559 <synopsis>
560 FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
561 </synopsis>
562 The <literal>AS</literal> key word is optional noise.
563 <replaceable>alias</replaceable> can be any identifier.
564 </para>
565
566 <para>
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:
570 <programlisting>
571 SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
572 </programlisting>
573 </para>
574
575 <para>
576 The alias becomes the new name of the table reference so far as the
577 current query is concerned &mdash; it is not allowed to refer to the
578 table by the original name elsewhere in the query. Thus, this is not
579 valid:
580 <programlisting>
581 SELECT * FROM my_table AS m WHERE my_table.a &gt; 5; -- wrong
582 </programlisting>
583 </para>
584
585 <para>
586 Table aliases are mainly for notational convenience, but it is
587 necessary to use them when joining a table to itself, e.g.:
588 <programlisting>
589 SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
590 </programlisting>
591 </para>
592
593 <para>
594 Parentheses are used to resolve ambiguities. In the following example,
595 the first statement assigns the alias <literal>b</literal> to the second
596 instance of <literal>my_table</literal>, but the second statement assigns the
597 alias to the result of the join:
598 <programlisting>
599 SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
600 SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
601 </programlisting>
602 </para>
603
604 <para>
605 Another form of table aliasing gives temporary names to the columns of
606 the table, as well as the table itself:
607 <synopsis>
608 FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> )
609 </synopsis>
610 If fewer column aliases are specified than the actual table has
611 columns, the remaining columns are not renamed. This syntax is
612 especially useful for self-joins or subqueries.
613 </para>
614
615 <para>
616 When an alias is applied to the output of a <literal>JOIN</literal>
617 clause, the alias hides the original
618 name(s) within the <literal>JOIN</literal>. For example:
619 <programlisting>
620 SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
621 </programlisting>
622 is valid SQL, but:
623 <programlisting>
624 SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
625 </programlisting>
626 is not valid; the table alias <literal>a</literal> is not visible
627 outside the alias <literal>c</literal>.
628 </para>
629 </sect3>
630
631 <sect3 id="queries-subqueries">
632 <title>Subqueries</title>
633
634 <indexterm zone="queries-subqueries">
635 <primary>subquery</primary>
636 </indexterm>
637
638 <para>
639 Subqueries specifying a derived table must be enclosed in
640 parentheses. They may be assigned a table alias name, and optionally
641 column alias names (as in <xref linkend="queries-table-aliases"/>).
642 For example:
643 <programlisting>
644 FROM (SELECT * FROM table1) AS alias_name
645 </programlisting>
646 </para>
647
648 <para>
649 This example is equivalent to <literal>FROM table1 AS
650 alias_name</literal>. More interesting cases, which cannot be
651 reduced to a plain join, arise when the subquery involves
652 grouping or aggregation.
653 </para>
654
655 <para>
656 A subquery can also be a <command>VALUES</command> list:
657 <programlisting>
658 FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
659 AS names(first, last)
660 </programlisting>
661 Again, a table alias is optional. Assigning alias names to the columns
662 of the <command>VALUES</command> list is optional, but is good practice.
663 For more information see <xref linkend="queries-values"/>.
664 </para>
665
666 <para>
667 According to the SQL standard, a table alias name must be supplied
668 for a subquery. <productname>PostgreSQL</productname>
669 allows <literal>AS</literal> and the alias to be omitted, but
670 writing one is good practice in SQL code that might be ported to
671 another system.
672 </para>
673 </sect3>
674
675 <sect3 id="queries-tablefunctions">
676 <title>Table Functions</title>
677
678 <indexterm zone="queries-tablefunctions"><primary>table function</primary></indexterm>
679
680 <indexterm zone="queries-tablefunctions">
681 <primary>function</primary>
682 <secondary>in the FROM clause</secondary>
683 </indexterm>
684
685 <para>
686 Table functions are functions that produce a set of rows, made up
687 of either base data types (scalar types) or composite data types
688 (table rows). They are used like a table, view, or subquery in
689 the <literal>FROM</literal> clause of a query. Columns returned by table
690 functions can be included in <literal>SELECT</literal>,
691 <literal>JOIN</literal>, or <literal>WHERE</literal> clauses in the same manner
692 as columns of a table, view, or subquery.
693 </para>
694
695 <para>
696 Table functions may also be combined using the <literal>ROWS FROM</literal>
697 syntax, with the results returned in parallel columns; the number of
698 result rows in this case is that of the largest function result, with
699 smaller results padded with null values to match.
700 </para>
701
702 <synopsis>
703 <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>
704 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>
705 </synopsis>
706
707 <para>
708 If the <literal>WITH ORDINALITY</literal> clause is specified, an
709 additional column of type <type>bigint</type> will be added to the
710 function result columns. This column numbers the rows of the function
711 result set, starting from 1. (This is a generalization of the
712 SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.)
713 By default, the ordinal column is called <literal>ordinality</literal>, but
714 a different column name can be assigned to it using
715 an <literal>AS</literal> clause.
716 </para>
717
718 <para>
719 The special table function <literal>UNNEST</literal> may be called with
720 any number of array parameters, and it returns a corresponding number of
721 columns, as if <literal>UNNEST</literal>
722 (<xref linkend="functions-array"/>) had been called on each parameter
723 separately and combined using the <literal>ROWS FROM</literal> construct.
724 </para>
725
726 <synopsis>
727 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>
728 </synopsis>
729
730 <para>
731 If no <replaceable>table_alias</replaceable> is specified, the function
732 name is used as the table name; in the case of a <literal>ROWS FROM()</literal>
733 construct, the first function's name is used.
734 </para>
735
736 <para>
737 If column aliases are not supplied, then for a function returning a base
738 data type, the column name is also the same as the function name. For a
739 function returning a composite type, the result columns get the names
740 of the individual attributes of the type.
741 </para>
742
743 <para>
744 Some examples:
745 <programlisting>
746 CREATE TABLE foo (fooid int, foosubid int, fooname text);
747
748 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
749 SELECT * FROM foo WHERE fooid = $1;
750 $$ LANGUAGE SQL;
751
752 SELECT * FROM getfoo(1) AS t1;
753
754 SELECT * FROM foo
755 WHERE foosubid IN (
756 SELECT foosubid
757 FROM getfoo(foo.fooid) z
758 WHERE z.fooid = foo.fooid
759 );
760
761 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
762
763 SELECT * FROM vw_getfoo;
764 </programlisting>
765 </para>
766
767 <para>
768 In some cases it is useful to define table functions that can
769 return different column sets depending on how they are invoked.
770 To support this, the table function can be declared as returning
771 the pseudo-type <type>record</type> with no <literal>OUT</literal>
772 parameters. When such a function is used in
773 a query, the expected row structure must be specified in the
774 query itself, so that the system can know how to parse and plan
775 the query. This syntax looks like:
776 </para>
777
778 <synopsis>
779 <replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
780 <replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
781 ROWS FROM( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
782 </synopsis>
783
784 <para>
785 When not using the <literal>ROWS FROM()</literal> syntax,
786 the <replaceable>column_definition</replaceable> list replaces the column
787 alias list that could otherwise be attached to the <literal>FROM</literal>
788 item; the names in the column definitions serve as column aliases.
789 When using the <literal>ROWS FROM()</literal> syntax,
790 a <replaceable>column_definition</replaceable> list can be attached to
791 each member function separately; or if there is only one member function
792 and no <literal>WITH ORDINALITY</literal> clause,
793 a <replaceable>column_definition</replaceable> list can be written in
794 place of a column alias list following <literal>ROWS FROM()</literal>.
795 </para>
796
797 <para>
798 Consider this example:
799 <programlisting>
800 SELECT *
801 FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
802 AS t1(proname name, prosrc text)
803 WHERE proname LIKE 'bytea%';
804 </programlisting>
805 The <xref linkend="contrib-dblink-function"/> function
806 (part of the <xref linkend="dblink"/> module) executes
807 a remote query. It is declared to return
808 <type>record</type> since it might be used for any kind of query.
809 The actual column set must be specified in the calling query so
810 that the parser knows, for example, what <literal>*</literal> should
811 expand to.
812 </para>
813
814 <para>
815 This example uses <literal>ROWS FROM</literal>:
816 <programlisting>
817 SELECT *
818 FROM ROWS FROM
819 (
820 json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
821 AS (a INTEGER, b TEXT),
822 generate_series(1, 3)
823 ) AS x (p, q, s)
824 ORDER BY p;
825
826 p | q | s
827 -----+-----+---
828 40 | foo | 1
829 100 | bar | 2
830 | | 3
831 </programlisting>
832 It joins two functions into a single <literal>FROM</literal>
833 target. <function>json_to_recordset()</function> is instructed
834 to return two columns, the first <type>integer</type>
835 and the second <type>text</type>. The result of
836 <function>generate_series()</function> is used directly.
837 The <literal>ORDER BY</literal> clause sorts the column values
838 as integers.
839 </para>
840 </sect3>
841
842 <sect3 id="queries-lateral">
843 <title><literal>LATERAL</literal> Subqueries</title>
844
845 <indexterm zone="queries-lateral">
846 <primary>LATERAL</primary>
847 <secondary>in the FROM clause</secondary>
848 </indexterm>
849
850 <para>
851 Subqueries appearing in <literal>FROM</literal> can be
852 preceded by the key word <literal>LATERAL</literal>. This allows them to
853 reference columns provided by preceding <literal>FROM</literal> items.
854 (Without <literal>LATERAL</literal>, each subquery is
855 evaluated independently and so cannot cross-reference any other
856 <literal>FROM</literal> item.)
857 </para>
858
859 <para>
860 Table functions appearing in <literal>FROM</literal> can also be
861 preceded by the key word <literal>LATERAL</literal>, but for functions the
862 key word is optional; the function's arguments can contain references
863 to columns provided by preceding <literal>FROM</literal> items in any case.
864 </para>
865
866 <para>
867 A <literal>LATERAL</literal> item can appear at the top level in the
868 <literal>FROM</literal> list, or within a <literal>JOIN</literal> tree. In the latter
869 case it can also refer to any items that are on the left-hand side of a
870 <literal>JOIN</literal> that it is on the right-hand side of.
871 </para>
872
873 <para>
874 When a <literal>FROM</literal> item contains <literal>LATERAL</literal>
875 cross-references, evaluation proceeds as follows: for each row of the
876 <literal>FROM</literal> item providing the cross-referenced column(s), or
877 set of rows of multiple <literal>FROM</literal> items providing the
878 columns, the <literal>LATERAL</literal> item is evaluated using that
879 row or row set's values of the columns. The resulting row(s) are
880 joined as usual with the rows they were computed from. This is
881 repeated for each row or set of rows from the column source table(s).
882 </para>
883
884 <para>
885 A trivial example of <literal>LATERAL</literal> is
886 <programlisting>
887 SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
888 </programlisting>
889 This is not especially useful since it has exactly the same result as
890 the more conventional
891 <programlisting>
892 SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
893 </programlisting>
894 <literal>LATERAL</literal> is primarily useful when the cross-referenced
895 column is necessary for computing the row(s) to be joined. A common
896 application is providing an argument value for a set-returning function.
897 For example, supposing that <function>vertices(polygon)</function> returns the
898 set of vertices of a polygon, we could identify close-together vertices
899 of polygons stored in a table with:
900 <programlisting>
901 SELECT p1.id, p2.id, v1, v2
902 FROM polygons p1, polygons p2,
903 LATERAL vertices(p1.poly) v1,
904 LATERAL vertices(p2.poly) v2
905 WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;
906 </programlisting>
907 This query could also be written
908 <programlisting>
909 SELECT p1.id, p2.id, v1, v2
910 FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
911 polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
912 WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;
913 </programlisting>
914 or in several other equivalent formulations. (As already mentioned,
915 the <literal>LATERAL</literal> key word is unnecessary in this example, but
916 we use it for clarity.)
917 </para>
918
919 <para>
920 It is often particularly handy to <literal>LEFT JOIN</literal> to a
921 <literal>LATERAL</literal> subquery, so that source rows will appear in
922 the result even if the <literal>LATERAL</literal> subquery produces no
923 rows for them. For example, if <function>get_product_names()</function> returns
924 the names of products made by a manufacturer, but some manufacturers in
925 our table currently produce no products, we could find out which ones
926 those are like this:
927 <programlisting>
928 SELECT m.name
929 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
930 WHERE pname IS NULL;
931 </programlisting>
932 </para>
933 </sect3>
934 </sect2>
935
936 <sect2 id="queries-where">
937 <title>The <literal>WHERE</literal> Clause</title>
938
939 <indexterm zone="queries-where">
940 <primary>WHERE</primary>
941 </indexterm>
942
943 <para>
944 The syntax of the <link linkend="sql-where"><literal>WHERE</literal></link>
945 clause is
946 <synopsis>
947 WHERE <replaceable>search_condition</replaceable>
948 </synopsis>
949 where <replaceable>search_condition</replaceable> is any value
950 expression (see <xref linkend="sql-expressions"/>) that
951 returns a value of type <type>boolean</type>.
952 </para>
953
954 <para>
955 After the processing of the <literal>FROM</literal> clause is done, each
956 row of the derived virtual table is checked against the search
957 condition. If the result of the condition is true, the row is
958 kept in the output table, otherwise (i.e., if the result is
959 false or null) it is discarded. The search condition typically
960 references at least one column of the table generated in the
961 <literal>FROM</literal> clause; this is not required, but otherwise the
962 <literal>WHERE</literal> clause will be fairly useless.
963 </para>
964
965 <note>
966 <para>
967 The join condition of an inner join can be written either in
968 the <literal>WHERE</literal> clause or in the <literal>JOIN</literal> clause.
969 For example, these table expressions are equivalent:
970 <programlisting>
971 FROM a, b WHERE a.id = b.id AND b.val &gt; 5
972 </programlisting>
973 and:
974 <programlisting>
975 FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val &gt; 5
976 </programlisting>
977 or perhaps even:
978 <programlisting>
979 FROM a NATURAL JOIN b WHERE b.val &gt; 5
980 </programlisting>
981 Which one of these you use is mainly a matter of style. The
982 <literal>JOIN</literal> syntax in the <literal>FROM</literal> clause is
983 probably not as portable to other SQL database management systems,
984 even though it is in the SQL standard. For
985 outer joins there is no choice: they must be done in
986 the <literal>FROM</literal> clause. The <literal>ON</literal> or <literal>USING</literal>
987 clause of an outer join is <emphasis>not</emphasis> equivalent to a
988 <literal>WHERE</literal> condition, because it results in the addition
989 of rows (for unmatched input rows) as well as the removal of rows
990 in the final result.
991 </para>
992 </note>
993
994 <para>
995 Here are some examples of <literal>WHERE</literal> clauses:
996 <programlisting>
997 SELECT ... FROM fdt WHERE c1 &gt; 5
998
999 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
1000
1001 SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
1002
1003 SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
1004
1005 SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
1006
1007 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 &gt; fdt.c1)
1008 </programlisting>
1009 <literal>fdt</literal> is the table derived in the
1010 <literal>FROM</literal> clause. Rows that do not meet the search
1011 condition of the <literal>WHERE</literal> clause are eliminated from
1012 <literal>fdt</literal>. Notice the use of scalar subqueries as
1013 value expressions. Just like any other query, the subqueries can
1014 employ complex table expressions. Notice also how
1015 <literal>fdt</literal> is referenced in the subqueries.
1016 Qualifying <literal>c1</literal> as <literal>fdt.c1</literal> is only necessary
1017 if <literal>c1</literal> is also the name of a column in the derived
1018 input table of the subquery. But qualifying the column name adds
1019 clarity even when it is not needed. This example shows how the column
1020 naming scope of an outer query extends into its inner queries.
1021 </para>
1022 </sect2>
1023
1024
1025 <sect2 id="queries-group">
1026 <title>The <literal>GROUP BY</literal> and <literal>HAVING</literal> Clauses</title>
1027
1028 <indexterm zone="queries-group">
1029 <primary>GROUP BY</primary>
1030 </indexterm>
1031
1032 <indexterm zone="queries-group">
1033 <primary>grouping</primary>
1034 </indexterm>
1035
1036 <para>
1037 After passing the <literal>WHERE</literal> filter, the derived input
1038 table might be subject to grouping, using the <literal>GROUP BY</literal>
1039 clause, and elimination of group rows using the <literal>HAVING</literal>
1040 clause.
1041 </para>
1042
1043 <synopsis>
1044 SELECT <replaceable>select_list</replaceable>
1045 FROM ...
1046 <optional>WHERE ...</optional>
1047 GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
1048 </synopsis>
1049
1050 <para>
1051 The <link linkend="sql-groupby"><literal>GROUP BY</literal></link> clause is
1052 used to group together those rows in a table that have the same
1053 values in all the columns listed. The order in which the columns
1054 are listed does not matter. The effect is to combine each set
1055 of rows having common values into one group row that
1056 represents all rows in the group. This is done to
1057 eliminate redundancy in the output and/or compute aggregates that
1058 apply to these groups. For instance:
1059 <screen>
1060 <prompt>=&gt;</prompt> <userinput>SELECT * FROM test1;</userinput>
1061 x | y
1062 ---+---
1063 a | 3
1064 c | 2
1065 b | 5
1066 a | 1
1067 (4 rows)
1068
1069 <prompt>=&gt;</prompt> <userinput>SELECT x FROM test1 GROUP BY x;</userinput>
1070 x
1071 ---
1072 a
1073 b
1074 c
1075 (3 rows)
1076 </screen>
1077 </para>
1078
1079 <para>
1080 In the second query, we could not have written <literal>SELECT *
1081 FROM test1 GROUP BY x</literal>, because there is no single value
1082 for the column <literal>y</literal> that could be associated with each
1083 group. The grouped-by columns can be referenced in the select list since
1084 they have a single value in each group.
1085 </para>
1086
1087 <para>
1088 In general, if a table is grouped, columns that are not
1089 listed in <literal>GROUP BY</literal> cannot be referenced except in aggregate
1090 expressions. An example with aggregate expressions is:
1091 <screen>
1092 <prompt>=&gt;</prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x;</userinput>
1093 x | sum
1094 ---+-----
1095 a | 4
1096 b | 5
1097 c | 2
1098 (3 rows)
1099 </screen>
1100 Here <literal>sum</literal> is an aggregate function that
1101 computes a single value over the entire group. More information
1102 about the available aggregate functions can be found in <xref
1103 linkend="functions-aggregate"/>.
1104 </para>
1105
1106 <tip>
1107 <para>
1108 Grouping without aggregate expressions effectively calculates the
1109 set of distinct values in a column. This can also be achieved
1110 using the <literal>DISTINCT</literal> clause (see <xref
1111 linkend="queries-distinct"/>).
1112 </para>
1113 </tip>
1114
1115 <para>
1116 Here is another example: it calculates the total sales for each
1117 product (rather than the total sales of all products):
1118 <programlisting>
1119 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
1120 FROM products p LEFT JOIN sales s USING (product_id)
1121 GROUP BY product_id, p.name, p.price;
1122 </programlisting>
1123 In this example, the columns <literal>product_id</literal>,
1124 <literal>p.name</literal>, and <literal>p.price</literal> must be
1125 in the <literal>GROUP BY</literal> clause since they are referenced in
1126 the query select list (but see below). The column
1127 <literal>s.units</literal> does not have to be in the <literal>GROUP
1128 BY</literal> list since it is only used in an aggregate expression
1129 (<literal>sum(...)</literal>), which represents the sales
1130 of a product. For each product, the query returns a summary row about
1131 all sales of the product.
1132 </para>
1133
1134 <indexterm><primary>functional dependency</primary></indexterm>
1135
1136 <para>
1137 If the products table is set up so that, say,
1138 <literal>product_id</literal> is the primary key, then it would be
1139 enough to group by <literal>product_id</literal> in the above example,
1140 since name and price would be <firstterm>functionally
1141 dependent</firstterm> on the product ID, and so there would be no
1142 ambiguity about which name and price value to return for each product
1143 ID group.
1144 </para>
1145
1146 <para>
1147 In strict SQL, <literal>GROUP BY</literal> can only group by columns of
1148 the source table but <productname>PostgreSQL</productname> extends
1149 this to also allow <literal>GROUP BY</literal> to group by columns in the
1150 select list. Grouping by value expressions instead of simple
1151 column names is also allowed.
1152 </para>
1153
1154 <indexterm>
1155 <primary>HAVING</primary>
1156 </indexterm>
1157
1158 <para>
1159 If a table has been grouped using <literal>GROUP BY</literal>,
1160 but only certain groups are of interest, the
1161 <literal>HAVING</literal> clause can be used, much like a
1162 <literal>WHERE</literal> clause, to eliminate groups from the result.
1163 The syntax is:
1164 <synopsis>
1165 SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable>
1166 </synopsis>
1167 Expressions in the <literal>HAVING</literal> clause can refer both to
1168 grouped expressions and to ungrouped expressions (which necessarily
1169 involve an aggregate function).
1170 </para>
1171
1172 <para>
1173 Example:
1174 <screen>
1175 <prompt>=&gt;</prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) &gt; 3;</userinput>
1176 x | sum
1177 ---+-----
1178 a | 4
1179 b | 5
1180 (2 rows)
1181
1182 <prompt>=&gt;</prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x &lt; 'c';</userinput>
1183 x | sum
1184 ---+-----
1185 a | 4
1186 b | 5
1187 (2 rows)
1188 </screen>
1189 </para>
1190
1191 <para>
1192 Again, a more realistic example:
1193 <programlisting>
1194 SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
1195 FROM products p LEFT JOIN sales s USING (product_id)
1196 WHERE s.date &gt; CURRENT_DATE - INTERVAL '4 weeks'
1197 GROUP BY product_id, p.name, p.price, p.cost
1198 HAVING sum(p.price * s.units) &gt; 5000;
1199 </programlisting>
1200 In the example above, the <literal>WHERE</literal> clause is selecting
1201 rows by a column that is not grouped (the expression is only true for
1202 sales during the last four weeks), while the <literal>HAVING</literal>
1203 clause restricts the output to groups with total gross sales over
1204 5000. Note that the aggregate expressions do not necessarily need
1205 to be the same in all parts of the query.
1206 </para>
1207
1208 <para>
1209 If a query contains aggregate function calls, but no <literal>GROUP BY</literal>
1210 clause, grouping still occurs: the result is a single group row (or
1211 perhaps no rows at all, if the single row is then eliminated by
1212 <literal>HAVING</literal>).
1213 The same is true if it contains a <literal>HAVING</literal> clause, even
1214 without any aggregate function calls or <literal>GROUP BY</literal> clause.
1215 </para>
1216 </sect2>
1217
1218 <sect2 id="queries-grouping-sets">
1219 <title><literal>GROUPING SETS</literal>, <literal>CUBE</literal>, and <literal>ROLLUP</literal></title>
1220
1221 <indexterm zone="queries-grouping-sets">
1222 <primary>GROUPING SETS</primary>
1223 </indexterm>
1224 <indexterm zone="queries-grouping-sets">
1225 <primary>CUBE</primary>
1226 </indexterm>
1227 <indexterm zone="queries-grouping-sets">
1228 <primary>ROLLUP</primary>
1229 </indexterm>
1230
1231 <para>
1232 More complex grouping operations than those described above are possible
1233 using the concept of <firstterm>grouping sets</firstterm>. The data selected by
1234 the <literal>FROM</literal> and <literal>WHERE</literal> clauses is grouped separately
1235 by each specified grouping set, aggregates computed for each group just as
1236 for simple <literal>GROUP BY</literal> clauses, and then the results returned.
1237 For example:
1238 <screen>
1239 <prompt>=&gt;</prompt> <userinput>SELECT * FROM items_sold;</userinput>
1240 brand | size | sales
1241 -------+------+-------
1242 Foo | L | 10
1243 Foo | M | 20
1244 Bar | M | 15
1245 Bar | L | 5
1246 (4 rows)
1247
1248 <prompt>=&gt;</prompt> <userinput>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</userinput>
1249 brand | size | sum
1250 -------+------+-----
1251 Foo | | 30
1252 Bar | | 20
1253 | L | 15
1254 | M | 35
1255 | | 50
1256 (5 rows)
1257 </screen>
1258 </para>
1259
1260 <para>
1261 Each sublist of <literal>GROUPING SETS</literal> may specify zero or more columns
1262 or expressions and is interpreted the same way as though it were directly
1263 in the <literal>GROUP BY</literal> clause. An empty grouping set means that all
1264 rows are aggregated down to a single group (which is output even if no
1265 input rows were present), as described above for the case of aggregate
1266 functions with no <literal>GROUP BY</literal> clause.
1267 </para>
1268
1269 <para>
1270 References to the grouping columns or expressions are replaced
1271 by null values in result rows for grouping sets in which those
1272 columns do not appear. To distinguish which grouping a particular output
1273 row resulted from, see <xref linkend="functions-grouping-table"/>.
1274 </para>
1275
1276 <para>
1277 A shorthand notation is provided for specifying two common types of grouping set.
1278 A clause of the form
1279 <programlisting>
1280 ROLLUP ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... )
1281 </programlisting>
1282 represents the given list of expressions and all prefixes of the list including
1283 the empty list; thus it is equivalent to
1284 <programlisting>
1285 GROUPING SETS (
1286 ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... ),
1287 ...
1288 ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable> ),
1289 ( <replaceable>e1</replaceable> ),
1290 ( )
1291 )
1292 </programlisting>
1293 This is commonly used for analysis over hierarchical data; e.g., total
1294 salary by department, division, and company-wide total.
1295 </para>
1296
1297 <para>
1298 A clause of the form
1299 <programlisting>
1300 CUBE ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, ... )
1301 </programlisting>
1302 represents the given list and all of its possible subsets (i.e., the power
1303 set). Thus
1304 <programlisting>
1305 CUBE ( a, b, c )
1306 </programlisting>
1307 is equivalent to
1308 <programlisting>
1309 GROUPING SETS (
1310 ( a, b, c ),
1311 ( a, b ),
1312 ( a, c ),
1313 ( a ),
1314 ( b, c ),
1315 ( b ),
1316 ( c ),
1317 ( )
1318 )
1319 </programlisting>
1320 </para>
1321
1322 <para>
1323 The individual elements of a <literal>CUBE</literal> or <literal>ROLLUP</literal>
1324 clause may be either individual expressions, or sublists of elements in
1325 parentheses. In the latter case, the sublists are treated as single
1326 units for the purposes of generating the individual grouping sets.
1327 For example:
1328 <programlisting>
1329 CUBE ( (a, b), (c, d) )
1330 </programlisting>
1331 is equivalent to
1332 <programlisting>
1333 GROUPING SETS (
1334 ( a, b, c, d ),
1335 ( a, b ),
1336 ( c, d ),
1337 ( )
1338 )
1339 </programlisting>
1340 and
1341 <programlisting>
1342 ROLLUP ( a, (b, c), d )
1343 </programlisting>
1344 is equivalent to
1345 <programlisting>
1346 GROUPING SETS (
1347 ( a, b, c, d ),
1348 ( a, b, c ),
1349 ( a ),
1350 ( )
1351 )
1352 </programlisting>
1353 </para>
1354
1355 <para>
1356 The <literal>CUBE</literal> and <literal>ROLLUP</literal> constructs can be used either
1357 directly in the <literal>GROUP BY</literal> clause, or nested inside a
1358 <literal>GROUPING SETS</literal> clause. If one <literal>GROUPING SETS</literal> clause
1359 is nested inside another, the effect is the same as if all the elements of
1360 the inner clause had been written directly in the outer clause.
1361 </para>
1362
1363 <para>
1364 If multiple grouping items are specified in a single <literal>GROUP BY</literal>
1365 clause, then the final list of grouping sets is the Cartesian product of the
1366 individual items. For example:
1367 <programlisting>
1368 GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
1369 </programlisting>
1370 is equivalent to
1371 <programlisting>
1372 GROUP BY GROUPING SETS (
1373 (a, b, c, d), (a, b, c, e),
1374 (a, b, d), (a, b, e),
1375 (a, c, d), (a, c, e),
1376 (a, d), (a, e)
1377 )
1378 </programlisting>
1379 </para>
1380
1381 <para>
1382 <indexterm zone="queries-grouping-sets">
1383 <primary>ALL</primary>
1384 <secondary>GROUP BY ALL</secondary>
1385 </indexterm>
1386 <indexterm zone="queries-grouping-sets">
1387 <primary>DISTINCT</primary>
1388 <secondary>GROUP BY DISTINCT</secondary>
1389 </indexterm>
1390 When specifying multiple grouping items together, the final set of grouping
1391 sets might contain duplicates. For example:
1392 <programlisting>
1393 GROUP BY ROLLUP (a, b), ROLLUP (a, c)
1394 </programlisting>
1395 is equivalent to
1396 <programlisting>
1397 GROUP BY GROUPING SETS (
1398 (a, b, c),
1399 (a, b),
1400 (a, b),
1401 (a, c),
1402 (a),
1403 (a),
1404 (a, c),
1405 (a),
1406 ()
1407 )
1408 </programlisting>
1409 If these duplicates are undesirable, they can be removed using the
1410 <literal>DISTINCT</literal> clause directly on the <literal>GROUP BY</literal>.
1411 Therefore:
1412 <programlisting>
1413 GROUP BY <emphasis>DISTINCT</emphasis> ROLLUP (a, b), ROLLUP (a, c)
1414 </programlisting>
1415 is equivalent to
1416 <programlisting>
1417 GROUP BY GROUPING SETS (
1418 (a, b, c),
1419 (a, b),
1420 (a, c),
1421 (a),
1422 ()
1423 )
1424 </programlisting>
1425 This is not the same as using <literal>SELECT DISTINCT</literal> because the output
1426 rows may still contain duplicates. If any of the ungrouped columns contains NULL,
1427 it will be indistinguishable from the NULL used when that same column is grouped.
1428 </para>
1429
1430 <note>
1431 <para>
1432 The construct <literal>(a, b)</literal> is normally recognized in expressions as
1433 a <link linkend="sql-syntax-row-constructors">row constructor</link>.
1434 Within the <literal>GROUP BY</literal> clause, this does not apply at the top
1435 levels of expressions, and <literal>(a, b)</literal> is parsed as a list of
1436 expressions as described above. If for some reason you <emphasis>need</emphasis>
1437 a row constructor in a grouping expression, use <literal>ROW(a, b)</literal>.
1438 </para>
1439 </note>
1440 </sect2>
1441
1442 <sect2 id="queries-window">
1443 <title>Window Function Processing</title>
1444
1445 <indexterm zone="queries-window">
1446 <primary>window function</primary>
1447 <secondary>order of execution</secondary>
1448 </indexterm>
1449
1450 <para>
1451 If the query contains any window functions (see
1452 <xref linkend="tutorial-window"/>,
1453 <xref linkend="functions-window"/> and
1454 <xref linkend="syntax-window-functions"/>), these functions are evaluated
1455 after any grouping, aggregation, and <literal>HAVING</literal> filtering is
1456 performed. That is, if the query uses any aggregates, <literal>GROUP
1457 BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions
1458 are the group rows instead of the original table rows from
1459 <literal>FROM</literal>/<literal>WHERE</literal>.
1460 </para>
1461
1462 <para>
1463 When multiple window functions are used, all the window functions having
1464 equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
1465 clauses in their window definitions are guaranteed to see the same
1466 ordering of the input rows, even if the <literal>ORDER BY</literal> does
1467 not uniquely determine the ordering.
1468 However, no guarantees are made about the evaluation of functions having
1469 different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
1470 (In such cases a sort step is typically required between the passes of
1471 window function evaluations, and the sort is not guaranteed to preserve
1472 ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.)
1473 </para>
1474
1475 <para>
1476 Currently, window functions always require presorted data, and so the
1477 query output will be ordered according to one or another of the window
1478 functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses.
1479 It is not recommended to rely on this, however. Use an explicit
1480 top-level <literal>ORDER BY</literal> clause if you want to be sure the
1481 results are sorted in a particular way.
1482 </para>
1483 </sect2>
1484 </sect1>
1485
1486
1487 <sect1 id="queries-select-lists">
1488 <title>Select Lists</title>
1489
1490 <indexterm>
1491 <primary>SELECT</primary>
1492 <secondary>select list</secondary>
1493 </indexterm>
1494
1495 <para>
1496 As shown in the previous section,
1497 the table expression in the <command>SELECT</command> command
1498 constructs an intermediate virtual table by possibly combining
1499 tables, views, eliminating rows, grouping, etc. This table is
1500 finally passed on to processing by the <firstterm>select list</firstterm>. The select
1501 list determines which <emphasis>columns</emphasis> of the
1502 intermediate table are actually output.
1503 </para>
1504
1505 <sect2 id="queries-select-list-items">
1506 <title>Select-List Items</title>
1507
1508 <indexterm>
1509 <primary>*</primary>
1510 </indexterm>
1511
1512 <para>
1513 The simplest kind of select list is <literal>*</literal> which
1514 emits all columns that the table expression produces. Otherwise,
1515 a select list is a comma-separated list of value expressions (as
1516 defined in <xref linkend="sql-expressions"/>). For instance, it
1517 could be a list of column names:
1518 <programlisting>
1519 SELECT a, b, c FROM ...
1520 </programlisting>
1521 The columns names <literal>a</literal>, <literal>b</literal>, and <literal>c</literal>
1522 are either the actual names of the columns of tables referenced
1523 in the <literal>FROM</literal> clause, or the aliases given to them as
1524 explained in <xref linkend="queries-table-aliases"/>. The name
1525 space available in the select list is the same as in the
1526 <literal>WHERE</literal> clause, unless grouping is used, in which case
1527 it is the same as in the <literal>HAVING</literal> clause.
1528 </para>
1529
1530 <para>
1531 If more than one table has a column of the same name, the table
1532 name must also be given, as in:
1533 <programlisting>
1534 SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
1535 </programlisting>
1536 When working with multiple tables, it can also be useful to ask for
1537 all the columns of a particular table:
1538 <programlisting>
1539 SELECT tbl1.*, tbl2.a FROM ...
1540 </programlisting>
1541 See <xref linkend="rowtypes-usage"/> for more about
1542 the <replaceable>table_name</replaceable><literal>.*</literal> notation.
1543 </para>
1544
1545 <para>
1546 If an arbitrary value expression is used in the select list, it
1547 conceptually adds a new virtual column to the returned table. The
1548 value expression is evaluated once for each result row, with
1549 the row's values substituted for any column references. But the
1550 expressions in the select list do not have to reference any
1551 columns in the table expression of the <literal>FROM</literal> clause;
1552 they can be constant arithmetic expressions, for instance.
1553 </para>
1554 </sect2>
1555
1556 <sect2 id="queries-column-labels">
1557 <title>Column Labels</title>
1558
1559 <indexterm zone="queries-column-labels">
1560 <primary>alias</primary>
1561 <secondary>in the select list</secondary>
1562 </indexterm>
1563
1564 <para>
1565 The entries in the select list can be assigned names for subsequent
1566 processing, such as for use in an <literal>ORDER BY</literal> clause
1567 or for display by the client application. For example:
1568 <programlisting>
1569 SELECT a AS value, b + c AS sum FROM ...
1570 </programlisting>
1571 </para>
1572
1573 <para>
1574 If no output column name is specified using <literal>AS</literal>,
1575 the system assigns a default column name. For simple column references,
1576 this is the name of the referenced column. For function
1577 calls, this is the name of the function. For complex expressions,
1578 the system will generate a generic name.
1579 </para>
1580
1581 <para>
1582 The <literal>AS</literal> key word is usually optional, but in some
1583 cases where the desired column name matches a
1584 <productname>PostgreSQL</productname> key word, you must write
1585 <literal>AS</literal> or double-quote the column name in order to
1586 avoid ambiguity.
1587 (<xref linkend="sql-keywords-appendix"/> shows which key words
1588 require <literal>AS</literal> to be used as a column label.)
1589 For example, <literal>FROM</literal> is one such key word, so this
1590 does not work:
1591 <programlisting>
1592 SELECT a from, b + c AS sum FROM ...
1593 </programlisting>
1594 but either of these do:
1595 <programlisting>
1596 SELECT a AS from, b + c AS sum FROM ...
1597 SELECT a "from", b + c AS sum FROM ...
1598 </programlisting>
1599 For greatest safety against possible
1600 future key word additions, it is recommended that you always either
1601 write <literal>AS</literal> or double-quote the output column name.
1602 </para>
1603
1604 <note>
1605 <para>
1606 The naming of output columns here is different from that done in
1607 the <literal>FROM</literal> clause (see <xref
1608 linkend="queries-table-aliases"/>). It is possible
1609 to rename the same column twice, but the name assigned in
1610 the select list is the one that will be passed on.
1611 </para>
1612 </note>
1613 </sect2>
1614
1615 <sect2 id="queries-distinct">
1616 <title><literal>DISTINCT</literal></title>
1617
1618 <indexterm zone="queries-distinct">
1619 <primary>ALL</primary>
1620 <secondary>SELECT ALL</secondary>
1621 </indexterm>
1622 <indexterm zone="queries-distinct">
1623 <primary>DISTINCT</primary>
1624 <secondary>SELECT DISTINCT</secondary>
1625 </indexterm>
1626
1627 <indexterm zone="queries-distinct">
1628 <primary>duplicates</primary>
1629 </indexterm>
1630
1631 <para>
1632 After the select list has been processed, the result table can
1633 optionally be subject to the elimination of duplicate rows. The
1634 <literal>DISTINCT</literal> key word is written directly after
1635 <literal>SELECT</literal> to specify this:
1636 <synopsis>
1637 SELECT DISTINCT <replaceable>select_list</replaceable> ...
1638 </synopsis>
1639 (Instead of <literal>DISTINCT</literal> the key word <literal>ALL</literal>
1640 can be used to specify the default behavior of retaining all rows.)
1641 </para>
1642
1643 <indexterm>
1644 <primary>null value</primary>
1645 <secondary sortas="DISTINCT">in DISTINCT</secondary>
1646 </indexterm>
1647
1648 <para>
1649 Obviously, two rows are considered distinct if they differ in at
1650 least one column value. Null values are considered equal in this
1651 comparison.
1652 </para>
1653
1654 <para>
1655 Alternatively, an arbitrary expression can determine what rows are
1656 to be considered distinct:
1657 <synopsis>
1658 SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ...
1659 </synopsis>
1660 Here <replaceable>expression</replaceable> is an arbitrary value
1661 expression that is evaluated for all rows. A set of rows for
1662 which all the expressions are equal are considered duplicates, and
1663 only the first row of the set is kept in the output. Note that
1664 the <quote>first row</quote> of a set is unpredictable unless the
1665 query is sorted on enough columns to guarantee a unique ordering
1666 of the rows arriving at the <literal>DISTINCT</literal> filter.
1667 (<literal>DISTINCT ON</literal> processing occurs after <literal>ORDER
1668 BY</literal> sorting.)
1669 </para>
1670
1671 <para>
1672 The <literal>DISTINCT ON</literal> clause is not part of the SQL standard
1673 and is sometimes considered bad style because of the potentially
1674 indeterminate nature of its results. With judicious use of
1675 <literal>GROUP BY</literal> and subqueries in <literal>FROM</literal>, this
1676 construct can be avoided, but it is often the most convenient
1677 alternative.
1678 </para>
1679 </sect2>
1680 </sect1>
1681
1682
1683 <sect1 id="queries-union">
1684 <title>Combining Queries (<literal>UNION</literal>, <literal>INTERSECT</literal>, <literal>EXCEPT</literal>)</title>
1685
1686 <indexterm zone="queries-union">
1687 <primary>UNION</primary>
1688 </indexterm>
1689 <indexterm zone="queries-union">
1690 <primary>INTERSECT</primary>
1691 </indexterm>
1692 <indexterm zone="queries-union">
1693 <primary>EXCEPT</primary>
1694 </indexterm>
1695 <indexterm zone="queries-union">
1696 <primary>set union</primary>
1697 </indexterm>
1698 <indexterm zone="queries-union">
1699 <primary>set intersection</primary>
1700 </indexterm>
1701 <indexterm zone="queries-union">
1702 <primary>set difference</primary>
1703 </indexterm>
1704 <indexterm zone="queries-union">
1705 <primary>set operation</primary>
1706 </indexterm>
1707
1708 <para>
1709 The results of two queries can be combined using the set operations
1710 union, intersection, and difference. The syntax is
1711 <synopsis>
1712 <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
1713 <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
1714 <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
1715 </synopsis>
1716 where <replaceable>query1</replaceable> and
1717 <replaceable>query2</replaceable> are queries that can use any of
1718 the features discussed up to this point.
1719 </para>
1720
1721 <para>
1722 <literal>UNION</literal> effectively appends the result of
1723 <replaceable>query2</replaceable> to the result of
1724 <replaceable>query1</replaceable> (although there is no guarantee
1725 that this is the order in which the rows are actually returned).
1726 Furthermore, it eliminates duplicate rows from its result, in the same
1727 way as <literal>DISTINCT</literal>, unless <literal>UNION ALL</literal> is used.
1728 </para>
1729
1730 <para>
1731 <literal>INTERSECT</literal> returns all rows that are both in the result
1732 of <replaceable>query1</replaceable> and in the result of
1733 <replaceable>query2</replaceable>. Duplicate rows are eliminated
1734 unless <literal>INTERSECT ALL</literal> is used.
1735 </para>
1736
1737 <para>
1738 <literal>EXCEPT</literal> returns all rows that are in the result of
1739 <replaceable>query1</replaceable> but not in the result of
1740 <replaceable>query2</replaceable>. (This is sometimes called the
1741 <firstterm>difference</firstterm> between two queries.) Again, duplicates
1742 are eliminated unless <literal>EXCEPT ALL</literal> is used.
1743 </para>
1744
1745 <para>
1746 In order to calculate the union, intersection, or difference of two
1747 queries, the two queries must be <quote>union compatible</quote>,
1748 which means that they return the same number of columns and
1749 the corresponding columns have compatible data types, as
1750 described in <xref linkend="typeconv-union-case"/>.
1751 </para>
1752
1753 <para>
1754 Set operations can be combined, for example
1755 <synopsis>
1756 <replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> EXCEPT <replaceable>query3</replaceable>
1757 </synopsis>
1758 which is equivalent to
1759 <synopsis>
1760 (<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) EXCEPT <replaceable>query3</replaceable>
1761 </synopsis>
1762 As shown here, you can use parentheses to control the order of
1763 evaluation. Without parentheses, <literal>UNION</literal>
1764 and <literal>EXCEPT</literal> associate left-to-right,
1765 but <literal>INTERSECT</literal> binds more tightly than those two
1766 operators. Thus
1767 <synopsis>
1768 <replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>
1769 </synopsis>
1770 means
1771 <synopsis>
1772 <replaceable>query1</replaceable> UNION (<replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>)
1773 </synopsis>
1774 You can also surround an individual <replaceable>query</replaceable>
1775 with parentheses. This is important if
1776 the <replaceable>query</replaceable> needs to use any of the clauses
1777 discussed in following sections, such as <literal>LIMIT</literal>.
1778 Without parentheses, you'll get a syntax error, or else the clause will
1779 be understood as applying to the output of the set operation rather
1780 than one of its inputs. For example,
1781 <synopsis>
1782 SELECT a FROM b UNION SELECT x FROM y LIMIT 10
1783 </synopsis>
1784 is accepted, but it means
1785 <synopsis>
1786 (SELECT a FROM b UNION SELECT x FROM y) LIMIT 10
1787 </synopsis>
1788 not
1789 <synopsis>
1790 SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)
1791 </synopsis>
1792 </para>
1793 </sect1>
1794
1795
1796 <sect1 id="queries-order">
1797 <title>Sorting Rows (<literal>ORDER BY</literal>)</title>
1798
1799 <indexterm zone="queries-order">
1800 <primary>sorting</primary>
1801 </indexterm>
1802
1803 <indexterm zone="queries-order">
1804 <primary>ORDER BY</primary>
1805 </indexterm>
1806
1807 <para>
1808 After a query has produced an output table (after the select list
1809 has been processed) it can optionally be sorted. If sorting is not
1810 chosen, the rows will be returned in an unspecified order. The actual
1811 order in that case will depend on the scan and join plan types and
1812 the order on disk, but it must not be relied on. A particular
1813 output ordering can only be guaranteed if the sort step is explicitly
1814 chosen.
1815 </para>
1816
1817 <para>
1818 The <literal>ORDER BY</literal> clause specifies the sort order:
1819 <synopsis>
1820 SELECT <replaceable>select_list</replaceable>
1821 FROM <replaceable>table_expression</replaceable>
1822 ORDER BY <replaceable>sort_expression1</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional>
1823 <optional>, <replaceable>sort_expression2</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional> ...</optional>
1824 </synopsis>
1825 The sort expression(s) can be any expression that would be valid in the
1826 query's select list. An example is:
1827 <programlisting>
1828 SELECT a, b FROM table1 ORDER BY a + b, c;
1829 </programlisting>
1830 When more than one expression is specified,
1831 the later values are used to sort rows that are equal according to the
1832 earlier values. Each expression can be followed by an optional
1833 <literal>ASC</literal> or <literal>DESC</literal> keyword to set the sort direction to
1834 ascending or descending. <literal>ASC</literal> order is the default.
1835 Ascending order puts smaller values first, where
1836 <quote>smaller</quote> is defined in terms of the
1837 <literal>&lt;</literal> operator. Similarly, descending order is
1838 determined with the <literal>&gt;</literal> operator.
1839 <footnote>
1840 <para>
1841 Actually, <productname>PostgreSQL</productname> uses the <firstterm>default B-tree
1842 operator class</firstterm> for the expression's data type to determine the sort
1843 ordering for <literal>ASC</literal> and <literal>DESC</literal>. Conventionally,
1844 data types will be set up so that the <literal>&lt;</literal> and
1845 <literal>&gt;</literal> operators correspond to this sort ordering,
1846 but a user-defined data type's designer could choose to do something
1847 different.
1848 </para>
1849 </footnote>
1850 </para>
1851
1852 <para>
1853 The <literal>NULLS FIRST</literal> and <literal>NULLS LAST</literal> options can be
1854 used to determine whether nulls appear before or after non-null values
1855 in the sort ordering. By default, null values sort as if larger than any
1856 non-null value; that is, <literal>NULLS FIRST</literal> is the default for
1857 <literal>DESC</literal> order, and <literal>NULLS LAST</literal> otherwise.
1858 </para>
1859
1860 <para>
1861 Note that the ordering options are considered independently for each
1862 sort column. For example <literal>ORDER BY x, y DESC</literal> means
1863 <literal>ORDER BY x ASC, y DESC</literal>, which is not the same as
1864 <literal>ORDER BY x DESC, y DESC</literal>.
1865 </para>
1866
1867 <para>
1868 A <replaceable>sort_expression</replaceable> can also be the column label or number
1869 of an output column, as in:
1870 <programlisting>
1871 SELECT a + b AS sum, c FROM table1 ORDER BY sum;
1872 SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
1873 </programlisting>
1874 both of which sort by the first output column. Note that an output
1875 column name has to stand alone, that is, it cannot be used in an expression
1876 &mdash; for example, this is <emphasis>not</emphasis> correct:
1877 <programlisting>
1878 SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
1879 </programlisting>
1880 This restriction is made to reduce ambiguity. There is still
1881 ambiguity if an <literal>ORDER BY</literal> item is a simple name that
1882 could match either an output column name or a column from the table
1883 expression. The output column is used in such cases. This would
1884 only cause confusion if you use <literal>AS</literal> to rename an output
1885 column to match some other table column's name.
1886 </para>
1887
1888 <para>
1889 <literal>ORDER BY</literal> can be applied to the result of a
1890 <literal>UNION</literal>, <literal>INTERSECT</literal>, or <literal>EXCEPT</literal>
1891 combination, but in this case it is only permitted to sort by
1892 output column names or numbers, not by expressions.
1893 </para>
1894 </sect1>
1895
1896
1897 <sect1 id="queries-limit">
1898 <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1899
1900 <indexterm zone="queries-limit">
1901 <primary>LIMIT</primary>
1902 </indexterm>
1903
1904 <indexterm zone="queries-limit">
1905 <primary>OFFSET</primary>
1906 </indexterm>
1907
1908 <para>
1909 <literal>LIMIT</literal> and <literal>OFFSET</literal> allow you to retrieve just
1910 a portion of the rows that are generated by the rest of the query:
1911 <synopsis>
1912 SELECT <replaceable>select_list</replaceable>
1913 FROM <replaceable>table_expression</replaceable>
1914 <optional> ORDER BY ... </optional>
1915 <optional> LIMIT { <replaceable class="parameter">count</replaceable> | ALL } </optional>
1916 <optional> OFFSET <replaceable class="parameter">start</replaceable> </optional>
1917 </synopsis>
1918 </para>
1919
1920 <para>
1921 If a limit count is given, no more than that many rows will be
1922 returned (but possibly fewer, if the query itself yields fewer rows).
1923 <literal>LIMIT ALL</literal> is the same as omitting the <literal>LIMIT</literal>
1924 clause, as is <literal>LIMIT</literal> with a NULL argument.
1925 </para>
1926
1927 <para>
1928 <literal>OFFSET</literal> says to skip that many rows before beginning to
1929 return rows. <literal>OFFSET 0</literal> is the same as omitting the
1930 <literal>OFFSET</literal> clause, as is <literal>OFFSET</literal> with a NULL argument.
1931 </para>
1932
1933 <para>
1934 If both <literal>OFFSET</literal>
1935 and <literal>LIMIT</literal> appear, then <literal>OFFSET</literal> rows are
1936 skipped before starting to count the <literal>LIMIT</literal> rows that
1937 are returned.
1938 </para>
1939
1940 <para>
1941 When using <literal>LIMIT</literal>, it is important to use an
1942 <literal>ORDER BY</literal> clause that constrains the result rows into a
1943 unique order. Otherwise you will get an unpredictable subset of
1944 the query's rows. You might be asking for the tenth through
1945 twentieth rows, but tenth through twentieth in what ordering? The
1946 ordering is unknown, unless you specified <literal>ORDER BY</literal>.
1947 </para>
1948
1949 <para>
1950 The query optimizer takes <literal>LIMIT</literal> into account when
1951 generating query plans, so you are very likely to get different
1952 plans (yielding different row orders) depending on what you give
1953 for <literal>LIMIT</literal> and <literal>OFFSET</literal>. Thus, using
1954 different <literal>LIMIT</literal>/<literal>OFFSET</literal> values to select
1955 different subsets of a query result <emphasis>will give
1956 inconsistent results</emphasis> unless you enforce a predictable
1957 result ordering with <literal>ORDER BY</literal>. This is not a bug; it
1958 is an inherent consequence of the fact that SQL does not promise to
1959 deliver the results of a query in any particular order unless
1960 <literal>ORDER BY</literal> is used to constrain the order.
1961 </para>
1962
1963 <para>
1964 The rows skipped by an <literal>OFFSET</literal> clause still have to be
1965 computed inside the server; therefore a large <literal>OFFSET</literal>
1966 might be inefficient.
1967 </para>
1968 </sect1>
1969
1970
1971 <sect1 id="queries-values">
1972 <title><literal>VALUES</literal> Lists</title>
1973
1974 <indexterm zone="queries-values">
1975 <primary>VALUES</primary>
1976 </indexterm>
1977
1978 <para>
1979 <literal>VALUES</literal> provides a way to generate a <quote>constant table</quote>
1980 that can be used in a query without having to actually create and populate
1981 a table on-disk. The syntax is
1982 <synopsis>
1983 VALUES ( <replaceable class="parameter">expression</replaceable> [, ...] ) [, ...]
1984 </synopsis>
1985 Each parenthesized list of expressions generates a row in the table.
1986 The lists must all have the same number of elements (i.e., the number
1987 of columns in the table), and corresponding entries in each list must
1988 have compatible data types. The actual data type assigned to each column
1989 of the result is determined using the same rules as for <literal>UNION</literal>
1990 (see <xref linkend="typeconv-union-case"/>).
1991 </para>
1992
1993 <para>
1994 As an example:
1995 <programlisting>
1996 VALUES (1, 'one'), (2, 'two'), (3, 'three');
1997 </programlisting>
1998
1999 will return a table of two columns and three rows. It's effectively
2000 equivalent to:
2001 <programlisting>
2002 SELECT 1 AS column1, 'one' AS column2
2003 UNION ALL
2004 SELECT 2, 'two'
2005 UNION ALL
2006 SELECT 3, 'three';
2007 </programlisting>
2008
2009 By default, <productname>PostgreSQL</productname> assigns the names
2010 <literal>column1</literal>, <literal>column2</literal>, etc. to the columns of a
2011 <literal>VALUES</literal> table. The column names are not specified by the
2012 SQL standard and different database systems do it differently, so
2013 it's usually better to override the default names with a table alias
2014 list, like this:
2015 <programlisting>
2016 =&gt; SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
2017 num | letter
2018 -----+--------
2019 1 | one
2020 2 | two
2021 3 | three
2022 (3 rows)
2023 </programlisting>
2024 </para>
2025
2026 <para>
2027 Syntactically, <literal>VALUES</literal> followed by expression lists is
2028 treated as equivalent to:
2029 <synopsis>
2030 SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
2031 </synopsis>
2032 and can appear anywhere a <literal>SELECT</literal> can. For example, you can
2033 use it as part of a <literal>UNION</literal>, or attach a
2034 <replaceable>sort_specification</replaceable> (<literal>ORDER BY</literal>,
2035 <literal>LIMIT</literal>, and/or <literal>OFFSET</literal>) to it. <literal>VALUES</literal>
2036 is most commonly used as the data source in an <command>INSERT</command> command,
2037 and next most commonly as a subquery.
2038 </para>
2039
2040 <para>
2041 For more information see <xref linkend="sql-values"/>.
2042 </para>
2043
2044 </sect1>
2045
2046
2047 <sect1 id="queries-with">
2048 <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
2049
2050 <indexterm zone="queries-with">
2051 <primary>WITH</primary>
2052 <secondary>in SELECT</secondary>
2053 </indexterm>
2054
2055 <indexterm>
2056 <primary>common table expression</primary>
2057 <see>WITH</see>
2058 </indexterm>
2059
2060 <para>
2061 <literal>WITH</literal> provides a way to write auxiliary statements for use in a
2062 larger query. These statements, which are often referred to as Common
2063 Table Expressions or <acronym>CTE</acronym>s, can be thought of as defining
2064 temporary tables that exist just for one query. Each auxiliary statement
2065 in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
2066 <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
2067 or <command>MERGE</command>; and the
2068 <literal>WITH</literal> clause itself is attached to a primary statement that can
2069 also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
2070 <command>DELETE</command>, or <command>MERGE</command>.
2071 </para>
2072
2073 <sect2 id="queries-with-select">
2074 <title><command>SELECT</command> in <literal>WITH</literal></title>
2075
2076 <para>
2077 The basic value of <command>SELECT</command> in <literal>WITH</literal> is to
2078 break down complicated queries into simpler parts. An example is:
2079
2080 <programlisting>
2081 WITH regional_sales AS (
2082 SELECT region, SUM(amount) AS total_sales
2083 FROM orders
2084 GROUP BY region
2085 ), top_regions AS (
2086 SELECT region
2087 FROM regional_sales
2088 WHERE total_sales &gt; (SELECT SUM(total_sales)/10 FROM regional_sales)
2089 )
2090 SELECT region,
2091 product,
2092 SUM(quantity) AS product_units,
2093 SUM(amount) AS product_sales
2094 FROM orders
2095 WHERE region IN (SELECT region FROM top_regions)
2096 GROUP BY region, product;
2097 </programlisting>
2098
2099 which displays per-product sales totals in only the top sales regions.
2100 The <literal>WITH</literal> clause defines two auxiliary statements named
2101 <structname>regional_sales</structname> and <structname>top_regions</structname>,
2102 where the output of <structname>regional_sales</structname> is used in
2103 <structname>top_regions</structname> and the output of <structname>top_regions</structname>
2104 is used in the primary <command>SELECT</command> query.
2105 This example could have been written without <literal>WITH</literal>,
2106 but we'd have needed two levels of nested sub-<command>SELECT</command>s. It's a bit
2107 easier to follow this way.
2108 </para>
2109 </sect2>
2110
2111 <sect2 id="queries-with-recursive">
2112 <title>Recursive Queries</title>
2113
2114 <para>
2115 <indexterm>
2116 <primary>RECURSIVE</primary>
2117 <secondary>in common table expressions</secondary>
2118 </indexterm>
2119 The optional <literal>RECURSIVE</literal> modifier changes <literal>WITH</literal>
2120 from a mere syntactic convenience into a feature that accomplishes
2121 things not otherwise possible in standard SQL. Using
2122 <literal>RECURSIVE</literal>, a <literal>WITH</literal> query can refer to its own
2123 output. A very simple example is this query to sum the integers from 1
2124 through 100:
2125
2126 <programlisting>
2127 WITH RECURSIVE t(n) AS (
2128 VALUES (1)
2129 UNION ALL
2130 SELECT n+1 FROM t WHERE n &lt; 100
2131 )
2132 SELECT sum(n) FROM t;
2133 </programlisting>
2134
2135 The general form of a recursive <literal>WITH</literal> query is always a
2136 <firstterm>non-recursive term</firstterm>, then <literal>UNION</literal> (or
2137 <literal>UNION ALL</literal>), then a
2138 <firstterm>recursive term</firstterm>, where only the recursive term can contain
2139 a reference to the query's own output. Such a query is executed as
2140 follows:
2141 </para>
2142
2143 <procedure>
2144 <title>Recursive Query Evaluation</title>
2145
2146 <step performance="required">
2147 <para>
2148 Evaluate the non-recursive term. For <literal>UNION</literal> (but not
2149 <literal>UNION ALL</literal>), discard duplicate rows. Include all remaining
2150 rows in the result of the recursive query, and also place them in a
2151 temporary <firstterm>working table</firstterm>.
2152 </para>
2153 </step>
2154
2155 <step performance="required">
2156 <para>
2157 So long as the working table is not empty, repeat these steps:
2158 </para>
2159 <substeps>
2160 <step performance="required">
2161 <para>
2162 Evaluate the recursive term, substituting the current contents of
2163 the working table for the recursive self-reference.
2164 For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard
2165 duplicate rows and rows that duplicate any previous result row.
2166 Include all remaining rows in the result of the recursive query, and
2167 also place them in a temporary <firstterm>intermediate table</firstterm>.
2168 </para>
2169 </step>
2170
2171 <step performance="required">
2172 <para>
2173 Replace the contents of the working table with the contents of the
2174 intermediate table, then empty the intermediate table.
2175 </para>
2176 </step>
2177 </substeps>
2178 </step>
2179 </procedure>
2180
2181 <note>
2182 <para>
2183 While <literal>RECURSIVE</literal> allows queries to be specified
2184 recursively, internally such queries are evaluated iteratively.
2185 </para>
2186 </note>
2187
2188 <para>
2189 In the example above, the working table has just a single row in each step,
2190 and it takes on the values from 1 through 100 in successive steps. In
2191 the 100th step, there is no output because of the <literal>WHERE</literal>
2192 clause, and so the query terminates.
2193 </para>
2194
2195 <para>
2196 Recursive queries are typically used to deal with hierarchical or
2197 tree-structured data. A useful example is this query to find all the
2198 direct and indirect sub-parts of a product, given only a table that
2199 shows immediate inclusions:
2200
2201 <programlisting>
2202 WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
2203 SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
2204 UNION ALL
2205 SELECT p.sub_part, p.part, p.quantity * pr.quantity
2206 FROM included_parts pr, parts p
2207 WHERE p.part = pr.sub_part
2208 )
2209 SELECT sub_part, SUM(quantity) as total_quantity
2210 FROM included_parts
2211 GROUP BY sub_part
2212 </programlisting>
2213 </para>
2214
2215 <sect3 id="queries-with-search">
2216 <title>Search Order</title>
2217
2218 <para>
2219 When computing a tree traversal using a recursive query, you might want to
2220 order the results in either depth-first or breadth-first order. This can
2221 be done by computing an ordering column alongside the other data columns
2222 and using that to sort the results at the end. Note that this does not
2223 actually control in which order the query evaluation visits the rows; that
2224 is as always in SQL implementation-dependent. This approach merely
2225 provides a convenient way to order the results afterwards.
2226 </para>
2227
2228 <para>
2229 To create a depth-first order, we compute for each result row an array of
2230 rows that we have visited so far. For example, consider the following
2231 query that searches a table <structname>tree</structname> using a
2232 <structfield>link</structfield> field:
2233
2234 <programlisting>
2235 WITH RECURSIVE search_tree(id, link, data) AS (
2236 SELECT t.id, t.link, t.data
2237 FROM tree t
2238 UNION ALL
2239 SELECT t.id, t.link, t.data
2240 FROM tree t, search_tree st
2241 WHERE t.id = st.link
2242 )
2243 SELECT * FROM search_tree;
2244 </programlisting>
2245
2246 To add depth-first ordering information, you can write this:
2247
2248 <programlisting>
2249 WITH RECURSIVE search_tree(id, link, data, <emphasis>path</emphasis>) AS (
2250 SELECT t.id, t.link, t.data, <emphasis>ARRAY[t.id]</emphasis>
2251 FROM tree t
2252 UNION ALL
2253 SELECT t.id, t.link, t.data, <emphasis>path || t.id</emphasis>
2254 FROM tree t, search_tree st
2255 WHERE t.id = st.link
2256 )
2257 SELECT * FROM search_tree <emphasis>ORDER BY path</emphasis>;
2258 </programlisting>
2259 </para>
2260
2261 <para>
2262 In the general case where more than one field needs to be used to identify
2263 a row, use an array of rows. For example, if we needed to track fields
2264 <structfield>f1</structfield> and <structfield>f2</structfield>:
2265
2266 <programlisting>
2267 WITH RECURSIVE search_tree(id, link, data, <emphasis>path</emphasis>) AS (
2268 SELECT t.id, t.link, t.data, <emphasis>ARRAY[ROW(t.f1, t.f2)]</emphasis>
2269 FROM tree t
2270 UNION ALL
2271 SELECT t.id, t.link, t.data, <emphasis>path || ROW(t.f1, t.f2)</emphasis>
2272 FROM tree t, search_tree st
2273 WHERE t.id = st.link
2274 )
2275 SELECT * FROM search_tree <emphasis>ORDER BY path</emphasis>;
2276 </programlisting>
2277 </para>
2278
2279 <tip>
2280 <para>
2281 Omit the <literal>ROW()</literal> syntax in the common case where only one
2282 field needs to be tracked. This allows a simple array rather than a
2283 composite-type array to be used, gaining efficiency.
2284 </para>
2285 </tip>
2286
2287 <para>
2288 To create a breadth-first order, you can add a column that tracks the depth
2289 of the search, for example:
2290
2291 <programlisting>
2292 WITH RECURSIVE search_tree(id, link, data, <emphasis>depth</emphasis>) AS (
2293 SELECT t.id, t.link, t.data, <emphasis>0</emphasis>
2294 FROM tree t
2295 UNION ALL
2296 SELECT t.id, t.link, t.data, <emphasis>depth + 1</emphasis>
2297 FROM tree t, search_tree st
2298 WHERE t.id = st.link
2299 )
2300 SELECT * FROM search_tree <emphasis>ORDER BY depth</emphasis>;
2301 </programlisting>
2302
2303 To get a stable sort, add data columns as secondary sorting columns.
2304 </para>
2305
2306 <tip>
2307 <para>
2308 The recursive query evaluation algorithm produces its output in
2309 breadth-first search order. However, this is an implementation detail and
2310 it is perhaps unsound to rely on it. The order of the rows within each
2311 level is certainly undefined, so some explicit ordering might be desired
2312 in any case.
2313 </para>
2314 </tip>
2315
2316 <para>
2317 There is built-in syntax to compute a depth- or breadth-first sort column.
2318 For example:
2319
2320 <programlisting>
2321 WITH RECURSIVE search_tree(id, link, data) AS (
2322 SELECT t.id, t.link, t.data
2323 FROM tree t
2324 UNION ALL
2325 SELECT t.id, t.link, t.data
2326 FROM tree t, search_tree st
2327 WHERE t.id = st.link
2328 ) <emphasis>SEARCH DEPTH FIRST BY id SET ordercol</emphasis>
2329 SELECT * FROM search_tree ORDER BY ordercol;
2330
2331 WITH RECURSIVE search_tree(id, link, data) AS (
2332 SELECT t.id, t.link, t.data
2333 FROM tree t
2334 UNION ALL
2335 SELECT t.id, t.link, t.data
2336 FROM tree t, search_tree st
2337 WHERE t.id = st.link
2338 ) <emphasis>SEARCH BREADTH FIRST BY id SET ordercol</emphasis>
2339 SELECT * FROM search_tree ORDER BY ordercol;
2340 </programlisting>
2341 This syntax is internally expanded to something similar to the above
2342 hand-written forms. The <literal>SEARCH</literal> clause specifies whether
2343 depth- or breadth first search is wanted, the list of columns to track for
2344 sorting, and a column name that will contain the result data that can be
2345 used for sorting. That column will implicitly be added to the output rows
2346 of the CTE.
2347 </para>
2348 </sect3>
2349
2350 <sect3 id="queries-with-cycle">
2351 <title>Cycle Detection</title>
2352
2353 <para>
2354 When working with recursive queries it is important to be sure that
2355 the recursive part of the query will eventually return no tuples,
2356 or else the query will loop indefinitely. Sometimes, using
2357 <literal>UNION</literal> instead of <literal>UNION ALL</literal> can accomplish this
2358 by discarding rows that duplicate previous output rows. However, often a
2359 cycle does not involve output rows that are completely duplicate: it may be
2360 necessary to check just one or a few fields to see if the same point has
2361 been reached before. The standard method for handling such situations is
2362 to compute an array of the already-visited values. For example, consider again
2363 the following query that searches a table <structname>graph</structname> using a
2364 <structfield>link</structfield> field:
2365
2366 <programlisting>
2367 WITH RECURSIVE search_graph(id, link, data, depth) AS (
2368 SELECT g.id, g.link, g.data, 0
2369 FROM graph g
2370 UNION ALL
2371 SELECT g.id, g.link, g.data, sg.depth + 1
2372 FROM graph g, search_graph sg
2373 WHERE g.id = sg.link
2374 )
2375 SELECT * FROM search_graph;
2376 </programlisting>
2377
2378 This query will loop if the <structfield>link</structfield> relationships contain
2379 cycles. Because we require a <quote>depth</quote> output, just changing
2380 <literal>UNION ALL</literal> to <literal>UNION</literal> would not eliminate the looping.
2381 Instead we need to recognize whether we have reached the same row again
2382 while following a particular path of links. We add two columns
2383 <structfield>is_cycle</structfield> and <structfield>path</structfield> to the loop-prone query:
2384
2385 <programlisting>
2386 WITH RECURSIVE search_graph(id, link, data, depth, <emphasis>is_cycle, path</emphasis>) AS (
2387 SELECT g.id, g.link, g.data, 0,
2388 <emphasis>false,
2389 ARRAY[g.id]</emphasis>
2390 FROM graph g
2391 UNION ALL
2392 SELECT g.id, g.link, g.data, sg.depth + 1,
2393 <emphasis>g.id = ANY(path),
2394 path || g.id</emphasis>
2395 FROM graph g, search_graph sg
2396 WHERE g.id = sg.link <emphasis>AND NOT is_cycle</emphasis>
2397 )
2398 SELECT * FROM search_graph;
2399 </programlisting>
2400
2401 Aside from preventing cycles, the array value is often useful in its own
2402 right as representing the <quote>path</quote> taken to reach any particular row.
2403 </para>
2404
2405 <para>
2406 In the general case where more than one field needs to be checked to
2407 recognize a cycle, use an array of rows. For example, if we needed to
2408 compare fields <structfield>f1</structfield> and <structfield>f2</structfield>:
2409
2410 <programlisting>
2411 WITH RECURSIVE search_graph(id, link, data, depth, <emphasis>is_cycle, path</emphasis>) AS (
2412 SELECT g.id, g.link, g.data, 0,
2413 <emphasis>false,
2414 ARRAY[ROW(g.f1, g.f2)]</emphasis>
2415 FROM graph g
2416 UNION ALL
2417 SELECT g.id, g.link, g.data, sg.depth + 1,
2418 <emphasis>ROW(g.f1, g.f2) = ANY(path),
2419 path || ROW(g.f1, g.f2)</emphasis>
2420 FROM graph g, search_graph sg
2421 WHERE g.id = sg.link <emphasis>AND NOT is_cycle</emphasis>
2422 )
2423 SELECT * FROM search_graph;
2424 </programlisting>
2425 </para>
2426
2427 <tip>
2428 <para>
2429 Omit the <literal>ROW()</literal> syntax in the common case where only one field
2430 needs to be checked to recognize a cycle. This allows a simple array
2431 rather than a composite-type array to be used, gaining efficiency.
2432 </para>
2433 </tip>
2434
2435 <para>
2436 There is built-in syntax to simplify cycle detection. The above query can
2437 also be written like this:
2438 <programlisting>
2439 WITH RECURSIVE search_graph(id, link, data, depth) AS (
2440 SELECT g.id, g.link, g.data, 1
2441 FROM graph g
2442 UNION ALL
2443 SELECT g.id, g.link, g.data, sg.depth + 1
2444 FROM graph g, search_graph sg
2445 WHERE g.id = sg.link
2446 ) <emphasis>CYCLE id SET is_cycle USING path</emphasis>
2447 SELECT * FROM search_graph;
2448 </programlisting>
2449 and it will be internally rewritten to the above form. The
2450 <literal>CYCLE</literal> clause specifies first the list of columns to
2451 track for cycle detection, then a column name that will show whether a
2452 cycle has been detected, and finally the name of another column that will track the
2453 path. The cycle and path columns will implicitly be added to the output
2454 rows of the CTE.
2455 </para>
2456
2457 <tip>
2458 <para>
2459 The cycle path column is computed in the same way as the depth-first
2460 ordering column show in the previous section. A query can have both a
2461 <literal>SEARCH</literal> and a <literal>CYCLE</literal> clause, but a
2462 depth-first search specification and a cycle detection specification would
2463 create redundant computations, so it's more efficient to just use the
2464 <literal>CYCLE</literal> clause and order by the path column. If
2465 breadth-first ordering is wanted, then specifying both
2466 <literal>SEARCH</literal> and <literal>CYCLE</literal> can be useful.
2467 </para>
2468 </tip>
2469
2470 <para>
2471 A helpful trick for testing queries
2472 when you are not certain if they might loop is to place a <literal>LIMIT</literal>
2473 in the parent query. For example, this query would loop forever without
2474 the <literal>LIMIT</literal>:
2475
2476 <programlisting>
2477 WITH RECURSIVE t(n) AS (
2478 SELECT 1
2479 UNION ALL
2480 SELECT n+1 FROM t
2481 )
2482 SELECT n FROM t <emphasis>LIMIT 100</emphasis>;
2483 </programlisting>
2484
2485 This works because <productname>PostgreSQL</productname>'s implementation
2486 evaluates only as many rows of a <literal>WITH</literal> query as are actually
2487 fetched by the parent query. Using this trick in production is not
2488 recommended, because other systems might work differently. Also, it
2489 usually won't work if you make the outer query sort the recursive query's
2490 results or join them to some other table, because in such cases the
2491 outer query will usually try to fetch all of the <literal>WITH</literal> query's
2492 output anyway.
2493 </para>
2494 </sect3>
2495 </sect2>
2496
2497 <sect2 id="queries-with-cte-materialization">
2498 <title>Common Table Expression Materialization</title>
2499
2500 <para>
2501 A useful property of <literal>WITH</literal> queries is that they are
2502 normally evaluated only once per execution of the parent query, even if
2503 they are referred to more than once by the parent query or
2504 sibling <literal>WITH</literal> queries.
2505 Thus, expensive calculations that are needed in multiple places can be
2506 placed within a <literal>WITH</literal> query to avoid redundant work. Another
2507 possible application is to prevent unwanted multiple evaluations of
2508 functions with side-effects.
2509 However, the other side of this coin is that the optimizer is not able to
2510 push restrictions from the parent query down into a multiply-referenced
2511 <literal>WITH</literal> query, since that might affect all uses of the
2512 <literal>WITH</literal> query's output when it should affect only one.
2513 The multiply-referenced <literal>WITH</literal> query will be
2514 evaluated as written, without suppression of rows that the parent query
2515 might discard afterwards. (But, as mentioned above, evaluation might stop
2516 early if the reference(s) to the query demand only a limited number of
2517 rows.)
2518 </para>
2519
2520 <para>
2521 However, if a <literal>WITH</literal> query is non-recursive and
2522 side-effect-free (that is, it is a <literal>SELECT</literal> containing
2523 no volatile functions) then it can be folded into the parent query,
2524 allowing joint optimization of the two query levels. By default, this
2525 happens if the parent query references the <literal>WITH</literal> query
2526 just once, but not if it references the <literal>WITH</literal> query
2527 more than once. You can override that decision by
2528 specifying <literal>MATERIALIZED</literal> to force separate calculation
2529 of the <literal>WITH</literal> query, or by specifying <literal>NOT
2530 MATERIALIZED</literal> to force it to be merged into the parent query.
2531 The latter choice risks duplicate computation of
2532 the <literal>WITH</literal> query, but it can still give a net savings if
2533 each usage of the <literal>WITH</literal> query needs only a small part
2534 of the <literal>WITH</literal> query's full output.
2535 </para>
2536
2537 <para>
2538 A simple example of these rules is
2539 <programlisting>
2540 WITH w AS (
2541 SELECT * FROM big_table
2542 )
2543 SELECT * FROM w WHERE key = 123;
2544 </programlisting>
2545 This <literal>WITH</literal> query will be folded, producing the same
2546 execution plan as
2547 <programlisting>
2548 SELECT * FROM big_table WHERE key = 123;
2549 </programlisting>
2550 In particular, if there's an index on <structfield>key</structfield>,
2551 it will probably be used to fetch just the rows having <literal>key =
2552 123</literal>. On the other hand, in
2553 <programlisting>
2554 WITH w AS (
2555 SELECT * FROM big_table
2556 )
2557 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2558 WHERE w2.key = 123;
2559 </programlisting>
2560 the <literal>WITH</literal> query will be materialized, producing a
2561 temporary copy of <structname>big_table</structname> that is then
2562 joined with itself &mdash; without benefit of any index. This query
2563 will be executed much more efficiently if written as
2564 <programlisting>
2565 WITH w AS NOT MATERIALIZED (
2566 SELECT * FROM big_table
2567 )
2568 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
2569 WHERE w2.key = 123;
2570 </programlisting>
2571 so that the parent query's restrictions can be applied directly
2572 to scans of <structname>big_table</structname>.
2573 </para>
2574
2575 <para>
2576 An example where <literal>NOT MATERIALIZED</literal> could be
2577 undesirable is
2578 <programlisting>
2579 WITH w AS (
2580 SELECT key, very_expensive_function(val) as f FROM some_table
2581 )
2582 SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
2583 </programlisting>
2584 Here, materialization of the <literal>WITH</literal> query ensures
2585 that <function>very_expensive_function</function> is evaluated only
2586 once per table row, not twice.
2587 </para>
2588
2589 <para>
2590 The examples above only show <literal>WITH</literal> being used with
2591 <command>SELECT</command>, but it can be attached in the same way to
2592 <command>INSERT</command>, <command>UPDATE</command>,
2593 <command>DELETE</command>, or <command>MERGE</command>.
2594 In each case it effectively provides temporary table(s) that can
2595 be referred to in the main command.
2596 </para>
2597 </sect2>
2598
2599 <sect2 id="queries-with-modifying">
2600 <title>Data-Modifying Statements in <literal>WITH</literal></title>
2601
2602 <para>
2603 You can use data-modifying statements (<command>INSERT</command>,
2604 <command>UPDATE</command>, <command>DELETE</command>, or
2605 <command>MERGE</command>) in <literal>WITH</literal>. This
2606 allows you to perform several different operations in the same query.
2607 An example is:
2608
2609 <programlisting>
2610 WITH moved_rows AS (
2611 DELETE FROM products
2612 WHERE
2613 "date" &gt;= '2010-10-01' AND
2614 "date" &lt; '2010-11-01'
2615 RETURNING *
2616 )
2617 INSERT INTO products_log
2618 SELECT * FROM moved_rows;
2619 </programlisting>
2620
2621 This query effectively moves rows from <structname>products</structname> to
2622 <structname>products_log</structname>. The <command>DELETE</command> in <literal>WITH</literal>
2623 deletes the specified rows from <structname>products</structname>, returning their
2624 contents by means of its <literal>RETURNING</literal> clause; and then the
2625 primary query reads that output and inserts it into
2626 <structname>products_log</structname>.
2627 </para>
2628
2629 <para>
2630 A fine point of the above example is that the <literal>WITH</literal> clause is
2631 attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within
2632 the <command>INSERT</command>. This is necessary because data-modifying
2633 statements are only allowed in <literal>WITH</literal> clauses that are attached
2634 to the top-level statement. However, normal <literal>WITH</literal> visibility
2635 rules apply, so it is possible to refer to the <literal>WITH</literal>
2636 statement's output from the sub-<command>SELECT</command>.
2637 </para>
2638
2639 <para>
2640 Data-modifying statements in <literal>WITH</literal> usually have
2641 <literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>),
2642 as shown in the example above.
2643 It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> the
2644 target table of the data-modifying statement, that forms the temporary
2645 table that can be referred to by the rest of the query. If a
2646 data-modifying statement in <literal>WITH</literal> lacks a <literal>RETURNING</literal>
2647 clause, then it forms no temporary table and cannot be referred to in
2648 the rest of the query. Such a statement will be executed nonetheless.
2649 A not-particularly-useful example is:
2650
2651 <programlisting>
2652 WITH t AS (
2653 DELETE FROM foo
2654 )
2655 DELETE FROM bar;
2656 </programlisting>
2657
2658 This example would remove all rows from tables <structname>foo</structname> and
2659 <structname>bar</structname>. The number of affected rows reported to the client
2660 would only include rows removed from <structname>bar</structname>.
2661 </para>
2662
2663 <para>
2664 Recursive self-references in data-modifying statements are not
2665 allowed. In some cases it is possible to work around this limitation by
2666 referring to the output of a recursive <literal>WITH</literal>, for example:
2667
2668 <programlisting>
2669 WITH RECURSIVE included_parts(sub_part, part) AS (
2670 SELECT sub_part, part FROM parts WHERE part = 'our_product'
2671 UNION ALL
2672 SELECT p.sub_part, p.part
2673 FROM included_parts pr, parts p
2674 WHERE p.part = pr.sub_part
2675 )
2676 DELETE FROM parts
2677 WHERE part IN (SELECT part FROM included_parts);
2678 </programlisting>
2679
2680 This query would remove all direct and indirect subparts of a product.
2681 </para>
2682
2683 <para>
2684 Data-modifying statements in <literal>WITH</literal> are executed exactly once,
2685 and always to completion, independently of whether the primary query
2686 reads all (or indeed any) of their output. Notice that this is different
2687 from the rule for <command>SELECT</command> in <literal>WITH</literal>: as stated in the
2688 previous section, execution of a <command>SELECT</command> is carried only as far
2689 as the primary query demands its output.
2690 </para>
2691
2692 <para>
2693 The sub-statements in <literal>WITH</literal> are executed concurrently with
2694 each other and with the main query. Therefore, when using data-modifying
2695 statements in <literal>WITH</literal>, the order in which the specified updates
2696 actually happen is unpredictable. All the statements are executed with
2697 the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they
2698 cannot <quote>see</quote> one another's effects on the target tables. This
2699 alleviates the effects of the unpredictability of the actual order of row
2700 updates, and means that <literal>RETURNING</literal> data is the only way to
2701 communicate changes between different <literal>WITH</literal> sub-statements and
2702 the main query. An example of this is that in
2703
2704 <programlisting>
2705 WITH t AS (
2706 UPDATE products SET price = price * 1.05
2707 RETURNING *
2708 )
2709 SELECT * FROM products;
2710 </programlisting>
2711
2712 the outer <command>SELECT</command> would return the original prices before the
2713 action of the <command>UPDATE</command>, while in
2714
2715 <programlisting>
2716 WITH t AS (
2717 UPDATE products SET price = price * 1.05
2718 RETURNING *
2719 )
2720 SELECT * FROM t;
2721 </programlisting>
2722
2723 the outer <command>SELECT</command> would return the updated data.
2724 </para>
2725
2726 <para>
2727 Trying to update the same row twice in a single statement is not
2728 supported. Only one of the modifications takes place, but it is not easy
2729 (and sometimes not possible) to reliably predict which one. This also
2730 applies to deleting a row that was already updated in the same statement:
2731 only the update is performed. Therefore you should generally avoid trying
2732 to modify a single row twice in a single statement. In particular avoid
2733 writing <literal>WITH</literal> sub-statements that could affect the same rows
2734 changed by the main statement or a sibling sub-statement. The effects
2735 of such a statement will not be predictable.
2736 </para>
2737
2738 <para>
2739 At present, any table used as the target of a data-modifying statement in
2740 <literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal>
2741 rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements.
2742 </para>
2743
2744 </sect2>
2745
2746 </sect1>
2747
2748 </chapter>