Any PostgreSQL type can be an array. Declare the column as text[] or int[], write values as '{a,b}' or ARRAY['a', 'b'], and read elements with tags[1] (indexes start at 1). To check whether an array contains a value, use 'value' = ANY(tags) or tags @> ARRAY['value'].
Posts 1 and 2 have the tag sql. tags[1] is the first element and cardinality counts them. Both literal forms store the same value; ARRAY[...] is easier when the elements come from expressions or contain commas.
Check if an array contains a value
Four operators cover membership:
| Test | Meaning | True for '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | one value is in the array | yes |
tags @> '{sql,json}' | the array contains all of these | yes |
tags <@ '{sql,json,go}' | every element is in this list | yes |
tags && '{go,json}' | the arrays share at least one element | yes |
Only post 1 has both tags. Posts 2 and 3 share an element with {go,json}. Post 3 is the only one where no_go is false. ALL is the counterpart of ANY: the comparison must hold for every element.
= ANY follows SQL's NULL rules. If the value is not found and the array holds a NULL, the result is NULL, not false. So WHERE NOT ('x' = ANY(tags)) can drop rows you expected. @> and && return true or false.
A GIN index makes @>, <@, && and = fast on large tables. It does not help = ANY(column), so for indexed lookups write tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Turn an array into rows with unnest
unnest returns one row per element, so you can count, join or filter on individual elements:
sql appears in two posts and the other tags in one each. Add WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) to get each element's position too. To collect rows back into an array, use array_agg (see string_agg and array_agg).
Add, remove and change elements
This returns {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} and a, b, c. In an UPDATE the same functions change a stored array: SET tags = array_append(tags, 'new') or SET tags = array_remove(tags, 'old'). array_remove deletes every copy of the value. string_to_array('a,b,c', ',') splits text into an array.
Get the length of an array
cardinality(tags) returns the number of elements, and 0 for an empty array. array_length(tags, 1) returns the length of dimension 1, and NULL for an empty array. So WHERE array_length(tags, 1) = 0 never matches. Test for an empty array with cardinality(tags) = 0 or tags = '{}'.
PostgreSQL arrays can have more than one dimension (int[][]), but every sub-array must have the same length, and the declared size (int[3]) is not enforced. For nested or uneven data, JSONB is usually a better fit.
Frequently Asked Questions
Are PostgreSQL arrays 0-based or 1-based?
1-based by default: tags[1] is the first element. An index past the end returns NULL instead of raising an error.
What is the difference between IN and = ANY in PostgreSQL?
x IN (1, 2, 3) takes a list written in the query; x = ANY(array) takes a single array value. ANY suits application code, because you can pass one array parameter (WHERE id = ANY($1)) instead of building a list of placeholders.
Should I use an array column or a separate table?
An array suits a short list that you read and write together with its row, such as tags. When the items need their own columns, foreign keys, or are updated one by one, a separate table with one row per item is easier to query and constrain.
How do I check if an array is empty in PostgreSQL?
Use cardinality(arr) = 0 or arr = '{}'. array_length(arr, 1) = 0 never matches, because array_length returns NULL for an empty array.