1SELECT(7) SQL Commands SELECT(7)
2
6 SELECT, TABLE, WITH - retrieve rows from a table or view
7
10 [ WITH [ RECURSIVE ] with_query [, ...] ]
11 SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
12 * | expression [ [ AS ] output_name ] [, ...]
13 [ FROM from_item [, ...] ]
14 [ WHERE condition ]
15 [ GROUP BY expression [, ...] ]
16 [ HAVING condition [, ...] ]
17 [ WINDOW window_name AS ( window_definition ) [, ...] ]
18 [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
19 [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
20 [ LIMIT { count | ALL } ]
21 [ OFFSET start [ ROW | ROWS ] ]
22 [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
23 [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
24 where from_item can be one of:
26 [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
28 ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
29 with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
30 function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
31 function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
32 from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
33 and with_query is:
35 with_query_name [ ( column_name [, ...] ) ] AS ( select )
37 TABLE { [ ONLY ] table_name [ * ] | with_query_name }
39
42 SELECT retrieves rows from zero or more tables. The general processing
43 of SELECT is as follows:
44 1. All queries in the WITH list are computed. These effectively
46 serve as temporary tables that can be referenced in the FROM
47 list. A WITH query that is referenced more than once in FROM is
48 computed only once. (See WITH Clause [select(7)] below.)
49 2. All elements in the FROM list are computed. (Each element in
51 the FROM list is a real or virtual table.) If more than one ele‐
52 ment is specified in the FROM list, they are cross-joined
53 together. (See FROM Clause [select(7)] below.)
54 3. If the WHERE clause is specified, all rows that do not satisfy
56 the condition are eliminated from the output. (See WHERE Clause
57 [select(7)] below.)
58 4. If the GROUP BY clause is specified, the output is divided into
60 groups of rows that match on one or more values. If the HAVING
61 clause is present, it eliminates groups that do not satisfy the
62 given condition. (See GROUP BY Clause [select(7)] and HAVING
63 Clause [select(7)] below.)
64 5. The actual output rows are computed using the SELECT output
66 expressions for each selected row. (See SELECT List [select(7)]
67 below.)
68 6. Using the operators UNION, INTERSECT, and EXCEPT, the output of
70 more than one SELECT statement can be combined to form a single
71 result set. The UNION operator returns all rows that are in one
72 or both of the result sets. The INTERSECT operator returns all
73 rows that are strictly in both result sets. The EXCEPT operator
74 returns the rows that are in the first result set but not in the
75 second. In all three cases, duplicate rows are eliminated unless
76 ALL is specified. (See UNION Clause [select(7)], INTERSECT
77 Clause [select(7)], and EXCEPT Clause [select(7)] below.)
78 7. If the ORDER BY clause is specified, the returned rows are
80 sorted in the specified order. If ORDER BY is not given, the
81 rows are returned in whatever order the system finds fastest to
82 produce. (See ORDER BY Clause [select(7)] below.)
83 8. DISTINCT eliminates duplicate rows from the result. DISTINCT ON
85 eliminates rows that match on all the specified expressions. ALL
86 (the default) will return all candidate rows, including dupli‐
87 cates. (See DISTINCT Clause [select(7)] below.)
88 9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the
90 SELECT statement only returns a subset of the result rows. (See
91 LIMIT Clause [select(7)] below.)
92 10. If FOR UPDATE or FOR SHARE is specified, the SELECT statement
94 locks the selected rows against concurrent updates. (See FOR
95 UPDATE/FOR SHARE Clause [select(7)] below.)
96 You must have SELECT privilege on each column used in a SELECT command.
98 The use of FOR UPDATE or FOR SHARE requires UPDATE privilege as well
99 (for at least one column of each table so selected).
100
102 WITH CLAUSE
103 The WITH clause allows you to specify one or more subqueries that can
104 be referenced by name in the primary query. The subqueries effectively
105 act as temporary tables or views for the duration of the primary query.
106 A name (without schema qualification) must be specified for each WITH
108 query. Optionally, a list of column names can be specified; if this is
109 omitted, the column names are inferred from the subquery.
110 If RECURSIVE is specified, it allows a subquery to reference itself by
112 name. Such a subquery must have the form
113 non_recursive_term UNION [ ALL ] recursive_term
115 where the recursive self-reference must appear on the right-hand side
117 of the UNION. Only one recursive self-reference is permitted per query.
118 Another effect of RECURSIVE is that WITH queries need not be ordered: a
120 query can reference another one that is later in the list. (However,
121 circular references, or mutual recursion, are not implemented.) With‐
122 out RECURSIVE, WITH queries can only reference sibling WITH queries
123 that are earlier in the WITH list.
124 A useful property of WITH queries is that they are evaluated only once
126 per execution of the primary query, even if the primary query refers to
127 them more than once.
128 See in the documentation for additional information.
130 FROM CLAUSE
132 The FROM clause specifies one or more source tables for the SELECT. If
133 multiple sources are specified, the result is the Cartesian product
134 (cross join) of all the sources. But usually qualification conditions
135 are added to restrict the returned rows to a small subset of the Carte‐
136 sian product.
137 The FROM clause can contain the following elements:
139 table_name
141 The name (optionally schema-qualified) of an existing table or
142 view. If ONLY is specified, only that table is scanned. If ONLY
143 is not specified, the table and any descendant tables are
144 scanned.
145 alias A substitute name for the FROM item containing the alias. An
147 alias is used for brevity or to eliminate ambiguity for self-
148 joins (where the same table is scanned multiple times). When an
149 alias is provided, it completely hides the actual name of the
150 table or function; for example given FROM foo AS f, the remain‐
151 der of the SELECT must refer to this FROM item as f not foo. If
152 an alias is written, a column alias list can also be written to
153 provide substitute names for one or more columns of the table.
154 select A sub-SELECT can appear in the FROM clause. This acts as though
156 its output were created as a temporary table for the duration of
157 this single SELECT command. Note that the sub-SELECT must be
158 surrounded by parentheses, and an alias must be provided for it.
159 A VALUES [values(7)] command can also be used here.
160 with_query_name
162 A WITH query is referenced by writing its name, just as though
163 the query's name were a table name. (In fact, the WITH query
164 hides any real table of the same name for the purposes of the
165 primary query. If necessary, you can refer to a real table of
166 the same name by schema-qualifying the table's name.) An alias
167 can be provided in the same way as for a table.
168 function_name
170 Function calls can appear in the FROM clause. (This is espe‐
171 cially useful for functions that return result sets, but any
172 function can be used.) This acts as though its output were cre‐
173 ated as a temporary table for the duration of this single SELECT
174 command. An alias can also be used. If an alias is written, a
175 column alias list can also be written to provide substitute
176 names for one or more attributes of the function's composite
177 return type. If the function has been defined as returning the
178 record data type, then an alias or the key word AS must be
179 present, followed by a column definition list in the form ( col‐
180 umn_name data_type [, ... ] ). The column definition list must
181 match the actual number and types of columns returned by the
182 function.
183 join_type
185 One of
186 · [ INNER ] JOIN
188 · LEFT [ OUTER ] JOIN
190 · RIGHT [ OUTER ] JOIN
192 · FULL [ OUTER ] JOIN
194 · CROSS JOIN
196 For the INNER and OUTER join types, a join condition must be specified,
198 namely exactly one of NATURAL, ON join_condition, or USING (join_column
199 [, ...]). See below for the meaning. For CROSS JOIN, none of these
200 clauses can appear.
201 A JOIN clause combines two FROM items. Use parentheses if necessary to
203 determine the order of nesting. In the absence of parentheses, JOINs
204 nest left-to-right. In any case JOIN binds more tightly than the commas
205 separating FROM items.
206 CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same
208 result as you get from listing the two items at the top level of FROM,
209 but restricted by the join condition (if any). CROSS JOIN is equiva‐
210 lent to INNER JOIN ON (TRUE), that is, no rows are removed by qualifi‐
211 cation. These join types are just a notational convenience, since they
212 do nothing you couldn't do with plain FROM and WHERE.
213 LEFT OUTER JOIN returns all rows in the qualified Cartesian product
215 (i.e., all combined rows that pass its join condition), plus one copy
216 of each row in the left-hand table for which there was no right-hand
217 row that passed the join condition. This left-hand row is extended to
218 the full width of the joined table by inserting null values for the
219 right-hand columns. Note that only the JOIN clause's own condition is
220 considered while deciding which rows have matches. Outer conditions are
221 applied afterwards.
222 Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row
224 for each unmatched right-hand row (extended with nulls on the left).
225 This is just a notational convenience, since you could convert it to a
226 LEFT OUTER JOIN by switching the left and right inputs.
227 FULL OUTER JOIN returns all the joined rows, plus one row for each
229 unmatched left-hand row (extended with nulls on the right), plus one
230 row for each unmatched right-hand row (extended with nulls on the
231 left).
232 ON join_condition
234 join_condition is an expression resulting in a value of type
235 boolean (similar to a WHERE clause) that specifies which rows in
236 a join are considered to match.
237 USING ( join_column [, ...] )
239 A clause of the form USING ( a, b, ... ) is shorthand for ON
240 left_table.a = right_table.a AND left_table.b = right_table.b
241 .... Also, USING implies that only one of each pair of equiva‐
242 lent columns will be included in the join output, not both.
243 NATURAL
245 NATURAL is shorthand for a USING list that mentions all columns
246 in the two tables that have the same names.
247 WHERE CLAUSE
249 The optional WHERE clause has the general form
250 WHERE condition
252 where condition is any expression that evaluates to a result of type
254 boolean. Any row that does not satisfy this condition will be elimi‐
255 nated from the output. A row satisfies the condition if it returns true
256 when the actual row values are substituted for any variable references.
257 GROUP BY CLAUSE
259 The optional GROUP BY clause has the general form
260 GROUP BY expression [, ...]
262 GROUP BY will condense into a single row all selected rows that share
265 the same values for the grouped expressions. expression can be an input
266 column name, or the name or ordinal number of an output column (SELECT
267 list item), or an arbitrary expression formed from input-column values.
268 In case of ambiguity, a GROUP BY name will be interpreted as an input-
269 column name rather than an output column name.
270 Aggregate functions, if any are used, are computed across all rows mak‐
272 ing up each group, producing a separate value for each group (whereas
273 without GROUP BY, an aggregate produces a single value computed across
274 all the selected rows). When GROUP BY is present, it is not valid for
275 the SELECT list expressions to refer to ungrouped columns except within
276 aggregate functions, since there would be more than one possible value
277 to return for an ungrouped column.
278 HAVING CLAUSE
280 The optional HAVING clause has the general form
281 HAVING condition
283 where condition is the same as specified for the WHERE clause.
285 HAVING eliminates group rows that do not satisfy the condition. HAVING
287 is different from WHERE: WHERE filters individual rows before the
288 application of GROUP BY, while HAVING filters group rows created by
289 GROUP BY. Each column referenced in condition must unambiguously refer‐
290 ence a grouping column, unless the reference appears within an aggre‐
291 gate function.
292 The presence of HAVING turns a query into a grouped query even if there
294 is no GROUP BY clause. This is the same as what happens when the query
295 contains aggregate functions but no GROUP BY clause. All the selected
296 rows are considered to form a single group, and the SELECT list and
297 HAVING clause can only reference table columns from within aggregate
298 functions. Such a query will emit a single row if the HAVING condition
299 is true, zero rows if it is not true.
300 WINDOW CLAUSE
302 The optional WINDOW clause has the general form
303 WINDOW window_name AS ( window_definition ) [, ...]
305 where window_name is a name that can be referenced from subsequent win‐
307 dow definitions or OVER clauses, and window_definition is
308 [ existing_window_name ]
310 [ PARTITION BY expression [, ...] ]
311 [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
312 [ frame_clause ]
313 If an existing_window_name is specified it must refer to an earlier
316 entry in the WINDOW list; the new window copies its partitioning clause
317 from that entry, as well as its ordering clause if any. In this case
318 the new window cannot specify its own PARTITION BY clause, and it can
319 specify ORDER BY only if the copied window does not have one. The new
320 window always uses its own frame clause; the copied window must not
321 specify a frame clause.
322 The elements of the PARTITION BY list are interpreted in much the same
324 fashion as elements of a GROUP BY Clause [select(7)], except that they
325 are always simple expressions and never the name or number of an output
326 column. Another difference is that these expressions can contain
327 aggregate function calls, which are not allowed in a regular GROUP BY
328 clause. They are allowed here because windowing occurs after grouping
329 and aggregation.
330 Similarly, the elements of the ORDER BY list are interpreted in much
332 the same fashion as elements of an ORDER BY Clause [select(7)], except
333 that the expressions are always taken as simple expressions and never
334 the name or number of an output column.
335 The optional frame_clause defines the window frame for window functions
337 that depend on the frame (not all do). It can be one of
338 RANGE UNBOUNDED PRECEDING
340 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
341 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
342 ROWS UNBOUNDED PRECEDING
343 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
344 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
345 The first two are equivalent and are also the default: they set the
347 frame to be all rows from the partition start up through the current
348 row's last peer in the ORDER BY ordering (which means all rows if there
349 is no ORDER BY). The options RANGE BETWEEN UNBOUNDED PRECEDING AND
350 UNBOUNDED FOLLOWING and ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
351 FOLLOWING are also equivalent: they always select all rows in the par‐
352 tition. Lastly, ROWS UNBOUNDED PRECEDING or its verbose equivalent
353 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW select all rows up
354 through the current row (regardless of duplicates). Beware that this
355 option can produce implementation-dependent results if the ORDER BY
356 ordering does not order the rows uniquely.
357 The purpose of a WINDOW clause is to specify the behavior of window
359 functions appearing in the query's SELECT List [select(7)] or ORDER BY
360 Clause [select(7)]. These functions can reference the WINDOW clause
361 entries by name in their OVER clauses. A WINDOW clause entry does not
362 have to be referenced anywhere, however; if it is not used in the query
363 it is simply ignored. It is possible to use window functions without
364 any WINDOW clause at all, since a window function call can specify its
365 window definition directly in its OVER clause. However, the WINDOW
366 clause saves typing when the same window definition is needed for more
367 than one window function.
368 Window functions are described in detail in in the documentation, in
370 the documentation, and in the documentation.
371 SELECT LIST
373 The SELECT list (between the key words SELECT and FROM) specifies
374 expressions that form the output rows of the SELECT statement. The
375 expressions can (and usually do) refer to columns computed in the FROM
376 clause.
377 Just as in a table, every output column of a SELECT has a name. In a
379 simple SELECT this name is just used to label the column for display,
380 but when the SELECT is a sub-query of a larger query, the name is seen
381 by the larger query as the column name of the virtual table produced by
382 the sub-query. To specify the name to use for an output column, write
383 AS output_name after the column's expression. (You can omit AS, but
384 only if the desired output name does not match any PostgreSQL keyword
385 (see in the documentation). For protection against possible future key‐
386 word additions, it is recommended that you always either write AS or
387 double-quote the output name.) If you do not specify a column name, a
388 name is chosen automatically by PostgreSQL. If the column's expression
389 is a simple column reference then the chosen name is the same as that
390 column's name; in more complex cases a generated name looking like
391 ?columnN? is usually chosen.
392 An output column's name can be used to refer to the column's value in
394 ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
395 there you must write out the expression instead.
396 Instead of an expression, * can be written in the output list as a
398 shorthand for all the columns of the selected rows. Also, you can write
399 table_name.* as a shorthand for the columns coming from just that ta‐
400 ble. In these cases it is not possible to specify new names with AS;
401 the output column names will be the same as the table columns' names.
402 UNION CLAUSE
404 The UNION clause has this general form:
405 select_statement UNION [ ALL ] select_statement
407 select_statement is any SELECT statement without an ORDER BY, LIMIT,
409 FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached
410 to a subexpression if it is enclosed in parentheses. Without parenthe‐
411 ses, these clauses will be taken to apply to the result of the UNION,
412 not to its right-hand input expression.)
413 The UNION operator computes the set union of the rows returned by the
415 involved SELECT statements. A row is in the set union of two result
416 sets if it appears in at least one of the result sets. The two SELECT
417 statements that represent the direct operands of the UNION must produce
418 the same number of columns, and corresponding columns must be of com‐
419 patible data types.
420 The result of UNION does not contain any duplicate rows unless the ALL
422 option is specified. ALL prevents elimination of duplicates. (There‐
423 fore, UNION ALL is usually significantly quicker than UNION; use ALL
424 when you can.)
425 Multiple UNION operators in the same SELECT statement are evaluated
427 left to right, unless otherwise indicated by parentheses.
428 Currently, FOR UPDATE and FOR SHARE cannot be specified either for a
430 UNION result or for any input of a UNION.
431 INTERSECT CLAUSE
433 The INTERSECT clause has this general form:
434 select_statement INTERSECT [ ALL ] select_statement
436 select_statement is any SELECT statement without an ORDER BY, LIMIT,
438 FOR UPDATE, or FOR SHARE clause.
439 The INTERSECT operator computes the set intersection of the rows
441 returned by the involved SELECT statements. A row is in the intersec‐
442 tion of two result sets if it appears in both result sets.
443 The result of INTERSECT does not contain any duplicate rows unless the
445 ALL option is specified. With ALL, a row that has m duplicates in the
446 left table and n duplicates in the right table will appear min(m,n)
447 times in the result set.
448 Multiple INTERSECT operators in the same SELECT statement are evaluated
450 left to right, unless parentheses dictate otherwise. INTERSECT binds
451 more tightly than UNION. That is, A UNION B INTERSECT C will be read as
452 A UNION (B INTERSECT C).
453 Currently, FOR UPDATE and FOR SHARE cannot be specified either for an
455 INTERSECT result or for any input of an INTERSECT.
456 EXCEPT CLAUSE
458 The EXCEPT clause has this general form:
459 select_statement EXCEPT [ ALL ] select_statement
461 select_statement is any SELECT statement without an ORDER BY, LIMIT,
463 FOR UPDATE, or FOR SHARE clause.
464 The EXCEPT operator computes the set of rows that are in the result of
466 the left SELECT statement but not in the result of the right one.
467 The result of EXCEPT does not contain any duplicate rows unless the ALL
469 option is specified. With ALL, a row that has m duplicates in the left
470 table and n duplicates in the right table will appear max(m-n,0) times
471 in the result set.
472 Multiple EXCEPT operators in the same SELECT statement are evaluated
474 left to right, unless parentheses dictate otherwise. EXCEPT binds at
475 the same level as UNION.
476 Currently, FOR UPDATE and FOR SHARE cannot be specified either for an
478 EXCEPT result or for any input of an EXCEPT.
479 ORDER BY CLAUSE
481 The optional ORDER BY clause has this general form:
482 ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]
484 The ORDER BY clause causes the result rows to be sorted according to
486 the specified expression(s). If two rows are equal according to the
487 leftmost expression, they are compared according to the next expression
488 and so on. If they are equal according to all specified expressions,
489 they are returned in an implementation-dependent order.
490 Each expression can be the name or ordinal number of an output column
492 (SELECT list item), or it can be an arbitrary expression formed from
493 input-column values.
494 The ordinal number refers to the ordinal (left-to-right) position of
496 the output column. This feature makes it possible to define an ordering
497 on the basis of a column that does not have a unique name. This is
498 never absolutely necessary because it is always possible to assign a
499 name to an output column using the AS clause.
500 It is also possible to use arbitrary expressions in the ORDER BY
502 clause, including columns that do not appear in the SELECT output list.
503 Thus the following statement is valid:
504 SELECT name FROM distributors ORDER BY code;
506 A limitation of this feature is that an ORDER BY clause applying to the
508 result of a UNION, INTERSECT, or EXCEPT clause can only specify an out‐
509 put column name or number, not an expression.
510 If an ORDER BY expression is a simple name that matches both an output
512 column name and an input column name, ORDER BY will interpret it as the
513 output column name. This is the opposite of the choice that GROUP BY
514 will make in the same situation. This inconsistency is made to be com‐
515 patible with the SQL standard.
516 Optionally one can add the key word ASC (ascending) or DESC (descend‐
518 ing) after any expression in the ORDER BY clause. If not specified, ASC
519 is assumed by default. Alternatively, a specific ordering operator name
520 can be specified in the USING clause. An ordering operator must be a
521 less-than or greater-than member of some B-tree operator family. ASC
522 is usually equivalent to USING < and DESC is usually equivalent to
523 USING >. (But the creator of a user-defined data type can define
524 exactly what the default sort ordering is, and it might correspond to
525 operators with other names.)
526 If NULLS LAST is specified, null values sort after all non-null values;
528 if NULLS FIRST is specified, null values sort before all non-null val‐
529 ues. If neither is specified, the default behavior is NULLS LAST when
530 ASC is specified or implied, and NULLS FIRST when DESC is specified
531 (thus, the default is to act as though nulls are larger than non-
532 nulls). When USING is specified, the default nulls ordering depends on
533 whether the operator is a less-than or greater-than operator.
534 Note that ordering options apply only to the expression they follow;
536 for example ORDER BY x, y DESC does not mean the same thing as ORDER BY
537 x DESC, y DESC.
538 Character-string data is sorted according to the locale-specific colla‐
540 tion order that was established when the database was created.
541 DISTINCT CLAUSE
543 If DISTINCT is specified, all duplicate rows are removed from the
544 result set (one row is kept from each group of duplicates). ALL speci‐
545 fies the opposite: all rows are kept; that is the default.
546 DISTINCT ON ( expression [, ...] ) keeps only the first row of each set
548 of rows where the given expressions evaluate to equal. The DISTINCT ON
549 expressions are interpreted using the same rules as for ORDER BY (see
550 above). Note that the ``first row'' of each set is unpredictable unless
551 ORDER BY is used to ensure that the desired row appears first. For
552 example:
553 SELECT DISTINCT ON (location) location, time, report
555 FROM weather_reports
556 ORDER BY location, time DESC;
557 retrieves the most recent weather report for each location. But if we
559 had not used ORDER BY to force descending order of time values for each
560 location, we'd have gotten a report from an unpredictable time for each
561 location.
562 The DISTINCT ON expression(s) must match the leftmost ORDER BY expres‐
564 sion(s). The ORDER BY clause will normally contain additional expres‐
565 sion(s) that determine the desired precedence of rows within each DIS‐
566 TINCT ON group.
567 LIMIT CLAUSE
569 The LIMIT clause consists of two independent sub-clauses:
570 LIMIT { count | ALL }
572 OFFSET start
573 count specifies the maximum number of rows to return, while start spec‐
575 ifies the number of rows to skip before starting to return rows. When
576 both are specified, start rows are skipped before starting to count the
577 count rows to be returned.
578 If the count expression evaluates to NULL, it is treated as LIMIT ALL,
580 i.e., no limit. If start evaluates to NULL, it is treated the same as
581 OFFSET 0.
582 SQL:2008 introduced a different syntax to achieve the same thing, which
584 PostgreSQL also supports. It is:
585 OFFSET start { ROW | ROWS }
587 FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
588 Both clauses are optional, but if present the OFFSET clause must come
590 before the FETCH clause. ROW and ROWS as well as FIRST and NEXT are
591 noise words that don't influence the effects of these clauses. In this
592 syntax, when using expressions other than simple constants for start or
593 count, parentheses will be necessary in most cases. If count is omitted
594 in FETCH, it defaults to 1.
595 When using LIMIT, it is a good idea to use an ORDER BY clause that con‐
597 strains the result rows into a unique order. Otherwise you will get an
598 unpredictable subset of the query's rows — you might be asking for the
599 tenth through twentieth rows, but tenth through twentieth in what
600 ordering? You don't know what ordering unless you specify ORDER BY.
601 The query planner takes LIMIT into account when generating a query
603 plan, so you are very likely to get different plans (yielding different
604 row orders) depending on what you use for LIMIT and OFFSET. Thus, using
605 different LIMIT/OFFSET values to select different subsets of a query
606 result will give inconsistent results unless you enforce a predictable
607 result ordering with ORDER BY. This is not a bug; it is an inherent
608 consequence of the fact that SQL does not promise to deliver the
609 results of a query in any particular order unless ORDER BY is used to
610 constrain the order.
611 It is even possible for repeated executions of the same LIMIT query to
613 return different subsets of the rows of a table, if there is not an
614 ORDER BY to enforce selection of a deterministic subset. Again, this is
615 not a bug; determinism of the results is simply not guaranteed in such
616 a case.
617 FOR UPDATE/FOR SHARE CLAUSE
619 The FOR UPDATE clause has this form:
620 FOR UPDATE [ OF table_name [, ...] ] [ NOWAIT ]
622 The closely related FOR SHARE clause has this form:
625 FOR SHARE [ OF table_name [, ...] ] [ NOWAIT ]
627 FOR UPDATE causes the rows retrieved by the SELECT statement to be
630 locked as though for update. This prevents them from being modified or
631 deleted by other transactions until the current transaction ends. That
632 is, other transactions that attempt UPDATE, DELETE, or SELECT FOR
633 UPDATE of these rows will be blocked until the current transaction
634 ends. Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another
635 transaction has already locked a selected row or rows, SELECT FOR
636 UPDATE will wait for the other transaction to complete, and will then
637 lock and return the updated row (or no row, if the row was deleted).
638 For further discussion see in the documentation.
639 To prevent the operation from waiting for other transactions to commit,
641 use the NOWAIT option. SELECT FOR UPDATE NOWAIT reports an error,
642 rather than waiting, if a selected row cannot be locked immediately.
643 Note that NOWAIT applies only to the row-level lock(s) — the required
644 ROW SHARE table-level lock is still taken in the ordinary way (see in
645 the documentation). You can use the NOWAIT option of LOCK [lock(7)] if
646 you need to acquire the table-level lock without waiting.
647 FOR SHARE behaves similarly, except that it acquires a shared rather
649 than exclusive lock on each retrieved row. A shared lock blocks other
650 transactions from performing UPDATE, DELETE, or SELECT FOR UPDATE on
651 these rows, but it does not prevent them from performing SELECT FOR
652 SHARE.
653 If specific tables are named in FOR UPDATE or FOR SHARE, then only rows
655 coming from those tables are locked; any other tables used in the
656 SELECT are simply read as usual. A FOR UPDATE or FOR SHARE clause with‐
657 out a table list affects all tables used in the command. If FOR UPDATE
658 or FOR SHARE is applied to a view or sub-query, it affects all tables
659 used in the view or sub-query. However, FOR UPDATE/FOR SHARE do not
660 apply to WITH queries referenced by the primary query. If you want row
661 locking to occur within a WITH query, specify FOR UPDATE or FOR SHARE
662 within the WITH query.
663 Multiple FOR UPDATE and FOR SHARE clauses can be written if it is nec‐
665 essary to specify different locking behavior for different tables. If
666 the same table is mentioned (or implicitly affected) by both FOR UPDATE
667 and FOR SHARE clauses, then it is processed as FOR UPDATE. Similarly, a
668 table is processed as NOWAIT if that is specified in any of the clauses
669 affecting it.
670 FOR UPDATE and FOR SHARE cannot be used in contexts where returned rows
672 cannot be clearly identified with individual table rows; for example
673 they cannot be used with aggregation.
674 Caution: Avoid locking a row and then modifying it within a
676 later savepoint or PL/pgSQL exception block. A subsequent roll‐
677 back would cause the lock to be lost. For example:
678 BEGIN;
680 SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
681 SAVEPOINT s;
682 UPDATE mytable SET ... WHERE key = 1;
683 ROLLBACK TO s;
684 After the ROLLBACK, the row is effectively unlocked, rather than
686 returned to its pre-savepoint state of being locked but not mod‐
687 ified. This hazard occurs if a row locked in the current trans‐
688 action is updated or deleted, or if a shared lock is upgraded to
689 exclusive: in all these cases, the former lock state is forgot‐
690 ten. If the transaction is then rolled back to a state between
691 the original locking command and the subsequent change, the row
692 will appear not to be locked at all. This is an implementation
693 deficiency which will be addressed in a future release of Post‐
694 greSQL.
695 Caution: It is possible for a SELECT command using both LIMIT
698 and FOR UPDATE/SHARE clauses to return fewer rows than specified
699 by LIMIT. This is because LIMIT is applied first. The command
700 selects the specified number of rows, but might then block try‐
701 ing to obtain a lock on one or more of them. Once the SELECT
702 unblocks, the row might have been deleted or updated so that it
703 does not meet the query WHERE condition anymore, in which case
704 it will not be returned.
705 Caution: Similarly, it is possible for a SELECT command using
708 ORDER BY and FOR UPDATE/SHARE to return rows out of order. This
709 is because ORDER BY is applied first. The command orders the
710 result, but might then block trying to obtain a lock on one or
711 more of the rows. Once the SELECT unblocks, one of the ordered
712 columns might have been modified and be returned out of order. A
713 workaround is to perform SELECT ... FOR UPDATE/SHARE and then
714 SELECT ... ORDER BY.
715 TABLE COMMAND
718 The command
719 TABLE name
721 is completely equivalent to
723 SELECT * FROM name
725 It can be used as a top-level command or as a space-saving syntax vari‐
727 ant in parts of complex queries.
728
730 To join the table films with the table distributors:
731 SELECT f.title, f.did, d.name, f.date_prod, f.kind
733 FROM distributors d, films f
734 WHERE f.did = d.did
735 title | did | name | date_prod | kind
737 -------------------+-----+--------------+------------+----------
738 The Third Man | 101 | British Lion | 1949-12-23 | Drama
739 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
740 ...
741 To sum the column len of all films and group the results by kind:
744 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
746 kind | total
748 ----------+-------
749 Action | 07:34
750 Comedy | 02:58
751 Drama | 14:28
752 Musical | 06:42
753 Romantic | 04:38
754 To sum the column len of all films, group the results by kind and show
757 those group totals that are less than 5 hours:
758 SELECT kind, sum(len) AS total
760 FROM films
761 GROUP BY kind
762 HAVING sum(len) < interval '5 hours';
763 kind | total
765 ----------+-------
766 Comedy | 02:58
767 Romantic | 04:38
768 The following two examples are identical ways of sorting the individual
771 results according to the contents of the second column (name):
772 SELECT * FROM distributors ORDER BY name;
774 SELECT * FROM distributors ORDER BY 2;
775 did | name
777 -----+------------------
778 109 | 20th Century Fox
779 110 | Bavaria Atelier
780 101 | British Lion
781 107 | Columbia
782 102 | Jean Luc Godard
783 113 | Luso films
784 104 | Mosfilm
785 103 | Paramount
786 106 | Toho
787 105 | United Artists
788 111 | Walt Disney
789 112 | Warner Bros.
790 108 | Westward
791 The next example shows how to obtain the union of the tables distribu‐
794 tors and actors, restricting the results to those that begin with the
795 letter W in each table. Only distinct rows are wanted, so the key word
796 ALL is omitted.
797 distributors: actors:
799 did | name id | name
800 -----+-------------- ----+----------------
801 108 | Westward 1 | Woody Allen
802 111 | Walt Disney 2 | Warren Beatty
803 112 | Warner Bros. 3 | Walter Matthau
804 ... ...
805 SELECT distributors.name
807 FROM distributors
808 WHERE distributors.name LIKE 'W%'
809 UNION
810 SELECT actors.name
811 FROM actors
812 WHERE actors.name LIKE 'W%';
813 name
815 ----------------
816 Walt Disney
817 Walter Matthau
818 Warner Bros.
819 Warren Beatty
820 Westward
821 Woody Allen
822 This example shows how to use a function in the FROM clause, both with
825 and without a column definition list:
826 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
828 SELECT * FROM distributors WHERE did = $1;
829 $$ LANGUAGE SQL;
830 SELECT * FROM distributors(111);
832 did | name
833 -----+-------------
834 111 | Walt Disney
835 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
837 SELECT * FROM distributors WHERE did = $1;
838 $$ LANGUAGE SQL;
839 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
841 f1 | f2
842 -----+-------------
843 111 | Walt Disney
844 This example shows how to use a simple WITH clause:
847 WITH t AS (
849 SELECT random() as x FROM generate_series(1, 3)
850 )
851 SELECT * FROM t
852 UNION ALL
853 SELECT * FROM t
854 x
856 --------------------
857 0.534150459803641
858 0.520092216785997
859 0.0735620250925422
860 0.534150459803641
861 0.520092216785997
862 0.0735620250925422
863 Notice that the WITH query was evaluated only once, so that we got two
865 sets of the same three random values.
866 This example uses WITH RECURSIVE to find all subordinates (direct or
868 indirect) of the employee Mary, and their level of indirectness, from a
869 table that shows only direct subordinates:
870 WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
872 SELECT 1, employee_name, manager_name
873 FROM employee
874 WHERE manager_name = 'Mary'
875 UNION ALL
876 SELECT er.distance + 1, e.employee_name, e.manager_name
877 FROM employee_recursive er, employee e
878 WHERE er.employee_name = e.manager_name
879 )
880 SELECT distance, employee_name FROM employee_recursive;
881 Notice the typical form of recursive queries: an initial condition,
883 followed by UNION, followed by the recursive part of the query. Be sure
884 that the recursive part of the query will eventually return no tuples,
885 or else the query will loop indefinitely. (See in the documentation for
886 more examples.)
887
889 Of course, the SELECT statement is compatible with the SQL standard.
890 But there are some extensions and some missing features.
891 OMITTED FROM CLAUSES
893 PostgreSQL allows one to omit the FROM clause. It has a straightforward
894 use to compute the results of simple expressions:
895 SELECT 2+2;
897 ?column?
899 ----------
900 4
901 Some other SQL databases cannot do this except by introducing a dummy
903 one-row table from which to do the SELECT.
904 Note that if a FROM clause is not specified, the query cannot reference
906 any database tables. For example, the following query is invalid:
907 SELECT distributors.* WHERE distributors.name = 'Westward';
909 PostgreSQL releases prior to 8.1 would accept queries of this form, and
911 add an implicit entry to the query's FROM clause for each table refer‐
912 enced by the query. This is no longer the default behavior, because it
913 does not comply with the SQL standard, and is considered by many to be
914 error-prone. For compatibility with applications that rely on this
915 behavior the add_missing_from configuration variable can be enabled.
916 OMITTING THE AS KEY WORD
918 In the SQL standard, the optional key word AS can be omitted before an
919 output column name whenever the new column name is a valid column name
920 (that is, not the same as any reserved keyword). PostgreSQL is slightly
921 more restrictive: AS is required if the new column name matches any
922 keyword at all, reserved or not. Recommended practice is to use AS or
923 double-quote output column names, to prevent any possible conflict
924 against future keyword additions.
925 In FROM items, both the standard and PostgreSQL allow AS to be omitted
927 before an alias that is an unreserved keyword. But this is impractical
928 for output column names, because of syntactic ambiguities.
929 ONLY AND PARENTHESES
931 The SQL standard requires parentheses around the table name after ONLY,
932 as in SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL sup‐
933 ports that as well, but the parentheses are optional. (This point
934 applies equally to all SQL commands supporting the ONLY option.)
935 NAMESPACE AVAILABLE TO GROUP BY AND ORDER BY
937 In the SQL-92 standard, an ORDER BY clause can only use output column
938 names or numbers, while a GROUP BY clause can only use expressions
939 based on input column names. PostgreSQL extends each of these clauses
940 to allow the other choice as well (but it uses the standard's interpre‐
941 tation if there is ambiguity). PostgreSQL also allows both clauses to
942 specify arbitrary expressions. Note that names appearing in an expres‐
943 sion will always be taken as input-column names, not as output-column
944 names.
945 SQL:1999 and later use a slightly different definition which is not
947 entirely upward compatible with SQL-92. In most cases, however, Post‐
948 greSQL will interpret an ORDER BY or GROUP BY expression the same way
949 SQL:1999 does.
950 WINDOW CLAUSE RESTRICTIONS
952 The SQL standard provides additional options for the window
953 frame_clause. PostgreSQL currently supports only the options listed
954 above.
955 LIMIT AND OFFSET
957 The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used
958 by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ...
959 FETCH {FIRST|NEXT} ... for the same functionality, as shown above in
960 LIMIT Clause [select(7)], and this syntax is also used by IBM DB2.
961 (Applications written for Oracle frequently use a workaround involving
962 the automatically generated rownum column, not available in PostgreSQL,
963 to implement the effects of these clauses.)
964 NONSTANDARD CLAUSES
966 The clause DISTINCT ON is not defined in the SQL standard.
967SQL - Language Statements 2011-09-22 SELECT(7)