postgresql 消除 postgres 中重复的数组值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3994556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
eliminate duplicate array values in postgres
提问by GVK
I have an array of type bigint, how can I remove the duplicate values in that array?
我有一个类型为 的数组bigint，如何删除该数组中的重复值？
Ex: array[1234, 5343, 6353, 1234, 1234]
前任： array[1234, 5343, 6353, 1234, 1234]
I should get array[1234, 5343, 6353, ...]
我应该得到 array[1234, 5343, 6353, ...]
I tested out the example SELECT uniq(sort('{1,2,3,2,1}'::int[]))in the postgres manual but it is not working.
我测试了SELECT uniq(sort('{1,2,3,2,1}'::int[]))postgres 手册中的示例，但它不起作用。
回答by Михаил Лисаков
I faced the same. But an array in my case is created via array_aggfunction. And fortunately it allows to aggregate DISTINCTvalues, like:
我也面临同样的情况。但是在我的情况下，数组是通过array_agg函数创建的。幸运的是，它允许聚合DISTINCT值，例如：
array_agg(DISTINCT value)
This works for me.
这对我有用。
回答by mnencia
The sort(int[])and uniq(int[])functionsare provided by the intarraycontrib module.
的sort(int[])和uniq(int[])功能是由提供intarray的contrib模块。
To enable its use, you must install the module.
要启用它，您必须安装模块。
If you don't want to use the intarray contrib module, or if you have to remove duplicates from arrays of different type, you have two other ways.
如果您不想使用 intarray contrib 模块，或者您必须从不同类型的数组中删除重复项，则还有另外两种方法。
If you have at least PostgreSQL 8.4 you could take advantage of unnest(anyarray)function
如果你至少有 PostgreSQL 8.4 你可以利用unnest(anyarray)功能
SELECT ARRAY(SELECT DISTINCT UNNEST('{1,2,3,2,1}'::int[]) ORDER BY 1);
?column?
----------
{1,2,3}
(1 row)
Alternatively you could create your own function to do this
或者，您可以创建自己的函数来执行此操作
CREATE OR REPLACE FUNCTION array_sort_unique (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL
AS $body$
SELECT ARRAY(
SELECT DISTINCT [s.i]
FROM generate_series(array_lower(,1), array_upper(,1)) AS s(i)
ORDER BY 1
);
$body$;
Here is a sample invocation:
这是一个示例调用：
SELECT array_sort_unique('{1,2,3,2,1}'::int[]);
array_sort_unique
-------------------
{1,2,3}
(1 row)
回答by Peter Krauss
... Where the statandardlibraries(?) for this kind of array_X utility??
...这种array_X 实用程序的标准库(?)在哪里？
Try to search... See some but no standard:
尝试搜索...看到一些但没有标准：
postgres.cz/wiki/Array_based_functions: good reference!
JDBurnZ/postgresql-anyarray, good initiative but needs some collaboration to enhance.
wiki.postgresql.org/Snippets, frustrated initiative, but "offcial wiki", needs some collaboration to enhance.
MADlib: good! .... but it is an elephant, not an "pure SQL snippets lib".
JDBurnZ/postgresql-anyarray，很好的主动性，但需要一些协作来增强。
wiki.postgresql.org/Snippets，受挫的倡议，但“官方维基”，需要一些协作来增强。
MADlib: 好！.... 但它是一头大象，而不是“纯 SQL 片段库”。
Simplest and faster array_distinct()snippet-lib function
最简单快速的array_distinct()snippet-lib函数
Here the simplest and perhaps faster implementation for array_unique()or array_distinct():
这是array_unique()or的最简单且可能更快的实现array_distinct()：
CREATE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $f$
SELECT array_agg(DISTINCT x) FROM unnest() t(x);
$f$ LANGUAGE SQL IMMUTABLE;
NOTE: it works as expected with any datatype, except with array of arrays,
注意：它适用于任何数据类型，除了数组数组，
SELECT array_distinct( array[3,3,8,2,6,6,2,3,4,1,1,6,2,2,3,99] ),
array_distinct( array['3','3','hello','hello','bye'] ),
array_distinct( array[array[3,3],array[3,3],array[3,3],array[5,6]] );
-- "{1,2,3,4,6,8,99}", "{3,bye,hello}", "{3,5,6}"
the "side effect" is to explode all arrays in a set of elements.
“副作用”是爆炸一组元素中的所有数组。
PS: with JSONB arrays works fine,
PS：使用 JSONB 数组工作正常，
SELECT array_distinct( array['[3,3]'::JSONB, '[3,3]'::JSONB, '[5,6]'::JSONB] );
-- "{"[3, 3]","[5, 6]"}"
Edit: more complex but useful, a "drop nulls" parameter
编辑：更复杂但有用的“删除空值”参数
CREATE FUNCTION array_distinct(
anyarray, -- input array
boolean DEFAULT false -- flag to ignore nulls
) RETURNS anyarray AS $f$
SELECT array_agg(DISTINCT x)
FROM unnest() t(x)
WHERE CASE WHEN THEN x IS NOT NULL ELSE true END;
$f$ LANGUAGE SQL IMMUTABLE;
回答by Joshua Burns
I have assembled a set of stored procedures (functions) to combat PostgreSQL's lack of array handling coined anyarray. These functions are designed to work across any array data-type, not just integers as intarray does: https://www.github.com/JDBurnZ/anyarray
我已经组装了一组存储过程（函数）来解决 PostgreSQL 缺乏数组处理的问题anyarray。这些函数旨在处理任何数组数据类型，而不仅仅是像 intarray 那样的整数：https://www.github.com/JDBurnZ/anyarray
In your case, all you'd really need is anyarray_uniq.sql. Copy & paste the contents of that file into a PostgreSQL query and execute it to add the function. If you need array sorting as well, also add anyarray_sort.sql.
在您的情况下，您真正需要的只是anyarray_uniq.sql. 将该文件的内容复制并粘贴到 PostgreSQL 查询中并执行它以添加函数。如果您还需要数组排序，还可以添加anyarray_sort.sql.
From there, you can peform a simple query as follows:
从那里，您可以执行一个简单的查询，如下所示：
SELECT ANYARRAY_UNIQ(ARRAY[1234,5343,6353,1234,1234])
SELECT ANYARRAY_UNIQ(ARRAY[1234,5343,6353,1234,1234])
Returns something similar to: ARRAY[1234, 6353, 5343]
返回类似于： ARRAY[1234, 6353, 5343]
Or if you require sorting:
或者，如果您需要排序：
SELECT ANYARRAY_SORT(ANYARRAY_UNIQ(ARRAY[1234,5343,6353,1234,1234]))
SELECT ANYARRAY_SORT(ANYARRAY_UNIQ(ARRAY[1234,5343,6353,1234,1234]))
Return exactly: ARRAY[1234, 5343, 6353]
准确返回： ARRAY[1234, 5343, 6353]
回答by alexkovelsky
Here's the "inline" way:
这是“内联”方式：
SELECT 1 AS anycolumn, (
SELECT array_agg(c1)
FROM (
SELECT DISTINCT c1
FROM (
SELECT unnest(ARRAY[1234,5343,6353,1234,1234]) AS c1
) AS t1
) AS t2
) AS the_array;
First we create a set from array, then we select only distinct entries, and then aggregate it back into array.
首先我们从数组创建一个集合，然后我们只选择不同的条目，然后将它聚合回数组。
回答by tbussmann
Using DISTINCTimplicitly sorts the array. If the relative orderof the array elements needs to be preserved while removing duplicates, the function can be designed like the following: (should work from 9.4 onwards)
使用DISTINCT隐式对数组进行排序。如果在删除重复项时需要保留数组元素的相对顺序，则可以将函数设计如下：（应该从 9.4 开始工作）
CREATE OR REPLACE FUNCTION array_uniq_stable(anyarray) RETURNS anyarray AS
$body$
SELECT
array_agg(distinct_value ORDER BY first_index)
FROM
(SELECT
value AS distinct_value,
min(index) AS first_index
FROM
unnest() WITH ORDINALITY AS input(value, index)
GROUP BY
value
) AS unique_input
;
$body$
LANGUAGE 'sql' IMMUTABLE STRICT;
回答by Gregorio Freidin
In a single query i did this:
在单个查询中，我这样做了：
SELECT (select array_agg(distinct val) from ( select unnest(:array_column) as val ) as u ) FROM :your_table;
回答by bayonatof
For people like me who still have to deal with postgres 8.2, this recursive function can eliminate duplicates without altering the sorting of the array
对于像我这样还需要处理 postgres 8.2 的人来说，这个递归函数可以在不改变数组排序的情况下消除重复项
CREATE OR REPLACE FUNCTION my_array_uniq(bigint[])
RETURNS bigint[] AS
$BODY$
DECLARE
n integer;
BEGIN
-- number of elements in the array
n = replace(split_part(array_dims(),':',2),']','')::int;
IF n > 1 THEN
-- test if the last item belongs to the rest of the array
IF ()[1:n-1] @> ()[n:n] THEN
-- returns the result of the same function on the rest of the array
return my_array_uniq([1:n-1]);
ELSE
-- returns the result of the same function on the rest of the array plus the last element
return my_array_uniq([1:n-1]) || [n:n];
END IF;
ELSE
-- if array has only one item, returns the array
return ;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
for exemple :
举个例子 ：
select my_array_uniq(array[3,3,8,2,6,6,2,3,4,1,1,6,2,2,3,99]);
will give
会给
{3,8,2,6,4,1,99}
