Search code examples
sqlpostgresqlcastingtype-conversionpostgresql-9.1

Convert integer to string in PostgreSQL

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.

Solution

  • 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