The operator does not exist: interval> integer

Question

I have a query that works on Postgresql 7.4, but not on Postgresql 8.3 with the 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. 

The modified column is timestamp and timeout is integer .

Are there any settings that I need to change on the server?

I am installing the client application on a new server (ubuntu), so I cannot change requests in the application.

Answer 1

 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; 

must do it.

Answer 2

There are many changes between 7.4 and 8.3. Some of the most radical were the removal of some automatic throws.

I assume the timeout is in seconds? If so, you can change the query:

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

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)