0

Let's say I have a table with an int[] column and values:

'[1,4,1300]'::int4[]

I want to be able to query the column and get a true if ANY of the elements match a BETWEEN statement. So, in this case, something like:

SELECT id FROM table WHERE col && '[1000,2000]'::int4range or similar.

The column uses gist__int_ops

IamIC
  • 17,747
  • 20
  • 91
  • 154

3 Answers3

1

You can use a range with the containment operator, e.g.:

select id 
from my_table 
where '[2,4]'::int4range @> any(col)

Unfortunately, the query does not use the index. I do not think you can find a query that does it (without defining your own operator).

Update. You can try to convert the range to an array. 

create or replace function int_range_to_array(int4range)
returns int[] language sql immutable as $$
    select array(
        select i 
        from generate_series(lower($1), upper($1)- 1) i)
$$;

select id
from my_table 
where col && int_range_to_array('[2,4]');

Obviously, the performance depends on range sizes.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Yeah, it's the not using the index part that kills. Although I have written C functions for PG, I'm not sure how to solve this. – IamIC Oct 19 '19 at 21:05
  • I think it's possible but the subject is too broad for this site. – klin Oct 19 '19 at 21:11
  • I think it's perhaps too specialized for this site. – IamIC Oct 19 '19 at 21:14
  • Of course, too specialized. I used the phrase commonly used here describing similar issues. – klin Oct 19 '19 at 21:23
  • I understand. The phrase is ironic. It occurred to me that I might just have to use the EAV model approach to solve this. I suppose that's not such a terrible approach. Interestingly, the only storage engine I've found that is capable of this type of query is ElasticSearch. – IamIC Oct 19 '19 at 21:26
1

The dogmatic answer is that you shouldn't use arrays in your tables, and should use child tables instead. I don't usually adhere to dogma, but in this case I think that this is probably the pragmatic answer, as well. I don't see anything in the extensibility API for indexes that looks like you could even implement your own extension to do this.

If you use a child table, you would go back to using BETWEEN..AND for your query, rather than int4range, to obtain indexability.

jjanes
  • 37,812
  • 5
  • 27
  • 34
0

You can unnest():

where exists (select 1
              from unnest(col) u(val)
              where u.val between 2 and 4
             )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786