FOR UPDATE locks selected rows to prevent concurrent modifications. If you don’t want to wait for locked rows, use NOWAIT to fail immediately or SKIP LOCKED to skip them, which is useful for queue-like tables.By default, it locks rows from all queried tables, but you can limit it to specific ones using FOR UPDATE OF table_name. In a JOIN, only the rows that appear in the final result set are locked. If applied to a view, FOR UPDATE locks all underlying tables.With LIMIT, locking stops once enough rows are fetched, but rows skipped by OFFSET still get locked.

SELECT FOR UPDATE locks all joined tables by default

computerscience

Computer Science

A sub-<code>SELECT </code>can appear in the <code>FROM </code>clause. This acts as though its output were created as a temporary table for the duration of this single <code>SELECT </code>command. Note that the sub-<code>SELECT </code>must be surrounded by parentheses, and an alias must be provided for it. A <a href="https://www.postgresql.org/docs/15/sql-values.html"><code>VALUES </code> </a>command can also be used here.

A <code>WITH </code>query is referenced by writing its name, just as though the query's name were a table name. (In fact, the <code>WITH </code>query hides any real table of the same name for the purposes of the primary query. If necessary, you can refer to a real table of the same name by schema-qualifying the table's name.) An alias can be provided in the same way as for a table.

Function calls can appear in the <code>FROM </code>clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though the function's output were created as a temporary table for the duration of this single <code>SELECT </code>command. If the function's result type is composite (including the case of a function with multiple <code>OUT </code>parameters), each attribute becomes a separate column in the implicit table.

When the optional <code>WITH ORDINALITY </code>clause is added to the function call, an additional column of type <code>bigint </code>will be appended to the function's result column(s). This column numbers the rows of the function's result set, starting from 1. By default, this column is named <code>ordinality </code>.

An alias can be provided in the same way as for a table. If an alias is written, a column alias list can also be written to provide substitute names for one or more attributes of the function's composite return type, including the ordinality column if present.

Multiple function calls can be combined into a single <code>FROM </code>-clause item by surrounding them with <code>ROWS FROM( ... ) </code>. The output of such an item is the concatenation of the first row from each function, then the second row from each function, etc. If some of the functions produce fewer rows than others, null values are substituted for the missing data, so that the total number of rows returned is always the same as for the function that produced the most rows.

If the function has been defined as returning the <code>record </code>data type, then an alias or the key word <code>AS </code>must be present, followed by a column definition list in the form <code>( <code>column_name </code> <code>data_type </code> [, ... ]) </code>. The column definition list must match the actual number and types of columns returned by the function.

When using the <code>ROWS FROM( ... ) </code>syntax, if one of the functions requires a column definition list, it's preferred to put the column definition list after the function call inside <code>ROWS FROM( ... ) </code>. A column definition list can be placed after the <code>ROWS FROM( ... ) </code>construct only if there's just a single function and no <code>WITH ORDINALITY </code>clause.

To use <code>ORDINALITY </code>together with a column definition list, you must use the <code>ROWS FROM( ... ) </code>syntax and put the column definition list inside <code>ROWS FROM( ... ) </code>.

<ul><li><code>[ INNER ] JOIN </code> </li><li><code>LEFT [ OUTER ] JOIN </code> </li><li><code>RIGHT [ OUTER ] JOIN </code> </li><li><code>FULL [ OUTER ] JOIN </code> </li> </ul>

For the <code>INNER </code>and <code>OUTER </code>join types, a join condition must be specified, namely exactly one of <code>ON <code>join_condition </code> </code>, <code>USING (<code>join_column </code> [, ...]) </code>, or <code>NATURAL </code>. See below for the meaning.

A <code>JOIN </code>clause combines two <code>FROM </code>items, which for convenience we will refer to as “tables ” , though in reality they can be any type of <code>FROM </code>item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, <code>JOIN </code>s nest left-to-right. In any case <code>JOIN </code>binds more tightly than the commas separating <code>FROM </code>-list items. All the <code>JOIN </code>options are just a notational convenience, since they do nothing you couldn't do with plain <code>FROM </code>and <code>WHERE </code>.

<code>LEFT OUTER JOIN </code>returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition. This left-hand row is extended to the full width of the joined table by inserting null values for the right-hand columns. Note that only the <code>JOIN </code>clause's own condition is considered while deciding which rows have matches. Outer conditions are applied afterwards.

