If you’re updating arrays in PostgreSQL and you’ve suddenly realised that some of the arrays aren’t being updated, it could be due to the following.

If you’ve been using the array_append() function and the array_prepend() function, you may have found that one function works but the other doesn’t. For example array_append() works but array_prepend() doesn’t, or vice-versa.

This could be due to a little “gotcha” with regards to the syntax of these two functions. Basically these two functions accept their arguments in the opposite order to each other. So if you’re switching between the two functions, you’ll need to switch your arguments too.

Example of the Problem

Here’s a simple example that demonstrates the issue.

Suppose we run the following statement:

SELECT array_append(ARRAY[ 'Cat', 'Dog' ], 'Rabbit');

Result:

{Cat,Dog,Rabbit}

That worked fine. We used array_append() to append 3 to the array.

But now let’s change array_append() to array_prepend():

SELECT array_prepend(ARRAY[ 'Cat', 'Dog' ], 'Rabbit');

Result:

ERROR:  could not find array type for data type text[]

We get an error.

The error message may differ, depending on the data in the array. For example, here’s another error message:

SELECT array_prepend(ARRAY[ 1, 2 ], 0);

Result:

ERROR:  function array_prepend(integer[], integer) does not exist
LINE 1: SELECT array_prepend(ARRAY[ 1, 2 ], 0);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Either way, it’s the same issue – we’re using the wrong syntax for the array_prepend() function. Our arguments are in the wrong order.

Solution

To fix this problem, all we need to do is swap the arguments around. So that the new value is the first argument:

SELECT array_prepend('Rabbit', ARRAY[ 'Cat', 'Dog' ]);

Result:

{Rabbit,Cat,Dog}

Same with the other example:

SELECT array_prepend(0, ARRAY[ 1, 2 ]);

Result:

{0,1,2}