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.