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

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.

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

jjanes · Accepted Answer · 2020-04-08 23:03:29Z

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

edited Apr 8, 2020 at 23:03

answered Apr 8, 2020 at 21:20

jjanes

42.6k3 gold badges44 silver badges54 bronze badges

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
– Sajeev

2020-04-08 21:37:45 +00:00
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.

jjanes
– jjanes

2020-04-08 22:08:24 +00:00
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.

Sajeev
– Sajeev

2020-04-12 04:45:20 +00:00
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.

Jasen
– Jasen

2020-09-20 02:08:06 +00:00
Commented Sep 20, 2020 at 2:08

Add a comment |

Sajeev · Accepted Answer · 2021-01-13 12:09:42Z

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.

answered Jan 13, 2021 at 12:09

Sajeev

1711 gold badge1 silver badge4 bronze badges

Add a comment |

Stack Exchange Network

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

2 Answers 2

Your Answer

Hot Network Questions

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

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions