pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplease help me with text cast to int ....
4+ messages / 3 participants
[nested] [flat]
* please help me with text cast to int ....
@ 2004-07-11 16:22 Theodore Petrosky <tedpet5@yahoo.com>
2004-07-11 17:01 ` Re: please help me with text cast to int .... Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-07-11 17:06 ` Re: please help me with text cast to int .... Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 4+ messages in thread
From: Theodore Petrosky @ 2004-07-11 16:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: please help me with text cast to int ....
2004-07-11 16:22 please help me with text cast to int .... Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-07-11 17:01 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
1 sibling, 0 replies; 4+ messages in thread
From: Phil Endecott @ 2004-07-11 17:01 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: please help me with text cast to int ....
2004-07-11 16:22 please help me with text cast to int .... Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-07-11 17:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-07-11 18:52 ` Re: please help me with text cast to int .... Theodore Petrosky <tedpet5@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2004-07-11 17:06 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: please help me with text cast to int ....
2004-07-11 16:22 please help me with text cast to int .... Theodore Petrosky <tedpet5@yahoo.com>
2004-07-11 17:06 ` Re: please help me with text cast to int .... Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-07-11 18:52 ` Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Theodore Petrosky @ 2004-07-11 18:52 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-07-11 18:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-11 16:22 please help me with text cast to int .... Theodore Petrosky <tedpet5@yahoo.com>
2004-07-11 17:01 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-07-11 17:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-07-11 18:52 ` Theodore Petrosky <tedpet5@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox