Which Postgres operator answers which array question, tested SQL with the rows each query returns, and the NULL trap in "does not contain" queries.
To check whether a Postgres array column contains a value, use = ANY(column) for a single value, the @> operator for a set of values, and && when any overlap will do. Each one answers a different question, and picking the wrong one is the most common reason an array query returns the wrong rows.
Everything below was run on PostgreSQL 16 against one small table you can create in ten seconds. After the three operators, the page covers the negative case, jsonb arrays, the index that keeps these queries fast, and how to write all of it from Rails.
Create a table you can run every query against
A products table with a text[] column called tags. Two rows share tags, one has an empty array, and one is NULL, because the last two are where array queries go wrong.
CREATE TABLE products (
id serial PRIMARY KEY,
name text NOT NULL,
tags text[]
);
INSERT INTO products (name, tags) VALUES
('Rails starter kit', '{ruby,rails,starter}'),
('Postgres cheat sheet', '{postgres,sql}'),
('Full-stack bundle', '{ruby,rails,postgres,sql}'),
('Blank notebook', '{}'),
('Untagged import', NULL);
Two ways to write an array literal, and both appear on this page. The string form '{ruby,rails}' is what you see in dumps and psql. The constructor form ARRAY['ruby','rails'] is easier to read and easier to build from application code. They are interchangeable in every query below.
Find rows where the array contains one value: = ANY
When you have a single value and want every row whose array includes it, compare the value against ANY of the array elements.
SELECT name FROM products WHERE 'ruby' = ANY(tags);
-- Rails starter kit
-- Full-stack bundle
The same question can be asked with the containment operator, and on a large table you will want to, because @> can use a GIN index and = ANY generally cannot. More on that below.
SELECT name FROM products WHERE tags @> ARRAY['ruby'];
-- Rails starter kit
-- Full-stack bundle
Use = ANY when the table is small or the query is one-off. Use @> when the column is indexed. The results are identical.
Find rows that contain every value in a set: @>
The @> operator reads as "contains." The row matches only if every element on the right appears somewhere in the array on the left. Order does not matter, and neither do extra elements in the column.
SELECT name FROM products WHERE tags @> ARRAY['ruby', 'postgres'];
-- Full-stack bundle
The starter kit has ruby but not postgres, so it is excluded. If you pass a set with one element, @> behaves exactly like = ANY, which is why it is the safer default for an indexed column.
The error most people hit here is a type mismatch: "operator does not exist: character varying[] @> text[]." That happens when the column was created as varchar[] (Rails does this for t.string columns) and the literal is text[]. Cast the literal to the column type and it goes away.
-- if tags is character varying[]
SELECT name FROM products WHERE tags @> ARRAY['ruby', 'postgres']::varchar[];
Find rows that contain any of several values: &&
The && operator reads as "overlaps." The row matches if at least one element is shared between the column and the set you pass in. This is the operator for filters where a user picks several categories and wants anything that matches one of them.
SELECT name FROM products WHERE tags && ARRAY['starter', 'sql'];
-- Rails starter kit
-- Postgres cheat sheet
-- Full-stack bundle
The blank notebook has nothing to overlap with and the NULL row cannot be evaluated, so neither appears. That second case matters more than it looks, and it is the subject of the next section.
Find rows where the array does not contain a value
The natural way to write this is to negate the ANY test, and it is right up to the point where a column allows NULL.
SELECT name FROM products WHERE NOT ('ruby' = ANY(tags));
-- Postgres cheat sheet
-- Blank notebook
The untagged import is missing. Its tags column is NULL, so 'ruby' = ANY(NULL) is NULL rather than false, NOT NULL is still NULL, and the WHERE clause drops the row. A NULL array vanishes from both the positive query and the negative one, which is how a "products without this tag" report quietly under-counts in production.
Two fixes. Either include NULL explicitly, or coalesce the column to an empty array so the comparison has something to evaluate.
SELECT name FROM products
WHERE tags IS NULL OR NOT ('ruby' = ANY(tags));
-- or, equivalently:
SELECT name FROM products
WHERE NOT ('ruby' = ANY(COALESCE(tags, '{}')));
-- Postgres cheat sheet
-- Blank notebook
-- Untagged import
The third fix is structural: declare the column NOT NULL DEFAULT '{}' in the first place. An empty array and a missing array almost never mean different things in an application, and forcing the empty form removes an entire class of bug. The same applies to NOT (tags @> ARRAY['ruby']) and to 'ruby' <> ALL(tags), which behave identically on NULL.
Search a jsonb array instead of a text[] column
If the array lives inside a jsonb document, the text[] operators do not apply. jsonb has its own containment operator (also written @>) and its own existence operator, ?.
ALTER TABLE products ADD COLUMN meta jsonb;
UPDATE products SET meta = '{"tags": ["ruby", "rails"]}' WHERE id = 1;
UPDATE products SET meta = '{"tags": ["postgres", "sql"]}' WHERE id = 2;
-- containment: does the document contain this structure?
SELECT name FROM products WHERE meta @> '{"tags": ["ruby"]}';
-- Rails starter kit
-- existence: does this string appear as an element of the tags array?
SELECT name FROM products WHERE meta -> 'tags' ? 'ruby';
-- Rails starter kit
Containment is the more general of the two and the one to reach for first: it can match nested structure and multiple elements in one expression. The ? operator only checks top-level strings, but it is exactly the check most tag lookups need. The honest tradeoff is that a jsonb array is harder to index for this pattern than a native text[] column and easier to fill with inconsistent data. If tags are the only thing in the document, make them a real array column.
Make the query use an index on a large table
All of the queries above run a sequential scan until you add an index, which is fine at ten thousand rows and not fine at ten million. The index type for arrays is GIN.
CREATE INDEX products_tags_gin ON products USING GIN (tags);
-- for containment queries on the jsonb column
CREATE INDEX products_meta_gin ON products USING GIN (meta);
-- for the ? query, which tests the meta -> 'tags' expression
CREATE INDEX products_meta_tags_gin ON products USING GIN ((meta -> 'tags'));
A GIN index on a text[] column serves @>, &&, <@ and =. It does not serve = ANY, which is the reason the containment form is recommended for the single-value case above. Run EXPLAIN on both to see the difference on your own data. On our test table, with sequential scans switched off because five rows is too few for the planner to bother with an index, the @> form shows a Bitmap Index Scan on products_tags_gin and the = ANY form stays on Seq Scan.
On the jsonb side, the default GIN operator class serves @>, ?, ?| and ?&, but only against the expression it was built on. An index on meta serves meta @> '{"tags": ["ruby"]}'; it does not serve meta -> 'tags' ? 'ruby', which needs the index on (meta -> 'tags'). If you only ever use @>, the jsonb_path_ops class is smaller and faster.
One caveat worth knowing before you add the index: GIN indexes are slower to update than B-tree indexes, so a column that changes on every request is a poorer fit than a tags column that changes once when a record is saved.
Run the same queries from Rails with ActiveRecord
Rails supports Postgres arrays natively. The migration declares the column with array: true, and the index goes on at the same time.
class CreateProducts < ActiveRecord::Migration[8.0]
def change
create_table :products do |t|
t.string :name, null: false
t.string :tags, array: true, null: false, default: []
t.timestamps
end
add_index :products, :tags, using: :gin
end
end
Note that t.string produces a character varying[] column, so the literals in the where clauses below are cast to varchar[]. If you declared the column with t.text, cast to text[] instead. Getting this wrong produces the "operator does not exist" error from earlier.
# contains one value
Product.where("? = ANY(tags)", "ruby")
Product.where("tags @> ARRAY[?]::varchar[]", ["ruby"]) # uses the GIN index
# contains every value in a set
Product.where("tags @> ARRAY[?]::varchar[]", %w[ruby postgres])
# contains any of several values
Product.where("tags && ARRAY[?]::varchar[]", %w[starter sql])
# does not contain a value (column is NOT NULL, so no COALESCE needed)
Product.where.not("tags @> ARRAY[?]::varchar[]", ["ruby"])
Three where clauses cover most applications. If you have several tagged columns, tag lists typed by users that need parsing, or tag clouds to generate, you will end up rebuilding the same scopes in every model, and that is the point at which a gem earns its place.
We maintain metka, a Rails gem for managing tags on Postgres array columns. It adds the scopes above by name and parses comma-separated tag lists. Since version 2.2 it writes its queries in the GIN-friendly form, and since 3.0 its exclusion scopes keep rows whose tag column is NULL.
class Product < ApplicationRecord
include Metka::Model(columns: %w[tags])
end
Product.with_all_tags("ruby, postgres") # @>
Product.with_any_tags("starter, sql") # &&
Product.without_any_tags("ruby") # NOT &&
The reason the gem exists is that we kept rewriting these scopes in client applications, and the NULL case is the one that kept going wrong: metka's own exclusion scopes dropped NULL rows until 3.0 wrapped them in NOT COALESCE(..., FALSE). The tradeoff is the usual one: a dependency to keep current in exchange for not maintaining the same twenty lines in five models. For one tagged column and two queries, write the where clauses.
Array columns show up in a large share of the Rails applications we build and maintain, usually as tags, roles or feature flags, and the NULL and index points above are the two things we check first when an existing app is filtering on one.
Frequently Asked Questions
How do I check if a Postgres array contains a value? Use = ANY: SELECT * FROM products WHERE 'ruby' = ANY(tags) returns every row whose tags array includes ruby. On an indexed column, write the same check as tags @> ARRAY['ruby'] so the query can use a GIN index. Both return identical rows.
How do I check if an array contains all of a set of values? Use the containment operator, @>: WHERE tags @> ARRAY['ruby', 'postgres'] matches only rows whose array includes every element on the right. Order does not matter, and extra elements in the column are ignored.
What is the difference between ANY, @> and && in Postgres? = ANY tests whether a single value appears in an array. @> tests whether an array contains every element of another array. && tests whether two arrays share at least one element. For a single value, = ANY and @> give the same answer, but only @> can use a GIN index.
How do I find rows where a Postgres array does not contain a value? Negate the test: WHERE NOT ('ruby' = ANY(tags)). If the column allows NULL, add tags IS NULL OR before it, or wrap the column in COALESCE(tags, '{}'), because a NULL array is excluded from both the positive and the negative query otherwise.
How do I query a Postgres array column in Rails? Declare the column with array: true, add a GIN index, and use where with a bound parameter: Product.where("tags @> ARRAY[?]::varchar[]", ["ruby"]). Cast the literal to your column type (varchar[] for t.string, text[] for t.text). For named scopes and tag-list parsing, the metka gem adds with_all_tags, with_any_tags and their without_ equivalents.