X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0D4CBD1B1A5 for ; Sun, 11 Jul 2004 14:06:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82400-03 for ; Sun, 11 Jul 2004 17:06:07 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 13639D1B174 for ; Sun, 11 Jul 2004 14:06:06 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1737D35715; Sun, 11 Jul 2004 10:06:06 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 15BA335299; Sun, 11 Jul 2004 10:06:06 -0700 (PDT) Date: Sun, 11 Jul 2004 10:06:06 -0700 (PDT) From: Stephan Szabo To: Theodore Petrosky Cc: postgres SQL Subject: Re: please help me with text cast to int .... In-Reply-To: <20040711162246.34618.qmail@web41004.mail.yahoo.com> Message-ID: <20040711100337.Y854@megazone.bigpanda.com> References: <20040711162246.34618.qmail@web41004.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/61 X-Sequence-Number: 18313 On Sun, 11 Jul 2004, Theodore Petrosky wrote: > I give up.. what don't I understand about casting and > ints and text.. > > i have a table jobinfo with: > > acode text, > jobnumber text default > nextval('public.jobinfo_seq'::text), > jobtitle text > > I have about 3000 rows starting with jobnumber = 1000. > > SELECT jobnumber, jobtitle FROM jobinfo WHERE > jobnumber >= 999 ORDER BY jobnumber ASC; This is going to do a textual comparison, not an integer one. So it's looking for jobnumbers greater than the string '999'. If you want to treat jobnumber as an integer, CAST(jobnumber as integer) >= 999 may be what you want. Note, however that it will fail if there are any non-numeric jobnumber rows. In general, storing numbers in strings that you want to act upon as numbers is a bad idea.