In PostgreSQL it’s possible to make an array larger without providing any values. For example, we can take an array with three elements, and enlarge it so that it contains say, five elements, with the two extra elements being NULL.

We can also do it so that some of the extra elements are non-NULL and others are NULL.

Example

Suppose we have the following array in a column:

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{1,2,3}

The following statement enlarges that array:

UPDATE t1 
SET c2[5] = 5
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{1,2,3,NULL,5}

In this case I specified that the value 5 is appended to the array at position five. Given the array only had three positions, it had to create two more. And it had to assign NULL to the fourth element, given I didn’t specify a value for that element.

We can also explicitly specify NULL as the value for the element:

UPDATE t1 
SET c2[7] = NULL
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

{1,2,3,NULL,5,NULL,NULL}

In this case all added elements are NULL.

Prepending Elements

It’s also possible to enlarge the array by pushing elements to the front of the array:

UPDATE t1 
SET c2[-2] = NULL
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

[-2:7]={NULL,NULL,NULL,1,2,3,NULL,5,NULL,NULL}

By default, arrays are one-based in PostgreSQL. If we prepend values to a one-based array, then the first element will be assigned zero, then any subsequent values will have a negative subscript. In my case, I specified a subscript of -2, which resulted in three elements being prepended to the array.

The array is no longer one-based and it now includes subscripted assignment that specifies the array’s new subscript range.

Concatenation

Another way to do it is with the concatenation operator. For example, we could do this:

UPDATE t1 
SET c2 = c2 || ARRAY[NULL,NULL]::integer[]
WHERE c1 = 1;

SELECT c2 FROM t1 WHERE c1 = 1;

Result:

[-2:9]={NULL,NULL,NULL,1,2,3,NULL,5,NULL,NULL,NULL,NULL}

The array now has two extra NULL elements, and it goes up to subscript 9.