blocks|key|1884941|text|我会用数组操作符来处理这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1884942|select+array(select+unnest(:arr1)+except+select+unnest(:arr2));|code-block|syntax|javascript|1884943|如果:arr1和:arr2不相交，使用array_agg()会导致null。|1884944|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I'd approach this with the array operator.

<pre><code>select array(select unnest(:arr1) except select unnest(:arr2));
</code></pre>

If :arr1 and :arr2 don't intersect, using array_agg() leads to a null.

blocks|key|6063365|text|select+array_agg(elements)
from+(
++select+unnest(array[12,3,5,7,8])
++except
++select+unnest(array[3,7,8])
)+t+(elements)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|6063366|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select array_agg(elements)
from (
 select unnest(array[12,3,5,7,8])
 except
 select unnest(array[3,7,8])
) t (elements)
</code></pre>

blocks|key|4647438|text|我构造了一组函数来专门处理这些类型的问题：https://github.com/JDBurnZ/anyarray|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4647439|最棒的是，这些函数适用于所有数据类型，而不只是整数，因为intarray是受限制的。|style|CODE|4647440|从GitHub加载这些SQL文件中定义的函数后，您需要做的就是：|4647441|SELECT
++ANYARRAY_DIFF(
++++ARRAY[12,+3,+5,+7,+8],
++++ARRAY[3,+7,+8]
++)|code-block|syntax|javascript|4647442|返回类似于：ARRAY[12,+5]的内容|4647443|如果您还需要返回排序后的值：|4647444|SELECT
++ANYARRAY_SORT(
++++ANYARRAY_DIFF(
++++++ARRAY[12,+3,+5,+7,+8],
++++++ARRAY[3,+7,+8]
++++)
++)|4647445|准确返回：ARRAY[5,+12]|4647446|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/JDBurnZ/anyarray^0|L|Z|0|0|S|8|0|0|0|6|C|0|0|0|5|C|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@$A|19|B|1A|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|1B|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|1C|8|@]|9|@]|C|$M|N]]|$1|O|3|P|5|6|7|1D|8|@$A|1E|B|1F|F|G]]|9|@]|C|$]]|$1|Q|3|R|5|6|7|1G|8|@]|9|@]|C|$]]|$1|S|3|T|5|L|7|1H|8|@]|9|@]|C|$M|N]]|$1|U|3|V|5|6|7|1I|8|@$A|1J|B|1K|F|G]]|9|@]|C|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|C|$]]]|X|$Y|$5|Z|10|11|C|$12|13]]]]

I've constructed a set of functions to deal specifically with these types of issues: <a href="https://github.com/JDBurnZ/anyarray">https://github.com/JDBurnZ/anyarray</a>

The greatest thing is these functions work across ALL data-types, not JUST integers, as <code>intarray</code> is limited to.

After loading loading the functions defined in those SQL files from GitHub, all you'd need to do is:

<pre><code>SELECT
 ANYARRAY_DIFF(
 ARRAY[12, 3, 5, 7, 8],
 ARRAY[3, 7, 8]
 )
</code></pre>

Returns something similar to: <code>ARRAY[12, 5]</code>

If you also need to return the values sorted:

<pre><code>SELECT
 ANYARRAY_SORT(
 ANYARRAY_DIFF(
 ARRAY[12, 3, 5, 7, 8],
 ARRAY[3, 7, 8]
 )
 )
</code></pre>

Returns exactly: <code>ARRAY[5, 12]</code>

