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.
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.