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

Question

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!

Answer 1

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