1

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?

2

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

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.