Conversely, <code>RIGHT OUTER JOIN </code>returns all the joined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). This is just a notational convenience, since you could convert it to a <code>LEFT OUTER JOIN </code>by switching the left and right tables.

<code>FULL OUTER JOIN </code>returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).

<code>join_condition </code> is an expression resulting in a value of type <code>boolean </code>(similar to a <code>WHERE </code>clause) that specifies which rows in a join are considered to match.

A clause of the form <code>USING ( a, b, ... ) </code>is shorthand for <code>ON left_table.a = right_table.a AND left_table.b = right_table.b ... </code>. Also, <code>USING </code>implies that only one of each pair of equivalent columns will be included in the join output, not both.

If a <code>join_using_alias </code> name is specified, it provides a table alias for the join columns. Only the join columns listed in the <code>USING </code>clause are addressable by this name. Unlike a regular <code>alias </code> , this does not hide the names of the joined tables from the rest of the query. Also unlike a regular <code>alias </code> , you cannot write a column alias list — the output names of the join columns are the same as they appear in the <code>USING </code>list.

<code>NATURAL </code>is shorthand for a <code>USING </code>list that mentions all columns in the two tables that have matching names. If there are no common column names, <code>NATURAL </code>is equivalent to <code>ON TRUE </code>.

<code>CROSS JOIN </code>is equivalent to <code>INNER JOIN ON (TRUE) </code>, that is, no rows are removed by qualification. They produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of <code>FROM </code>, but restricted by the join condition (if any).

The <code>LATERAL </code>key word can precede a sub-<code>SELECT </code><code>FROM </code>item. This allows the sub-<code>SELECT </code>to refer to columns of <code>FROM </code>items that appear before it in the <code>FROM </code>list. (Without <code>LATERAL </code>, each sub-<code>SELECT </code>is evaluated independently and so cannot cross-reference any other <code>FROM </code>item.)

<code>LATERAL </code>can also precede a function-call <code>FROM </code>item, but in this case it is a noise word, because the function expression can refer to earlier <code>FROM </code>items in any case.

A <code>LATERAL </code>item can appear at top level in the <code>FROM </code>list, or within a <code>JOIN </code>tree. In the latter case it can also refer to any items that are on the left-hand side of a <code>JOIN </code>that it is on the right-hand side of.

When a <code>FROM </code>item contains <code>LATERAL </code>cross-references, evaluation proceeds as follows: for each row of the <code>FROM </code>item providing the cross-referenced column(s), or set of rows of multiple <code>FROM </code>items providing the columns, the <code>LATERAL </code>item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

The column source table(s) must be <code>INNER </code>or <code>LEFT </code>joined to the <code>LATERAL </code>item, else there would not be a well-defined set of rows from which to compute each set of rows for the <code>LATERAL </code>item. Thus, although a construct such as <code><code>X </code> RIGHT JOIN LATERAL <code>Y </code> </code>is syntactically valid, it is not actually allowed for <code>Y </code> to reference <code>X </code> .

The optional <code>WHERE </code>clause has the general form

where <code>condition </code> is any expression that evaluates to a result of type <code>boolean </code>. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

The optional <code>GROUP BY </code>clause has the general form

