Re: Type coercion on column in a query

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