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:
- The actual subtraction processing
- 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.
Top comments (3)
Nice! If you want to get fancy, you can create an operator like this:
And use like this:
Also check out PostgreSQL's
intarrayextension! It already contains the-operator.That
intarraypackage is really cool. Really wish there was a "textarray" package with similar function.