Using Arrays in PostgreSQL: A Guide

When you think about SQL images of tables, SELECT-Queries and the concept of one row per data point with no nested structure probably come to mind.

But what if you wanted to store a list of numbers, products and more within a row?

You could expand the list into many rows, but this can lead to a jungle of rows that may be hard to keep track of. Luckily, PostgreSQL offers the array data type which can be used for storing and manipulating lists.

What Is PostgreSQL Array?

With ARRAY, PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type or domain can be created.

How to Create Arrays in PostgreSQL

Arrays can take on many forms, and there are multiple ways to declare them.

You can use the keyword ARRAY after the data type declaration for a column to indicate that you want to create an Array of the before declared data type, as shown below.

You could also follow the data type declaration with square brackets,such as text[], but I find this to be less explicit. And it doesn’t conform to the SQL Standard, which the keyword version does.

In the following example, I create a small table to mock a shopping card and populate it with some permutations of multiple products. If you run the complete query, you can also see how arrays are represented as output.

CREATE TEMP TABLE shopping_cart (
  cart_id serial PRIMARY KEY,
  products text ARRAY
  );
INSERT INTO
  shopping_cart(products)
VALUES
  (ARRAY['product_a', 'product_b']),
  (ARRAY['product_c', 'product_d']),
  (ARRAY['product_a', 'product_b', 'product_c']),
  (ARRAY['product_a', 'product_b', 'product_d']),
  (ARRAY['product_b', 'product_d']);
SELECT
  *
FROM
  shopping_cart;

Another way to add an array to a table is by using the {}-syntax instead of the ARRAY keyword with brackets. Both ways work. You just have to pay close attention to the quotations with the curly brackets. Again, I find the keyword syntax more explicit and the brackets closer to array representations in other programming languages, but both work fine.

-- insert using {} syntax
INSERT INTO
  shopping_cart(products)
VALUES
  ('{"product_a", "product_d"}');
  
SELECT
  *
FROM
  shopping_cart;

More on Data Science10 Advanced SQL Concepts You Should Know for Data Science Interviews

1. Unnesting the Array in PostgreSQL

What if you actually needed to explode or unnest the array for specific rows to join some product information table or perform other operations on a list item basis?

No problem. Arrays in PostgreSQL can be easily unnested with the UNNEST keyword:

-- unnest array
SELECT
  cart_id,
  UNNEST(products) AS products
FROM
  shopping_cart
WHERE
  cart_id IN (3, 4);

As expected UNNEST creates one row per item in the respective array in the original row:

2. Accessing Array Items in PostgreSQL

You might wonder whether you have to unnest an array each time you want to access a single item for further operations. If that’s the case, an array structure might not be a good idea after all. Fortunately, arrays in PostgreSQL allow access through indexing with brackets and slices, as shown below. This allows for easy selection of for example the first item in every list or a specific range of items in a list.

Indexing in PostgreSQL arrays starts at one, not at zero, which may differ from what you are used to in other programming languages.

-- access array elements
SELECT
  cart_id,
  products[1] AS first_product -- indexing starts at 1
FROM
  shopping_cart;

Slices work with the [start:end]-syntax. Array item access through slicing for all rows with more than two array elements.

-- return slice of array
SELECT
  cart_id,
  products [1:2] AS first_two_products
FROM
  shopping_cart
WHERE
  CARDINALITY(products) > 2;

If you aren’t sure how many items your array has, or if you want to filter by the size of an array, as I have done above, you can use the CARDINALITY keyword. This will return the number of items in the array as an integer.

-- return length of array
SELECT
  cart_id,
  CARDINALITY(products) AS num_products
FROM
  shopping_cart;

3. Filter by an Array Element in PostgreSQL

What if you wanted to select all shopping carts that have a specific product in the array? Or any other filter operation that is based on an array item? No problem, PostgreSQL allows you to use the item value in the WHERE clause in conjunction with the ANY keyword. 

Below I am filtering for rows that contain ‘product_c’ in the products array column. This way of filtering is most useful for single filter values.

-- return if  product in array
SELECT
  cart_id,
  products
FROM
  shopping_cart
WHERE
  'product_c' = ANY (products);

4. Using the Contains Operator for PosgreSQL Array

You could also extend the filter logic from above and ask for any shopping card that contains a specific sub-array. Here I am using the ‘@>’ operator, which stands for ‘contains’. It can be read as follows:

“The product array contains the array [‘product_a’, ‘product_b’].”

-- contains
SELECT
  cart_id,
  products
FROM
  shopping_cart
WHERE
  products  @> ARRAY['product_a', 'product_b'];

5. Updating the Array in PostgreSQL

Arrays and array values can be updated similarly to other data types, with an UPDATE … SET … clause, as shown below. You can either update a single array item through indexing or the whole array.

-- update arrays 
UPDATE
  shopping_cart
SET
  products = ARRAY['product_a','product_b','product_e']
WHERE
  cart_id = 1;
UPDATE 
  shopping_cart
SET
  products[1] = 'product_f'
WHERE
  cart_id = 2;
SELECT
  *
FROM
  shopping_cart
ORDER BY cart_id;

6. Prepend and Append an Array in PostgreSQL

If you wish to append or prepend (inserting before the current values) an array you can use the ARRAY_APPEND and ARRAY_PREPEND functions accordingly. Make sure to note the difference in the argument order, which is different for each function but follows an intuitive order.

-- update by appending and prepending
UPDATE
  shopping_cart
SET
  products = ARRAY_APPEND(products, 'product_x')
WHERE
  cart_id = 1;
UPDATE 
  shopping_cart
SET
  products = ARRAY_PREPEND('product_x', products)
WHERE
  cart_id = 2;
SELECT
  *
FROM
  shopping_cart
ORDER BY cart_id;

7. Removing Array Items in PostgreSQL

If you want to get rid of a specific Array item, you can use UPDATE…SET… together with the ARRAY_REMOVE function. This allows you to either remove an item from the arrays for all rows or just one specific item when used with a WHERE clause.

-- remove element from array

UPDATE
  shopping_cart
SET
  products = array_remove(products, 'product_e')
WHERE cart_id = 1;
SELECT
  *
FROM
  shopping_cart
ORDER BY cart_id;

More on Data Science10 Common SQL Interview Questions

8. Concat Arrays in PostgreSQL

Lastly, you can concatenate PostgreSQL arrays into one larger array with ARRAY_CAT, as follows:

-- concat arrays
SELECT
  cart_id,
  ARRAY_CAT(products, ARRAY['promo_product_1', 'promo_product_2'])
FROM shopping_cart
ORDER BY cart_id;

Can PostgreSQL Arrays do even more? Of course, head over to the PostgreSQL docs to find many additional possibilities.

You now have all the tools to use lists and nested structures within your SQL tables. While these powerful tools allow you to break out of the one-row-one-record paradigm, you should only use them if they provide real value and can’t be substituted by a more traditional approach. There are good reasons for existing SQL best practices, so your reasons for going around them should be even better.