Re: Select gives the wrong results

It looks like your "value" column is of a varchar(), char() or text
type. The > and < operators compare the ordinal value of the text
when used on text types. You'll want to use ALTER TABLE ... ALTER
COLUMN ... to change value into a numeric type (probably INT or
BIGINT), and then you'll get the intended result

On 8/29/05, Crystle Numan <crys(at)guidedvision(dot)com> wrote:
> Dear all:
>
> I am fairly knowledgeable about PostgreSQL but this behaviour is
> stumping me. Any help would be wonderful. If you think it is a bug, let
> me now and I'll file one.
>
> (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005,
> no results)
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>'946702800' AND value<'1104555600';
> id | person | field | value
> ----+--------+-------+-------
> (0 rows)
>
> (select values in DB (date stamps) between Jan 1, 2003 and Jan 1, 2005, 4
> results (!))
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>'1041397200' AND value<'1104555600';
> id | person | field | value
> ------+--------+-------+------------
> 1187 | 454 | 2 | 1051156800
> 1188 | 460 | 2 | 1053316800
> 1219 | 472 | 2 | 1057723200
> 1181 | 441 | 2 | 1042520400
> (4 rows)
>
> The first select should have those 4 results plus any more. We tried
> putting quotes (") around the word 'value' to see if that made a
> difference, and no it didn't. We tried reversing the two clauses and
> that made no difference.
>
> Here's another funny one. Not the one that doesn't belong.
>
> db_name=# SELECT * from person_detail WHERE field='2' AND
> value>='1000000001' AND value<='1104555600';
> id | person | field | value
> ------+--------+-------+------------
> 3 | 218 | 2 | 1017464400
> 253 | 295 | 2 | 1002340800
> 514 | 323 | 2 | 100155600
> 1126 | 405 | 2 | 1006750800
> 1179 | 439 | 2 | 1035172800
> 1187 | 454 | 2 | 1051156800
> 1188 | 460 | 2 | 1053316800
> 1219 | 472 | 2 | 1057723200
> 1181 | 441 | 2 | 1042520400
> 1152 | 434 | 2 | 1032321600
> 1129 | 410 | 2 | 1024027200
> (11 rows)
>
> Anyone see what's going on here?
>
> Thanks!
> Crystle
>
>
> --
> Crystle Numan, B.Sc., Web Developer
> Guided Vision: the possibilities are endless
> 905.528.3095 http://guidedvision.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org