How do I convert an integer to string as part of a PostgreSQL query?
PostgresqlPostgresql 9.1Postgresql Problem Overview
How do I convert an integer to string as part of a PostgreSQL query?
So, for example, I need:
SELECT * FROM table WHERE <some integer> = 'string of numbers'
where <some integer> can be anywhere from 1 to 15 digits long.
Postgresql Solutions
Solution 1 - Postgresql
Because the number can be up to 15 digits, you'll need to cast to an 64 bit (8-byte) integer. Try this:
SELECT * FROM table
WHERE myint = mytext::int8
The :: cast operator is historical but convenient. Postgres also conforms to the SQL standard syntax
myint = cast ( mytext as int8)
If you have literal text you want to compare with an int, cast the int to text:
SELECT * FROM table
WHERE myint::varchar(255) = mytext
Solution 2 - Postgresql
You can cast an integer to a string in this way
intval::text
and so in your case
SELECT * FROM table WHERE <some integer>::text = 'string of numbers'
Solution 3 - Postgresql
You could do this:
SELECT * FROM table WHERE cast(YOUR_INTEGER_VALUE as varchar) = 'string of numbers'
Solution 4 - Postgresql
And if some integer which could be stored as string contains decimal points and you would want to compare decimal to decimal, below would help
select NULLIF('105.0', '')::decimal
SELECT * FROM table WHERE NULLIF('105.0', '')::decimal = 105.0
Below won't convert
select NULLIF('105.0', '')::int
select NULLIF('105.0', '')::integer
For this question you will just go by
select 105.3::text