postgresql query between

Asked

Viewed 47 times

0

Table X has the following information:

(id serial unique, vlinicial numeric(10,2), vlfinal numeri(10,2))

ID VLINICIAL VLFINAL
--------------------
1   0.01     10000.00
2   10000.01 20000.00
3   20000.01 30000.00
4   30000.01 40000.00
5   40000.01 999999.00

My query to verify which id to choose:

select * from x where 17000.00 between vlinicial and vlfinal

The result should be ID 2 but it turns out it gives 0 lines...

If you put a value below 10000.00 the ID 1 already appears:

select * from x where 7000.00 between vlinicial and vlfinal

I tested this way and it worked, but not above consult no.

select id 
from
(select 1 as id, 0.01::numeric as vli, 10000.00::numeric as vlf
union all
select 2 as id, 10000.01::numeric as vli, 20000.00::numeric as vlf
union all
select 3 as id, 20000.01::numeric as vli, 30000.00::numeric as vlf
union all
select 4 as id, 30000.01::numeric as vli, 40000.00::numeric as vlf) as t1   
where 15000.00::numeric between t1.vli and t1.vlf

Any suggestions?

  • 2

    What version of Postgresql are you using? I tested on Sqlfiddle and it works

  • @Williamjohnadamtrindade, 9.6

  • 1

    @Williamjohnadamtrindade look here http://sqlfiddle.com/#! 17/04e99/5, it’s working

  • 1

    @Groot Works on 9.6 also: Fiddle. Are you sure that table X has been filled?

  • @Williamjohnadamtrindade, yes, table filled with all the information, and I’ve broken my head with this which is very strange... I’m going to go through it in detail to see what’s wrong. thank you.

  • @Groot Is there to be with the insertion of the data not commited? Depending on the configuration of the client software, for example pgadmin, it is possible to leave the autocommit disabled

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.