We’ve already seen (in my last post on this topic) how we can find all of the rows in which the “stuff” array is of a certain length. But sometimes — more often, perhaps — I want all of the rows in which a particular value is somewhere in the array. (You can think of this as the SQL equivalent of Ruby’s “select” on an enumerable, or Python’s “filter” on a sequence.) For this, we need to use PostgreSQL’s ANY operator. I have always found the syntax of ANY (and its synonym, SOME) to be a bit hard to understand. The IN operator works by itself, but ANY only works with an = sign. For example, if I want all of the rows from the above “foo” table in which the “stuff” array contains an element ‘abc’, I can say:

This is important to remember, actually: ANY looks at the elements of the array, and no further. So the string ‘ghi’ was compared with the elements of “stuff”, which in the case of row ID 10, was the three strings ‘abc’, ‘def’, and ‘ghi jkl’. PostgreSQL compared ‘ghi’ with each of these, found them to be unequal, and thus return 0 rows. Remember this when you’re trying to search through your text[] array column; you can look for specific elements of the array, but no further. What I personally find confusing, and easy to forget, is the fact that using ANY requires that I use an = sign before the call to ANY. Moreover, it’s important to remember that ANY and the array go on the right-hand side of the equal sign, for reasons that I don’t quite understand. Thus, the following will not work:

I’ve never had any occasion to use it, but you should know that PostgreSQL also offers an ALL operator. It works the same way as ANY, except that it only returns a TRUE value if all of the elements in the array are equal to the value on the left-hand side. For example:

In this example, we only get one row back, because the stipulation is that all of the array elements need to be equal to ‘abc’. I’ve got a few more posts planned in this series about PostgreSQL arrays. If there is something about this subject that you have always wanted to know, please contact me, and I’ll do what I can to answer.