current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote -1 down vote favorite

Is it possible to remove some elements in array?
Before removing elements Array1 is : 

{1,2,3,4}

Array2 that contains some elements I wish to remove:

{1,4}

And I want to get:

 {2,3}

How to operate?

share|improve this question
    
You can remove individual elements using array_remove() but unless you install the intarray module you can't easily remove one array from another – a_horse_with_no_name Jun 20 at 8:52
up vote 1 down vote

Just use -:

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

Result:

{2,3}

Online demonstration

You'll need to install the intarray extension if you didn't already. It adds many convenient functions and operators if you're dealing with arrays of integers.

share|improve this answer
    
- is not allowed in arrays..... – k.xf Jun 20 at 8:47
    
You need the intarray extension for that to work: postgresql.org/docs/current/static/intarray.html – a_horse_with_no_name Jun 20 at 8:49
    
My sql is 9.3 too, well it doesn't work.. – k.xf Jun 20 at 8:50
up vote 1 down vote

Use unnest() with array_agg(), e.g.:

with cte(array1, array2) as (
    values (array[1,2,3,4], array[1,4])
    )
select array_agg(elem)
from cte, unnest(array1) elem
where elem <> all(array2);

 array_agg 
-----------
 {2,3}
(1 row)

You can use the query in an sql function:

create or replace function array_subtract(array1 anyarray, array2 anyarray)
returns anyarray language sql as $$
    select array_agg(elem)
    from unnest(array1) elem
    where elem <> all(array2)
$$;

select array_subtract(array['a','b','c','d'], array['a','d']);

 array_subtract 
----------------
 {b,c}
(1 row)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.