HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to check if int[] array contains a value less than X?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
arraythanvaluecontainslesshowcheckint

Problem

We can check if a specific value is contained in an array using 'X' = ANY(haystack). However, how do I check if an array of integers contains a value that is less than X?, e.g. I want to find all arrays that have at least one value lower than 100.

Solution

Don't know if this is the answer, but one way of doing this is to create a range and then check if they overlap, e.g.
int4range(0, 100, '[]') @> ANY(t2.ages)


This works in cases when dealing with finite numbers, but would not work if ages can contain any negative value.

Update: Never mind, it was just 100 >= ANY(t2.ages).

Context

StackExchange Database Administrators Q#303318, answer score: 8

Revisions (0)

No revisions yet.