Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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

share|improve this question
1  
Nice question. Sample data, expected results, Pg version, clear description. Thanks! – Craig Ringer Oct 24 '12 at 7:58

2 Answers

up vote 6 down vote accepted

Assuming your table is named readings:

Find cities where at least one temperature is bigger than 80:

select *
from readings r
where exists (select 1 
              from unnest(r.temps) i
              where i > 80)

Find cities where all temperatures are bigger than 68:

select *
from readings r
where 68 < all (select i
                from unnest(r.temps) i)
share|improve this answer
this absolutely solves my problem. do you predict much of a performance penalty by the use of unnest? – Joshua Burns Oct 23 '12 at 20:28
@JoshuaBurns: no, I wouldn't expect that as the number of readings per city seems to be quite small. I would worry if they get more. In that case you might want to consider a more normalized design. – a_horse_with_no_name Oct 23 '12 at 21:35
2  
You don't need to unnest the arrays, you can use them directly in any and all. See added answer. – Craig Ringer Oct 24 '12 at 7:55
@CraigRinger: ah. Thanks. I wasn't aware of that (you live and learn ;) ) – a_horse_with_no_name Oct 24 '12 at 8:23
@a_horse_with_no_name I only picked it up from another SO answer recently, along with the awesome LIKE ANY trick. – Craig Ringer Oct 24 '12 at 8:30
up vote 4 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.