Greetings, I'm doing an SQL query. The column to which I apply the WHERE condition is of type character varying, but the data with which I complete the condition is an integer.
SELECT * FROM tablaCodigo WHERE codigo = 123
Getting the following message:
No operator matches the given name and argument type(s). You might need to add explicit type casts.
I know I have to do a cast on 123 to be taken as character varying. What is the correct way to do it? thanks
When you write
SELECT * FROM tablaCodigo WHERE codigo = 123
What you are doing is not clear. It can be interpreted in two ways:
to_number(codigo) = 123
or
codigo = '123'
the first case would make them equal '0123' and '123' and '123.0'. That is why you have to be explicit. Other ways of writing it are:
SELECT * FROM tablaCodigo WHERE codigo::integer = 123
or
SELECT * FROM tablaCodigo WHERE codigo = 123::text
Just add it like that
SELECT * FROM tablaCodigo WHERE codigo = '123'
If you want to do a formatting of your value, you could use < a href="https://www.techonthenet.com/postgresql/functions/to_char.php"> to_char () that receives two parameters the value int and one mascara format (for example for 3 characters without leading zeros '999')
SELECT * FROM tablaCodigo WHERE codigo = to_char(123,'999');
/* o cast directamente */
SELECT * FROM tablaCodigo WHERE codigo = 123::text
Taking into consideration sending to the desired query always the type of value that you specify in the table of your database, if it is a chain sending it a string and not an Integer
SELECT * FROM tablaCodigo WHERE codigo = '123'