DEV Community

HAP
HAP

Posted on

Subtract Arrays in PostgreSQL

#postgres #programming

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;
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[]);
 array_subtract 
----------------
 {one,two}
(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[]);
 array_subtract 
----------------
 {}
(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)

Collapse
 
cpursley profile image
Chase Pursley • • Edited

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

CREATE OPERATOR - (
  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
Collapse
 
cstork profile image
cstork •

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

Collapse
 
redhap profile image
HAP •

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

Read next

pykpyky profile image

Designing a scalable application with interfaces in C#

Mary 🇪🇺 🇷🇴 🇫🇷 -

mikeyoung44 profile image

New AttackVector Jailbreaks LLMs by Prompt Manipulation

Mike Young -

mikeyoung44 profile image

Unraveling the Might of "Super Weights" in Massive Language Models: Identification and Management

Mike Young -

chintanonweb profile image

Step-by-Step Guide to Using React Components as Props Like a Pro

chintanonweb -