We have several options when it comes to appending elements to arrays in PostgreSQL. We can use an operator to concatenate the value to the array or we can use a function to do the job.

Below are four ways to append elements to arrays in PostgreSQL.

Option 1: The || Operator

One option is to use the || operator. We can use this operator to concatenate two arrays, or an array with a non-array value. Either way, we can append values to an array with this operator.

Example:

SELECT ARRAY[ 1, 2 ] || 3;

Result:

{1,2,3}

We can append multiple values by including them in another array:

SELECT ARRAY[ 1, 2 ] || ARRAY[ 3, 4 ];

Result:

{1,2,3,4}

Or we could use multiple || operators:

SELECT ARRAY[ 1, 2 ] || 3 || 4;

Result:

{1,2,3,4}

We may sometimes need to include the value in an array, depending on the value/type:

SELECT ARRAY[ 'Car', 'Bus' ] || '{Bicycle}';

Result:

{Car,Bus,Bicycle}

Here it is without the array:

SELECT ARRAY[ 'Car', 'Bus' ] || 'Bicycle';

Result:

ERROR:  malformed array literal: "Bicycle"
LINE 1: SELECT ARRAY[ 'Car', 'Bus' ] || 'Bicycle';
                                        ^
DETAIL:  Array value must start with "{" or dimension information.

I passed a string but it didn’t like that.

Option 2: The array_append() Function

As its name suggests, the array_append() function allows us to append a value to an array. We pass the array as the first argument, followed by the value we want to append:

SELECT array_append( ARRAY[ 'Car', 'Bus' ], 'Bicycle');

Result:

{Car,Bus,Bicycle}

Note that we didn’t need to put the string in its own array. If we’d done that, we’d get the following:

SELECT array_append( ARRAY[ 'Car', 'Bus' ], '{Bicycle}');

Result:

{Car,Bus,"{Bicycle}"}

There’s also an array_prepend() function that is used for prepending a value to an array. One thing to be aware of is that we need to swap the arguments around with that function (the new value comes first, followed by the array).

Option 3: The array_cat() Function

Another option is the array_cat() function. This function concatenates two arrays. Therefore we can do the following:

SELECT array_cat( ARRAY[ 'Car', 'Bus' ], '{Bicycle}');

Result:

{Car,Bus,Bicycle}

In this example I used two different methods for constructing the arrays (one uses the ARRAY constructor, the other uses an array literal), but there’s no real reason for this. I could just have easily used the same method for each array.

For example:

SELECT 
    array_cat( ARRAY[ 'Car', 'Bus' ], ARRAY[ 'Bicycle' ]) AS "1",
    array_cat( '{"Car","Bus"}', '{Bicycle}') AS "2";

Result:

         1         |         2         
-------------------+-------------------
 {Car,Bus,Bicycle} | {Car,Bus,Bicycle}

The same applies to the above methods. We can use whichever method we want to create the arrays.

Option 4: Assign a New Element

We can also simply assign a new element at a position that doesn’t yet exist in the array.

Suppose we have a row in a database table with the following array:

SELECT c2 FROM t1 WHERE c1 = 4;

Result:

{10,11,12}

We can use an UPDATE statement to append a value to that array, like this:

UPDATE t1 
SET c2[6] = 15
WHERE c1 = 4;

SELECT c2 FROM t1 WHERE c1 = 4;

Result:

{10,11,12,NULL,NULL,15}

Here, not only did we append the value, but we created a gap between it and the previous element. This resulted in NULL being assigned to the elements in between. Of course, I didn’t need to create the gap. I could’ve just used SET c2[4] = 15 instead of SET c2[6] = 15 and the element would’ve been assigned to subscript 4 (and there would be no gap between it and the previous element). Either way, this demonstrates that we can enlarge an array and append an element to the end if we wish.

While we’re on the subject, there’s nothing to stop us from enlarging the array exclusively with NULLs either:

UPDATE t1 
SET c2[10] = NULL
WHERE c1 = 4;

SELECT c2 FROM t1 WHERE c1 = 4;

Result:

{10,11,12,NULL,NULL,15,NULL,NULL,NULL,NULL}