Postgres, query error: ERROR: operator does not exist: character varying = bigint?

Question

I am trying to run this query:

select *
from my_table
where column_one=${myValue}

I get the following error in Datagrip:

[42883] ERROR: operator does not exist: character varying = bigint Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Now, I have found this question, and I can fix the error by putting a string like this:

select *
from my_table
where column_one='123'

What I need is a way to pass in the '123' as a parameter. I usually do this ${myValue} and it works, but I am not sure how to keep my variable there as an input so I can run dynamic queries in code and let Postgres understand I want to pass in a string and not a number.

Any suggestions?

Here's a screenshot of how I am putting the parameter value in DataGrip...:

Ok, so, I just tried to put quotes in the data grip parameters input field for myValue @thirumal's answer things work. I didn't know I have to quote the value for it to work. This is what it looks like:

but how are you binding the myValue parameter? can you show us how you execute the query? — Marc Sances
– Marc Sances, Commented Aug 20, 2020 at 21:31
Which programming language and database interface are you using? As Marc already mentioned: the code that runs that statement and passes the parameter is important — user330315
– user330315, Commented Aug 21, 2020 at 5:47
I am using Datagrip from JetBrains. I literally pasted in the query as shown and then set the value in the parameters pop up. ( have attached a screenshot of that) — zumzum
– zumzum, Commented Aug 21, 2020 at 18:24

Thirumal · Accepted Answer · 2020-08-21 05:37:57Z

1

Type cast ${myValue} using SQL Standard,

cast(${myValue} AS varchar)

or using Postgres Syntax:

${myValue}::varchar

answered Aug 21, 2020 at 5:37

Thirumal

9,94616 gold badges71 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zumzum Over a year ago

I have tried that, but, if the string I pass in to myValue has a "+" in it the query runs but it will not find the row. if column_one has value "+123", and then I pass in "+123", the query won't match the row, so, empty result. If I modify column_one to be "123" and then run the query with myValue "123" then things work. Why is the "+" breaking the query? (I tried both, same problem with both)

Collectives™ on Stack Overflow

Postgres, query error: ERROR: operator does not exist: character varying = bigint?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related