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

how to convert character to integer in postgresql
postgres cast string to integer
postgres cast to text
postgres cast string to uuid
postgresql cast bigint to integer
convert date to integer in postgresql
postgres cast to boolean
postgres cast to varchar

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.

Because the number can be up to 15 digits, you'll meed 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

PostgreSQL CAST Convert From One Data Type Into Another, 1) Cast a string to an integer example. The following statement converts a string constant to an integer: SELECT CAST ('100'  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 &lt;some integer&gt; can be

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'

Re: How to convert integer to string in functions, > writing a parameterized query (using ?) you can write "?::text" ( or Cast(? > AS type) ) to explicitly cast the unknown parameter. The "text"  Hello. you can use a ::int for converting to integer. Or better - you can alter column to integer. It will be faster and more correct. Regards. Pavel Stehule

You could do this:

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

Documentation: 9.3: Data Type Formatting Functions, (Integer Unix epochs are implicitly cast to double precision.) Table 9-21. Formatting Functions to_char(int, text), text, convert integer to string, to_char(​125, '999'). to_char (double the start of the year. (See Section 9.9.1 for more information.)  From: Szymon Guz <mabewlun(at)gmail(dot)com> To: venkat <ven(dot)tammineni(at)gmail(dot)com> Cc: Postgres General <pgsql-general(at)postgresql(dot)org>, pgsql-sql(at

Re: converting number to string in query, Re: converting number to string in query. From: Andreas Wenk <a(dot)wenk(at)​netzmeister-st-pauli(dot)de>. but this will work only if for all rows you can convert this field to integer

Re: [SQL] convert text to number or number to text, Re: [SQL] convert text to number or number to text convert that string to an integer? - it will always be a prova=> select cast(i as text),cast(t as int)from test; The example of int to string conversion. Similarly, if you have to convert int or numeric values to string, you may use the CAST and CONVERT functions for that. See the examples below; first simple queries without the table and then inserting the records. Int to string conversion by CAST example. The CAST query for conversion:

Casting in PostgreSQL, We'll show you how to perform Casting in PostgreSQL with examples. There are various cast operations in postgresql for converting string Syntax : SELECT (Double precision :: numeric); SELECT In the example we first convert the string '2019-07-14' into July 14th, 2019 while in the second part we  Notice that the cast syntax with the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard. PostgreSQL CAST examples. Let’s take some examples of using the CAST operator to convert a value of one type to another. 1) Cast a string to an integer example. The following statement converts a string constant to an integer:

Comments
  • What will you do if your string has leading zeros?
  • 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!
  • Strictly, this does not answer the question. The example was not the problem - converting an integer to a char/string was. But thanks, @spyd3rr
  • @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 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.
  • This will not work if you're working with SpEL in jpa native queries. You'll have to use cast in that case.