From: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: postgres SQL <pgsql-sql@postgresql.org>
Subject: Re: please help me with text cast to int ....
Date: Sun, 11 Jul 2004 18:01:45 +0100
Message-ID: <40F17279.4080204@chezphil.org> (raw)
In-Reply-To: <20040711162246.34618.qmail@web41004.mail.yahoo.com>
References: <20040711162246.34618.qmail@web41004.mail.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_from_postgresql_sql@chezphil.org, tedpet5@yahoo.com
Subject: Re: please help me with text cast to int ....
In-Reply-To: <40F17279.4080204@chezphil.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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