<code>GROUP BY </code>will condense into a single row all selected rows that share the same values for the grouped expressions. An <code>expression </code> used inside a <code>grouping_element </code> can be an input column name, or the name or ordinal number of an output column (<code>SELECT </code>list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a <code>GROUP BY </code>name will be interpreted as an input-column name rather than an output column name.

If any of <code>GROUPING SETS </code>, <code>ROLLUP </code>or <code>CUBE </code>are present as grouping elements, then the <code>GROUP BY </code>clause as a whole defines some number of independent <code>grouping sets </code> . The effect of this is equivalent to constructing a <code>UNION ALL </code>between subqueries with the individual grouping sets as their <code>GROUP BY </code>clauses. The optional <code>DISTINCT </code>clause removes duplicate sets before processing; it does not transform the <code>UNION ALL </code>into a <code>UNION DISTINCT </code>. For further details on the handling of grouping sets see <a href="https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-GROUPING-SETS">Section 7.2.4 </a>.

Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group. (If there are aggregate functions but no <code>GROUP BY </code>clause, the query is treated as having a single group comprising all the selected rows.) The set of rows fed to each aggregate function can be further filtered by attaching a <code>FILTER </code>clause to the aggregate function call; see <a href="https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-AGGREGATES">Section 4.2.7 </a>for more information. When a <code>FILTER </code>clause is present, only those rows matching it are included in the input to that aggregate function.

When <code>GROUP BY </code>is present, or any aggregate functions are present, it is not valid for the <code>SELECT </code>list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

Keep in mind that all aggregate functions are evaluated before evaluating any “scalar ” expressions in the <code>HAVING </code>clause or <code>SELECT </code>list. This means that, for example, a <code>CASE </code>expression cannot be used to skip evaluation of an aggregate function; see <a href="https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-EXPRESS-EVAL">Section 4.2.14 </a>.

Currently, <code>FOR NO KEY UPDATE </code>, <code>FOR UPDATE </code>, <code>FOR SHARE </code>and <code>FOR KEY SHARE </code>cannot be specified with <code>GROUP BY </code>.

The optional <code>HAVING </code>clause has the general form

where <code>condition </code> is the same as specified for the <code>WHERE </code>clause.

<code>HAVING </code>eliminates group rows that do not satisfy the condition. <code>HAVING </code>is different from <code>WHERE </code>: <code>WHERE </code>filters individual rows before the application of <code>GROUP BY </code>, while <code>HAVING </code>filters group rows created by <code>GROUP BY </code>. Each column referenced in <code>condition </code> must unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns.

The presence of <code>HAVING </code>turns a query into a grouped query even if there is no <code>GROUP BY </code>clause. This is the same as what happens when the query contains aggregate functions but no <code>GROUP BY </code>clause. All the selected rows are considered to form a single group, and the <code>SELECT </code>list and <code>HAVING </code>clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the <code>HAVING </code>condition is true, zero rows if it is not true.

Currently, <code>FOR NO KEY UPDATE </code>, <code>FOR UPDATE </code>, <code>FOR SHARE </code>and <code>FOR KEY SHARE </code>cannot be specified with <code>HAVING </code>.

The optional <code>WINDOW </code>clause has the general form

<code>window_definition </code>

where <code>window_name </code> is a name that can be referenced from <code>OVER </code>clauses or subsequent window definitions, and <code>window_definition </code> is

<code>existing_window_name </code>

If an <code>existing_window_name </code> is specified it must refer to an earlier entry in the <code>WINDOW </code>list; the new window copies its partitioning clause from that entry, as well as its ordering clause if any. In this case the new window cannot specify its own <code>PARTITION BY </code>clause, and it can specify <code>ORDER BY </code>only if the copied window does not have one. The new window always uses its own frame clause; the copied window must not specify a frame clause.

The elements of the <code>PARTITION BY </code>list are interpreted in much the same fashion as elements of a <a href="https://www.postgresql.org/docs/15/sql-select.html#SQL-GROUPBY"><code>GROUP BY </code> </a>clause, except that they are always simple expressions and never the name or number of an output column. Another difference is that these expressions can contain aggregate function calls, which are not allowed in a regular <code>GROUP BY </code>clause. They are allowed here because windowing occurs after grouping and aggregation.

Similarly, the elements of the <code>ORDER BY </code>list are interpreted in much the same fashion as elements of a statement-level <a href="https://www.postgresql.org/docs/15/sql-select.html#SQL-ORDERBY"><code>ORDER BY </code> </a>clause, except that the expressions are always taken as simple expressions and never the name or number of an output column.

The optional <code>frame_clause </code> defines the window frame for window functions that depend on the frame (not all do). The window frame is a set of related rows for each row of the query (called the current row ). The <code>frame_clause </code> can be one of

where <code>frame_start </code> and <code>frame_end </code> can be one of

and <code>frame_exclusion </code> can be one of

If <code>frame_end </code> is omitted it defaults to <code>CURRENT ROW </code>. Restrictions are that <code>frame_start </code> cannot be <code>UNBOUNDED FOLLOWING </code>, <code>frame_end </code> cannot be <code>UNBOUNDED PRECEDING </code>, and the <code>frame_end </code> choice cannot appear earlier in the above list of <code>frame_start </code> and <code>frame_end </code> options than the <code>frame_start </code> choice does — for example <code>RANGE BETWEEN CURRENT ROW AND <code>offset </code> PRECEDING </code>is not allowed.

The default framing option is <code>RANGE UNBOUNDED PRECEDING </code>, which is the same as <code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW </code>; it sets the frame to be all rows from the partition start up through the current row's last peer (a row that the window's <code>ORDER BY </code>clause considers equivalent to the current row; all rows are peers if there is no <code>ORDER BY </code>). In general, <code>UNBOUNDED PRECEDING </code>means that the frame starts with the first row of the partition, and similarly <code>UNBOUNDED FOLLOWING </code>means that the frame ends with the last row of the partition, regardless of <code>RANGE </code>, <code>ROWS </code>or <code>GROUPS </code>mode. In <code>ROWS </code>mode, <code>CURRENT ROW </code>means that the frame starts or ends with the current row; but in <code>RANGE </code>or <code>GROUPS </code>mode it means that the frame starts or ends with the current row's first or last peer in the <code>ORDER BY </code>ordering. The <code>offset </code> <code>PRECEDING </code>and <code>offset </code> <code>FOLLOWING </code>options vary in meaning depending on the frame mode. In <code>ROWS </code>mode, the <code>offset </code> is an integer indicating that the frame starts or ends that many rows before or after the current row. In <code>GROUPS </code>mode, the <code>offset </code> is an integer indicating that the frame starts or ends that many peer groups before or after the current row's peer group, where a peer group is a group of rows that are equivalent according to the window's <code>ORDER BY </code>clause. In <code>RANGE </code>mode, use of an <code>offset </code> option requires that there be exactly one <code>ORDER BY </code>column in the window definition. Then the frame contains those rows whose ordering column value is no more than <code>offset </code> less than (for <code>PRECEDING </code>) or more than (for <code>FOLLOWING </code>) the current row's ordering column value. In these cases the data type of the <code>offset </code> expression depends on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column, but for datetime ordering columns it is an <code>interval </code>. In all these cases, the value of the <code>offset </code> must be non-null and non-negative. Also, while the <code>offset </code> does not have to be a simple constant, it cannot contain variables, aggregate functions, or window functions.