blocks|key|2534763|text|让我们试着使用unnest()+/+except：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2534764|EXPLAIN+ANALYZE+SELECT+array(select+unnest(ARRAY[1,2,3,n])+EXCEPT+SELECT+unnest(ARRAY[2,3,4,n]))+FROM+generate_series(+1,10000+)+n;
+Function+Scan+on+generate_series+n++(cost=0.00..62.50+rows=1000+width=4)+(actual+time=1.373..140.969+rows=10000+loops=1)
+++SubPlan+1
+++++->++HashSetOp+Except++(cost=0.00..0.05+rows=1+width=0)+(actual+time=0.011..0.011+rows=1+loops=10000)
+++++++++++->++Append++(cost=0.00..0.04+rows=2+width=0)+(actual+time=0.002..0.008+rows=8+loops=10000)
+++++++++++++++++->++Subquery+Scan+"*SELECT*+1"++(cost=0.00..0.02+rows=1+width=0)+(actual+time=0.002..0.003+rows=4+loops=10000)
+++++++++++++++++++++++->++Result++(cost=0.00..0.01+rows=1+width=0)+(actual+time=0.001..0.002+rows=4+loops=10000)
+++++++++++++++++->++Subquery+Scan+"*SELECT*+2"++(cost=0.00..0.02+rows=1+width=0)+(actual+time=0.001..0.003+rows=4+loops=10000)
+++++++++++++++++++++++->++Result++(cost=0.00..0.01+rows=1+width=0)+(actual+time=0.001..0.002+rows=4+loops=10000)
+Total+runtime:+142.531+ms|code-block|syntax|javascript|2534765|和intarray特殊运算符：|2534766|EXPLAIN+ANALYZE+SELECT+ARRAY[1,2,3,n]+-+ARRAY[2,3,4,n]+FROM+generate_series(+1,10000+)+n;
+Function+Scan+on+generate_series+n++(cost=0.00..15.00+rows=1000+width=4)+(actual+time=1.338..11.381+rows=10000+loops=1)
+Total+runtime:+12.306+ms|2534767|基准：|2534768|EXPLAIN+ANALYZE+SELECT+ARRAY[1,2,3,n],+ARRAY[2,3,4,n]+FROM+generate_series(+1,10000+)+n;
+Function+Scan+on+generate_series+n++(cost=0.00..12.50+rows=1000+width=4)+(actual+time=1.357..7.139+rows=10000+loops=1)
+Total+runtime:+8.071+ms|2534769|每个数组交集的时间：|2534770|intarray+-+++++++++++:++0.4+µs
unnest()+/+intersect+:+13.4+µs|2534771|当然，intarray方法要快得多，但我发现postgres可以在13.4微秒内清除一个依赖子查询(其中包含哈希和其他内容)，这让我感到惊讶……|2534772|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Let's try the unnest() / except :

<pre><code>EXPLAIN ANALYZE SELECT array(select unnest(ARRAY[1,2,3,n]) EXCEPT SELECT unnest(ARRAY[2,3,4,n])) FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n (cost=0.00..62.50 rows=1000 width=4) (actual time=1.373..140.969 rows=10000 loops=1)
 SubPlan 1
 -&gt; HashSetOp Except (cost=0.00..0.05 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=10000)
 -&gt; Append (cost=0.00..0.04 rows=2 width=0) (actual time=0.002..0.008 rows=8 loops=10000)
 -&gt; Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.002..0.003 rows=4 loops=10000)
 -&gt; Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=4 loops=10000)
 -&gt; Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.003 rows=4 loops=10000)
 -&gt; Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=4 loops=10000)
 Total runtime: 142.531 ms
</code></pre>

And the intarray special operator :

<pre><code>EXPLAIN ANALYZE SELECT ARRAY[1,2,3,n] - ARRAY[2,3,4,n] FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n (cost=0.00..15.00 rows=1000 width=4) (actual time=1.338..11.381 rows=10000 loops=1)
 Total runtime: 12.306 ms
</code></pre>

Baseline :

<pre><code>EXPLAIN ANALYZE SELECT ARRAY[1,2,3,n], ARRAY[2,3,4,n] FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n (cost=0.00..12.50 rows=1000 width=4) (actual time=1.357..7.139 rows=10000 loops=1)
 Total runtime: 8.071 ms
</code></pre>

Time per array intersection :

<pre><code>intarray - : 0.4 µs
unnest() / intersect : 13.4 µs
</code></pre>

Of course the intarray way is much faster, but I find it amazing that postgres can zap a dependent subquery (which contains a hash and other stuff) in 13.4 µs...

blocks|key|6063362|text|contrib/intarray模块提供了这个功能--无论如何，都是针对整数数组的。对于其他数据类型，您可能需要编写自己的函数(或修改intarray提供的函数)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|6063363|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/intarray.html^0|0|G|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

The <a href="http://www.postgresql.org/docs/current/static/intarray.html" rel="nofollow">contrib/intarray</a> module provides this functionality--for arrays of integers, anyway. For other data types, you may have to write your own functions (or modify the ones provided with intarray).

