How to Update Array in PostgreSQL

PostgreSQL makes it easy to work with array data type. Sometimes you may need to update array in PostgreSQL with new data. In this article, we will look at how to change array in PostgreSQL, modify array in PostgreSQL, and merge arrays in PostgreSQL.

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}

Also read : How to Create Array in PostgreSQL

Replace all elements in an Array

You can easily replace all elements of an array using UPDATE … SET statement.

# 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.

Also read : How to Insert Into Array in PostgreSQL

Replace some elements in an Array

You can also 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.

Also read : How to Convert String Case in PostgreSQL

Append and Prepend to Array

You an also append and prepend values to your array using array_append and array_prepend functions respectively, as shown below.

# 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.

# 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.

Also read : How to Concatenate Strings in PostgreSQL

Remove array element

Similarly, 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.

Also read : How to Update Multiple Columns in PostgreSQL

Merge Arrays in PostgreSQL

You can merge two arrays 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.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!