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)
$$;

Answer 1

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