Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Randall Lucas <rlucas@tercent.net>
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 17:32:11 +0100
User-Agent: KMail/1.4.3
Cc: "Kenny Mok" <kenny@vis-ken.com>, <pgsql-sql@postgresql.org>
References: <8D873114-8626-11D7-8D9A-000A957653D6@tercent.net>
In-Reply-To: <8D873114-8626-11D7-8D9A-000A957653D6@tercent.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200305141732.11386.dev@archonet.com>

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 (sor=
ry,=20
I don't use TCL). There's no exception handling in Postgresql (nor will the=
re=20
be in the next few months).

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

--=20
  Richard Huxton