Operator does not exist: interval > integer

Question

I have a query that works on Postgresql 7.4 but not on Postgresql 8.3 with same database.

Query:

SELECT * FROM login_session WHERE (now()-modified) > timeout;

Gets the following error:

ERROR:  operator does not exist: interval > integer
LINE 1: ...ELECT * FROM login_session WHERE (now()-modified) > timeout ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Column modified is a timestamp and timeout is an integer.

Is there some settings I need to change on server?

I'm installing an application for a client on new server (ubuntu) so I can't change queries in the application.

Answer 1

There are a lot of changes between 7.4 and 8.3. Some of the most drastic were the removal of some automatic casts.

I suppose the "timeout" is in seconds? If so you could change the query to:

SELECT
    *
FROM
    login_session
WHERE
    (CURRENT_TIMESTAMP - modified) > (timeout * '1 sec'::interval);

Answer 2

create or replace function int2interval (x integer) returns interval as $$ select $1*'1 sec'::interval $$ language sql;
create cast (integer as interval) with function int2interval (integer) as implicit;

ought to do it.