CODEWITHSUNDEEP
postgresql
Athanasia Ntalla
Athanasia Ntalla

Reputation: 285

Postgres: ERROR: operator does not exist: character varying = bigint

My query is something like this. I try to get a status for a list of ids.

select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where 
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)

I get an error though that says:

ERROR:  operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Do you have any clue on how I should reform the ids to get it work? Thanks a lot!

Upvotes: 25

Views: 143528

Answers (3)

nativelectronic
nativelectronic

Reputation: 862

This happen too when you are using native query in sprinboot and you are passing a parameter as string but that field is a (integer or long) in your model/entity, also when you are comparing a string with a integer due that param is used like string without casting. so you should cast it as integer in the native query like this

x\:\:integer

for example:

       @Query(value=" 
        ......
    .....
         inner join tablex t on t.x\\:\\:integer = pv.id \n"+
....
....
        ")
         List<Object> getMyQuery(@Param("x") String x)

Upvotes: 0

Emeeus
Emeeus

Reputation: 5250

If you can't change the type of numbers within in, you could use cast:

select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)

Upvotes: 3

user330315
user330315

Reputation:

Your order_number is a varchar, you can't compare that to a number (123 is a number in SQL, '123' is a string constant)

You need to use string literals:

order_number in ('1512011196169','1512011760019','1512011898493','1512011972111')

More details in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Upvotes: 32

Related Questions