Arrays: Lists in a Column

In PostgreSQL, a column can hold a list of values, called an array. Many other databases do not have this.

For this lesson, we use a new table called products. Its tags column is a TEXT[] column: an array of text values.

SELECT id, name, tags FROM products;

Output:

 id |    name    |          tags           
----+------------+-------------------------
  1 | Laptop     | {electronics,computers}
  2 | Phone      | {electronics,mobile}
  3 | Desk       | {furniture,office}
  4 | Chair      | {furniture,office}
  5 | Headphones | {electronics,audio}
(5 rows)

psql shows an array in curly braces { }, with commas between the values.


Example 1: Get One Item with [n]

Use square brackets to get one item from an array. In PostgreSQL, the first item is number 1:

SELECT name, tags[1] AS main_tag
FROM products;

Output:

    name    |  main_tag   
------------+-------------
 Laptop     | electronics
 Phone      | electronics
 Desk       | furniture
 Chair      | furniture
 Headphones | electronics
(5 rows)

Example 2: Find Rows Where the Array Contains a Value

value = ANY(array) is true when the array contains the value:

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

Output:

 name  
-------
 Desk
 Chair
(2 rows)

Example 3: Count the Items

CARDINALITY(array) returns the number of items in an array:

SELECT name, CARDINALITY(tags) AS tag_count
FROM products
WHERE id <= 2;

Output:

  name  | tag_count 
--------+-----------
 Laptop |         2
 Phone  |         2
(2 rows)

Writing an Array

To insert an array, write the values inside '{ }', or use ARRAY[...]:

INSERT INTO products (id, name, price, tags)
VALUES (6, 'Lamp', 45.00,
        ARRAY['furniture', 'lighting']);

SELECT name, tags FROM products WHERE id = 6;

Output:

INSERT 0 1
 name |         tags         
------+----------------------
 Lamp | {furniture,lighting}
(1 row)

Tip

Arrays are good for short lists, like tags. When the list items need their own details, a separate table is usually better.

In the exercise, find the products with a certain tag.