When it comes to prepending a values to arrays in PostgreSQL, we have a number of options available to us. Below are four methods we can use to prepend a value to an array in PostgreSQL.

Option 1: The || Operator

One option is to use the || operator. This operator allows us to concatenate two values. This includes concatenating a non-array value with an array. Therefore, we can use this operator to prepend values to an array.

Example:

SELECT 0 || ARRAY[ 1, 2 ];

Result:

{0,1,2}

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

SELECT ARRAY[ -1, 0 ] || ARRAY[ 1, 2 ];

Result:

{-1,0,1,2}

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

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

Result:

{Bicycle,Car,Bus}

Here it is without the array:

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

Result:

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

Returned an error.

Option 2: The array_prepend() Function

The array_prepend() function allows us to prepend a value to an array. The first argument is the value we want to prepend, and the second argument is the array:

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

Result:

{Bicycle,Car,Bus}

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_prepend( '{Bicycle}', ARRAY[ 'Car', 'Bus' ]);

Result:

{"{Bicycle}",Car,Bus}

There’s also an array_append() function that is used for appending a value to an array. When we use that function we need to swap the arguments around (so that the array comes first, followed by the value to append).

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( '{Bicycle}', ARRAY[ 'Car', 'Bus' ]);

Result:

{Bicycle,Car,Bus}

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[ 'Bicycle' ], ARRAY[ 'Car', 'Bus' ]) AS "1",
    array_cat( '{Bicycle}', '{"Car","Bus"}') AS "2";

Result:

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

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 = 2;

Result:

{4,51,62}

By default, PostgreSQL arrays are one-based. This means that the above array’s subscripts go 1, 2, 3. Therefore, the first value (4) is at subscript 1. If we want to prepend a value, we need to change the array to be at least zero-based. I say “at least” because we can go further. We can have the subscripts start at a negative value if required.

With that said, we can use an UPDATE statement to prepend a value to that array, like this:

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

SELECT c2 FROM t1 WHERE c1 = 2;

Result:

[-2:3]={7,NULL,NULL,4,51,62}

Here, not only did we prepend the value, but we created a gap between it and the next 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[0] = 7 instead of SET c2[-2] = 7 and the element would’ve been assigned to subscript 0 (and there would be no gap between it and the next element). Either way, this demonstrates that we can enlarge an array and prepend an element to the start if we wish.

When we change an array from being one-based, the array is prepended with the new range. So we can see in our example that [-2:3]= has been prepended to the array. Therefore we know that the array is not one-based. It starts at -2 and goes up to 3.

There’s nothing to stop us from enlarging the array exclusively with NULLs either:

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

SELECT c2 FROM t1 WHERE c1 = 2;

Result:

[-7:3]={NULL,NULL,NULL,NULL,NULL,7,NULL,NULL,4,51,62}