Node-postgres: Prepared statements maximum bindings

[ghost]

I am trying to do some big bulk inserts to Postgres via node-postgres When the bindings array exceeds 65536 values then passes to postgres the rest of values and when the query it runs I take the error

[error: bind message supplies 4 parameters, but prepared statement "" requires 65540]

[brianc]

@konhondros Postgres itself doesn't support more than 34464 parameters for a bound statement. You'll need to break up your bulk insert into multiple statements.

[hulbert]

@brianc this bit us pretty badly in production (via using knex, actually). Is there any way to have a more graceful error here? My team is happy to contribute it if you want to point us in a direction.

[charmander]

@hulbert You shouldn’t be binding an unbounded number of parameters. (If it’s through the intended use of a knex builtin, that’s a knex bug.) Use array parameters instead.

[hulbert]

@charmander do you mean something like this? Found here (https://github.com/brianc/node-postgres/wiki/Parameterized-queries-and-Prepared-Statements)

client.query('SELECT * FROM table WHERE id = ANY($1::int[])', [[id1, id2, id3]])

[charmander]

@hulbert Yes.

[phiresky]

@brianc It would still be useful if this would somehow throw a different error.

As it stands, for a parameterized query with X parameters you just get the error
_[bind message supplies X modulo 65536 parameters, but prepared statement "" requires X]_
which is very unhelpful for diagnosing the cause for the problem.

Also the limit of 34464 parameters is wrong, at least for postgresql 9.6.3 I can do a single insert with 65535 parameters without problems.