PostgreSQL: How to Remove Duplicate Values from Array

Often in PostgreSQL you may want to remove duplicate values from an array.

You can use the following syntax to do so:

SELECT *,
       (SELECT array_agg(DISTINCT val)
        FROM ( SELECT UNNEST(positions) AS val ) AS u ) AS non_dups
FROM athletes;

This particular example will create a new column named non_dups that removes all duplicate values from the positions column in the athletes table.

The following example shows how to use this syntax in practice.

Example: How to Remove Duplicate Values from Array in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball teams:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  positions TEXT[]
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', '{"Guard", "Forward", "Forward"}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{"Guard", "Center", "Guard"}');
INSERT INTO athletes VALUES (0003, 'Kings', '{"Guard", "Forward", "Center"}');
INSERT INTO athletes VALUES (0004, 'Lakers', '{"Forward", "Forward", "Forward"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nuggets', '{"Guard", "Center", "Forward"}');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |         positions         
----+---------+---------------------------
  1 | Mavs    | {Guard,Forward,Forward}
  2 | Hawks   | {Guard,Center,Guard}
  3 | Kings   | {Guard,Forward,Center}
  4 | Lakers  | {Forward,Forward,Forward}
  5 | Celtics | {Guard,Center,Center}
  6 | Nuggets | {Guard,Center,Forward}

Suppose that we would like to remove all duplicate values from each array in the positions column.

We can use the following syntax to do so:

SELECT *,
       (SELECT array_agg(DISTINCT val)
        FROM ( SELECT UNNEST(positions) AS val ) AS u ) AS non_dups
FROM athletes;

 id |  team   |         positions         |        non_dups        
----+---------+---------------------------+------------------------
  1 | Mavs    | {Guard,Forward,Forward}   | {Forward,Guard}
  2 | Hawks   | {Guard,Center,Guard}      | {Center,Guard}
  3 | Kings   | {Guard,Forward,Center}    | {Center,Forward,Guard}
  4 | Lakers  | {Forward,Forward,Forward} | {Forward}
  5 | Celtics | {Guard,Center,Center}     | {Center,Guard}
  6 | Nuggets | {Guard,Center,Forward}    | {Center,Forward,Guard}

Notice that the new column named non_dups contains the values from the positions column with all duplicate values removed.

Feel free to use similar syntax to remove duplicate values from arrays in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Random Value from Array
PostgreSQL: Select Rows where Value NOT in Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty