Drizzle Team•15mo ago

Pg: Inserting new data with auto increment column

I am getting error while inserting data to postgres from nextjs api. Error: detail: 'Key (id)=(21) already exists.', I have table with three columns and the API is submitting values as name and status. I am guessing postgres would take care of auto increment. what is the issue here ?

    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    status: varchar('status', { length: 100 }).notNull().default('DRAFT')

    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    status: varchar('status', { length: 100 }).notNull().default('DRAFT')

API

 const res = await db.insert(dashboards)
            .values({ ...json, status: 'DRAFT' }).returning({ id: dashboards.id })
        return NextResponse.json(res, { status: 200 })

 const res = await db.insert(dashboards)
            .values({ ...json, status: 'DRAFT' }).returning({ id: dashboards.id })
        return NextResponse.json(res, { status: 200 })

3 Replies

quitelistenerOP•15mo ago

Full error msg:

length: 196,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (id)=(22) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'dashboards',
  column: undefined,
  dataType: undefined,
  constraint: 'dashboards_pkey',
  file: 'nbtinsert.c',
  line: '668',
  routine: '_bt_check_unique'

length: 196,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (id)=(22) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'dashboards',
  column: undefined,
  dataType: undefined,
  constraint: 'dashboards_pkey',
  file: 'nbtinsert.c',
  line: '668',
  routine: '_bt_check_unique'

Angelelz•15mo ago

If the data you're passing to insert has an id field, it will be sent to the database That's why you're getting the error

quitelistenerOP•15mo ago

No, I did not include ID field in json data. This was another weird issue from poatgres Aurora. I have table with 25 records, manually uploaded using csv file. For some reason, database was considering 21 as latest available auto id. I tried 4 times to push the data from API and it failed. It finally reached 26 and API call was success. It is working fine now. I have no idea what caused auto increment to fail from db side.

Gaming

Programming

Pg: Inserting new data with auto increment column