CODEWITHSUNDEEP
postgresqlrange
Steve
Steve

Reputation: 350

Postgresql - VALUE between two columns

I have a long list of six digit numbers (e.g. 123456)

In my postgresql DB I have a table with two columns start_value and end_value. The table has rows with start and end values which are 9 digits in length and represent a range of numbers i.e. start_value might be 123450000 and end_value might be 123459999.

I need to match each of the six digit numbers with it's row in the DB table which falls in its range.

For many numbers in my list I can simply run the following 

SELECT * FROM table WHERE start_value=(number + 000)

However, this does not cover numbers which fall inside a range, but do not match this pattern.

I have been trying statements such as:

SELECT * FROM table WHERE start_value > (number + 000) AND end_value < (number + 999)

But this doesn't work because some rows cover larger ranges than xxxxx0000 to xxxxx9999 and so the statement above may return 20 rows or none.

Any points would be most welcome!

EDIT: the Data Type of the columns are numeric(25)

Upvotes: 3

Views: 7794

Answers (3)

Kaleem Ullah
Kaleem Ullah

Reputation: 7049

POSTGRESQL

Some time we stuck in data type casting problems and null value exceptions.

SELECT *
FROM TABLE
WHERE COALESCE(number::int8, 0::int8) * 1000 BETWEEN start_value::int8 AND end_value::int8
;

number::int8 type cast to integer

start_value::int8 type cast to integer

COALESCE(number::int8, 0::int8) return number or zero if value is empty to avoid exceptions

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

Assuming number is numeric:

select *
from table
where number * 1000 between start_value and end_value

Upvotes: 7

Scott S
Scott S

Reputation: 2746

Ok, so if I'm understanding correctly, first you need to pad your search value to 9 digits. You can do that with this - 12345 * (10 ^ (9 - length(12345::text))). length(12345::text) gets the number of digits you currently have, then it subtracts that from 9 and multiplies your search value by 10 to the power of the result. Then you just throw it in your search. The resulting query looks something like this -

SELECT * FROM table WHERE (12345 * (10 ^ (9 - length(12345::text)))) > start_value AND (12345 * (10 ^ (9 - length(12345::text)))) < end_value

You could also use the BETWEEN operator, but it is inclusive, which doesn't match the example query you have.

Upvotes: 2

Related Questions