How can I get a unique array in PostgreSQL?

Question

The following

SELECT ARRAY[a,b,c,d]
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one of the duplicate foo elements removed? Does PostgreSQL have a unique function that works on a text-array, or an anyarray of anyelement?

Answer 1

While there is no function to accomplish that, you can use

1. unnest() to convert an array of elements, to a table of rows of one-column,
2. DISTINCT to remove duplicates
3. ARRAY(query) to recreate the row.

That idiom looks like,

ARRAY( SELECT DISTINCT ... FROM unnest(arr) )

And in practice is applied like this,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e))
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

If you want it sorted you can do,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e) ORDER BY e)
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

And that can all can be written with CROSS JOIN LATERAL which is much cleaner,

SELECT ARRAY(
  SELECT DISTINCT e
  FROM ( VALUES
    ('foo', 'bar', 'foo', 'baz' )
  ) AS t(a,b,c,d)
  CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e)
  -- ORDER BY e; -- if you want it sorted
);

---

• Answer inspired by RhodiumToad on irc.freenode.net/#postgresql

Answer 2

If the input values are in one column (essentially an array in relational terms), then the unique array can also be obtained using the stock array_agg() function with a DISTINCT keyword:

SELECT array_agg(DISTINCT v)
FROM ( VALUES
  ('foo'), ('bar'), ('foo'), ('baz')
) AS t(v);

Output:

   array_agg   
---------------
 {bar,baz,foo}
(1 row)

If the input is an array, an unnest() can be used to turn the input into a one-column table first, and then array_agg(DISTINCT ...).

Answer 3

A simple function based on @Evan Carroll's answer

create or replace function array_unique (a text[]) returns text[] as $$
  select array (
    select distinct v from unnest(a) as b(v)
  )
$$ language sql;

with that in place you can

select internal.array_unique(array['foo','bar'] || array['foo'])

=> {'foo','bar'}

Answer 4

Perhaps more of a question / comment.

This code below preserves the original ordering. How can it be improved / made more efficient.

In essence, I believe it will also accomplish the set task.

select e
from
(
    select  array_agg(e) over (order by wf_rn) as e, row_number() over (order by wf_rn DESC) as wf_rn 
    from
    (
        select *, row_number() over (partition by e order by wf_rn) as wf_rn_e
        from
        (
            SELECT *, row_number() over () as wf_rn
            FROM 
            ( 
                select unnest(ARRAY['foo', 'bar', 'foo', 'baz']) as e
            ) AS t
        ) as A
    ) as A
    where wf_rn_e = 1
) as A
where wf_rn = 1

I have to say thank you for the "lateral join" pointer - that makes it super easy to execute this without having to wrap it as a function :-)