CODEWITHSUNDEEP
sqlpostgresqlcastingtype-conversionpostgresql-9.1
spyd3rr
spyd3rr

Reputation: 3075

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.

Upvotes: 270

Views: 655639

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can convert INT to VARCHAR as shown below. *The doc explains type conversion in detail:

postgres=# SELECT 123::VARCHAR;
 varchar
---------
 123
(1 row)

Or:

postgres=# SELECT CAST(123 AS VARCHAR);
 varchar
---------
 123
(1 row)

In addition, you can use pg_typeof() to check the type of 123 as shown below:

postgres=# SELECT pg_typeof(123);
 pg_typeof
-----------
 integer
(1 row)

And, if you set VARCHAR(2) to 123, the result is 12 as shown below:

postgres=# SELECT 123::VARCHAR(2);
 varchar
---------
 12
(1 row)

But, the type of 12 is VARCHAR(CHARACTER VARYING) rather than VARCHAR(2)(CHARACTER VARYING(2)) as shown below:

postgres=# SELECT pg_typeof(123::VARCHAR(2));
     pg_typeof
-------------------
 character varying
(1 row)

Upvotes: 6

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

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

Upvotes: 11

Bohemian
Bohemian

Reputation: 425033

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

Upvotes: 277

djgupta
djgupta

Reputation: 500

You could do this:

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

Upvotes: 40

Brugolo
Brugolo

Reputation: 5105

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'

Upvotes: 210

Related Questions