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.

  • Note that 9.1 is unsupported and no longer maintained. You should upgrade as soon as possible. postgresql.org/support/versioning
    – user1822
    Commented May 15, 2017 at 17:57

3 Answers 3


Use the concatenation operator

myarray || 9

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


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 ;

test=# SELECT ARRAY[1]::smallint[] + 1;
(1 row)

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

(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;


SELECT ARRAY[1::smallint] || 5;
  • 1
    No need for an extension. The built-in || operator will work just fine.
    – user1822
    Commented May 15, 2017 at 17:56

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.

  • array_append is just the functional backend of ||. For clarity, I wouldn't use it. Commented May 15, 2017 at 18:14
  • Why is it a bad idea to use it?
    – Kyle K
    Commented May 15, 2017 at 18:20
  • 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. Commented May 15, 2017 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.