Today I Learned

hashrocket A Hashrocket project

Postgres Contains/Contained By Array Operators

Today I learned about the Postgres 'contains' array operator (@>). This compares two arrays, returning true if the first array contains all of the elements of the second array.

myapp_development=# select array[1,2,3] @> array[1,3];
 ?column?
----------
 t
(1 row)

myapp_development=# select array[1,2,3] @> array[5,9];
 ?column?
----------
 f
(1 row)

It works in reverse via the 'is contained by' array operator (<@).

myapp_development=# select array[1,3] <@ array[1,2,3,4];
 ?column?
----------
 t
(1 row)

A practical example might be comparing two arrays, one of names and one of common nicknames associated with that name.

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.