RANGE clause error within window function

Question

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it:

SELECT date("aa0"."created_at") AS "Day of Created At",
 "aa0"."user_id" AS "User Id",
 count (*) activities_today,
 count(*) over (PARTITION BY "aa0"."user_id"
 ORDER BY date("aa0"."created_at") ASC
 RANGE BETWEEN date("aa0"."created_at" - interval '4 weeks')
 AND 
 date("aa0"."created_at" - interval '1 day')
 ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id" 
ORDER BY "Day of Created At" ASC LIMIT 1000;

However, I get the following error:

ERROR: syntax error at or near ")" Position: 459

If I use ROWS instead of RANGE it works but this is not the correct logic since it's not guaranteed that there will be a row for every day:

SELECT date("aa0"."created_at") AS "Day of Created At",
 "aa0"."user_id" AS "User Id",
 count(*) activities_today,
 count(*) over (PARTITION BY "aa0"."user_id"
 ORDER BY date("aa0"."created_at") ASC
 ROWS BETWEEN 28 PRECEEDING AND 1 PRECEEDING
 ) active_days_past_4_weeks
FROM "public"."activity_activity" AS "aa0"
GROUP BY date("aa0"."created_at"), "aa0"."user_id" 
ORDER BY "Day of Created At" ASC LIMIT 1000;

Schema:

CREATE TABLE activity_activity (
 user_id int,
 created_at timestamp
)

Version:

select version()
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

Answer 1

PostgreSQL doesn't support the RANGE clause yet. So even if you'd got the syntax right, it would've just failed with:

regress=> SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 FOLLOWING);
ERROR: RANGE FOLLOWING is only supported with UNBOUNDED
LINE 1: SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 F...

To work around this, you can do a left outer join on generate_series(first_day, last_day, INTERVAL '1' DAY) then use a ROWS based window. That way a row exists for every day. It's a whole lot less efficient but unless you feel like getting your hands dirty in PostgreSQL's source code, it's probably the only option.

• ajw0100: Interesting. So what is the difference between ROWS and RANGE? Why even have RANGE if you can't use expressions but instead need to specify a number of rows?
• Craig Ringer: Because RANGE is actually really useful - but PostgreSQL doesn't support it for non-trivial cases yet. I only used the above as a simplistic example to show that any attempt to use RANGE with correct syntax will throw an error indicating that PostgreSQL's support doesn't work yet. I admit that the error could be clearer, indicating that it's a PostgreSQL limitation not a fundamental limitation in the spec, though. See discussion: postgresql.org/message-id/…
• Craig Ringer: and postgresql.org/message-id/… . It might help to understand that window functions are part of the SQL specification, which PostgreSQL is one implementation of. Like most implementations there are places where spec support is incomplete. This is one of them - PostgreSQL recognises the syntax for window functions with range windows, but doesn't know how to execute them yet.
• ajw0100: Yea RANGE is extremely useful. I use it all the time in Oracle. Was hoping I could use it in Postgres as well. Thanks.
• Craig Ringer: @AmanMathur Sure, but that has a completely different meaning and does not really relate to this question. Unless your rows have contiguous integer IDs and you are interested in a range starting at 1, ROW and RANGE are not equivalent.