7

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

Improve this question
1
  • that's messed up, how many now() functions does database1 have and what are their return-types? what casts are defined on timestamptz? Commented Sep 20, 2020 at 2:04

2 Answers 2

Reset to default
1

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
4
  • 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 ********** " Commented Apr 8, 2020 at 21:37
  • Sorry, in 9.6 it must be spelled 'procedure'. 'function' wasn't added as a synonym for that until 11. Commented Apr 8, 2020 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. Commented Apr 12, 2020 at 4:45
  • change the select to select $1::date + $2, this changes the rreturn type so you'll need to drop the operatot and the function and redefine both. Commented Sep 20, 2020 at 2:08
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.