Adding value to int array

Question

I looked at the postgres website and to add a integer to a intarray it says int[] + int. I have a int array in a table and I eventually want to insert the array with another value added to it into another table. So testing with a select statement I am not sure where I am messing up.

So a am doing:

SELECT myarray + 9::integer from foo
ERROR: operator does not exist: smallint[] + integer

What am I missing. It looks just like the postgres website on intarrays from 9.1 postgres.

Answer 1

Use the concatenation operator

myarray || 9

Details in the manual: https://www.postgresql.org/docs/current/9.1/functions-array.html

Answer 2

intarray is an extension, you need to add it first to use its push element onto array (add it to end of array) opperator.. Before you add the extension, you get this.

SELECT ARRAY[1]::smallint[] + 1;
ERROR:  operator does not exist: smallint[] + integer
LINE 1: SELECT ARRAY[1]::smallint[] + 1;
                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Then we add the extension and retry,

test=# CREATE EXTENSION intarray ;
CREATE EXTENSION

test=# SELECT ARRAY[1]::smallint[] + 1;
 ?column? 
----------
 {1,1}
(1 row)

Or you can use the regular array-to-element concatenation ||

SELECT ARRAY[1] || 1;
 ?column? 
----------
 {1,1}
(1 row)

But be careful, it seems to be substantially slower. Also, in your example you're using smallint, you may have to explicitly cast the rhs to smallint.

SELECT ARRAY[1::smallint] || 5::smallint;

-vs-

SELECT ARRAY[1::smallint] || 5;

Answer 3

Ya I actually just figured it out. I need to use something like this

select array_append(myarray,1::smallint) from foo 

I was using wrong command.