How do I convert an integer to string as part of a PostgreSQL query?

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.

4 Answers

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 

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' 

You could do this:

SELECT * FROM table WHERE cast(YOUR_INTEGER_VALUE as varchar) = 'string of numbers' 

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