PostgreSQL's array data type Show archive.org snapshot 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.)
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} |
'{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[]
.WHERE topics @> 'value'
, better: WHERE topics @> '{value}'
Check the PostgreSQL documentation on Array Functions and Operators for more information, and other useful functions.