PostgreSQL: Apply Function to Each Element of Array Column

Often in PostgreSQL you may want to apply a function to each element of an array column.

You can use the following syntax to do so:

SELECT *,
       (SELECT array_agg(ceil(n)) FROM unnest(points) AS n) AS points_round
FROM athletes;

This particular example applies the ceil function (round a number up to nearest integer) to each element in the array column named points.

Here is how this syntax works:

• First, we use unnest to convert the array into individual rows.
• Then, we apply the ceil function to each row.
• Then, we use array_agg to convert the rows back to an array.

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

Example: How to Apply Function to Each Element of Array Column in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', '{12.1,15.6,17.8}');
INSERT INTO athletes VALUES (0002, 'Hawks', '{22.5,16.5,18.7}');
INSERT INTO athletes VALUES (0003, 'Rockets', '{20.1,24.4,34.6}');
INSERT INTO athletes VALUES (0004, 'Spurs', '{29.4,30.5,31.2}');
INSERT INTO athletes VALUES (0005, 'Kings', '{12.6,15.5,17.9}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{20.2,23.4,26.6}');
INSERT INTO athletes VALUES (0007, 'Lakers', '{18.8,20.1,23.5}');

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

 id |  team   |      points      
----+---------+------------------
  1 | Mavs    | {12.1,15.6,17.8}
  2 | Hawks   | {22.5,16.5,18.7}
  3 | Rockets | {20.1,24.4,34.6}
  4 | Spurs   | {29.4,30.5,31.2}
  5 | Kings   | {12.6,15.5,17.9}
  6 | Celtics | {20.2,23.4,26.6}
  7 | Lakers  | {18.8,20.1,23.5}

The points column shows the average points scored by each player during three consecutive seasons.

Suppose that we would like to round the numbers in each array of the points column up to the nearest integer.

We can use the following syntax to do so:

SELECT *,
       (SELECT array_agg(ceil(n)) FROM unnest(points) AS n) AS points_round
FROM athletes;

 id |  team   |      points      | points_round 
----+---------+------------------+--------------
  1 | Mavs    | {12.1,15.6,17.8} | {13,16,18}
  2 | Hawks   | {22.5,16.5,18.7} | {23,17,19}
  3 | Rockets | {20.1,24.4,34.6} | {21,25,35}
  4 | Spurs   | {29.4,30.5,31.2} | {30,31,32}
  5 | Kings   | {12.6,15.5,17.9} | {13,16,18}
  6 | Celtics | {20.2,23.4,26.6} | {21,24,27}
  7 | Lakers  | {18.8,20.1,23.5} | {19,21,24}

Notice that the new column named points_round contains each of the values from the points column rounded up to the nearest integer.

Feel free to use similar syntax to apply a function to each element of an array column 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