Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding value to Postgres integer array

I am looking for help in adding a value 10 to an int[] in PostgreSQL 9.5.

Looking at the documentation I should be able to use this format to update it but it is not working:

int[] + int   push element onto array (add it to end of array) 

I have tried running this:

update table1 set integer_array = integer_array + 10::Integer.  

It did not work and I got this error:

ERROR: operator does not exist: integer[] + integer   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.   Position: 67 

I feel this is the same format like the one presented in the documentation on how to perform this operation.

like image 850
Kyle K Avatar asked Nov 16 '16 18:11

Kyle K


People also ask

How do I extend an array in PostgreSQL?

PostgreSQL does not have an EXTEND method like Oracle does. PostgreSQL, however, can extend 1-dimensional arrays automatically by assigning array elements beyond the end of the current array length.

How do you add values to an array in SQL?

The ARRAY_APPEND function is used to append values to an array. As with all array functions the values can be field values, calculated values, literals, or a combination thereof. The only requirement is that all the values are of the same data type. The following is an example of a query using ARRAY_APPEND .

How do you update an array in a database?

You can use the update_batch function of codeigniter for the desired result as you are sending a multi dimension array to the update function it will through a error.

How do I change the value of an array in SQL?

You use the Update statement ADD clause to add elements into an array. You use a SET clause to change the value of an existing array element. And you use a REMOVE clause to remove elements from an array.


2 Answers

Use array_append function to append an element at the end of an array:

UPDATE table1 SET integer_array = array_append(integer_array, 5); 

5 is a value of choice, it's of an integer datatype in your case. You probably need some WHERE clause as well not to update the entire table.

Try below to see how it works:

SELECT ARRAY[1,2], array_append(ARRAY[1,2],3); 

Result:

 array | array_append -------+--------------  {1,2} | {1,2,3} 
like image 134
Kamil Gosciminski Avatar answered Oct 04 '22 04:10

Kamil Gosciminski


I like this way better:

UPDATE table1 SET integer_array = integer_array || '{10}'; 

You can also add multiple values with single query:

UPDATE table1 SET integer_array = integer_array || '{10, 11, 12}'; 
like image 39
Marat Safin Avatar answered Oct 04 '22 02:10

Marat Safin