Mastering PostgreSQL 12 - Third Edition

Understanding the subtle difference between ROWS and RANGE

So far, you have seen sliding windows using OVER ... ROWS. However, there is more. Let's take a look at the SQL specification taken directly from the PostgreSQL documentation:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

There is more than just ROWS. In real life, we have seen that many people are struggling to understand the difference between RANGE and ROWS. In many cases, the result is the same, which adds even more to the confusion. To understand the problem, let's first create some simple data:

test=# SELECT *, x / 3 AS y FROM generate_series(1, 15) AS x;  x | y ----+---  1 | 0  2 | 0 3 | ...