5

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 bar elements removed? Does PostgreSQL have a unique function that works on a text-array, or an anyarray of anyelement?

11

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
| improve this answer | |
0

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 :-)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.