eliminate duplicate array values in postgres

The sort(int[]) and uniq(int[]) functions are provided by the intarray contrib module.

To enable it's use you must register the module by executing the _int.sql file that you can found in contrib directory of your postgresql installation.

On a Debian/Ubuntu system you must install the postgresql-contrib-8.4 package, then the file will be under /usr/share/postgresql/8.4/contrib/_int.sql (version numbers may be different)

If you don't want to use the intarray contrib module, or if you have to remove duplicates from arrays of different type, you have two other ways.

If you have at least PostgreSQL 8.4 you could take advantage of unnest(anyarray) function

SELECT ARRAY(SELECT DISTINCT UNNEST('{1,2,3,2,1}'::int[]) ORDER BY 1);
 ?column? 
----------
 {1,2,3}
(1 row)

Alternatively you could create your own function to do this

CREATE OR REPLACE FUNCTION array_sort_unique (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL
AS $body$
  SELECT ARRAY(
    SELECT DISTINCT $1[s.i]
    FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY 1
  );
$body$;

Here is a sample invocation:

SELECT array_sort_unique('{1,2,3,2,1}'::int[]);
 array_sort_unique 
-------------------
 {1,2,3}
(1 row)

I'd recommend the following approach which does not require the intarray contrib module and works across all data-types.

1. First examples contain a self-defined array data-type and values.
2. Second examples assume there is a table with an array column present, from which the values are being pulled.

Returning Unique Values

Integers

SELECT ARRAY(SELECT DISTINCT UNNEST('{2,1,3,1,1,2}'::int[]) a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_int_arr_col) a) AS unique_values FROM my_table

Returns: {2,1,3}

Text

SELECT ARRAY(SELECT DISTINCT UNNEST('{"this","is","this","some","text","is"}'::text[]) a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_text_arr_col) a) AS unique_values FROM my_table

Returns: {text,this,some,is}

Dates

SELECT ARRAY(SELECT DISTINCT UNNEST('{2012-04-08,2012-04-09,2012-04-07,2012-04-08,2012-04-09}'::date[]) a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_date_arr_col) a) AS unique_values FROM my_table

Returns: {2012-04-08,2012-04-09,2012-04-07}

Sorting Unique Values

If you'd like to return the values in sorted fashion, append ORDER BY a directly after the UNNEST() call similar to the following:

Integers

SELECT ARRAY(SELECT DISTINCT UNNEST('{2,1,3,1,1,2}'::int[]) a ORDER BY a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_int_arr_col) a ORDER BY a) AS unique_values FROM my_table

Returns: {1,2,3}

Text

SELECT ARRAY(SELECT DISTINCT UNNEST('{"this","is","this","some","text","is"}'::text[]) a ORDER BY a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_text_arr_col) a ORDER BY a) AS unique_values FROM my_table

Returns: {is,some,text,this}

Dates

SELECT ARRAY(SELECT DISTINCT UNNEST('{2012-04-08,2012-04-09,2012-04-07,2012-04-08,2012-04-09}'::date[]) a ORDER BY a) AS unique_values
-- or --
SELECT ARRAY(SELECT DISTINCT UNNEST(my_date_arr_col) a ORDER BY a) AS unique_values FROM my_table

Returns: {2012-04-07,2012-04-08,2012-04-09}

For people like me who still have to deal with postgres 8.2, this recursive function can eliminate duplicates without altering the sorting of the array

CREATE OR REPLACE FUNCTION my_array_uniq(bigint[])
  RETURNS bigint[] AS
$BODY$
DECLARE
    n integer;
BEGIN

    -- number of elements in the array
    n = replace(split_part(array_dims($1),':',2),']','')::int;

    IF n > 1 THEN
        -- test if the last item belongs to the rest of the array
        IF ($1)[1:n-1] @> ($1)[n:n] THEN
            -- returns the result of the same function on the rest of the array
            return my_array_uniq($1[1:n-1]);
        ELSE
            -- returns the result of the same function on the rest of the array plus the last element               
            return my_array_uniq($1[1:n-1]) || $1[n:n];
        END IF;
    ELSE
        -- if array has only one item, returns the array
        return $1;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

for exemple :

select my_array_uniq(array[3,3,8,2,6,6,2,3,4,1,1,6,2,2,3,99]);

will give

{3,8,2,6,4,1,99}