How to Use Frame Clause in PostgreSQL: Syntax and Examples

In PostgreSQL, the FRAME clause defines the subset of rows used by window functions within a partition. It specifies the start and end points relative to the current row using options like ROWS, RANGE, or GROUPS to control the window frame.

Syntax

The FRAME clause in PostgreSQL window functions has this general syntax:

WINDOW_FUNCTION() OVER (PARTITION BY column ORDER BY column frame_clause)

The frame_clause can be:

• ROWS BETWEEN start AND end: Counts physical rows relative to the current row.
• RANGE BETWEEN start AND end: Counts logical range of values based on ORDER BY.
• GROUPS BETWEEN start AND end: Counts groups of peers with the same ORDER BY value.

Start and end can be UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, or a number of rows before/after.

sql

SELECT
  value,
  SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_last_two
FROM numbers;

Example

This example shows how to use the FRAME clause with ROWS BETWEEN 1 PRECEDING AND CURRENT ROW to calculate a running sum of the current and previous row values.

sql

CREATE TEMP TABLE numbers (id INT, value INT);
INSERT INTO numbers VALUES
  (1, 10),
  (2, 20),
  (3, 30),
  (4, 40);

SELECT
  id,
  value,
  SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_sum
FROM numbers
ORDER BY id;

Output

 id | value | running_sum 
----+-------+-------------
  1 |    10 |          10
  2 |    20 |          30
  3 |    30 |          50
  4 |    40 |          70

Common Pitfalls

Common mistakes when using the FRAME clause include:

• Using RANGE frame with non-numeric ORDER BY columns can cause errors or unexpected results.
• Not specifying ORDER BY when using a frame clause, which is required for ROWS or RANGE.
• Confusing ROWS and RANGE: ROWS counts physical rows, RANGE counts logical value ranges.

Example of wrong usage and fix:

-- Wrong: RANGE frame without ORDER BY
SELECT SUM(value) OVER (RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) FROM numbers;

-- Right: Add ORDER BY
SELECT SUM(value) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) FROM numbers;

Quick Reference

Frame Clause	Description	Example
ROWS BETWEEN start AND end	Counts physical rows relative to current row	ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
RANGE BETWEEN start AND end	Counts logical range of ORDER BY values	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
GROUPS BETWEEN start AND end	Counts groups of peers with same ORDER BY value	GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING
start/end options	Define frame boundaries	UNBOUNDED PRECEDING, CURRENT ROW, 2 FOLLOWING

Key Takeaways

The frame clause controls which rows a window function uses around the current row.

Use ROWS for physical row counts, RANGE for value ranges, and GROUPS for peer groups.

Always include ORDER BY when using frame clauses with ROWS or RANGE.

Frame boundaries can be unbounded, current row, or a number of rows before/after.

Incorrect frame usage often causes errors or unexpected results, so test carefully.