Thread: Type coercion on column in a query

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 :

testing=# SELECT
testing-#    cast(data1 as numeric) - cast(data2 as numeric)
testing-#    as result from test
testing-#    where id = 2;
ERROR:  Cannot cast type 'character varying' to 'numeric'
testing=#
testing=# SELECT
testing-#    to_number(data1) - to_number(data2)
testing-#    as result from test
testing-#    where id = 2;
ERROR:  Function 'to_number(varchar)' does not exist       Unable to identify a function that satisfies the given
argument
types       You may need to add explicit typecasts
testing=#

It seems that using the casting function and to_number function cannot
casting the target columns into my requied type. However, when the argument
given to these functions are literals, the result is like this :

testing=# SELECT
testing-#   cast('1234' as numeric) - cast('34' as numeric)
testing-#   as result ;result
--------  1200
(1 row)

testing=# SELECT
testing-#   to_number('1234') - to_number('34')
testing-#   as result ;
ERROR:  Function 'to_number(unknown)' does not exist       Unable to identify a function that satisfies the given
argument
types       You may need to add explicit typecasts
testing=#

The casting function works as my expected while the to_number function does
not.
So, how can I cast the column into the datatype I want during querying
before shown in front of my users?? And any concept that I have
mis-understood already ?? Thanks for your help in advance.

^_^

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 functionsResult data type |   Schema   |       Name       | Argument
datatypes 
------------------+------------+------------------+---------------------numeric          | pg_catalog | numeric
| bigintnumeric          | pg_catalog | numeric          | double precisionnumeric          | pg_catalog | numeric
   | integernumeric          | pg_catalog | numeric          | numeric, integernumeric          | pg_catalog | numeric
       | realnumeric          | pg_catalog | numeric          | smallintnumeric          | 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

On Thursday 08 May 2003 02:59, 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 :
>
> testing=# SELECT
> testing-#    cast(data1 as numeric) - cast(data2 as numeric)
> testing-#    as result from test
> testing-#    where id = 2;
> ERROR:  Cannot cast type 'character varying' to 'numeric'

There is no cast between varchar and numeric / int; you will need to
go via text, e.g.
 SELECT data1::text::numeric

or
 SELECT cast(cast(data1 as text) as numeric)



Ian Barwick
barwick@gmx.net

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
>

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

Thanks Richard,

Do you know if there's a way to access the same method that Postgres 
uses for determining cast-ability?

I don't mind writing a new function in plperl.  I am concerned not so 
much about the difficulty of the numeric type, but as I move into other 
types, such as timestamp, I don't want to re-implement half-assedly the 
heuristics for date determination when those are already pretty 
bulletproof in Postgres.

Best,

Randall


On Wednesday, May 14, 2003, at 12:32 PM, Richard Huxton wrote:

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

On Wednesday 14 May 2003 5:46 pm, Randall Lucas wrote:
> Thanks Richard,
>
> Do you know if there's a way to access the same method that Postgres
> uses for determining cast-ability?

I don't *think* so (bear in mind I'm not a developer, just a user). My
understanding is that PG does the following:
1. Identify types involved
2. Look up the function required to convert
3. Execute the function

It's the function itself that raises an error in the case of say
text=>timestamp where the text contains garbage. I don't think there's much
the developers can do about this - if you want to be able to add new types
simply, they need to be fairly self contained.

Now it shouldn't be beyond the wit of man to be able to produce an
is_castable(value, destination_type) function that calls these conversion
functions and traps the error.  In this particular case there shouldn't* be
any side effects to worry about. However, that's probably more work than
you're interested in.

> I don't mind writing a new function in plperl.  I am concerned not so
> much about the difficulty of the numeric type, but as I move into other
> types, such as timestamp, I don't want to re-implement half-assedly the
> heuristics for date determination when those are already pretty
> bulletproof in Postgres.

I think you're out of luck here. The "proper" way to do this is:
1. try to insert values into columns of correct type
2. when it fails, handle error in application

* - that's shouldn't as in I can't think of a good reason why right now
--  Richard Huxton