Often in PostgreSQL you may want to append an item to an array column.
You can use the array_append() function with the following syntax to do so:
UPDATE athletes SET points = array_append(points,'50');
This particular example will append the value 50 to the end of each array in the array column named points of the athletes table.
Note that you can also use a WHERE clause to only append a value to arrays that meet specific conditions:
UPDATE athletes SET points = array_append(points,'50')
WHERE team = 'Mavs';
This particular example will append the value 50 to the end of each array in the array column named points only for the rows where the value in the team column is equal to ‘Mavs’.
The following example shows how to use this syntax in practice.
Example: How to Append Item to 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, 'Mavs', '{29.4,30.5,31.2}');
INSERT INTO athletes VALUES (0005, 'Mavs', '{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 | Mavs | {29.4,30.5,31.2}
5 | Mavs | {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 append the value 50 to the end of each array in the points column.
We can use the following syntax to do so:
-- append 50 to each array in 'points' column
UPDATE athletes SET points = array_append(points,'50');
-- view updated table
SELECT * FROM athletes;
id | team | points
----+---------+---------------------
1 | Mavs | {12.1,15.6,17.8,50}
2 | Hawks | {22.5,16.5,18.7,50}
3 | Rockets | {20.1,24.4,34.6,50}
4 | Mavs | {29.4,30.5,31.2,50}
5 | Mavs | {12.6,15.5,17.9,50}
6 | Celtics | {20.2,23.4,26.6,50}
7 | Lakers | {18.8,20.1,23.5,50}
Notice that the value 50 has been appended to each array in the points column of the table.
Suppose instead that we would like to append the value 50 to the end of each array in the points column only for the rows where the team column is equal to Mavs.
We can use the following syntax to do so:
-- append 50 to each array in 'points' column where 'team' is Mavs
UPDATE athletes SET points = array_append(points,'50')
WHERE team = 'Mavs';
-- view updated table
SELECT * FROM athletes;
id | team | points
----+---------+---------------------
2 | Hawks | {22.5,16.5,18.7}
3 | Rockets | {20.1,24.4,34.6}
6 | Celtics | {20.2,23.4,26.6}
7 | Lakers | {18.8,20.1,23.5}
1 | Mavs | {12.1,15.6,17.8,50}
4 | Mavs | {29.4,30.5,31.2,50}
5 | Mavs | {12.6,15.5,17.9,50}
Notice that the value 50 has been appended to each array in the points column of the table only for the rows where the team column is equal to Mavs.
Feel free to use similar syntax to append values to 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: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty
