Array difference in postgresql

Question

I have two arrays [1,2,3,4,7,6] and [2,3,7] in PostgreSQL which may have common elements. What I am trying to do is to exclude from the first array all the elements that are present in the second. So far I have achieved the following:

SELECT array
  (SELECT unnest(array[1, 2, 3, 4, 7, 6])
   EXCEPT SELECT unnest(array[2, 3, 7]));

However, the ordering is not correct as the result is {4,6,1} instead of the desired {1,4,6}. How can I fix this ?

I finally created a custom function with the following definition (taken from here) which resolved my issue:

create or replace function array_diff(array1 anyarray, array2 anyarray)
returns anyarray language sql immutable as $$
    select coalesce(array_agg(elem), '{}')
    from unnest(array1) elem
    where elem <> all(array2)
$$;

You could install the intarray extension which offers an operator for that. — user330315
– user330315, Commented Mar 22, 2019 at 17:52

Kaushik Nayak · Accepted Answer · 2019-03-22 17:09:22Z

9

I would use ORDINALITY option of UNNEST and put an ORDER BY in the array_agg function while converting it back to array. NOT EXISTS is preferred over except to make it simpler.

SELECT array_agg(e order by id) 
   FROM unnest( array[1, 2, 3, 4, 7, 6] ) with ordinality as s1(e,id)
    WHERE not exists 
   (
     SELECT 1 FROM unnest(array[2, 3, 7]) as s2(e)
      where s2.e = s1.e
    )

DEMO

answered Mar 22, 2019 at 17:09

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mewtwo Over a year ago

I will accept the answer since it resolves the initial question. However, I ended up creating a custom function which computes the "difference" of two arrays, the definition of which I have added in the initial question.

Bergi Over a year ago

Instead of using NOT EXISTS you could simplify to WHERE NOT s1.e = ANY(ARRAY[2, 3, 7])

Joel · Accepted Answer · 2024-06-13 21:29:21Z

0

I took Kaushik Nayak's answer and made it into a custom operator for easier use

create or replace function array_difference(anyarray, anyarray)
returns anyarray language sql immutable as $$
    SELECT coalesce(array_agg(e order by id), '{}') 
    FROM unnest( $1 ) with ordinality as s1(e,id)
    WHERE not exists 
    (
      SELECT 1 FROM unnest($2) as s2(e)
      where s2.e = s1.e
    )
$$;

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

Usage:

select '{1,2,3,4}'::int[] - '{4}'::int[]
-- '{1,2,3}'::int[]
select '{1,2,3,4}'::text[] - '{4}'::text[]
-- '{1,2,3}'::text[]

select '{1,2,3,4,4}'::int[] - '{4}'::int[]
-- '{1,2,3}'::int[]
select '{1,2,3,4,4}'::text[] - '{4}'::text[]
-- '{1,2,3}'::text[]

select '{4}'::text[] - '{1,2,3,4}'::text[]
-- '{}'::text[]
select '{4}'::int[] - '{1,2,3,4}'::int[]
-- '{}'::int[]

answered Jun 13, 2024 at 21:29

Joel

1145 bronze badges

1 Comment

Bergi Over a year ago

commutator = - is not strictly true, as the order in the result array depends on the first operand.

Istopopoki · Accepted Answer · 2022-12-02 08:37:15Z

-2

Postgres is unfortunately lacking this functionality. In my case, what I really needed to do was to detect cases where the array difference was not empty. In that specific case you can do that with the @> operator which means "Does the first array contain the second?"

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

See doc

answered Dec 2, 2022 at 8:37

Istopopoki

1,7731 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

Array difference in postgresql

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related