Querying in postgres for a range around an integer

Question

In postgresql, I'd like to determine whether a known integer is within a +/- range of another integer. What is the function for this query?

Example: In my dataset, I have 2 Tables:

Table_1
ID   integer
 1     2000
 2     3000
 3     4000

Table_2
ID   integer
 1     1995
 2     3050
 3     4100

For each ID-pair, I'd like to query whether Table_1.integer is +/- 25 of Table_2.integer.

The answers would be:
ID 1: TRUE
ID 2: FALSE
ID 3: FALSE

Any help is much appreciated. I am new to using postgresql and all programming languages in general.

Tim Biegeleisen · Accepted Answer · 2019-07-25 06:36:48Z

1

We can try checking the absolute value of the difference between the two integer values, for each ID:

SELECT
    t1.ID,
    CASE WHEN ABS(t1.integer - t2.integer) <= 25 THEN 'TRUE' ELSE 'FALSE' END AS answer
FROM Table_1 t1
INNER JOIN Table_2 t2
    ON t1.ID = t2.ID
ORDER BY
    t1.ID;

Demo

If you want to just output the raw boolean value, then use:

SELECT
    t1.ID,
    ABS(t1.integer - t2.integer) <= 25 AS answer
FROM ...

edited Jul 25, 2019 at 6:36

answered Jul 25, 2019 at 5:44

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

user330315 Over a year ago

CASE WHEN ABS(t1.integer - t2.integer) <= 25 THEN 'TRUE' ELSE 'FALSE' can be simplified to ABS(t1.integer - t2.integer) <= 25

Tim Biegeleisen Over a year ago

@a_horse_with_no_name I assume the OP wants actual text string output. Other than that, I agree with your suggestion.

Kaushik Nayak · Accepted Answer · 2019-07-25 06:12:59Z

0

This is almost similar to @Tim's solution but without the CASE expression, useful if you wish to output boolean types.

SELECT t1.ID,ABS(t1.integer - t2.integer) <= 25 as res
FROM table_1 t1 JOIN table_2 t2
    ON t1.ID = t2.ID;

DEMO

answered Jul 25, 2019 at 6:12

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Querying in postgres for a range around an integer

2 Answers 2

Demo

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related