CODEWITHSUNDEEP
sqlarrayspostgresql
k.xf
k.xf

Reputation: 399

How to remove elements of array in PostgreSQL?

Is it possible to remove multiple elements from an 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?

Upvotes: 26

Views: 50133

Answers (6)

Adé
Adé

Reputation: 494

You can use this function for when you are dealing with bigint/int8 numbers and want to maintain order:

CREATE OR REPLACE FUNCTION arr_subtract(int8[], int8[])
  RETURNS int8[] AS
$func$
SELECT ARRAY(
    SELECT a
    FROM   unnest($1) WITH ORDINALITY x(a, ord)
    WHERE  a <> ALL ($2)
    ORDER  BY ord
    );
$func$  LANGUAGE sql IMMUTABLE;

I got this solution from the following answer to a similar question: https://stackoverflow.com/a/8584080/1544473

Upvotes: 2

Dror Dromi
Dror Dromi

Reputation: 163

User array re-dimension annotation

array[<start index>:<end index>] 

WITH t(stack, dim) as (
  VALUES(ARRAY[1,2,3,4], ARRAY[1,4])
) SELECT stack[dim[1]+1:dim[2]-1] FROM t

Upvotes: 0

Majid
Majid

Reputation: 3471

This answer is the simplest I think: https://stackoverflow.com/a/6535089/673187

SELECT array(SELECT unnest(:array1) EXCEPT SELECT unnest(:array2));

so you can easily use it in an UPDATE command, when you need to remove some elements from an array column:

UPDATE table1 SET array1_column=(SELECT array(SELECT unnest(array1_column) EXCEPT SELECT unnest('{2, 3}'::int[])));

Upvotes: 9

klin
klin

Reputation: 121574

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)

If you often need this functionality, define the simple function:

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 can use the function for any array, not only int[]:

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

 array_diff 
------------
 {b,c}
(1 row)

Upvotes: 23

MichaelG
MichaelG

Reputation: 737

With some help from this post:

select array_agg(elements) from 
   (select unnest('{1,2,3,4}'::int[]) 
except 
   select unnest('{1,4}'::int[])) t (elements)

Result:

{2,3}

Upvotes: 18

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382122

With the intarray extension, you can simply 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.

Upvotes: 12

Related Questions