Postgres data type cast

Question

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.

Solution

cast(varchar_col AS int)  -- SQL standard

or

varchar_col::int          -- Postgres syntax shorthand

Theses syntax variants are valid (almost) anywhere. The second may require nesting parentheses in special situations:

• PostgreSQL: Create index on length of all table fields

And the first may be required where only functional notation is allowed by syntax restrictions:

• PostgreSQL - CAST vs :: operator on LATERAL table function

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() or int().

Note also that the last form does not work for array types. int[] '{1,2,3}' has to be '{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.

Comments

[Abel Callejo]

it doesn't work if the column is of character data type

[Erwin Brandstetter]

@AbelMelquiadesCallejo: What is "it"? All of the above works for data type character (except where explicitly noted). The string must be a valid integer, of course.