Arrays are one of the most significant data structures in programming paradigms that store the data in contiguous memory locations. In Postgres, the ARRAY data type is used to store the data of similar data types. Postgres allows us to update an already existing array using the UPDATE SET statement. In PostgreSQL, an entire array or only specific elements of an array can be updated using the UPDATE command.

This post presents a detailed guide on updating the array’s elements using the UPDATE query.

How to Update an Array in PostgreSQL?

Use the UPDATE query to update a specific array element or an entire array in PostgreSQL:

UPDATE tbl_name
SET array_name = '{array_values}' 
WHERE condition;

The above syntax is used to update/overwrite a whole array. Use the below-given syntax to update a specific element of an array:

UPDATE tbl_name
SET array_name[index] = '{array_value}' 
WHERE condition;

Let’s learn these concepts practically!

Example 1: Updating an Entire Array

A sample table named “std_info” has already been created with the following data:

SELECT * FROM std_info;

Suppose we want to modify the “std_num” array for a student whose id is 3. To accomplish this task, the UPDATE statement will be executed as follows:

UPDATE std_info
SET std_num = '{52, 80, 60, 51, 60}' 
WHERE std_id = 3;

Run the SELECT command to verify the updated array’s data:

SELECT * FROM std_info;

The entire “std_num” array for id 3 has been updated successfully.

Example 2: Updating Specific Elements of an Array

In this example, we will update some specific elements of the “std_num” array:

UPDATE std_info
SET std_num[2] = 68 
WHERE std_id = 3;

The above statement will modify the second index of the “std_num” array for id 3:

Use the SELECT command to verify the updated array’s element:

SELECT * FROM std_info;

The specified array element has been updated successfully.

Conclusion

PostgreSQL allows us to update an already existing array using the “UPDATE SET” statement. In Postgres, an entire array or only specific elements of an array can be updated using the UPDATE command. Specify the array index with the array name to update only a specific element of an array. This Post has demonstrated a practical guide on how to update an array in PostgreSQL using suitable examples.