Database Reference
In-Depth Information
In PostgreSQL, any aggregate function you create can be used as a window function.
Other databases tend to limit window functions to using built-in aggregates such as
AVG
,
SUM
,
MIN
, and
MAX
.
Common Table Expressions
Essentially, common table expressions (CTEs) allow you to define a query that can be
reused in a larger query. PostgreSQL has supported this feature since version 8.4 and
expanded the feature in version 9.1 with the introduction of writable CTEs. CTEs act
as temporary tables defined within the scope of the statement; they're gone once the
enclosing statement has finished execution.
There are three ways to use CTEs:
Basic CTE
This is your plain-vanilla CTE, used to make your SQL more readable or to en‐
courage the planner to materialize a costly intermediate result for better perfor‐
mance.
Writable CTE
This is an extension of the basic CTE with
UPDATE
,
INSERT
, and
DELETE
commands.
A common final step in the CTE is to return changed rows.
Recursive CTE
This puts an entirely new whirl on standard CTE. The rows returned by a recursive
CTE vary during the execution of the query.
PostgreSQL allows you to have a CTE that is both writable and recursive.
Basic CTEs
The basic CTE looks like
Example 7-28
. The
WITH
keyword introduces the CTE.
Example 7-28. Basic CTE
WITH
cte
AS
(
SELECT
tract_id
,
substring
(
tract_id
,
1
,
5
)
As
county_code
,
COUNT
(
*
)
OVER
(
PARTITION
BY
substring
(
tract_id
,
1
,
5
))
As
cnt_tracts
FROM
census
.
lu_tracts
)
SELECT
MAX
(
tract_id
)
As
last_tract
,
county_code
,
cnt_tracts
FROM
cte
WHERE
cnt_tracts
>
100
GROUP
BY
county_code
,
cnt_tracts
;
