Subtract Arrays in PostgreSQL

PostgreSQL provides a wealth of functions built-in, but one that's lacking is a way to "subtract" one array from another. This is the result of research and examples found in multiple places on the interwebs.

If you have two arrays (say, array_1 and array_2; just to be boring), the goal here would be to subtract array_2 from array_1 and return a new array with the difference. The difference being a new array containing any element of array_1 that is not present in array_2. Got it?

Two specific pieces of functionality are needed here:

1. The actual subtraction processing
2. The returning of the result in another array.

Set functionality was used for the subtraction and use of the array functionality to bundle that result into a new array. There's no easy way to do this in a Pure ANSI SQL way, so it was necessary to utilize good ol' PLPGSQL.

Here is the function:

create or replace function public.array_subtract(
    minuend anyarray, subtrahend anyarray, out difference anyarray
)
returns anyarray as
$$
begin
    execute 'select array(select unnest($1) except select unnest($2))'
      using minuend, subtrahend
       into difference;
end;
$$ language plpgsql returns null on null input;

Demo Time!

Subtract ['three', 'four', 'five] from ['one', 'two', 'three']:

postgresql=# select array_subtract('{one,two,three}'::text[], '{three,four,five}'::text[]);
 array_subtract 
----------------
 {one,two}
(1 row)

Subtract [1,2,3,4,5] from [2,3,4]:

postgresql=# select array_subtract('{2,3,4}'::int[], '{1,2,3,4,5}'::int[]);
 array_subtract 
----------------
 {}
(1 row)

Easy peasy, lemon squeezy.

There's been an instance or two where this has really come in handy in my professional career. Hopefully it can help you out as well.