SQLearnAcademy
←Function reference
Arrays and seriesPostgreSQL only

ARRAY_APPEND

Returns an array with an element added at the end.

Syntax

ARRAY_APPEND(<array>, <element>)
ARRAY_PREPEND(<element>, <array>)
<array> || <array>

Adds one element to the end of an array, ARRAY_PREPEND adds it to the start, and the || operator joins two arrays. The array itself doesn't change: the functions return a new one. To remove an element, use ARRAY_REMOVE.

Add to the end and to the start
SELECT ARRAY_APPEND(ARRAY['sql', 'postgres'], 'database') AS appended,
ARRAY_PREPEND('intro', ARRAY['sql', 'postgres']) AS prepended;
appendedprepended
["sql","postgres","database"]["intro","sql","postgres"]
Join two arrays
SELECT ARRAY['sql', 'postgres'] || ARRAY['database', 'index'] AS combined;
combined
["sql","postgres","database","index"]
Start from an empty one
SELECT ARRAY_APPEND(ARRAY[]::text[], 'first') AS from_empty,
ARRAY_APPEND(NULL, 'first') AS from_null;
from_emptyfrom_null
["first"]["first"]

Watch out

The argument order differs: ARRAY_APPEND(<array>, <element>), but ARRAY_PREPEND(<element>, <array>). Mix them up and ARRAY_PREPEND(ARRAY['a'], 'b') throws a puzzling could not find array type for data type text[].

You can append to NULL: ARRAY_APPEND(NULL, 'sql') gives a one-element array, not NULL.

The functions only return a new array. To change the data in a table, you need UPDATE … SET tags = ARRAY_APPEND(tags, 'sql').