CODEWITHSUNDEEP
arrayspostgresqlpostgresql-9.5
Kyle K
Kyle K

Reputation: 553

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.

Upvotes: 45

Views: 78581

Answers (4)

noreply
noreply

Reputation: 917

single:

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

multiple:

UPDATE table1
SET integer_array = array_cat(integer_array, ARRAY[4,5]);

https://www.postgresql.org/docs/9.1/functions-array.html#ARRAY-FUNCTIONS-TABLE

Upvotes: 12

Fernando Meneses Gomes
Fernando Meneses Gomes

Reputation: 867

-- Declaring the array

arrayName int8[];

-- Adding value 2206 to int array

arrayName := arrayName || 2206;

-- looping throught the array

FOREACH i IN ARRAY arrayName 
LOOP 

 RAISE NOTICE 'array value %', i;

END LOOP;

cheers

Upvotes: 8

Marat Safin
Marat Safin

Reputation: 1909

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}';

Upvotes: 25

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

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}

Upvotes: 76

Related Questions