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.

• user1822: Note that 9.1 is unsupported and no longer maintained. You should upgrade as soon as possible. postgresql.org/support/versioning

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;

• user1822: No need for an extension. The built-in || operator will work just fine.

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.

• Evan Carroll: array_append is just the functional backend of ||. For clarity, I wouldn't use it.
• Kyle K: Why is it a bad idea to use it?
• Evan Carroll: It's less clear, || is known as concatenation and it's used everywhere with all different types. Type \doS+ || in psql. All opperators in PostgreSQL have functional backends, we usually treat them as implementation details though and don't use them.