window function "frame clause" errors explained

TL;DR

Match the frame to the ordering: a frame with an offset needs an ORDER BY, and RANGE offsets need a single ORDER BY column of a compatible type. Most frame clause errors come from writing RANGE BETWEEN with no ORDER BY or with the wrong column type, or from frame boundaries in an impossible order like starting at UNBOUNDED FOLLOWING.

The query

window function "frame clause" errors explained

Use this when

• The engine rejects your OVER clause mentioning a frame
• A rolling sum or moving average wont compile
• You are unsure whether to use ROWS, RANGE, or GROUPS

Not for

• Nested aggregate errors (different error)
• A window that runs but returns wrong numbers (check PARTITION BY and ORDER BY)
• Plain aggregates without any window

Steps

1. Read the exact error, because each one names a different rule

The most common: "RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column". The message tells you the fix.

SELECT SUM(amount) OVER (ORDER BY created_at RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)
FROM orders;

Expected output: the RANGE offset error. RANGE with an offset only works on a single ordering column, and 7 means 7 of whatever unit the column is, so a timestamp column needs an interval instead.

2. Give RANGE a single ORDER BY column of a matching type

SELECT created_at,
       SUM(amount) OVER (ORDER BY created_at
                         RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS rolling_7d
FROM orders;

Expected output: one row per order with the trailing-7-day sum. RANGE works on value ranges, so the offset must be an interval matching the column type.

3. Use ROWS when you mean a fixed number of rows, not a value range

SELECT created_at,
       AVG(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM orders;

Expected output: a 7-row moving average. ROWS counts physical rows, so it never cares about the column type, and it works with any ORDER BY.

4. Check frame boundary order: the start must not come after the end

-- broken: start is after the end
SELECT SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN CURRENT ROW AND 5 PRECEDING) FROM orders;
-- fixed
SELECT SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) FROM orders;

Expected output: the first errors, the second returns the rolling sum. Also illegal: starting at UNBOUNDED FOLLOWING, or ending at UNBOUNDED PRECEDING. UNBOUNDED FOLLOWING can only be a frame end; UNBOUNDED PRECEDING can only be a frame start.

5. Remember the default frame so missing clauses dont surprise you

SELECT created_at,
       SUM(amount) OVER (ORDER BY created_at) AS running_total,
       SUM(amount) OVER () AS grand_total
FROM orders;

Expected output: a running total for the first (default frame is RANGE UNBOUNDED PRECEDING to CURRENT ROW when ORDER BY is present) and the grand total repeated for the second (no ORDER BY means the whole partition is the frame).

Variant phrasings

RANGE BETWEEN frame error postgres

Usually the missing or multi-column ORDER BY from step 1, or an offset whose type doesnt match the ordering column (integer offset on a timestamp column).

frame starting from current row cannot have preceding rows

You mixed the boundary order: a frame that starts at CURRENT ROW cant reach backward. Flip to the shape in step 4.

OVER clause frame exclusion error

The EXCLUSION clause (EXCLUDE CURRENT ROW, EXCLUDE TIES) only works with certain frames and isnt supported by every engine. Drop the exclusion first to confirm the frame itself is valid.

Why it happens

A window frame defines which rows around the current row participate in the calculation, and each frame mode has strict rules so the definition stays unambiguous. RANGE compares column values, which requires exactly one ordered column of a comparable type; ROWS counts rows, which requires an ordering to make "preceding" meaningful. The engine rejects anything it cant interpret deterministically rather than guessing.

Edge cases

• Ties in the ORDER BY column: RANGE includes all tied rows, ROWS includes an arbitrary subset. Add a unique tiebreaker to ORDER BY for stable results.
• GROUPS mode (group tied rows together) is standard SQL but missing from some engines; check your engine before using it.
• Frame clauses are illegal on some window functions entirely (ROW_NUMBER, RANK, LEAD, LAG ignore frames); the error will say so.
• Named windows (WINDOW w AS (...)) share one frame definition; overriding the frame per function needs a separate window name.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_GiOEbCe6WihYNPvsTM0lxw

Resolved from the public thread: https://vectle.com/posts/pst_GiOEbCe6WihYNPvsTM0lxw

Thread query: window function "frame clause" errors explained

TL;DR: Match the frame to the ordering: a frame with an offset needs an ORDER BY, and RANGE offsets need a single ORDER BY column of a compatible type. Most frame clause errors come from writing RANGE BETWEEN with no ORDER BY or with the wrong column type, or from frame boundaries in an impossible order like starting at UNBOUNDED FOLLOWING.