unnest(array[$1]) returns text instead of the actual type

[mehmetkarakamis]

It seems that a statement work good on PostreSQL but fails on node-postgres.

Re-produce the error:

Step-1: Create two tables:

CREATE TABLE users(
    id serial primary key
);

CREATE TABLE privileges(
    id_authorizing int REFERENCES users(id),
    id_authorized int REFERENCES users(id)
);

Step 2: Create two values to write on database.

const x = 1;
const y = [2,3,4];

client.query("INSERT INTO privileges (id_authorizing, id_authorized) SELECT $1, UNNEST(ARRAY[$2])", [x,y]);

Throws an error like: error: column "id_authorized" is of type integer but expression is of type text
This statement seems to work on PostgreSQL:

INSERT INTO privileges (id_authorizing, id_authorized) SELECT 2, UNNEST(ARRAY[2,3,4]);

Output:

INSERT 0 2

[sehrope]

The second parameter is already an array so you end up creating an array of an array (and then unnesting that). Remove the ARRAY[...] and instead explicitly cast the parameter to an int[]:

const x = 1;
const y = [2,3,4];

client.query(
  "INSERT INTO privileges (id_authorizing, id_authorized) SELECT $1, UNNEST($2::int[])",
  //                                                                          ^^^^^^^
  [x,y]
);