112
If the <literal>GROUP BY</literal> clause is specified, the
112
If the <literal>GROUP BY</literal> clause is specified,
113
or if there are aggregate function calls, the
113
114
output is combined into groups of rows that match on one or more
114
values. If the <literal>HAVING</literal> clause is present, it
115
values, and the results of aggregate functions are computed.
116
If the <literal>HAVING</literal> clause is present, it
115
117
eliminates groups that do not satisfy the given condition. (See
116
118
<xref linkend="sql-groupby" endterm="sql-groupby-title"> and
117
119
<xref linkend="sql-having" endterm="sql-having-title"> below.)
639
641
Aggregate functions, if any are used, are computed across all rows
640
making up each group, producing a separate value for each group
641
(whereas without <literal>GROUP BY</literal>, an aggregate
642
produces a single value computed across all the selected rows).
643
The set of rows fed to the aggregate function can be further filtered by
642
making up each group, producing a separate value for each group.
643
(If there are aggregate functions but no <literal>GROUP BY</literal>
644
clause, the query is treated as having a single group comprising all
646
The set of rows fed to each aggregate function can be further filtered by
644
647
attaching a <literal>FILTER</literal> clause to the aggregate function
645
648
call; see <xref linkend="syntax-aggregates"> for more information. When
646
649
a <literal>FILTER</literal> clause is present, only those rows matching it
648
When <literal>GROUP BY</literal> is present, it is not valid for
650
are included in the input to that aggregate function.
654
When <literal>GROUP BY</literal> is present,
655
or any aggregate functions are present, it is not valid for
649
656
the <command>SELECT</command> list expressions to refer to
650
ungrouped columns except within aggregate functions or if the
657
ungrouped columns except within aggregate functions or when the
651
658
ungrouped column is functionally dependent on the grouped columns,
652
659
since there would otherwise be more than one possible value to
653
660
return for an ungrouped column. A functional dependency exists if
666
Keep in mind that all aggregate functions are evaluated before
667
evaluating any <quote>scalar</> expressions in the <literal>HAVING</>
668
clause or <literal>SELECT</> list. This means that, for example,
669
a <literal>CASE</> expression cannot be used to skip evaluation of
670
an aggregate function; see <xref linkend="syntax-express-eval">.
659
674
Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>,
660
675
<literal>FOR SHARE</> and <literal>FOR KEY SHARE</> cannot be
661
676
specified with <literal>GROUP BY</literal>.
1301
<literal>FOR UPDATE</literal> causes the rows retrieved by the
1302
<command>SELECT</command> statement to be locked as though for
1303
update. This prevents them from being modified or deleted by
1304
other transactions until the current transaction ends. That is,
1305
other transactions that attempt <command>UPDATE</command>,
1306
<command>DELETE</command>,
1307
<command>SELECT FOR UPDATE</command>,
1308
<command>SELECT FOR NO KEY UPDATE</command>,
1309
<command>SELECT FOR SHARE</command> or
1310
<command>SELECT FOR KEY SHARE</command>
1311
of these rows will be blocked until the current transaction ends.
1312
The <literal>FOR UPDATE</> lock mode
1313
is also acquired by any <command>DELETE</> on a row, and also by an
1314
<command>UPDATE</> that modifies the values on certain columns. Currently,
1315
the set of columns considered for the <command>UPDATE</> case are those that
1316
have a unique index on them that can be used in a foreign key (so partial
1317
indexes and expressional indexes are not considered), but this may change
1319
Also, if an <command>UPDATE</command>, <command>DELETE</command>,
1320
or <command>SELECT FOR UPDATE</command> from another transaction
1321
has already locked a selected row or rows, <command>SELECT FOR
1322
UPDATE</command> will wait for the other transaction to complete,
1323
and will then lock and return the updated row (or no row, if the
1324
row was deleted). Within a <literal>REPEATABLE READ</> or <literal>SERIALIZABLE</> transaction,
1325
however, an error will be thrown if a row to be locked has changed
1326
since the transaction started. For further discussion see <xref
1331
<literal>FOR NO KEY UPDATE</> behaves similarly, except that the lock
1332
acquired is weaker: this lock will not block
1333
<literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
1334
a lock on the same rows. This lock mode is also acquired by any
1335
<command>UPDATE</> that does not acquire a <literal>FOR UPDATE</> lock.
1339
<literal>FOR SHARE</literal> behaves similarly, except that it
1340
acquires a shared rather than exclusive lock on each retrieved
1341
row. A shared lock blocks other transactions from performing
1342
<command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
1343
FOR UPDATE</command> or <command>SELECT FOR NO KEY UPDATE</>
1344
on these rows, but it does not prevent them
1345
from performing <command>SELECT FOR SHARE</command> or
1346
<command>SELECT FOR KEY SHARE</command>.
1350
<literal>FOR KEY SHARE</> behaves similarly to <literal>FOR SHARE</literal>,
1351
except that the lock
1352
is weaker: <literal>SELECT FOR UPDATE</> is blocked, but
1353
not <literal>SELECT FOR NO KEY UPDATE</>. A key-shared
1354
lock blocks other transactions from performing <command>DELETE</command>
1355
or any <command>UPDATE</command> that changes the key values, but not
1356
other <command>UPDATE</>, and neither does it prevent
1357
<command>SELECT FOR NO KEY UPDATE</>, <command>SELECT FOR SHARE</>, or
1358
<command>SELECT FOR KEY SHARE</>.
1317
For more information on each row-level lock mode, refer to
1318
<xref linkend="locking-rows">.
added
removed
doc/src/sgml/ref/select.sgml
