Last updated on July 2nd, 2024 at 09:09 am
PostgreSQL supports a vast range of data types including arrays. It provides tons of functions and operators that makes it easy to work with array data type. Sometimes you may need to update array in PostgreSQL with new data. This is commonly required in transactional databases such as that of Ecommerce sites. Updating arrays presents several interesting use cases. In this article, we will look at how to change array in PostgreSQL, modify array in PostgreSQL, and merge arrays in PostgreSQL.
Why Update Array in PostgreSQL
Typically, when we need to update one or more column values, we simply use UPDATE statement. But since an array column data type contains an entire array in a single column, using UPDATE statement will not work. Updating one or more items in an array becomes even more difficult since UPDATE statement will completely overwrite the entire column value for a given row. Luckily, PostgreSQL offers many specific functions and statements that allow you to update one or more rows, append, prepend, remove or merge array items. These are really important use cases and without these functions we will have to constantly rewrite the entire final array value from scratch using UPDATE statement.
How to Update Array in PostgreSQL
Here are the different ways to update array in PostgreSQL. Let us say you have the following array column.
#create table employees (id int,name varchar,sales integer[]);
In the above example, we have created column sales as an array of integers.
Let us also insert data in our array column.
# insert into employees (id, name, sales)
values (1, ' John Doe', ARRAY [9,7,5,4,3,2]),
(2, ' Jim Day', ARRAY [8,6,3,2,9,2]);
# select * from employees;
id | name | sales
----+-----------+---------------
1 | John Doe | {9,7,5,4,3,2}
2 | Jim Day | {8,6,3,2,9,2}
Replace all elements in an Array
PostgreSQL allows you to update all or some elements in an array. Each of these use cases requires a different approach. But in all cases you need to use UPDATE…SET statement to replace all elements of an array.
# update employees
set sales = '{10,12,31,10,11}'
where id=1;
# select * from employees;
id | name | sales
----+-----------+------------------
2 | Jim Day | {8,6,3,2,9,2}
1 | John Doe | {10,12,31,10,11}
In the above UPDATE statement, we provide array values within curly braces enclosed in single quotes. Postgres will replace the entire array for rows that match the WHERE condition. The new array can be of different length and/or data type than the old array. The new array will completely overwrite the old one.
Replace some elements in an Array
Often you may need to update specific array items. For this purpose, you will need to use update set along with the index of the item that you need to update. Here is an example to replace specific array elements as shown below.
=# update employees
set sales[2] = 41
where id=1;
# select * from employees;
id | name | sales
----+-----------+------------------
2 | Jim Day | {8,6,3,2,9,2}
1 | John Doe | {10,41,31,10,11}
In the above statement, we replace only the second array element by setting sales[2] value. The array item index starts from 1 and not 0. So sales[2] refers to the second array item.
Append and Prepend to Array
Sometimes you don’t need to update one or more items of an array but need to add one or more items before or after the array. For this, you can append and prepend values to your array using array_append and array_prepend functions respectively, as shown below. In the following query, we append 1 to the array in sales column where id=1.
# update employees
set sales = array_append(sales, 1)
where id=1;
# select * from employees;
id | name | sales
----+-----------+--------------------
2 | Jim Day | {8,6,3,2,9,2}
1 | John Doe | {10,41,31,10,11,1}
Here is the query to prepend to an array. Here is an example to prepend 9 to the array in sales column where id=1.
# update employees
set sales = array_prepend(9,sales)
where id=1;
# select * from employees;
id | name | sales
----+-----------+----------------------
2 | Jim Day | {8,6,3,2,9,2}
1 | John Doe | {9,10,41,31,10,11,1}
Please note, in array_append, you need to specify the value to be appended as second argument in function. In case of array_prepend, you need to specify it as the first element.
Remove array element
Sometimes, you may need to remove one or more items in an array. For this, you can use array_remove function to remove element from array.
# select array_remove(sales, 9) as reduced_array from employees where id=1;
reduced_array
--------------------
{10,41,31,10,11,1}
In array_remove, you need to specify the array element that you want to remove.
Merge Arrays in PostgreSQL
You may need to merge two or more arrays into a single array. You can do this in PostgreSQL using array_cat function
# select array_cat(sales, '{9,3}') as merged_array
from employees where id=1;
merged_array
--------------------------
{9,10,41,31,10,11,1,9,3}
# select array_cat(array[1,2],'{9,3}') as merged_array
from employees where id=1;
merged_array
--------------
{1,2,9,3}
In array_cat function, you can pass array column or using ARRAY literal object. But please note, array_cat() can concatenate only two arrays at a time. If you want to concatenate more than 2 arrays, you need to use the output of one array_cat() as the input of another array_cat().
array_cat(array_cat(array1,array2),array3)
Conclusion
In this article, we have learnt how to update arrays in PostgreSQL. Arrays are very useful data types for every application and updating it can be tricky. Unless you know the exact statement or function required to carry out the updating, it will become a very tedious problem for you. You can update one or more items of an array using UPDATE…SET statement. You can append, prepend, remove, merge array items using array_append(), array_prepend(), array_remove() and array_merge() functions.
Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read :
How to Create Array in PostgreSQL
How to Insert Into Array in PostgreSQL
How to Update Multiple Columns in PostgreSQL
How to Concatenate Strings in PostgreSQL
How to Convert String Case in PostgreSQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.