PostgreSQL error: bind message has N parameter formats but 0 parameters
bind message has 9914 parameter formats but 0 parametersShort 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.
Fix it fast
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);2Match what you see, then jump to its fix
What does the error look like?
bind message has 9914 parameter formats but 0 parametersQueryFailedError: 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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- WorkforceA field app, an admin console and a self-hosted Node.js server for push and reconciliation.
Related fixes
Back end
TypeORM sync fails when a new column is NOT NULL on an existing table
QueryFailedError: column "price" contains null values
TypeORM columns are NOT NULL by default.
3 causes, each with a fixBack end
TypeORM migration:generate finds no changes to put in the migration
typeorm migration:generate finds no changes in the database schema, writes no file and exits with code 1
migration:generate compares the entities your DataSource loads with the real database, and writes only the difference.
4 causes, each with a fixBack end
TypeORM connects but creates no tables or columns
A new column is missing, with no error at startup
TypeORM does not create tables by itself.
3 causes, each with a fixBack end
TypeScript cannot find module '@nestjs/core' or its type declarations
error TS2307: Cannot find module '@nestjs/common' or its corresponding type declarations.
TS2307 means TypeScript searched node_modules and found neither the package nor its types.
3 causes, each with a fixBack end
Node.js in Docker Compose cannot reach MySQL on 127.0.0.1:3306
Error: connect ECONNREFUSED 127.0.0.1:3306
Inside a container, 127.0.0.1 is that container, not MySQL.
3 causes, each with a fixBack 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