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?
While there is no function to accomplish that, you can use
unnest() to convert an array of elements, to a table of rows of one-column,DISTINCT to remove duplicatesARRAY(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
);
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 ...).
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'}
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 :-)
