Re: Type coercion on column in a query - Mailing list pgsql-sql

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 '
beginif $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
>