If you’re getting an error that reads “source array too small” when updating an array in PostgreSQL, it’s probably because you’re passing a slice that’s smaller than your specified range. For example, if you specify a range that covers four elements but provide an array that contains just three elements, then you’d get this error.

To fix the error, be sure to provide an an array that contains at least as many elements as are specified in the range.

Example of Error

Suppose we have the following array:

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

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

And suppose we try to update it like this:

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

Result:

ERROR:  source array too small

I got the error because my range specifies three elements but my array only contains two elements.

Solution

We can fix this issue either by reducing the range to match the array:

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

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{1,2,6,9,8,12,14}

Or we can increase the number of elements in the array to match the range:

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

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{1,2,3,9,8,12,14}

Normally we would want the number of elements to match the range exactly, but it is possible to provide a larger slice. When we do this, the specified range takes precedence. Therefore, it will take as many elements from the new array as are specified in the range.

Here’s an example of what I mean:

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

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{5,6,3,9,8,12,14}

In this case only the first two elements were updated, even though I provided an array with three elements. That’s because the range was only two.