From: Richard Huxton <dev@archonet.com>
To: Kenny Mok <kenny@vis-ken.com>
To: pgsql-sql@postgresql.org
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 16:28:06 +0100
Message-ID: <200305141628.06751.dev@archonet.com> (raw)
In-Reply-To: <000e01c314fd$208822e0$0303a8c0@home3>
References: <000e01c314fd$208822e0$0303a8c0@home3>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, kenny@vis-ken.com
Subject: Re: Type coercion on column in a query
In-Reply-To: <200305141628.06751.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox