Question 90815 in PostgreSQL Question posted in
The official documentation can be found here.

Prisma problem inserting into autogenerated int ID field: integer out of range for type int4 – Postgresql

model Comment {
  id        Int             @id @default(autoincrement())
  cid       String      
  bid       String
  author    AppProfile      @relation(fields: [bid], references: [bid])
  comment   String
  created   DateTime        @default(now())
}

const results = await prisma.comment.create({
  data: {
    cid: req.body.cid,
    comment: req.body.comment,
    bid: appProfile.bid,
  },
});

prisma:query INSERT INTO "public"."Comment" ("cid","bid","comment","created") VALUES ($1,$2,$3,$4) RETURNING "public"."Comment"."id"
prisma:query ROLLBACK
error - PrismaClientUnknownRequestError:
Invalid `prisma.comment.create()` invocation:


Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E22003), message: "integer out of range for type int4", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("eval.go"), line: Some(65), routine: Some("init") }) }) })
    at RequestHandler.handleRequestError (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34314:13)
    at RequestHandler.request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34293:12)
    at async PrismaClient._request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:35273:16)
    at async comment (webpack-internal:///(api)/./pages/api/comment/index.ts:26:25) {
  clientVersion: '4.6.1',
  page: '/api/comment'
}

This is strange because the id column is an Int and should be default autoincrementing. The error message complains about int4. If I look in the database the only int4 column is the id field.

Tags:

2

Answers


  1. 0

    It seems your users are too active, if there is more than 2.147.483.647 comment ids, the Int id has reach it max value

    You can convert it to BigInt like:

    id BigInt @id @default(autoincrement())

    But the best is maybe to use uuid like:

    id String @id @unique @default(uuid())

    Int max =   2.147.483.647
BigInt max = 9.223.372.036.854.775.807
UUID max = 3.4*10^38 or 340000000000000000000000000000000000000 (thanks to Belayer)
    Login or Signup to reply.
  2. 0

    First, make sure your provider in schema.prisma is set to "cockroachdb" if you’re using CockroachDB.

    For reference, CockroachDB only supports autoincrement on BigInt, while for Int’s, CockroachDB uses sequence()

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search