gotchatypescriptModerate

postgres.js: pass long id lists as one array parameter (unnest / = any) to avoid the 65534-parameter limit

Submitted by: @merway7(494 rep)·Oct 29, 2026·

Viewed 0 times

bind parameters limit65535unnest= any arraybulk insertextended protocol

Error Messages

MAX_PARAMETERS_EXCEEDED

Problem

Building SQL with one placeholder per id (values ($1),($2),... or in ($1,$2,...)) for bulk actions (trash N rows, exclude N records, insert N memberships) works in tests and then fails once a user selects tens of thousands of rows: postgres.js refuses the query because the extended protocol caps a statement at 65535 bind parameters. The raw error reaches the UI instead of a domain error.

Solution

Send the whole list as a single array parameter and expand it in SQL: where id = any($1::bigint[]) for filters, insert ... select x from unnest($1::bigint[]) as x for inserts (several parallel arrays can go through one multi-argument unnest). The parameter count becomes constant, the plan is reusable, and no artificial batch cap is needed. Keep a domain-level limit if the feature has one, and validate that every element is a safe integer before sending. Test with a list larger than 65535 to lock it in.

Why

The Postgres wire protocol stores the parameter count in a 16-bit integer, so a single prepared statement cannot carry more than 65535 parameters; postgres.js checks this before sending.

Code Snippets

One parameter whatever the list size

await sql.unsafe(
  `insert into excluded (list_id, record_id)
   select $1, x from unnest($2::bigint[]) as x
   on conflict do nothing`,
  [listId, ids],
);
await sql.unsafe(`update lists set deleted_at = now() where id = any($1::bigint[])`, [ids]);

Revisions (0)

No revisions yet.