How to Append to Array in PostgreSQL: Syntax and Examples

In PostgreSQL, you can append an element to an array using the array_append(array, element) function. This function returns a new array with the element added at the end without modifying the original array.

Syntax

The array_append function takes two arguments:

• array: The original array you want to add an element to.
• element: The value you want to append to the array.

The function returns a new array with the element added at the end.

sql

array_append(anyarray, anyelement) RETURNS anyarray

Example

This example shows how to append a new number to an integer array and a new string to a text array.

sql

SELECT array_append(ARRAY[1, 2, 3], 4) AS appended_int_array;

SELECT array_append(ARRAY['apple', 'banana'], 'cherry') AS appended_text_array;

Output

appended_int_array
{1,2,3,4}

appended_text_array
{apple,banana,cherry}

Common Pitfalls

One common mistake is trying to modify the original array directly, but array_append returns a new array and does not change the original. Also, ensure the element type matches the array type to avoid errors.

Wrong usage example:

UPDATE my_table SET my_array = my_array || 'new_element'; -- This works only if types match and uses concatenation

Right usage example:

UPDATE my_table SET my_array = array_append(my_array, 'new_element');

Quick Reference

Use array_append(array, element) to add one element at the end of an array.

For adding multiple elements, use array concatenation with ||.

Operation	Syntax	Description
Append one element	array_append(array, element)	Returns a new array with the element added at the end.
Concatenate arrays	array1 || array2	Joins two arrays into one.

Key Takeaways

Use array_append(array, element) to add an element to the end of an array in PostgreSQL.

array_append returns a new array and does not modify the original array in place.

Ensure the element type matches the array type to avoid errors.

For adding multiple elements, use the array concatenation operator ||.

Remember to assign the result back if you want to update a table column.