X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id ECFDA476508 for ; Wed, 14 May 2003 11:34:06 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 65172-01 for ; Wed, 14 May 2003 11:34:04 -0400 (EDT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by postgresql.org (Postfix) with ESMTP id 96787476476 for ; Wed, 14 May 2003 11:28:15 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19FyAt-0005UM-0U; Wed, 14 May 2003 16:28:11 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 223E216F38; Wed, 14 May 2003 16:28:10 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 734CE16F36; Wed, 14 May 2003 16:28:09 +0100 (BST) Content-Type: text/plain; charset="big5" From: Richard Huxton Organization: Archonet Ltd To: "Kenny Mok" , Subject: Re: Type coercion on column in a query Date: Wed, 14 May 2003 16:28:06 +0100 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: quoted-printable Message-Id: <200305141628.06751.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/277 X-Sequence-Number: 13442 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=3D# 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=20 from the psql command-line. =3D> \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= =20 fields - you're throwing away all the type checking PG can do for you. --=20 Richard Huxton