PostgreSQL offers numerous array functions that are used to perform different operations on the arrays. For instance, the ARRAY_REMOVE() deletes the array’s elements, the ARRAY_APPEND() adds a new element at the end of an array, and so on. The TRIM_ARRAY() is one such array function that is used to remove or trim the specific number of array elements.

This article will explain the usage of the Postgres TRIM_ARRAY() function using appropriate examples.

How to Use TRIM_ARRAY() Function in PostgreSQL?

The TRIM_ARRAY() function accepts an array and the number of elements to be trimmed as arguments. As a result, it trimmed the specified number of elements from the end of the given array and return a newly updated array. Here is the basic syntax of the TRIM_ARRAY() function:

TRIM_ARRAY(arr, num);

The stated function returns a modified array, however, if the input array is NULL then NULL will be retrieved.

Example 1: How to Use the TRIM_ARRAY() in Postgres?

In the following example, the TRIM_ARRAY() function is used on a 1-D array:

SELECT TRIM_ARRAY(
ARRAY['Joseph', 'John', 'Joe', 'Seth', 'Stephen'], 2
);

The output clarifies that the two elements from the right side of the array have been trimmed.

Example 2: How to Use the TRIM_ARRAY() on Multi-dimensional Arrays?

Let’s learn how to use the TRIM_ARRAY() function on a multi-dimensional array:

SELECT TRIM_ARRAY(
ARRAY[['Seth', 'Joseph'],
['John', 'Joe'], 
['Mike', 'Ambrose'],
['Seth', 'Stephen']], 2
);

Here is what we will get on successful execution:

The output proved that the specified number of elements have been removed from the array.

Example 3: Using the TRIM_ARRAY() Function With a Negative Value

In this example, we will show you how the TRIM_ARRAY() function deal with a negative value:

SELECT TRIM_ARRAY(
ARRAY[['Seth', 'Joseph'],
['John', 'Joe'], 
['Mike', 'Ambrose'],
['Seth', 'Stephen']], -2
);

The output snippet shows that an error occurs when we tried to pass a negative value.

Example 4: Using the TRIM_ARRAY() Function on Table’s Data

In this example, we will use an already created table named “std_info”:

SELECT * FROM std_info;

Let’s use the TRIM_ARRAY() function on the “std_num” column to trim 3 values from each array:

SELECT std_name, std_num, TRIM_ARRAY(std_num, 3)
FROM std_info;

The output shows that 3 elements have been trimmed from the “std_num” array.

Conclusion

In PostgreSQL, the TRIM_ARRAY() function accepts an array and the number of elements to be trimmed as arguments. As a result, it trimmed the specified number of elements from the end of the given array and return a newly updated array. It accepts a positive integer as the second argument, passing a negative integer will result in an error. This write-up has explained the usage of the Postgres TRIM_ARRAY() function with appropriate examples.