PostgreSQL is a commonly used relational database that supports the ARRAY data type to store the data of the same type in consecutive blocks of memory. To deal with the array's data efficiently, Postgres supports a variety of built-in functions, such as ARRAY_PREPEND(), ARRAY_CAT(), etc. Each of these array functions has distinct objectives/functionality.

This post will discuss the below-stated array functions in PostgreSQL:

- ARRAY_APPEND()
- ARRAY_PREPEND()
- ARRAY_CAT()
- ARRAY_REPLACE( )
- ARRAY_REMOVE( )

PostgreSQL ARRAY_APPEND() Function

ARRAY_APPEND() is one of the most frequently used array functions that append a new element at the end of an array using the following syntax:

ARRAY_APPEND(array, new_element);

Here, "new_element" represents the element you want to add, and "array" represents the array where you want to append the new element.

Example: Applying ARRAY_APPEND

The following example uses the “ARRAY_APPEND()” function to insert “.com” element at the end of the array:

SELECT ARRAY_APPEND(ARRAY['Welcome', 'to', 'commandprompt'], '.com');

The output illustrates that the desired element has been successfully appended/inserted at the end of the provided array:

Follow our dedicated guide on how to use ARRAY_APPEND() function to learn more about it.

PostgreSQL ARRAY_PREPEND() Function

The ARRAY_PREPEND() function works in a contrary manner to the ARRAY_APPEND() function. It adds/inserts a new element at the beginning of an array by utilizing the below-provided syntax:

ARRAY_PREPEND(new_element, array);

Here, "new_element" represents the element you want to add, and "array" represents the array where you want to prepend the new element.

Example: Applying ARRAY_PREPEND

The following example uses the “ARRAY_PREPEND()” function to append “Hello” at the start of the array:

SELECT ARRAY_PREPEND('Hello', ARRAY['Welcome', 'to', 'commandprompt']);

The output displays that the provided element has been inserted at the start of the given array:

PostgreSQL ARRAY_CAT() Function

As the name itself describes, the “ARRAY_CAT()” function concatenates/combines given arrays and retrieves a single array:

SELECT ARRAY_CAT(array_1, array_2);

Here, the “array_1” and “array_2” are the arrays to be combined.

Example: Applying ARRAY_CAT()

The following example code concatenates two TEXT-type arrays:

SELECT ARRAY_CAT(ARRAY['Hello', '!!!'], ARRAY['Welcome', 'to', 'commandprompt']);

The given arrays have been successfully concatenated:

For more details, check out the following guide on concatenating multiple arrays using ARRAY_CAT() function.

PostgreSQL ARRAY_REPLACE() Function

PostgreSQL allows us to replace an element in an array by specifying the “new element” and the “element to be replaced” in the ARRAY_REPLACE function. Here is a simple syntax that will allow you to comprehend this function better:

ARRAY_REPLACE(array, existing_element, new_element);

The “new_element” will replace all the occurrences of the “existing_element”.

Example: Applying ARRAY_REPLACE() Function

In the provided example code, the “Hello” element will be replaced with the “Hi”:

SELECT ARRAY_REPLACE(ARRAY['Hello', 'Welcome', 'to', 'commandprompt'], 'Hello', 'Hi!');

The selected element has been successfully replaced with the desired element:

For more details, check our detailed guide on ARRAY_REPLACE() Function in PostgreSQL.

PostgreSQL ARRAY_REMOVE() Function

Use the ARRAY_REMOVE() function to remove/delete all the occurrences of any unwanted elements from a particular array:

ARRAY_REMOVE(array, element);

Here, the element represents any array element that needs to be deleted/removed.

Example: Applying the ARRAY_REMOVE Function

The following example utilizes the ARRAY_REMOVE() function to remove an element “Hello” from the given array:

SELECT ARRAY_REMOVE(ARRAY['Hello', 'Welcome', 'to', 'commandprompt', 'Hello'], 'Hello');

The ARRAY_REMOVE() function has successfully removed all the occurrences of the “Hello” element from the provided array:

Check out the following guide to learn various use cases of the ARRAY_REMOVE() function in PostgreSQL.

Conclusion

PostgreSQL supports various built-in array functions, such as ARRAY_PREPEND(), ARRAY_CAT(), etc., to deal with the array's data efficiently. Each of these array functions offers distinct objectives/functionality. This guide has elaborated on some of the most frequently used array functions along with their basic syntax and suitable examples.