From: | "Kenny Mok" <kenny(at)vis-ken(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Type coercion on column in a query |
Date: | 2003-05-08 00:59:49 |
Message-ID: | 000e01c314fd$208822e0$0303a8c0@home3 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
^_^
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 2003-05-08 01:28:13 | Re: Replication for a large database |
Previous Message | Randall Lucas | 2003-05-07 23:52:30 | Re: [SQL] Unanswered Questions WAS: An unresolved performance problem. |