PostgreSQL arrays

Any type can be an array: text[], integer[]. Arrays suit small lists that belong to one row, like the tags on a product. (If items need their own attributes or get queried heavily, a separate table is usually better.)

Literals: '{a,b}' or ARRAY['a','b']. Indexes start at 1: tags[1].
'usb' = ANY(tags): is the value in the array?
@> contains, <@ is contained by, && overlaps (shares any element).
unnest(arr) expands an array to rows; array_agg(x) collects rows into an array.
array_length(arr, 1), cardinality(arr), array_append, array_remove, array_to_string.

Syntax

WHERE 'usb' = ANY(tags)
WHERE tags @> ARRAY['audio','wireless']
SELECT unnest(tags) FROM products;

Examples

Edit the code and run it. Output is printed the way psql prints it.

20.1Read and search arrays

1
2
3

SELECT name, tags, tags[1] AS first_tag, cardinality(tags) AS n_tags
FROM products
WHERE 'usb' = ANY(tags);

= ANY(tags) checks membership. tags[1] is the first element (arrays are 1-based in PostgreSQL).

20.2Contains and overlaps

1
2

SELECT name, tags FROM products WHERE tags @> ARRAY['audio','wireless'];
SELECT name, tags FROM products WHERE tags && ARRAY['ergonomic','led'];

@> needs all listed tags; && needs any of them.

20.3unnest and array_agg

SELECT tag, count(*) AS products
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY products DESC, tag;

SELECT category, array_agg(name ORDER BY name) AS names
FROM products
GROUP BY category;

unnest gives one row per tag, so tags can be counted like normal rows. array_agg goes the other way, packing rows into an array.

20.4Modifying arrays

SELECT array_append(ARRAY['a','b'], 'c')     AS appended,
       array_remove(ARRAY['a','b','a'], 'a') AS removed,
       ARRAY[1,2] || ARRAY[3,4]              AS concatenated,
       array_to_string(ARRAY['x','y','z'], '-') AS as_text,
       string_to_array('red,green,blue', ',') AS from_text;

Arrays are values, so these functions return new arrays; use them in UPDATE ... SET tags = array_append(tags, 'sale') to change a column.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 20.1

List the name of products tagged wireless.

Hint

'wireless' = ANY(tags)

Solution

SELECT name FROM products WHERE 'wireless' = ANY(tags);

Exercise 20.2

Show each distinct tag and how many products use it as uses, only for tags used more than once.

Hint

unnest then GROUP BY ... HAVING count(*) > 1

Solution

SELECT tag, count(*) AS uses FROM products, unnest(tags) AS tag GROUP BY tag HAVING count(*) > 1;