PostgreSQL allows us to create arrays and store them in the database as an array type. We can then go back later and update those arrays as required.

We can either replace the whole array, append or prepend elements, or update individual elements within the array. When we update individual elements, we can update one element or a whole slice. Below are examples of updating an array in PostgreSQL.

Sample Data

The examples below use the following table:

SELECT * FROM t1;

Result:

 c1 |     c2     |              c3               
----+------------+-------------------------------
  1 | {1,2,3}    | {{1,2,3},{4,5,6}}
  2 | {4,5,6}    | {{7,8,9},{10,11,12}}
  3 | {7,8,9}    | {{7,13,14},{15,16,17}}
  4 | {10,11,12} | {{100,200,300},{400,500,600}}

Append/Prepend Elements to an Array

There are several ways to append/prepend elements to an array. One option is to use either the array_append() or array_prepend() function.

Here’s an example of using the array_append() function:

UPDATE t1 
SET c2 = array_append(c2, 4) 
WHERE c1 = 1;

SELECT * FROM t1 WHERE c1 = 1;

Result:

 c1 |    c2     |        c3         
----+-----------+-------------------
  1 | {1,2,3,4} | {{1,2,3},{4,5,6}}

Here’s an example of prepending an element with the array_prepend() function:

UPDATE t1 
SET c2 = array_prepend(0, c2) 
WHERE c1 = 1;

SELECT * FROM t1 WHERE c1 = 1;

Result:

 c1 |     c2      |        c3         
----+-------------+-------------------
  1 | {0,1,2,3,4} | {{1,2,3},{4,5,6}}

Note that with array_prepend() the first argument is the new value, whereas with array_append() it’s the second argument (i.e. the other way around).

We can also use the array_cat() function to concatenate arrays. One benefit of this function over the other two is that it works on multi dimensional arrays. Here’s an example that uses this function:

UPDATE t1 
SET c3 = array_cat( c3, ARRAY[7,8,9] ) 
WHERE c1 = 1;

SELECT * FROM t1 WHERE c1 = 1;

Result:

 c1 |     c2      |            c3             
----+-------------+---------------------------
  1 | {0,1,2,3,4} | {{1,2,3},{4,5,6},{7,8,9}}

Update an Individual Element

We can update individual array elements by specifically referencing the element in our UPDATE statement:

UPDATE t1 
SET c3[1][2] = 250
WHERE c1 = 4;

SELECT * FROM t1 WHERE c1 = 4;

Result:

 c1 |     c2     |              c3               
----+------------+-------------------------------
  4 | {10,11,12} | {{100,250,300},{400,500,600}}

In this case I updated a two dimensional array, which is reflected in the element reference.

Update a Whole Slice

We can also update a whole slice of an array. So instead of updating just a single element, we can update multiple elements in one go:

UPDATE t1 
SET c2[2:3] = '{51,62}'
WHERE c1 = 2;

SELECT * FROM t1 WHERE c1 = 2;

Result:

 c1 |    c2     |          c3          
----+-----------+----------------------
  2 | {4,51,62} | {{7,8,9},{10,11,12}}

In this example my slice consisted of just two elements. These were referenced using the colon syntax (with the first element of the slice on the left and the last element on the right).

Enlarge an Array

It’s also possible to enlarge an array when updating it. What I mean is that we can add elements at a specific subscript where there’s a gap between that and the nearest actual subscript in the array. When we do this, any element in that gap is populated with NULL.

Example:

UPDATE t1 
SET c2[6] = 23
WHERE c1 = 3;

SELECT * FROM t1 WHERE c1 = 3;

Result:

 c1 |          c2          |           c3           
----+----------------------+------------------------
  3 | {7,8,9,NULL,NULL,23} | {{7,13,14},{15,16,17}}

Here I added an element at position 6, even though the existing array only went up to position 3. Therefore three elements were appended to the array, with the last one being the one specified. That one was assigned its value as specified, and the other two were assigned as NULL.