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.


Use the concatenation operator

myarray || 9

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

| improve this answer | |

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;
| improve this answer | |
  • 1
    No need for an extension. The built-in || operator will work just fine. – a_horse_with_no_name May 15 '17 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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