PostgreSQL offers various built-in functions to deal with the arrays. For instance, the ARRAY_LENGTH() function retrieves the array’s length, the ARRAY_REPLACE() function replaces an array element with some other element, the ARRAY_REMOVE() function removes the array elements, and so on.

Similarly, Postgres provides an ARRAY_APPEND() function that is used to append/add elements at the end of the array.

This blog post will present an in-depth overview of the ARRAY_APPEND() function via practical examples. So, let’s get started.

How to Use ARRAY_APPEND() Function in Postgres?

In PostgreSQL, the ARRAY_APPEND() function accepts two parameters an array and an element to be appended:

ARRAY_APPEND(arr, arr_element);

In the above syntax, arr is an array to be modified, while arr_element represents an element to be appended at the end of the selected array.

Example 1: How Does ARRAY_APPEND() Function Work in Postgres?

Use the below piece of code to append/add an element at the end of the array:

SELECT ARRAY_APPEND(ARRAY['John','Mike', 'AMBROSE'], 'SETH');

The output proves that a new element has been appended at the end of the given array.

How to Append Elements in an Array Using ARRAY_APPEND() Function?

Following is the syntax for appending elements to an array-type column:

UPDATE tbl_name 
SET col_name = ARRAY_APPEND(col_name, arr_element) 
WHERE condition;

Here, the UPDATE statement and SET clause are used with the ARRAY_APPEND() function to append elements to an existing array-type column.

Example: How to Use ARRAY_APPEND() Function on Table’s Data?

Let’s create a sample table named staff_data with four columns: st_id, st_name, st_phone, st_email:

CREATE TABLE staff_data(
st_id INT PRIMARY KEY,
st_name TEXT,
st_phone INT[],
st_email VARCHAR[]
);

The table named staff_data has been created successfully. Let’s insert the below-listed records into the “staff_data” table:

INSERT INTO staff_data(st_id, st_name, st_phone, st_email)
VALUES
(1, 'Mike', '{1234567811, 1112223312}', '{"mike123@gmail.com", "mike123@hotmail.com"}'),
(2, 'Tim', '{1899872311, 2123124551}', '{"tim123@gmail.com", "tim123@hotmail.com"}');

To verify the newly inserted data, execute the SELECT statement as follows:

SELECT * FROM staff_data;

The output shows that the “staff_data” table has been created successfully. Suppose Tim wants to add one more email; for this purpose, the ARRAY_APPEND() function will be used as follows:

UPDATE staff_data 
SET st_email = ARRAY_APPEND(st_email, 'tim@asdf.com') 
WHERE st_id = 2;

The output shows that modifications have been made to the selected table. Let’s verify the updated record via the SELECT statement:

SELECT * FROM staff_data;

The output shows that an email address has been appended at the end of the array.

Conclusion

Postgres provides an ARRAY_APPEND() function that is used to append/add elements at the end of the array. It accepts two parameters: an array and an element to be appended, and consequently, adds/appends the given element at the end of the array. This blog post explained various use cases of the ARRAY_APPEND() function via appropriate examples.