Search code examples
sqlpostgresqlrangepostgresql-12

PostgreSQL: Is it possible to determine if any elements in an array overlap a range?

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

Solution

  • 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.