blocks|key|6063539|text|Denis答案的一个扩展，它返回差值，而不管首先输入的是哪个数组。这不是最简洁的查询，也许有人有更简洁的方式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6063540|select+array_cat(
+++(select+array(select+unnest(a.b::int[])+except+select+unnest(a.c::int[]))),
+++(select+array(select+unnest(a.c::int[])+except+select+unnest(a.b::int[]))))
from+(select+'{1,2}'::int[]+b,'{1,3}'::int[]+c)+as+a;|code-block|syntax|javascript|6063541|返回：|6063542|{2,3}|6063543|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

An extension to Denis' answer that returns the difference, regardless of which array was entered first. It's not the most concise query, maybe someone has a tidier way.

<pre><code>select array_cat(
 (select array(select unnest(a.b::int[]) except select unnest(a.c::int[]))),
 (select array(select unnest(a.c::int[]) except select unnest(a.b::int[]))))
from (select '{1,2}'::int[] b,'{1,3}'::int[] c) as a;
</code></pre>

Returns:

<pre><code>{2,3}
</code></pre>

blocks|key|6063517|text|我将使用与@a_horse_with_no_name描述的相同的except逻辑创建一个函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6063518|CREATE+FUNCTION+array_subtract(a1+int[],+a2+int[])+RETURNS+int[]+AS+$$
DECLARE
++++ret+int[];
BEGIN
++++IF+a1+is+null+OR+a2+is+null+THEN
++++++++return+a1;
++++END+IF;
++++SELECT+array_agg(e)+INTO+ret
++++FROM+(
++++++++SELECT+unnest(a1)
++++++++EXCEPT
++++++++SELECT+unnest(a2)
++++)+AS+dt(e);
++++RETURN+ret;
END;
$$+language+plpgsql;|code-block|syntax|javascript|6063519|然后，您可以使用此函数相应地更改base_array变量：|6063520|base_array+:=+array_subtract(base_array,+temp_array);|6063521|6063522|使用@Denis更快的解决方案，并且只有SQL，我们可以将泛型函数表示为|6063523|CREATE+FUNCTION+array_subtract(anyarray,anyarray)+RETURNS+anyarray+AS+$f$
++SELECT+array(
++++SELECT+unnest($1)
++++EXCEPT
++++SELECT+unnest($2)
++)
$f$+language+SQL+IMMUTABLE;|6063524|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|W|8|@]|9|@]|A|$]]|$1|N|3|O|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|P|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|Q|$]]

I would create a function using the same except logic as described by @a_horse_with_no_name:

<pre><code>CREATE FUNCTION array_subtract(a1 int[], a2 int[]) RETURNS int[] AS $$
DECLARE
 ret int[];
BEGIN
 IF a1 is null OR a2 is null THEN
 return a1;
 END IF;
 SELECT array_agg(e) INTO ret
 FROM (
 SELECT unnest(a1)
 EXCEPT
 SELECT unnest(a2)
 ) AS dt(e);
 RETURN ret;
END;
$$ language plpgsql;
</code></pre>

Then you can use this function to change your base_array variable accordingly:

<pre><code>base_array := array_subtract(base_array, temp_array);
</code></pre>

<hr>

Using the @Denis's faster solution, and only SQL, we can express a generic function as

<pre><code>CREATE FUNCTION array_subtract(anyarray,anyarray) RETURNS anyarray AS $f$
 SELECT array(
 SELECT unnest($1)
 EXCEPT
 SELECT unnest($2)
 )
$f$ language SQL IMMUTABLE;
</code></pre>

How can I pick only the non matching elements between two arrays.

Example:

<pre><code>base_array [12,3,5,7,8]
temp_array [3,7,8]
</code></pre>

So here I want to compare both the arrays and remove the matching elements from the base array.

Now base_array should be like <code>[12,5]</code>

How to compare two arrays and pick only the non matching elements In postgres

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

如何才能只挑选两个数组之间不匹配的元素。示例：base_array [12,3,5,7,8]temp_array [3,7,8]所以这里我想要比较这两个数组，并从基数组中删除匹配的元素。现在，base_array应该像[12,5]一样

问如何在postgres中比较两个数组并只挑选不匹配的元素
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在postgres中比较两个数组并只挑选不匹配的元素EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在postgres中比较两个数组并只挑选不匹配的元素
EN