DEV Community


Posted on

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
    execute 'select array(select unnest($1) except select unnest($2))'
      using minuend, subtrahend
       into difference;
$$ language plpgsql returns null on null input;
Enter fullscreen mode Exit fullscreen mode

Demo Time!

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

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

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

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

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.

Top comments (3)

cpursley profile image
Chase Pursley • Edited

Nice! If you want to get fancy, you can create an operator like this:

  LEFTARG = anyarray,
  RIGHTARG = anyarray,
  PROCEDURE = array_subtract
Enter fullscreen mode Exit fullscreen mode

And use like this:

select ('{2,3,4}'::int[] - '{1,2,3,4,5}'::int[])'
Enter fullscreen mode Exit fullscreen mode
cstork profile image

Also check out PostgreSQL's intarray extension! It already contains the - operator.

redhap profile image

That intarray package is really cool. Really wish there was a "textarray" package with similar function.