How can I get a unique array in PostgreSQL?

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