Changing Data in Arrays: array_append(), array_remove()

Sometimes you don’t just want to read arrays, but actually change them: add a new value, remove an old one, or update the contents. Luckily, PostgreSQL has special functions for this that let you easily manage array contents without rewriting them by hand: array_append() and array_remove(). Let’s see how they work.

Adding Elements to an Array with array_append()

The array_append() function is used to add a new element to the end of an array. It takes two arguments:

1. The original array you want to modify.
2. The value you want to add to the array.

Example 1: Simple Array of Numbers

Let’s say we have a table called projects with a team_members column, where we store arrays of user IDs working on a project.

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    team_members INT[]
);

-- Let's add a few rows
INSERT INTO projects (name, team_members)
VALUES
('Project A', ARRAY[1, 2, 3]),
('Project B', ARRAY[4, 5]);

Now we have a project called "Project A", and we want to add a new member with ID 7 to the team_members array. Let’s use array_append():

UPDATE projects
SET team_members = array_append(team_members, 7)
WHERE name = 'Project A';

-- Let’s check the result:
SELECT * FROM projects;

Result:

As you can see, the member with ID 7 was successfully added to the array!

Example 2: Adding Text Elements

You can use array_append() with arrays of other types too. For example, let’s add a new category to a product’s categories array.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    categories TEXT[]
);

-- Add a product with categories
INSERT INTO products (name, categories)
VALUES
('Smartphone', ARRAY['Electronics', 'Gadgets']),
('Laptop', ARRAY['Computers', 'Electronics']);

-- Add a new category "Popular" for "Smartphone"
UPDATE products
SET categories = array_append(categories, 'Popular')
WHERE name = 'Smartphone';

-- Let’s check the result:
SELECT * FROM products;

Result:

Removing Elements from an Array with array_remove()

If array_append() is like adding a new task to a list, then array_remove() is the tool for crossing off a task you’ve already done. This function takes as arguments:

1. The original array.
2. The value you want to remove.

Example 1: Removing an Element from a Number Array

Let’s go back to the projects table. We found out that the member with ID 7 is no longer working on "Project A". Let’s remove them from the team_members array:

UPDATE projects
SET team_members = array_remove(team_members, 7)
WHERE name = 'Project A';

-- Let’s check the result:
SELECT * FROM projects;

Result:

The array_remove() function successfully removed the member with ID 7.

Example 2: Removing a Text Element

Now let’s look at the products table. If the "Popular" category is no longer relevant for the smartphone, we can remove it:

UPDATE products
SET categories = array_remove(categories, 'Popular')
WHERE name = 'Smartphone';

-- Let’s check the result:
SELECT * FROM products;

Result:

Combining array_append() and array_remove()

Sometimes you want to make sure an element isn’t already in the array before adding it. You can do this by combining functions:

Example: Adding Unique Elements

Let’s make sure the member with ID 7 only gets added to the array if they’re not already there. We’ll use a check with array_remove():

UPDATE projects
SET team_members = array_append(team_members, 7)
WHERE name = 'Project A' AND NOT (team_members @> ARRAY[7]);

-- Let’s check the result:
SELECT * FROM projects;

Explanation:

• The @> operator checks if the array contains the element 7.
• If team_members already contains 7, nothing gets added.

Common Mistakes and Tips

1. Trying to Add NULL to an Array. If you try to use array_append() with NULL (like array_append(team_members, NULL)), it’ll just add a NULL value to the array. You won’t get an error, but the result might not be what you expected. To avoid this, check that the value isn’t NULL before adding it.

-- Example check:
UPDATE projects
SET team_members = array_append(team_members, COALESCE(NULL, -1))
WHERE name = 'Project A';

2. Mixing Data Types in an Array. Arrays in PostgreSQL have to contain values of the same type. For example, you can’t add a string to a number array:

-- This will throw an error:
UPDATE projects
SET team_members = array_append(team_members, 'example');

To avoid this, make sure your data types match. If you’re not sure, cast the value to the right type using :::

-- Example of type casting:
UPDATE projects
SET team_members = array_append(team_members, '5'::INT);