Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

PostgreSQL error: bind message has N parameter formats but 0 parameters

Errorbind message has 9914 parameter formats but 0 parameters

Short answer

One query is carrying more bound values than the Postgres protocol can describe. The count is stored in a 16-bit field, so a very large placeholder count cannot be described. Bulk inserts (rows times columns) and huge IN lists reach it. Split the work into batches.

  • Applies to PostgreSQL 16, TypeORM 0.3, NestJS 11, Node.js 22
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Count the placeholders that one call creates: rows times columns for an insert, or the length of the array for an IN filter.

    // log before the failing call
    console.log(rows.length, Object.keys(rows[0]).length, rows.length * Object.keys(rows[0]).length);
  2. 2Match what you see, then jump to its fix

    1. 1The failing call is save(), insert() or a query builder values() with a very large arrayA bulk save or insert with too many rowsFix
    2. 2The failing call is find() with In(ids), whereInIds or a query builder IN (:...ids) built from a big arrayA very long IN (...) list in a read queryFix

What does the error look like?

  • bind message has 9914 parameter formats but 0 parameters
  • QueryFailedError: bind message has N parameter formats but 0 parameters

Cause 1 of 2

A bulk save or insert with too many rows

1Check

A multi-row insert binds one value for every column of every row, so 20,000 rows of 4 columns means 80,000 values in one statement. The Bind message stores its counts as Int16 fields, which is a 16-bit size.

// Compare rows.length * columns with the number in the error. Tens of thousands means this cause.

2Fix

Send the rows in batches that stay far below the limit.

const size = 1000;
for (let i = 0; i < rows.length; i += size) {
  await repo.insert(rows.slice(i, i + size));
}

3Verify

The loop finishes without the error, and the row count matches.

console.log(await repo.count());

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 2

A very long IN (...) list in a read query

1Check

Every element of the list becomes its own placeholder, so a list of 70,000 ids is 70,000 values.

console.log(ids.length);

2Fix

Read in batches of ids and merge the results. In raw SQL, one array parameter with ANY is another option, because the array is a single value.

import { In } from 'typeorm';

const found = [];
for (let i = 0; i < ids.length; i += 1000) {
  found.push(...await repo.findBy({ id: In(ids.slice(i, i + 1000)) }));
}

3Verify

The read returns the same number of rows as ids that exist.

console.log(found.length);

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Wrap bulk writes in a helper that always batches, so no caller can forget.
  • Log the size of an array before you pass it to a query.
  • Keep batches in the low thousands of values per statement.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Back end

NestJS GraphQL cannot work out the output type of a field or query

Cannot determine a GraphQL output type for the "<name>". Make sure your class is decorated with an appropriate decorator.

In code-first mode Nest reads TypeScript types through decorators, and arrays, promises and unions disappear at runtime.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.