The <code>frame_exclusion </code> option allows rows around the current row to be excluded from the frame, even if they would be included according to the frame start and frame end options. <code>EXCLUDE CURRENT ROW </code>excludes the current row from the frame. <code>EXCLUDE GROUP </code>excludes the current row and its ordering peers from the frame. <code>EXCLUDE TIES </code>excludes any peers of the current row from the frame, but not the current row itself. <code>EXCLUDE NO OTHERS </code>simply specifies explicitly the default behavior of not excluding the current row or its peers.

Beware that the <code>ROWS </code>mode can produce unpredictable results if the <code>ORDER BY </code>ordering does not order the rows uniquely. The <code>RANGE </code>and <code>GROUPS </code>modes are designed to ensure that rows that are peers in the <code>ORDER BY </code>ordering are treated alike: all rows of a given peer group will be in the frame or excluded from it.

The purpose of a <code>WINDOW </code>clause is to specify the behavior of window functions appearing in the query's <a href="https://www.postgresql.org/docs/15/sql-select.html#SQL-SELECT-LIST"><code>SELECT </code>list </a>or <a href="https://www.postgresql.org/docs/15/sql-select.html#SQL-ORDERBY"><code>ORDER BY </code> </a>clause. These functions can reference the <code>WINDOW </code>clause entries by name in their <code>OVER </code>clauses. A <code>WINDOW </code>clause entry does not have to be referenced anywhere, however; if it is not used in the query it is simply ignored. It is possible to use window functions without any <code>WINDOW </code>clause at all, since a window function call can specify its window definition directly in its <code>OVER </code>clause. However, the <code>WINDOW </code>clause saves typing when the same window definition is needed for more than one window function.

Currently, <code>FOR NO KEY UPDATE </code>, <code>FOR UPDATE </code>, <code>FOR SHARE </code>and <code>FOR KEY SHARE </code>cannot be specified with <code>WINDOW </code>.

