Re: Type coercion on column in a query

On Wednesday 14 May 2003 5:10 pm, Randall Lucas wrote:
> Along the same lines, is it possible to tell whether a column may be
> cast to a given type (e.g. numeric) without throwing an error?
>
> Specifically, I have some values that will probably be numbers but
> might have some free text or other gobbledygook in there.  I want to
> take the sum of all the numeric values, and maybe the average as well.
> So what I envision is:
>
> table dirty_data (
>       id                 int,
>       dirty_numbers         text
> );

> select sum( safe_numeric_cast(dirty_numbers) ) from dirty_data where
> safe_numeric_cast(dirty_numbers) is not null;

You'd have to write "safe_numeric_cast()" in plperl, or possibly pltcl (sorry,
I don't use TCL). There's no exception handling in Postgresql (nor will there
be in the next few months).

Alternatively, you could conceivably write a bunch of is_numeric(), is_int()
etc functions and test first.

--  Richard Huxton