如何在PostgreSQL中删除数组元素？

Question

能否从数组中移除多个元素？ 移除元素前，Array1 的值为：

{1,2,3,4}

包含一些需要移除元素的Array2：

{1,4}

我希望获得：

 {2,3}

如何操作？

- k.xf

---

您可以使用array_remove()删除单个元素，但是除非安装了intarray模块，否则您无法轻松地从另一个数组中删除一个数组。 - user330315

对于少量的值，你可以多次使用array_remove；对于更多的值，你可以使用循环和array_remove。 - Sławomir Lenart

这个回答解决了你的问题吗？如何在Postgres中比较两个数组并选择仅不匹配的元素。 - Majid

Answer 1

使用unnest（）与array_agg（）一起使用，例如：

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)

如果您经常需要此功能，请定义简单函数：

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

您可以对任何数组使用该函数，不仅限于int[]：

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

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

- klin

---

这个需求似乎是一些琐碎的东西，非常普遍，为什么没有本地函数来实现它呢？是否有计划实现这个功能？ - Christophe Roussy

好的，即使在Python中也没有本地功能。 另一方面，没有什么可以阻止您在数据库中创建该函数并将其视为本地功能。 - klin

好的，答案已更新，现在该函数返回一个空数组。谢谢。 - klin

在这种情况下，all()函数是做什么用的？ - Dex

@Dex - 行和数组比较 - klin

Answer 2

借助于这篇文章的帮助：

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

结果：

{2,3}

- MichaelG

---

到目前为止，最简单的解决方案！ - Luke

非常好的解决方案。易于理解，不需要扩展，并且适用于字符串！ - opx

这很完美。优雅的解决方案。我理解最后的 t 是别名，但 (elements) 的作用是什么呢？没有它就不起作用。 - Tushar Jajodia

不幸的是，这个解决方案（以及本页面上的其他解决方案）都接近，但并不足够。在某些情况下，它们可能会改变数组的顺序。例如，在此答案中，尝试将上面的“{1,4}”替换为“{2,4}”。结果是“{3,1}”。 - ffxsam

Answer 3

使用 intarray 扩展，您只需使用 - 即可：

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

结果：

{2,3}

在线演示

如果尚未安装intarray扩展程序，则需要进行安装以便在处理整数数组时使用其提供的众多方便函数和操作符。

- Denys Séguret

---

为此需要使用intarray扩展：https://www.postgresql.org/docs/current/static/intarray.html - user330315

"只需使用“-”即可"，需要安装扩展名是矛盾的，不是吗？" - Ondřej Želazko

@OndřejŽelazko 你说得对。我已经重新措辞了。但是，当处理这样的数组时，这应该是您的pg安装中的标准... - Denys Séguret

Answer 4

我认为这是最简单的答案： https://dev59.com/Cmw15IYBdhLWcg3wkMnz#6535089

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

因此，当您需要从数组列中删除一些元素时，可以轻松地将其用于UPDATE命令：

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

- Majid

Answer 5

你可以使用这个函数来处理bigint/int8数字并保持顺序：

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;

我从类似问题的以下答案中得到了这个解决方案: https://dev59.com/SV7Va4cB1Zd3GeqPKX4D#8584080

- Adé

Answer 6

用户数组重新维度注释

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

- Dror Dromi