Age range on age range in WHERE to heavy - POSTGRES I have 2 columns in the database for events.


agelow (int)
agehigh (int)


How it's structured:


agehigh is being used for "and under" which is represented by 0 in the column
the high age is placed in the agelow column
The "and over" is represented by 99 and is not a problem since it has the agehigh and the agelow in the right columns.
all ages are represented with 99 in both columns.


I am trying to write a WHERE that can pull all event ages if no ages are passed in but also need to be able to pull events just for one age passed in or for an age range passed in. The age range is making this a big headache.

Example

If I pass in agelow 5 and agehigh 15, I would need to grab all events that are


available for anyone from 5 up to 15 which includes the "5 and under", "5 and up", "15 and under" and all ages.


Here is the WHERE part:

WHERE
((events.age_low &gt;= agelow AND events.age_high &lt;= agehigh) OR
 (events.age_low &lt;= agelow AND events.age_high &gt;= agehigh) OR
 (events.age_low &lt;= agelow /* LOW (99 for all) */ AND events.age_high &gt;= agelow /* LOW (99 for all) */) OR
 (events.age_low &lt;= agelow /* LOW (99 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low &gt;= agelow /* LOW (99 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR 
 (events.age_high &gt;= agehigh /* HIGH (0 for all) */ AND events.age_low &lt;= agehigh /* HIGH (0 for all) */) OR 
 (events.age_low &gt;= agehigh /* HIGH (0 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR 
 (events.age_low &lt;= agehigh /* HIGH (0 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR  
 (events.age_low = 99 AND events.age_high = 99));


Here is a small selection of the age columns:

CREATE TABLE events(age_low,age_high)
AS VALUES
  (  99 ,  99 ),
  (   3 ,  12 ),
  (   1 ,   6 ),
  (   5 ,   0 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (   5 ,  11 ),
  (   5 ,   0 ),   -- this is 5 and below
  (  16 ,  99 ),   -- this is 16 and up
  (   1 ,   4 ),
  (  13 ,  18 ),
  (  99 ,  99 ),   -- this is all ages
  (   5 ,   0 ),
  (  99 ,  99 );
One approach to improve readability would be to use a CTE to add a couple of calculated columns..  However, this approach could also be slow, depending on the size of the table..  But it would look something like this

WITH true_ages AS (
  SELECT
    &lt;cols&gt;,
    CASE WHEN age_high &lt; age_low THEN age_high -- handle case of 'x and below'
         WHEN age_low = 99 and age_high = 99 THEN 0 -- handle case of 'all ages'
         ELSE age_low END as true_low,
    CASE WHEN age_low &lt; age_high and age_high = 99 THEN 999 -- handle case of 'x and above'
         WHEN age_low = 99 and age_high = 99 THEN 999 -- handle case of 'all ages'
         ELSE age_high END as true_high
  FROM &lt;table&gt;
) SELECT
  &lt;cols&gt;
FROM true_ages
WHERE &lt;target_age&gt; BETWEEN true_low and true_high;


You could potentially add some WHERE clause in the CTE to reduce the result set initially, but that might add unnecessary complexity.