Inserting large number of rows leads to exec_bind_message error in postgres

[dimitriSaplatkin]

Hi,

I am currently facing a database error when trying to insert a large number of rows. In the example below I tried to insert 50.000 rows:

interface Database {
  person: PersonTable;
}

interface PersonTable {
  id: Generated<number>;
  createdAt: Date;
  updatedAt: Daten;
  firstName: string;
  lastName: string;
  email: string;
}

type NewPerson = Insertable<PersonTable>;

function createRandomString(length: number) {
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let result = '';
  for (let i = 0; i < length; i++) {
    result += chars.charAt(Math.floor(Math.random() * chars.length));
  }
  return result;
}

const newPersons: NewPerson[] = [];

for (let i = 0; i < 50000; i++) {
  newPersons.push({
      createdAt: new Date(),
      updatedAt: Daten(),
      firstName: createRandomString(5),
      lastName: createRandomString(5),
     email: createRandomString(5),
   })
}

await kysely.db.insertInto('person').values(newPersons).execute();

The insert query leads to the following error:

{
  "length": 126,
  "name": "error",
  "severity": "FEHLER",
  "code": "08P01",
  "file": "postgres.c",
  "line": "1672",
  "routine": "exec_bind_message"
}

The same example works when I reduce the amount of rows for example to 10.000.

I am using the built-in postgres dialect.

Thanks for your help!

[koskimas]

Your query exceeds the maximum number of parameters Postgres supports.

You need to insert in chunks. There's nothing we can do here. Kysely will always run a single query per execute call.