Friday, March 6, 2015

Fancy SQL Friday: subtracting arrays

Here's one which just came up:  how to see all of the elements in a new array which were not in the old array.  This isn't currently supported by any of PostgreSQL's array operators, but thanks to UNNEST() and custom operators, you can create your own:

    create or replace function diff_elements_text (
        text[], text[] )
    returns text[]
    language sql
    immutable
    as $f$
    SELECT array_agg(DISTINCT new_arr.elem)
    FROM
        unnest($1) as new_arr(elem)
        LEFT OUTER JOIN
        unnest($2) as old_arr(elem)
        ON new_arr.elem = old_arr.elem
    WHERE old_arr.elem IS NULL;
    $f$;

    create operator - (
        procedure = diff_elements_text,
        leftarg = text[],
        rightarg = text[]
    );

Now you can just subtract text arrays:

    josh=# select array['n','z','d','e'] - array['a','n','z'];
    ?column?
    ----------
    {d,e}
    (1 row)

Unfortunately, you'll need to create a new function and operator for each base  type; I haven't been able to get it to work with "anyarray".  But this should save you some time/code on array comparisons.  Enjoy!


at

4 comments:

  1. xocolatlMarch 6, 2015 at 5:44 PM

    This comment has been removed by the author.

    ReplyDelete
  2. xocolatlMarch 6, 2015 at 5:45 PM

    You can do it easily with anyarray if you use set operators instead of an antijoin.

    create or replace function diff_elements(anyarray, anyarray)
    returns anyarray as
    $$
    select array(
    select unnest($2)
    except
    select unnest($1)
    );
    $$
    language sql immutable;

    create operator - (
    procedure = diff_elements,
    leftarg=anyarray,
    rightarg=anyarray
    );

    =# select array['a', 'n', 'z'] - array['n', 'z', 'd', 'e'];
    ?column?
    ----------
    {d,e}
    (1 row)

    =# select array[1, 5, 6] - array[5, 6, 23, 64];
    ?column?
    ----------
    {23,64}
    (1 row)

    It seems that the operator is backwards, but I kept it how you had it.

    ReplyDelete