CODEWITHSUNDEEP
arrayspostgresqlpg-promise
Ron I
Ron I

Reputation: 4250

append integer to an array (using express and pg-promise)

I am using the array_append function from PostgreSQL's docs, and I don't see what I'm doing wrong, yet get the following error:

error:  { error: invalid input syntax for integer: "{22}"

Here is the query:

UPDATE epics 
   SET collection_ids = array_append(collection_ids, '{${id}}') 
WHERE id = ${epicId}

Here is the code for the table:

CREATE TABLE epics (
  id SERIAL PRIMARY KEY,
  title varchar,
  collection_ids SMALLINT[]
);

Upvotes: 2

Views: 230

Answers (2)

vitaly-t
vitaly-t

Reputation: 25840

As per the comment from @abelisto, this is the best solution:

UPDATE epics SET collection_ids = collection_ids || ${id}::SMALLINT
WHERE id = ${epicId}

It is more elegant, because it is SQL-native, no type ambiguity, and even supports an array, if it becomes needed (below we presume ${id} passed in as an array):

UPDATE epics SET collection_ids = collection_ids || ${id}::SMALLINT[]
WHERE id = ${epicId}

Because in pg-promise, an array of 1,2,3 will be automatically formatted as array[1,2,3].

Upvotes: 2

Ron I
Ron I

Reputation: 4250

UPDATE epics SET collection_ids = array_append(collection_ids, '${id}') WHERE id = ${epicId}

Works.

Upvotes: -1

Related Questions