Operator does not exist: interval > integer

Question

A query working on Postgresql 7.4 but not on Postgresql 8.3 with same database.

Query:

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

Get 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 timestamp and timeout is integer.

Is there some settings I need to change on server.

I'm installation an application for a client on new server (ubuntu) so I cant change query's 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.

Answer 3

CREATE OR REPLACE FUNCTION intToInterval(arg integer)
  RETURNS interval AS
$BODY$
   BEGIN      
      return CAST( arg || ' seconds' AS interval ); 
   END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE CAST (integer AS interval)
WITH FUNCTION intToInterval ( integer )
AS IMPLICIT;

(Assuming timeout is measured in seconds - change accordingly otherwise)