From: Richard Huxton <dev@archonet.com>
To: Randall Lucas <rlucas@tercent.net>
Cc: Kenny Mok <kenny@vis-ken.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 17:32:11 +0100
Message-ID: <200305141732.11386.dev@archonet.com> (raw)
In-Reply-To: <8D873114-8626-11D7-8D9A-000A957653D6@tercent.net>
References: <8D873114-8626-11D7-8D9A-000A957653D6@tercent.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, rlucas@tercent.net, kenny@vis-ken.com
Subject: Re: Type coercion on column in a query
In-Reply-To: <200305141732.11386.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox