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

Question

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.

What will you do if your string has leading zeros? – thisfeller Dec 10 '12 at 21:35 — thisfeller, Dec 10 '12 at 21:35

score 250 · Accepted Answer · edited Dec 16 '20 at 07:56

250

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

edited Dec 16 '20 at 07:56

Smart Manoj

answered Dec 10 '12 at 21:32

Bohemian

55

Ah. I actually needed it the other way around (myint converted to varchar) but your answer was enough to guide me to the right place. I just did `myint::varchar(255) = mytext` and it works. Thanks! – spyd3rr Dec 10 '12 at 21:47
6

Strictly, this does not answer the question. The example was not the problem - converting an integer to a char/string was. But thanks, @spyd3rr – Frederik Struck-Schøning Jan 24 '18 at 22:10
@fred Not so: OP's *actual* problem was unsuccessfully trying to compare numeric and text values. This answer shows how to fix that by casting the text to a number (even though OP tried casting a number to text). – Bohemian Jan 25 '18 at 06:14
9

@Bohemian The question title and text says: "How do I convert an integer to string as part of a PostgreSQL query?". Then an *example* of when this *could* be relevant. In my case, I found this thread because I actually had to convert an integer to a string - not for comparison but for using inside an aggregate function that would fail, if the argument was not a string. In which case, this was not an answer to the question, as it literally reads atm. – Frederik Struck-Schøning Jan 25 '18 at 11:26

score 194 · Answer 2 · answered Feb 10 '16 at 10:53

194

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'

answered Feb 10 '16 at 10:53

Brugolo

2

This will not work if you're working with SpEL in jpa native queries. You'll have to use cast in that case. – Raj Shah Jun 25 '19 at 07:09

djgupta · Answer 3 · 2020-05-21T08:57:30.153

36

You could do this:

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

edited May 21 '20 at 08:57

answered Aug 09 '18 at 14:35

djgupta

score 10 · Answer 4 · answered Apr 14 '21 at 06:50

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

4 Answers4