PostgreSQL: How to Remove Specific Value from Array

Often in PostgreSQL you may want to remove a specific value from an array.

You can use the array_remove() function with the following syntax to do so:

UPDATE athletes SET positions = array_remove(positions, 'Guard');

This particular example will remove the value ‘Guard’ from each array in the position column of the table named athletes.

If the value ‘Guard’ appears multiple times in an array then each occurrence will be removed.

If the value ‘Guard’ does not appear at all in an array then the array will remain unchanged.

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

Example: How to Remove Specific Value 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', '{"Center", "Center", "Center"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"Guard", "Center", "Center"}');
INSERT INTO athletes VALUES (0006, 'Nuggets', '{"Guard", "Center", "Center"}');

-- 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}

The positions column shows the positions of various players on each team.

Suppose that we would like to remove the value ‘Guard’ from each array in the positions column.

We can use the following syntax to do so:

-- remove 'Guard' from each array in positions column
UPDATE athletes SET positions = array_remove(positions, 'Guard');

-- view updated table
SELECT * FROM athletes;

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

Notice that the value ‘Guard’ has been removed from each array in the positions column.

Feel free to use similar syntax to remove a specific value from an array column of 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: How to Get Last Element of an Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty