PostgreSQL: Query DB for rows containing integer[] > or < than specified value

postgresqlpostgresql-9.1

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less than a specified value? Here's an example table:

+=========+==================+
| city    | temps            |
+=========+==================+
| Seattle | {65,72,63,56,72} |
+---------+------------------+
| Miami   | {83,75,69,72,79} |
+---------+------------------+

Let's say I want to query for cities which have experienced a temp higher than 80. In this example only Miami has experienced temps higher than 80, so only that row should be returned. I've tried a few queries with no success, looked into intarray, but that doesn't seem to solve my problem either. Thanks much!

I'm running PostgreSQL 9.1

Best Answer

As an alternative to a_horse_with_no_name's solution, the simplest option is to use < and the any row-or-array comparision with the array:

SELECT city FROM cities WHERE 80 < any (temps);

See SQLFiddle.

It is not necessary to unnest the array, as any and all work on arrays as well as on rowsets.

Unlike using the array operators, this operation does not benefit from and can not use an index (b-tree or GIN) on temps, so a normalized design that splits temps into a separate table with foreign-key reference to cities may actually be faster if you have lots of cities and/or lots of samples.

Earlier I suggested that you may want to use a GIN array index and the array operators, but I was mistaken. These do not support the desired operation; I made an error in my testing that made them appear to.

For frequent updates I'd normalize it into another table and btree-index that table. If there's only a small amount of data I wouldn't bother with indexes and I'd just use < any (temps).

Related Question