Window functions are described in detail in <a href="https://www.postgresql.org/docs/15/tutorial-window.html">Section 3.5 </a>, <a href="https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">Section 4.2.8 </a>, and <a href="https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-WINDOW">Section 7.2.5 </a>.

The <code>SELECT </code>list (between the key words <code>SELECT </code>and <code>FROM </code>) specifies expressions that form the output rows of the <code>SELECT </code>statement. The expressions can (and usually do) refer to columns computed in the <code>FROM </code>clause.

Just as in a table, every output column of a <code>SELECT </code>has a name. In a simple <code>SELECT </code>this name is just used to label the column for display, but when the <code>SELECT </code>is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query. To specify the name to use for an output column, write <code>AS </code><code>output_name </code> after the column's expression. (You can omit <code>AS </code>, but only if the desired output name does not match any PostgreSQL keyword (see <a href="https://www.postgresql.org/docs/15/sql-keywords-appendix.html">Appendix C </a>). For protection against possible future keyword additions, it is recommended that you always either write <code>AS </code>or double-quote the output name.) If you do not specify a column name, a name is chosen automatically by PostgreSQL . If the column's expression is a simple column reference then the chosen name is the same as that column's name. In more complex cases a function or type name may be used, or the system may fall back on a generated name such as <code>?column? </code>.

An output column's name can be used to refer to the column's value in <code>ORDER BY </code>and <code>GROUP BY </code>clauses, but not in the <code>WHERE </code>or <code>HAVING </code>clauses; there you must write out the expression instead.

Instead of an expression, <code>* </code>can be written in the output list as a shorthand for all the columns of the selected rows. Also, you can write <code><code>table_name </code> .* </code>as a shorthand for the columns coming from just that table. In these cases it is not possible to specify new names with <code>AS </code>; the output column names will be the same as the table columns' names.

According to the SQL standard, the expressions in the output list should be computed before applying <code>DISTINCT </code>, <code>ORDER BY </code>, or <code>LIMIT </code>. This is obviously necessary when using <code>DISTINCT </code>, since otherwise it's not clear what values are being made distinct. However, in many cases it is convenient if output expressions are computed after <code>ORDER BY </code>and <code>LIMIT </code>; particularly if the output list contains any volatile or expensive functions. With that behavior, the order of function evaluations is more intuitive and there will not be evaluations corresponding to rows that never appear in the output. PostgreSQL will effectively evaluate output expressions after sorting and limiting, so long as those expressions are not referenced in <code>DISTINCT </code>, <code>ORDER BY </code>or <code>GROUP BY </code>. (As a counterexample, <code>SELECT f(x) FROM tab ORDER BY 1 </code>clearly must evaluate <code>f(x) </code>before sorting.) Output expressions that contain set-returning functions are effectively evaluated after sorting and before limiting, so that <code>LIMIT </code>will act to cut off the output from a set-returning function.

PostgreSQL versions before 9.6 did not provide any guarantees about the timing of evaluation of output expressions versus sorting and limiting; it depended on the form of the chosen query plan.

If <code>SELECT DISTINCT </code>is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). <code>SELECT ALL </code>specifies the opposite: all rows are kept; that is the default.

<code>SELECT DISTINCT ON ( <code>expression </code> [, ...] ) </code>keeps only the first row of each set of rows where the given expressions evaluate to equal. The <code>DISTINCT ON </code>expressions are interpreted using the same rules as for <code>ORDER BY </code>(see above). Note that the “first row ” of each set is unpredictable unless <code>ORDER BY </code>is used to ensure that the desired row appears first. For example:

retrieves the most recent weather report for each location. But if we had not used <code>ORDER BY </code>to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.

The <code>DISTINCT ON </code>expression(s) must match the leftmost <code>ORDER BY </code>expression(s). The <code>ORDER BY </code>clause will normally contain additional expression(s) that determine the desired precedence of rows within each <code>DISTINCT ON </code>group.

Currently, <code>FOR NO KEY UPDATE </code>, <code>FOR UPDATE </code>, <code>FOR SHARE </code>and <code>FOR KEY SHARE </code>cannot be specified with <code>DISTINCT </code>.

The <code>UNION </code>clause has this general form:

PostgreSQL - Behavior of SELECT FOR UPDATE

SELECT FOR UPDATE locks all joined tables by default

Similar ideas