My database is Postgres 8. I need to cast data type to another. That means, one of columns data type is varchar and need to cast it into int with Postgres in a SELECT statement.
Currently, I get the string value and cast it into int in Java.
Is there any way to do it? Sample code would be highly appreciated.
cast(varchar_col AS int) -- SQL standardor
varchar_col::int -- Postgres syntax shorthandTheses syntax variants are valid (almost) anywhere. The second may require nesting parentheses in special situations:
And the first may be required where only functional notation is allowed by syntax restrictions:
There are two more variants:
int4(varchar_col) -- only works for some type names
int '123' -- must be an untyped, quoted string literal
Note how I wrote int4(varchar_col). That's the internal type name and there is also a function defined for it. Wouldn't work as integer()int()
Note also that the last form does not work for array types. int[] '{1,2,3}''{1,2,3}'::int[] or cast('{1,2,3}' AS int[]).
Details in the manual here and here.
To be valid for integer, the string must be comprised of an optional leading sign (+/-) followed by digits only. Leading / trailing white space is ignored.
character (except where explicitly noted). The string must be a valid integer, of course.