How to get distinct array elements with postgres?

Question

I have an array with duplicate values in postgres. For example:

SELECT cardinality(string_to_array('1,2,3,4,4', ',')::int[]) as foo
=> "foo"=>"5"

I would like to get unique elements, for example:

SELECT cardinality(uniq(string_to_array('1,2,3,4,4', ',')::int[])) as foo
=> -- No function matches the given name and argument types. You might need to add explicit type casts.

Can I get unique elements of an array in postgres without using UNNEST ?

whats wrong with UNNEST? using UNNEST you will be able to SELECT DISTINCT and be done with the job.. — Bartosz Bilicki, Nov 16 '16 at 15:53
There is a specific `uniq(int[])` function in the [`intarray` module](https://www.postgresql.org/docs/current/static/intarray.html), but other than that, you have to use `unnest()`. — pozs, Nov 16 '16 at 16:00

Aliday K · Answer 1 · 2017-01-18T17:48:55.773

14

I prefer this syntax (about 5% faster)

create or replace function public.array_unique(arr anyarray)
returns anyarray as $body$
    select array( select distinct unnest($1) )
$body$ language 'sql';

using:

select array_unique(ARRAY['1','2','3','4','4']);

edited Jan 18 '17 at 17:48

answered Jan 18 '17 at 16:49

Aliday K

141
1
3

klin · Accepted Answer · 2020-04-08T19:26:46.783

For integer arrays use intarray extension:

create extension if not exists intarray;
select cardinality(uniq(string_to_array('1,2,3,4,4', ',')::int[])) as foo

or the function

create or replace function public.array_unique(arr anyarray)
    returns anyarray
    language sql
as $function$
    select array_agg(distinct elem)
    from unnest(arr) as arr(elem) 
$function$;

for any array. You can easily modify the function to preserve the original order of the array elements:

create or replace function public.array_unique_ordered(arr anyarray)
    returns anyarray
    language sql
as $function$
    select array_agg(elem order by ord)
    from (
        select distinct on(elem) elem, ord
        from unnest(arr) with ordinality as arr(elem, ord)
        order by elem, ord
        ) s
$function$;

Example:

with my_data(arr) as (values ('{d,d,a,c,b,b,a,c}'::text[]))
select array_unique(arr), array_unique_ordered(arr)
from my_data

 array_unique | array_unique_ordered
--------------+----------------------
 {a,b,c,d}    | {d,a,c,b}
(1 row)

I ended up doing the function as you suggest. I find it a bit disappointing that this is not by default it postgres. — Benjamin Crouzier, Nov 16 '16 at 21:55

score 1 · Answer 3 · answered May 25 '18 at 20:11

Going off of @klin's accepted answer, I modified it to remove nulls in the process of choosing only the distinct values.

create or replace function public.array_unique_no_nulls(arr anyarray)
returns anyarray
language sql
as $function$
select array_agg(distinct a)
from (
    select unnest(arr) a 
) alias
where a is not null
$function$;

How to get distinct array elements with postgres?

3 Answers3

Linked