Posted about 2 years ago. Visible to the public.

PostgreSQL: How to add/remove/modify array values (and how to replace 1 value with multiple values)

PostgreSQL's array data type is pretty useful, but manipulating values of arrays can be awkward because of its syntax.

Consider the following users table which each example below will start from:

name topics
Alice {cats,dogs}
Bob {llamas}

(PostgreSQL uses curly braces to represent arrays, true story.)

Adding values

Use the array_cat function, or the || operator.

These calls will add the values "cats" and "mice" to users.topics:

Copy
UPDATE users SET topics = array_cat(topics, '{cats,mice}');
Copy
UPDATE users SET topics = topics || '{cats,mice}';

Note that this may produce duplicates.

name topics
Alice {cats,dogs,cats,mice}
Bob {llamas,cats,mice}

Removing values

Use the array_remove function (Postgres 9.2+) to remove 1 value from the array (all its occurences, should it appear more than once):

Copy
UPDATE users SET topics = array_remove(topics, 'cats');
name topics
Alice {dogs}
Bob {llamas}

Replacing values

Postgres 9.3+ offers array_replace. It will find 1 value (again all occurences) and replace it with 1 given other value.

Copy
UPDATE users SET topics = array_replace(topics, 'dogs', 'mice');
name topics
Alice {cats,mice}
Bob {llamas}

Replacing multiple values

To replace multiple values, you need to combine what you learned above as array_replace won't do the trick. Here is one way:

Copy
UPDATE users SET topics = array_remove(topics, 'cats') || '{mice,pandas}' WHERE topics @> ARRAY['cats'];

Note the condition. It's important as we want to append "mice" and "pandas" only to those arrays that include "cats" in the first place.

name topics
Alice {dogs,mice,pandas}
Bob {llamas}

Check the PostgreSQL documentation on Array Functions and Operators for more information, and other useful functions.

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Author of this card:

Avatar
Arne Hartherz
Last edit:
about 2 years ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandropedia