Looking in PostgreSQL arrays with ANY

So far, this series has looked at how to create PostgreSQL arrays, how to retrieve data from them, and how to get the length of an array’s outer and inner dimensions. But one of the most common actions that we’ll want to do with an array is look inside to see if a particular value is in there.  Today, I’ll show you how to do that with the PostgreSQL ANY operator and its cousins. For example, given my table “foo”:

[local]/reuven=# select * from foo;
+----+---------------------+
| id | stuff               |
+----+---------------------+
| 8  | {abc}               |
| 9  | {abc,def}           |
| 10 | {abc,def,"ghi jkl"} |
+----+---------------------+
(3 rows)

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:

[local]/reuven=# select * from foo where 'abc' = ANY(stuff);
+----+---------------------+
| id | stuff               |
+----+---------------------+
| 8  | {abc}               |
| 9  | {abc,def}           |
| 10 | {abc,def,"ghi jkl"} |
+----+---------------------+
(3 rows)

Sure enough, because each of these three arrays contains an element ‘abc’, we get all three rows back.  What if I ask for ‘def’?

[local]/reuven=# select * from foo where 'def' = ANY(stuff);
+----+---------------------+
| id | stuff               |
+----+---------------------+
| 9  | {abc,def}           |
| 10 | {abc,def,"ghi jkl"} |
+----+---------------------+
(2 rows)

Again, not a surprise.  What if I search for ‘ghi’?

[local]/reuven=# select * from foo where 'ghi' = ANY(stuff);
+----+-------+
| id | stuff |
+----+-------+
+----+-------+
(0 rows)

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:

[local]/reuven=# select * from foo where ANY(stuff) = 'abc';
ERROR: 42601: syntax error at or near "ANY"
LINE 1: select * from foo where ANY(stuff) = 'abc';
                                ^

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:

[local]/reuven=# select * from foo where 'abc' = ALL(stuff);
+----+-------+
| id | stuff |
+----+-------+
| 8  | {abc} |
+----+-------+
(1 row)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

thirty nine + = forty three