How to use PostgreSQL arrays

In this small article, I am going to show you how to use arrays in PostgreSQL.

PostgreSQL has a built-in array type.

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.

Let's see how we can use it.
Let's assume an employee_vacations table, that stores each employee's name and the days of the week when they have a vacation.
This table can be created with the following statement:

CREATE TABLE employee_vacations (
    name            TEXT,
    vacations  TEXT[]
);

We annotate that a column is an array of a specific type T by writing T[], just like we would in a normal programming language.

Inserting Rows

Let's add some rows to it.

INSERT INTO employee_vacations
    ("name", "vacations")
VALUES 
    ('Alice', '{"Friday", "Saturday"}');
INSERT INTO employee_vacations
    ("name", "vacations")
VALUES 
    ('Bob', '{"Saturday", "Sunday"}');

Filtering

Let's select employees who have Friday as their first vacation.

SELECT * FROM employee_vacations WHERE vacations[1] = 'Friday';

The above query will return this:

name	vacations
Alice	{Friday,Saturday}

Note that we wrote vacations[1] instead of vacations[0].
That's because unlike most programming languages, arrays in PostgreSQL and one-indexed.

Now you're probably wondering, how to select rows whose array contain specific values, regardless of their index.

Here's how to do it:

SELECT * FROM employee_vacations WHERE ARRAY['Saturday'] <@ vacations;

This asks PostgreSQL to find all the rows whose vacations' array field contains all the values of the array we provided, which is 1 value, 'Saturday', but we could add more values if we want.

This is the result we get:

name	vacations
Alice	{Friday,Saturday}
Bob	{Saturday,Sunday}

Removing Values

Here's how to remove values from an array:

UPDATE employee_vacations SET vacations = array_remove(vacations, 'Saturday') WHERE ARRAY['Saturday'] <@ vacations;

This removes Saturday from all rows whose array contains the Saturday value.

Appending Values

Here's how to append values to an array:

UPDATE employee_vacations SET vacations = array_append(vacations, 'Saturday');

This adds Saturday to the vacations array for all rows.

The Ninjaneer Blog