Postgres Pro
Downloads
Home   >   mailing lists

Thread: please help me with text cast to int ....

please help me with text cast to int ....

From
Theodore Petrosky
Date:
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.

select version();                                                      version
            
 

-------------------------------------------------------------------------------------------------------------------------PostgreSQL
7.4.2on powerpc-apple-darwin7.3.0,
 
compiled by GCC gcc (GCC) 3.3 20030304 (Apple
Computer, Inc. build 1495)
(1 row)

Ted

    
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail

Re: please help me with text cast to int ....

From
Phil Endecott
Date:
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.

Re: please help me with text cast to int ....

From
Stephan Szabo
Date:
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.

Re: please help me with text cast to int ....

From
Theodore Petrosky
Date:
Thank you.... this pointed me to the problem. I have
non castable entries. I had picked up legacy data (for
the jobnumbers [that's why they were text in the first
place]) and some of the jobnumbers where 1162_01  
1162_02   so this would fail. 

The error message was giving me the correct message
however I failed to see it. Silly me, I kept thinking
that it was an error code that I didn't recognize...

Thanks again.

Ted

--- Stephan Szabo <sszabo@megazone.bigpanda.com>
wrote:
> 
> 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.
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
> cleanly
> 

    
__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail