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.
-
What will you do if your string has leading zeros?thisfeller– thisfeller2012-12-10 21:35:11 +00:00Commented Dec 10, 2012 at 21:35
Add a comment
|
5 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
5 Comments
spyd3rr
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!
Frederik Struck-Schøning
Strictly, this does not answer the question. The example was not the problem - converting an integer to a char/string was. But thanks, @spyd3rr
Bohemian
@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).
Frederik Struck-Schøning
@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.
Krusty the Clown
a bit unrelated, but VARCHAR(255) usually indicates a misunderstanding of text types in Postgres.
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'
1 Comment
Raj Shah
This will not work if you're working with SpEL in jpa native queries. You'll have to use cast in that case.
You could do this:
SELECT * FROM table WHERE cast(YOUR_INTEGER_VALUE as varchar) = 'string of numbers'
Comments
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
Comments
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)
answered Dec 27, 2023 at 17:34
Super Kai - Kazuya Ito
42.7k23 gold badges258 silver badges257 bronze badges