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

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.2 on 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

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