Message-ID: <40F17279.4080204@chezphil.org>
Date: Sun, 11 Jul 2004 18:01:45 +0100
User-Agent: Mozilla Thunderbird 0.6 (X11/20040605)
MIME-Version: 1.0
To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: postgres SQL <pgsql-sql@postgresql.org>
Subject: Re: please help me with text cast to int ....
References: <20040711162246.34618.qmail@web41004.mail.yahoo.com>
In-Reply-To: <20040711162246.34618.qmail@web41004.mail.yahoo.com>
Content-Type: text/plain; charset="us-ascii"; format="flowed"
Content-Transfer-Encoding: 7bit
From: Phil Endecott <spam_from_postgresql_sql@chezphil.org>

Theodore,

Because jobnumber is declared as text, you are getting "dictionary 
order" (lexicographic) ordering on the values.  In a dictionary, "abc" 
comes after "aaaaaaaaa", obviously.  So indeed "999" will come after 
"1000".  To get the effect that you want you need to treat jobnumber as 
a number.  The easiest thing to do would be to change the declaration of 
the table.  If for some reason you can't do that, you need to do a cast 
in the query; that would make your WHERE expression work, but I don't 
know about ORDER BY (look it up).

For example:
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber::integer >= 200 ORDER BY jobnumber ASC;

Do you ever have non-numeric values in the jobnumber field?  (Is that 
why it's declared as text?)  If you do you will get problems because 
they cannot be converted to integers in order to perform the comparison.

Regards,

--Phil.


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;
> 
> The above SQL produces no rows. however...
> 
> SELECT jobnumber, jobtitle FROM jobinfo WHERE
> jobnumber >= 200 ORDER BY jobnumber ASC;
> 
> produces rows with jobnumber >= 2000
> 
> if I change the query with jobnumber >= 201, I get
> rows >= 2010.
> 
> it is as if there was a silent zero being appended to
> the end of my int in the query. What am I missing,
> please.