Postgresql - VALUE between two columns

Question

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)

What are the types of start_value, end_value and number? — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 19, 2013 at 11:53
data types of the columns are numeric(25), the data type of the list is anything I want, but starts out as a String (as I am parsing it from a text file) — Steve
– Steve, Commented Apr 19, 2013 at 11:56

Clodoaldo Neto · Accepted Answer · 2013-04-19 12:03:52Z

7

Assuming number is numeric:

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

edited Apr 19, 2013 at 12:03

answered Apr 19, 2013 at 11:57

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott S · Accepted Answer · 2013-04-19 11:58:01Z

2

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.

answered Apr 19, 2013 at 11:58

Scott S

2,73618 silver badges21 bronze badges

Comments

Kaleem Ullah · Accepted Answer · 2020-08-05 06:44:43Z

0

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

answered Aug 5, 2020 at 6:44

Kaleem Ullah

7,1293 gold badges45 silver badges48 bronze badges

Collectives™ on Stack Overflow

Postgresql - VALUE between two columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related