eliminate duplicate array values in postgres

Question

I have an array of type bigint,how con i remove the duplicate values in that array.

ex: array[1234,5343,6353,1234,1234]

I should get array[1234,5343,6353,]

I tested out the example SELECT uniq(sort('{1,2,3,2,1}'::int[])) in the postgres manual but it is not working.

Answer 1

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)