Re: Type coercion on column in a query

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
);

function safe_numeric_cast(text) returns numeric as '
begin
if $1::numeric::text = $1 then
return $1::numeric;
else
return null;
end if;
end;
' language 'pseudopgsql';

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

However, the holdup seems to be that I can't even try to cast a dirty
string to numeric lest I throw an "ERROR: Bad numeric input format."
Suggestions?

Best,

Randall

On Wednesday, May 14, 2003, at 11:28 AM, Richard Huxton wrote:

> On Thursday 08 May 2003 1:59 am, Kenny Mok wrote:
>> Dear all,
>>
>> I am just a novice in SQL and also in PostgreSQL. I have encountered
>> some
>> difficulties in developing a website using PostgreSQL as my backend
>> database.
>>
>> My situation is, I have such a table "test" :
>>
>> testing=# SELECT * from test ;
>> id | data1 | data2
>> ----+------------+--------
>> 1 | 2003-5-6 | 3 days
>> 2 | 1234 | 34
>> (2 rows)
>>
>> where columns data1 and data 2 are with data types varchar, where all
>> my
>> data is stored into it.
>>
>> What I want to do is to extracts the data from this database and
>> casting it
>> before shown in front of my client. So, I do the following queries :
>
> You can see what functions are available and what types they support
> with \df
> from the psql command-line.
>
> => \df numer*
> List of functions
> Result data type | Schema | Name | Argument data types
> ------------------+------------+------------------
> +---------------------
> numeric | pg_catalog | numeric | bigint
> numeric | pg_catalog | numeric | double precision
> numeric | pg_catalog | numeric | integer
> numeric | pg_catalog | numeric | numeric, integer
> numeric | pg_catalog | numeric | real
> numeric | pg_catalog | numeric | smallint
> numeric | pg_catalog | numeric | text
> etc...
>
> So you want ...(data2::text)::numeric
>
> That's not to say I think it's a good idea to store all your values in
> text
> fields - you're throwing away all the type checking PG can do for you.
> --
> Richard Huxton
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>