How ARRAY_PREPEND() Works in PostgreSQL

In PostgreSQL, the array_prepend() function prepends an element to the start of an array.

The first argument is the element to prepend, and the second argument is the array.

Example

Here’s an example to demonstrate:

SELECT array_prepend('Bird', array['Cat', 'Dog']);

Result:

{Bird,Cat,Dog}

In this case I prepended the array with Bird.

Here’s a database example:

SELECT 
    c2,
    array_prepend(0, c2)
FROM t1
WHERE c1 = 1;

Result:

     c2      | array_prepend 
-------------+---------------
{1,2,3,4,5} | {0,1,2,3,4,5}

Concatenation Equivalent

The array_prepend() function does the same thing that the || operator does when we use the array to the right of the operator, and the new element to the left.

Therefore, we could rewrite the previous example as follows:

SELECT 
    c2,
    0 || c2 AS "Concatenated"
FROM t1
WHERE c1 = 1;

Result:

     c2      | Concatenated  
-------------+---------------
{1,2,3,4,5} | {0,1,2,3,4,5}

Prepending Elements

There’s also an array_append() function that appends elements to the end of the array. We switch the arguments around when using that function though; the array comes first, followed by the element to append. This function is just like using the || operator with the array to the left of the operator, and the new element to the right.