operator does not exist: timestamp with time zone + integer in PostgreSql

Posted on

Question :

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

Answer :

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);

I have added the below query to solve the issue.

CREATE OPERATOR + ( PROCEDURE = adddays,LEFTARG = TIMESTAMPTZ, RIGHTARG = NUMERIC,COMMUTATOR = +); CREATE OPERATOR – ( PROCEDURE = adddays,LEFTARG = TIMESTAMPTZ, RIGHTARG = NUMERIC,COMMUTATOR = -);

Note: When ever I had restored the database, I used to get this issue.

Leave a Reply

Your email address will not be published. Required fields are marked *