Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Array difference in postgresql

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)
$$;
like image 488
Mewtwo Avatar asked Mar 22 '19 16:03

Mewtwo


People also ask

Should you use arrays in Postgres?

When you are considering portability (e.g. rewriting your system to work with other databses) then you must not use arrays. If you are sure you'll stick with Postgres, then you can safely use arrays where you find appropriate. They exist for a reason and are neither bad design nor non-compliant.

What is array type in PostgreSQL?

The Array Type With the Array PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

How do I query an array in PostgreSQL?

Using unnest() expands an array to multiple rows. The non-array columns get repeated for each row.

What is array length in PostgreSQL?

array_length() is a system function returning the length of the requested array dimension. array_length() was added in PostgreSQL 8.4.


1 Answers

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

like image 177
Kaushik Nayak Avatar answered Nov 04 '22 14:11

Kaushik Nayak