When we update an array in PostgreSQL, we can update one specific element or we can update a whole slice (a range of elements).

To update a slice, we use a syntax that references the lower and upper bounds of the slice, separated by a colon. We also provide the new values that will be assigned to each element within that slice.

Example

Suppose we have a table with the following data:

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}}

We can use the following code to update a slice in the array in the c2 column:

UPDATE t1 
SET c2[2:4] = '{7,6,5}'
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{0,7,6,5,4}

We can see that the middle three elements were updated. I specified that elements 2 to 4 be updated to the new values. In this case, the slice consists of three values and so I provided three new values.

Enlarging the Array

It’s possible to specify a range that doesn’t currently exist in the array and therefore add some elements while updating others.

For example, we could do the following:

UPDATE t1 
SET c2[4:7] = '{9,8,12,14}'
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{0,7,6,9,8,12,14}

Here I updated a slice (elements 4 and 5) and I also added two new elements to the array. The result is that the array now has seven elements.

Multi Dimensional Arrays

Our c3 column contains a multi dimensional array. Let’s update a slice in that array:

UPDATE t1 
SET c3[1:2] = '{{9,7,8},{3,7,5}}'
WHERE c1 = 1;

SELECT c3 FROM t1 WHERE c1 = 1;

Result:

{{9,7,8},{3,7,5},{7,8,9}}

In this case I updated the first two elements in the array (which are arrays themselves). To update them, I provided the two new arrays inside an outer array. If we don’t provide the outer array, we get the following error:

UPDATE t1 
SET c3[1:2] = '{9,7,8},{3,7,5}'
WHERE c1 = 1;

Result:

ERROR:  malformed array literal: "{9,7,8},{3,7,5}"
LINE 2: SET c3[1:2] = '{9,7,8},{3,7,5}'
                      ^
DETAIL:  Junk after closing right brace.

We can also target a slice within an individual array within the multi dimensional array:

UPDATE t1 
SET c3[2:2][1:2] = '{10,20}'
WHERE c1 = 1;

SELECT c3 FROM t1 WHERE c1 = 1;

Result:

{{9,7,8},{10,20,5},{7,8,9}}

Non One-Based Arrays

By default, arrays are one-based in PostgreSQL. That means the numbering starts at 1 and increments from there. But we also have the option of changing it so that the array starts with a different number. For example, we could create a zero-based array, so that the numbering starts at 0.

When we do this, we need to be mindful of the numbering system when updating a slice. Our slices would use a different range if the array starts at a different number.

To demonstrate what I mean, suppose we have the following array in another row:

SELECT c2 FROM t1 WHERE c1 = 2;

Result:

[0:4]={1,2,3,4,5}

We can see that this array is zero-based. We know that because it’s prefixed with [0:4]=, which is subscripted assignment that defines the array’s range (which in this case goes from zero to four).

Therefore if we want to update a slice (or any individual element for that matter), we need to use the range that is applicable to the array’s range.

Example:

UPDATE t1 
SET c2[1:3] = '{9,7,8}'
WHERE c1 = 2;

SELECT c2 FROM t1 WHERE c1 = 2;

Result:

[0:4]={1,9,7,8,5}

In this example I specified that elements 1 through 3 be updated. We can see that this updated the second element through the fourth (due to it being a zero-based array).

Source Array Too Small?

If you get an error stating that the source array is too small, it’s probably because you haven’t provided enough elements for the range that you specified.

Here’s an example:

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

Result:

ERROR:  source array too small

Here I specified a range of three elements but I only provided two replacement elements. This produced an error.

We can fix this by either reducing the range to match the number of elements, or by increasing the number of elements to match the range.

Providing an Array Larger than the Specified Range

If we do the opposite (i.e. provide an array that has more elements than the range that we specify), then we don’t get an error. Instead, the original array is updated, but only as far as the specified range allows:

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

SELECT c2 FROM t1 WHERE c1 = 2;

Result:

[0:4]={1,1,2,3,5}

Here I provided an array of four elements, but my range only covered three elements. In this case 1,2, and 3 were used but 4 wasn’t.