Read more

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

Arne Hartherz
October 05, 2015Software engineer at makandra GmbH

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

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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:

UPDATE users SET topics = array_cat(topics, '{cats,mice}');
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):

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.

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:

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}

Notes on working with String arrays in PostgreSQL

  • Prefer the '{one,two}' syntax. If you use the ARRAY[] syntax, you'll need to add a type in order to perform operations with e.g. a string array column: ARRAY['one', 'two']::varchar[].
  • Avoid working with plain strings, always wrap them as arrays when you are working with arrays. E.g. bad: WHERE topics @> 'value', better: WHERE topics @> '{value}'

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

Arne Hartherz
October 05, 2015Software engineer at makandra GmbH
Posted by Arne Hartherz to makandra dev (2015-10-05 16:50)