Question 89519 in PostgreSQL Question posted in
The official documentation can be found here.

How to select every row to satisfy any rule described in a different table? – Postgresql

I have a table numbers:

id n
1 1
2 2
3 5
4 7
5 9

And I have a table of rules:

rule_type n
LT 2
GT 7
GT 9
EQ 2

(LT – less-than, GT – greater-than, EQ – equals).

I want to select every id from numbers, that satisfies any rule from table rules.

Expected result:

id
1
2
5

P.S. I use PostgreSQL 15.1.

Tags:

2

Answers


  1. 0

    You could try this query

    SELECT * 
FROM numbers v
WHERE EXISTS (
     SELECT 1
     FROM rules r
     WHERE (r.rule_type = 'LT' AND v.n < r.n)
        OR (r.rule_type = 'GT' AND v.n > r.n)
        OR (r.rule_type = 'EQ' AND v.n = r.n)
)

    See demo here

    Login or Signup to reply.
  2. 0

    I have used your tables to create this SQLFIDDLE and wrote a query and tested it.

    The following query works :

    select distinct n.id 
from numbers n 
join rules r ON (
  (r.rule_type = 'LT' and n.n < r.n) or 
  (r.rule_type = 'GT' and n.n > r.n) or 
  (r.rule_type = 'EQ' and n.n = r.n)
);

    It returns the expected output :

    id
1
2
5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search