Using Arrays (`ARRAY`)

Today we’re gonna chat a bit about arrays — one of PostgreSQL’s powerful tools that lets you store a list of values of the same type inside a single column. We’ll dive deeper into arrays in later levels.

When you hear “arrays,” you probably think of lists. Like, think about how you write a shopping list before hitting the store: apples, bananas, milk, bread. In PostgreSQL, an array is kinda like that list, where you can keep several values in one field, and every value is of the same data type. For example, an array of numbers, an array of texts, an array of dates, and so on.

Let’s check out a few examples.

Array of numbers:

{1, 2, 3, 4}

Array of strings:

{"apple", "banana", "cherry"}

Array of dates:

{2024-10-01, 2024-10-02, 2024-10-03}

Arrays let you store a bunch of values in one field, saving space in your table and making your queries more flexible.

Why use arrays? Main benefits

Compact data storage: store multiple values in a single field. This is super handy for data that’s logically connected. Like, a list of tags for an article or a list of grades for a student. Do you really wanna make a separate table just for a couple of tags?

Easy querying: arrays let you grab a whole list of values from one field, and it’s easy to mess with individual elements of the array too.

Performance: in some cases, arrays help you avoid making extra tables for related data, which can make your database architecture simpler and speed up development. And that’s always nice.

How do you work with arrays in PostgreSQL?

Creating an array in SELECT

You can use the ARRAY[] constructor to make an array. Here’s an example:

-- Example of a number array
SELECT ARRAY[1, 2, 3, 4];

-- Example of a string array
SELECT ARRAY['apple', 'banana', 'cherry'];

Too easy, right? Let’s make a table and try selecting something from it.

Creating a table with arrays

First, let’s create a table where one of the columns is an array.

Let’s see what we’ve got:

SELECT * FROM students;

Extracting data from arrays

PostgreSQL gives you a bunch of functions and operators for pulling data out of arrays.

Extracting an array element

To get an array element by its index, use square brackets []. Heads up: indexing starts at 1 (not 0 like in most programming languages).

-- Get the first subject for each student
SELECT name, subjects[1] AS first_subject FROM students;

And here’s what you get:

Extracting the whole array row

To get all the elements of an array, just use a regular SELECT:

SELECT name, subjects FROM students;

Searching for values inside an array

Sometimes you need to check if an array contains a specific value. For that, use the ANY operator.

-- Select students who study "Matematika"
SELECT name
FROM students
WHERE 'Matematika' = ANY(subjects);

Query result:

PostgreSQL has a ton of functions and operators for working with arrays, but you’ll learn about those in future levels :P

Common mistakes when working with arrays

Mixing up 0-based and 1-based indexing.

In PostgreSQL, array indexing starts at 1. If you’re used to languages like Python or JavaScript, this can totally trip you up.

Empty arrays.

An empty array {} and NULL are different things in PostgreSQL. If you’re comparing arrays, don’t mix these up.

Working with big arrays.

Storing a huge amount of data in an array can slow down your queries. Use arrays for small amounts of data.