Content-Type: text/plain;
  charset="big5"
From: Ian Barwick <barwick@gmx.net>
To: <pgsql-sql@postgresql.org>
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 17:35:18 +0200
User-Agent: KMail/1.4.3
References: <000e01c314fd$208822e0$0303a8c0@home3>
In-Reply-To: <000e01c314fd$208822e0$0303a8c0@home3>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200305141734.45558.barwick@gmx.net>

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