Re: Behavior of negative OFFSET

On Mon, Nov 7, 2011 at 3:47 PM, Robert James <srobertjames@gmail.com> wrote:
> I've been using a query on Postgres 8.4 with a negative OFFSET, which
> works fine:
>
>   SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name
> ASC LIMIT 15 OFFSET -15
>
> When I run the same query on Postgres 9.1, I get an error:
>   ERROR: OFFSET must not be negative
>
>
> Question:
> 1. Was this changed in a version of Postgres? Is this configurable?
> 2. How do I get the original behavior of negative offsets?

the original behavior was undefined. to kinda sorta get it,
create function oldoffset(int) returns int as
$$
  select case when $1 < 0 then 0 else $1 end;
$$ language sql immutable;

select v from generate_series(1,15) v limit 15 offset oldoffset(-15);

merlin