In PostgreSQL, we can use the trim_array() function to trim a given number of elements from the end of an array.

The first argument is the array, and the second is the number of elements to trim.

Example

Here’s a basic example to demonstrate the function:

SELECT trim_array(ARRAY['Cat','Dog','Bull','Ant','Rat'], 2);

Result:

{Cat,Dog,Bull}

Here I trimmed the last two elements from the array. The resulting array consisted of the first three elements of the source array.

Multi Dimensional Arrays

If the array is multidimensional, only the first dimension is trimmed:

SELECT trim_array(
    ARRAY[
        ARRAY['Cat','Dog'],
        ARRAY['Bull','Ant'],
        ARRAY['Rat','Zebra']
        ], 2);

Result:

{{Cat,Dog}}

At least, this is how it works at the time of writing (PostgreSQL 16).

Attempting to Overtrim

If the second argument is greater than the number of elements in the array, then an error is returned:

SELECT trim_array(ARRAY['Cat','Dog','Bull','Ant','Rat'], 7);

Result:

ERROR:  number of elements to trim must be between 0 and 5

The error message reflects the number of elements in the array. Here it is again, but with less array elements:

SELECT trim_array(ARRAY['Cat','Dog','Bull'], 7);

Result:

ERROR:  number of elements to trim must be between 0 and 3

Negative Trim

We can’t trim the array with a negative amount:

SELECT trim_array(ARRAY['Cat','Dog','Bull'], -2);

Result:

ERROR:  number of elements to trim must be between 0 and 3

Zero Trim

But as the error message indicates, we can specify zero:

SELECT trim_array(ARRAY['Cat','Dog','Bull'], 0);

Result:

{Cat,Dog,Bull}

In this case, nothing is trimmed.