Postgres Data Type Cast

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.