Duplicate Key Error when creating a new record after seeding Data in Postgres

[shakeerah]

Whenever I tried to create a new record after running seed, I get a duplicate record.
This happens when I ran fresh migrations and seed some data.
So I have two existing seeded records when I tried to create the new record; I get the duplicate error.
However when I tried registering the third time the record got created and there was no error.

 error:  insert into "users" ("created_at", "email", "first_name", "last_name", "mobile_number", "password", "updated_at", "user_role_id") values ($1, $2, $3, $4, $5, $6, $7, $8) 
 returning "id" - duplicate key value violates unique constraint "users_pkey"
    at Parser.parseErrorMessage (~projectsC/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (~projectsC/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (~projectsC/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (~projectsC/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:390:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
  length: 180,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key (id)=(1) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'users',
  column: undefined,
  dataType: undefined,
  constraint: 'users_pkey',
  file: 'nbtinsert.c',
  line: '670',
  routine: '_bt_check_unique'
}

Migration and Seed works perfectly

UserSeeder.ts

import User from 'App/Models/User'
import BaseSeeder from '@ioc:Adonis/Lucid/Seeder'
import Database from '@ioc:Adonis/Lucid/Database'

export default class UserSeeder extends BaseSeeder {
  public async run() {
    const usersData = [
      {
        id: 1,
        first_name: 'Super',
        last_name: 'Admin',
        email: '[email protected]',
        mobile_number: '01234567890',
        password: superAdminPassword,
        user_role_id: 1,
        is_active: true,
        is_deleted: false,
      },
      {
        id: 2,
        first_name: 'Customer',
        last_name: 'User',
        email: '[email protected]',
        mobile_number: '08098273638',
        password: otherUserPassword,
        user_role_id: 3,
        is_active: true,
        is_deleted: false,
      },
    ]

    Database.raw('SET FOREIGN_KEY_CHECKS = 0;')
    await User.truncate(true)
    await User.createMany(usersData)

    Database.raw('SET FOREIGN_KEY_CHECKS = 1;')
  }
}

Method to create User

  const user = new User()
   user.firstName = firstName
   user.lastName = lastName
   user.email = email
   user.mobileNumber = mobileNumber
   
   return user.save()

I dont know if this is a bug or if I am doing. something wrong

[DeVoresyah]

I got the same issue like this. But in my case, I used .create() method instead using .save()

[tomgobich]

With increments('id') columns the database sequence tracks what the next insertable id value is. The issue here is that when you manually define an id value, like you are in your seeder, the sequence doesn't get updated to take that manual id into consideration.

So, when you go to insert another record after your seeder, the sequence is still trying to insert a record with the id of 1, since it hasn't been updated, as noted in this line of the error detail: 'Key (id)=(1) already exists.'

If you omit the manually defined id on your seeder usersData the database will handle setting the id for you using & updating the sequence. Give your database a refresh and everything should start working as expected.

const usersData = [
      {
        // id: 1, 👈 let the database set the id
        first_name: 'Super',
        last_name: 'Admin',
        email: '[email protected]',
        mobile_number: '01234567890',
        password: superAdminPassword,
        user_role_id: 1,
        is_active: true,
        is_deleted: false,
      },
      {
        // id: 2, 👈 let the database set the id
        first_name: 'Customer',
        last_name: 'User',
        email: '[email protected]',
        mobile_number: '08098273638',
        password: otherUserPassword,
        user_role_id: 3,
        is_active: true,
        is_deleted: false,
      },
]```