In PostgreSQL, the UPDATE statement is used to modify the whole array or only specific indexes of an array. However, Postgres provides numerous built-in functions that can be used with the UPDATE statement to modify the arrays more efficiently, such as ARRAY_PREPEND(), ARRAY_REMOVE(), ARRAY_CAT(), etc. Using these methods, users can add new elements to arrays, delete or replace unnecessary elements from arrays, concatenate multiple arrays, etc.
This post will discuss how to modify the Postgres arrays using various built-in array functions.
How to Update Arrays Using Built-in Array Functions in Postgres?
In this section we will learn how to update an array using the following array functions:
- ARRAY_PREPEND()
- ARRAY_APPEND()
- ARRAY_CAT()
- ARRAY_REMOVE()
- ARRAY_REPLACE()
Sample Table
A sample table named “std_info” has already been created with the following records:
SELECT * FROM std_info;
The above-provided array functions will be used in the following examples to update the “std_num” array.
Example 1: Updating an Array Using ARRAY_PREPEND() Function
Use the ARRAY_PREPEND() function to add a new element at the start of a given array:
UPDATE std_info SET std_num = ARRAY_PREPEND(50, std_num);
Execute the “SELECT *” command to validate the modified array:
SELECT * FROM std_info;
The output clarifies that a new element has been successfully inserted at the start of the given array.
Example 2: Updating an Array Using ARRAY_APPEND() Function
The ARRAY_APPEND() function allows us to add a new element at the end of the given array:
UPDATE std_info SET std_num = ARRAY_APPEND(std_num, 55);
Let’s verify the updated array using the following command:
SELECT * FROM std_info;
The output snippet validates that the selected array has been modified.
Example 3: Updating an Array Using ARRAY_CAT() Function
Use the ARRAY_CAT() function to combine/concatenate an array with any other array:
UPDATE std_info SET std_num = ARRAY_CAT(std_num, ARRAY[65, 40, 72]);
Run the SELECT query to check the modified array:
SELECT * FROM std_info;
The output shows that a new array has been successfully concatenated with the “std_num” array.
Example 4: Updating an Array Using ARRAY_REMOVE() Function
In Postgres, the ARRAY_REMOVE() function helps us remove the unnecessary array elements from a particular array:
UPDATE std_info SET std_num = ARRAY_REMOVE(std_num, 72);
To validate the modified array, run the SELECT query as follows:
SELECT * FROM std_info;
From the output snippet, you can observe that all the occurrences of “72” have been removed from the given array.
Example 5: Updating an Array Using ARRAY_REPLACE() Function
Use the ARRAY_REPLACE() function to replace an array element with some particular value:
UPDATE std_info SET std_num = ARRAY_REPLACE(std_num, 50, 61);
To demonstrate the modified array elements, use the SELECT query as follows:
SELECT * FROM std_info;
The output depicts that all occurrences of “50” have been replaced with “61”.
Conclusion
Postgres offers different built-in array functions that are used with the UPDATE command to modify an array. For instance, the ARRAY_PREPEND() function to insert an element at the start of an array, ARRAY_REMOVE() to remove any particular element from an array, ARRAY_CAT() to concatenate multiple arrays, etc. This Postgres guide provided numerous examples of how to modify an array using appropriate examples.
