Check if each number from a list matches a range in a int4range[]

Question

I have a field: ages pg_catalog.int4range[][][]
Example value is {"[0,6)","[8,10)"} which means 2 ranges 0-6 and 8-10.
How to add correct condition to WHERE clause to check:

• 9 and 3 fit example value
• 9 and 9 do not fit (only one value per range should be)

Something like WHERE ages <@ ARRAY[9,3], WHERE ages <@ ARRAY[9,9]

Update
The logic describes contract conditions for applying child age rules. I was thinking it is attractive to keep all data inside one field and avoid several stages of checking and compare - just to intersect values and ranges.
Number of ranges is usually variable, can be 1,2,3,4 etc. Values for check are counted and applied only to matching number of ranges - if 3 ages then only arrays with 3 ranges inside.

Answer 1

Clarify problem

You question displays a 1-dimensional array. So use a proper column definition:

ages int4range[]

Not that it makes any difference other than being confusing. I quote the chapter Declaration of Array Types from the manual:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

Also, the question itself is ambiguous. Ex.:

INSERT INTO tbl(ages) VALUES ('{"[0,6)","[5,9)","[8,10)"}')

Test with: '{5,8,7}'::int[].

If applied in order, the check fails:
5 fits 1st range [0,6).
8 fits 2nd range [5,9).
7 doesn't fit 3nd range [8,10).

However, applied in the different order 5,7,8, the check succeeds. You need to

1. Either guarantee non-overlapping intervals.
2. Or apply the checks in a defined order.
3. Clarify that the number of elements on either side always matches.

Finally, edit the explanation about what you are trying to do into the question. Don't hide important information in comments.

Answer

As per comment, assuming 1., 2. and 3. from the catalog above. This query tests all ages (array of int4range) from tbl whether they fit a supplied age_list (int[]):

WITH tbl(id, ages) AS (
   VALUES
     (1, '{"[0,2)","[4,6)","[8,10)"}'::int4range[])
    ,(2, '{"[2,4)","[6,8)","[9,10)"}')
   )                                    -- stand-in for table
SELECT id, bool_and(i <@ r) AS passes_test
FROM  (
   SELECT id
         ,unnest('{1,5,9}'::int[]) AS i -- supply age_list here
         ,unnest(ages)             AS r
   FROM tbl
   ) AS sub
GROUP  BY 1

Returns:

 id | passes_test
----+------------
  1 | t
  2 | f

Major points

• The CTE tbl is just a stand-in for your table. You can remove it if tbl actually exists.

• The two unnest() functions unnest the two arrays side by side. I add id (any unique column) to identify rows.

• Check for each element whether it is contained in the range with the <@ operator and re-aggregate with bool_and. Returns TRUE if and only if all checks are TRUE.