I came across with a strange issue in postgres. I have two databases create in two different periods. Both are running in the same PostrgreSql 9.6 version in the same machine. When I run the query SELECT now() + 30 in database 1 -> Its working properly. The same query I am copy paste in the second database, its giving error: operator does not exist: timestamp with time zone + integer in PostgreSql

Can some one guide me on this.

enter image description here


Maybe someone created the operator with those types in one database, but not in the other one.

create function addit(timestamptz,int) returns timestamptz immutable language sql as $$ 
    select $1+ interval '1 day'*$2 
create operator + (leftarg =timestamptz, rightarg =int, procedure=addit);
| improve this answer | |
  • Thanks @jjanes. But while running the above code in the database, I am getting the below error: " WARNING: operator attribute "function" not recognized ERROR: operator procedure must be specified ********** Error ********** " – Sajeev Apr 8 at 21:37
  • Sorry, in 9.6 it must be spelled 'procedure'. 'function' wasn't added as a synonym for that until 11. – jjanes Apr 8 at 22:08
  • the query executed very well, Thanks. Can we get the return format as date(yyyy-MM-dd). Now it is returning the result as timestamp with time zone. – Sajeev Apr 12 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.