Node-postgres: How to insert multiple rows with array field in a single query?

[Shekhrozx]

My table fields are like that:
id: pk integer
name: text
info: integer[]
I am gonna insert multiple rows in a single query.

client.query(
    "INSERT INTO mytable (name, info) SELECT * FROM UNNEST ($1::text[], $2::int[])",
    [
         ["John", "Adam", "Mark"],
         [[1,2,3,4], [2,3,5,4],[4,4,5,8]]
    ]
)

But I got the error:
error: column "info" is of type integer[] but expression is of type integer.
How can I do this?

[charmander]

Since each parameter represents the values of one column for several rows, it needs to be an array of the column type. You have name right (name is text, $1 is text[]), but $2 needs to be an array of arrays of integers.

… SELECT * FROM UNNEST ($1::text[], $2::int[][])
--                                           ^^

[Shekhrozx]

I tried to do that way$2::int[][]. But it gave the same error.

[charmander]

Sorry, I forgot that PostgreSQL doesn’t have arrays of arrays. (Kind of an important thing to forget.) pg-format might work, as described in https://github.com/brianc/node-postgres/issues/957#issuecomment-200000070. Other than that… there’s manual string conversion, which is not very nice:

client.query(
    "INSERT INTO mytable (name, info) " +
        "SELECT name, info::int[] FROM UNNEST ($1::text[], $2::text[]) AS t (name, info)",
    [
         ["John", "Adam", "Mark"],
         [[1,2,3,4], [2,3,5,4],[4,4,5,8]].map(a => '{' + a + '}')
    ]
)

[gajus]

See:

• https://github.com/gajus/slonik#sqlunnestlist
• https://github.com/gajus/slonik#slonik-recipes-inserting-large-number-of-rows

[retorquere]

@charmander will that work when one of the elements within the array has quotes (either double or single or both)?

[charmander]

@retorquere No, that example was specific to ints. If you want text, you could use require('pg/lib/utils').prepareValue, which includes this more versatile array encoding: https://github.com/brianc/node-postgres/blob/5a92ba3701e576b91697e653c6c369500811e9e5/lib/utils.js#L14-L43

Passing a JSON parameter might be a better choice.

[retorquere]

I settled on a JSON parameter, works nicely.

Any chance that at some point it'd just be possible to insert multiple semi-colon separated statements in one query?

[charmander]

That’s already possible when there are no parameters. With parameters, no; pg doesn’t parse queries. (There’s some message pipelining as a potential alternative that pg doesn’t expose, but user-defined PostgreSQL functions might work too.)

[retorquere]

Could I use node-postgres to make a parametrized query into a flat string; concat a bunch together, and then send those in one go?

[charmander]

You could use a combination of prepareValue and pg-format to do that.

[Nielsbishere]

Will this ever be added? Does the map(a => '{' + a + '}') have any side-effects on int[][]/smallint[][]?
Because I still get the error error: column "testing" is of type smallint[] but expression is of type smallint with that. Input looks like the following:

[
[
    '{4995}',  '{2495,1295}', '{1995}'
]
]

insert into test(testing) 
select * from unnest ($1::smallint[][]) 
on conflict do nothing

[charmander]

@Nielsbishere The cast from text is necessary:

SELECT ints::smallint[] FROM unnest ($1::text[]) AS t (ints)

My guess is that this will never be added to pg, because it’s not something that’s missing from pg’s implementation of its intended functionality.

[charmander]

Generic answer to the original question using JSON:

client.query(
    `INSERT INTO mytable (name, info)
     SELECT name, info FROM jsonb_to_recordset($1::jsonb) AS t (name text, info int[])`,
    [
        JSON.stringify([
            {name: "John", info: [1, 2, 3, 4]},
            {name: "Adam", info: [2, 3, 5, 4]},
            {name: "Mark", info: [4, 4, 5, 8]},
        ]),
    ]
)