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

UI/UX Design by

We make sure that your target audience has the best possible experience with your digital product. You get:

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

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}

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}

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}

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}

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.