On Tue, Mar 20, 2012 at 7:14 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> But this only works if the input is a clean list of number characters already!
> Anything other than this will issue an error:
>
> postgres=# SELECT '10'::int;
>
> After trying hard to cope with anything possibly as an input string I
> found this:
>
> postgres=# SELECT to_number('0'||mytextcolumn,
> '99999999999.000')::int FROM mytable;
I came across the same issue, specifically wanting semantics like C's
atoi function. Some discussion on this list turned up a few options.
Beginning of thread, including one possibility:
http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00090.html
Another well-researched option, with slightly different semantics:
http://osdir.com/ml/postgresql-pgsql-general/2012-03/msg00094.html
Hope that helps!
ChrisA
