Database Seeding causes "duplicate key" error

[kohenkatz]

I am using Laravel 4.2, with a Postgres 9.3 database. I have a model Category created with the following migration:

public function up()
{
    Schema::create('categories', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name', 400);
        $table->text('alternate_toc')->nullable();
        $table->text('banner')->nullable();
        $table->integer('parent_id')->unsigned();
        $table->integer('display_order');
        $table->string('slug', 100);
        $table->boolean('is_active');

        $table->timestamps();
        $table->softDeletes();
    });
}

I also have a seed file that inserts 280 categories into the database. After I run the seed command, I am unable to insert more categories. Instead, I get an error like this:

PGError: ERROR: duplicate key violates unique constraint "categories_pkey" ...

Examining the database, I see that the insertions done by the seeder have not incremented the categories_id_seq.

If I run select setval('categories_id_seq', (select max(id) + 1 from categories));, this fixes the problem.

I know that I can add execution of this SQL query to my seed file, but I would have thought that fixing the series should be built into the seeding process.

Thoughts?

[franzliedke]

Are you using Eloquent or fluent queries for the insert? Please show us the
code.
Am 30.06.2014 01:52 schrieb "MK" notifications@github.com:

I am using Laravel 4.2, with a Postgres 9.3 database. I have a model
Category created with the following migration:

public function up()
{
Schema::create('categories', function(Blueprint $table)
{
$table->increments('id');
$table->string('name', 400);
$table->text('alternate_toc')->nullable();
$table->text('banner')->nullable();
$table->integer('parent_id')->unsigned();
$table->integer('display_order');
$table->string('slug', 100);
$table->boolean('is_active');

    $table->timestamps();
    $table->softDeletes();
});

}

I also have a seed file that inserts 280 categories into the database.
After I run the seed command, I am unable to insert more categories.
Instead, I get an error like this:

PGError: ERROR: duplicate key violates unique constraint "categories_pkey" ...

Examining the database, I see that the insertions done by the seeder have
not incremented the categories_id_seq.

If I run select setval('categories_id_seq', (select max(id) + 1 from
categories));, this fixes the problem.

I know that I can add execution of this SQL query to my seed file, but I
would have thought that fixing the series should be built into the seeding
process.

Thoughts?

—
Reply to this email directly or view it on GitHub
#4897.

[kohenkatz]

The full code is long, so I put it as a separate Gist. For completeness, I included the migration and the Model.

The Seed code is using \DB::table('categories')->insert..., and my later insertion uses Eloquent. (The insertion sample in the Gist is copied from doing it by hand in php artisan tinker that I had done to figure out why the one in the controller did not work.)

I would guess that if my Seed did not include the id column that I would not have this problem, but I would have thought that specifying id should automatically move the sequence number past the id that was inserted. (If I remember correctly, Microsoft SQL Server does it that way automatically so you have to start seeding with a command to disable auto-incrementing and then end with a command that re-enables it.) I assumed that this is the kind of thing a seeding framework should handle.

[franzliedke]

Try ordering the inserts by ID, that might be the problem. I don't think the sequence is automatically set to a proper value after inserts. Not specifying the IDs should work better, though.

[Gufran]

truncate before seeding the table will sure clear out the table but the serial of column id is not set to 1 again. So basically what you are doing is, you are inserting the records for first time and incrementing the serial to, say, 50. then on second run, you are deleting the records leaving sequence of id i.e. categories_id_seq to 50, and then you start seeding from id 1 which results in violation of primary key. This is the default behaviour in PostgreSQL, laravel's seeder can't do anything about it, so instead of truncating the tables you should refresh the migrations before seeding.
If you dont specify the ID's in seed then Postgre will choose the next value available to serial.

[kohenkatz]

@Gufran I am specifying the IDs on insert (because they need to match an existing system).

[Gufran]

Yes, I saw the gist. Your best bet would be to refresh the migrations before seeding (artisan migrate:refresh --seed). or just embed the query to reset the serial in your seed file.
All in all, this is not a bug in laravel's seeder or migrator class (or in any other class for that matter).

[lukejoyce]

I've just had the same problem when seeding after switching from MySQL to Postgres.

It's only an issue when specifying the primary key values which I needed to do to preserve table relations. When I was using MySQL the sequence was updated automatically but not with Postgres.

As @kohenkatz says, manually updating the sequence solves the problem but I can't help but feel it should be handled automatically.

[beaulm]

This is still a problem. artisan migrate:refresh --seed does not help. I wound up having to throw code like this into my seeder:

    if(DB::connection()->getName() == 'pgsql')
    {
        $tablesToCheck = array('table1', 'table2');
        foreach($tablesToCheck as $tableToCheck)
        {
            $this->command->info('Checking the next id sequence for '.$tableToCheck);
            $highestId = DB::table($tableToCheck)->select(DB::raw('MAX(id)'))->first();
            $nextId = DB::table($tableToCheck)->select(DB::raw('nextval(\''.$tableToCheck.'_id_seq\')'))->first();
            if($nextId->nextval < $highestId->max)
            {
                DB::select('SELECT setval(\''.$tableToCheck.'_id_seq\', '.$highestId->max.')');
                $highestId = DB::table($tableToCheck)->select(DB::raw('MAX(id)'))->first();
                $nextId = DB::table($tableToCheck)->select(DB::raw('nextval(\''.$tableToCheck.'_id_seq\')'))->first();
                if($nextId->nextval > $highestId->max)
                {
                    $this->command->info($tableToCheck.' autoincrement corrected');
                }
                else
                {
                    $this->command->info('Arff! The nextval sequence is still all screwed up on '.$tableToCheck);
                }
            }
        }
    }

[oncet]

Just had same problem in Laravel 5.1 and PostgreSQL 9.4. Solved it manually using a raw query:

$pdo = DB::getPdo();
$pdo->beginTransaction();
$statement = $pdo->prepare("SELECT setval('id', ?, false);");
$statement->execute([$next]);
$pdo->commit();

[ghost]

Still same error in 5.6 with psql (PostgreSQL) 10.5 (Debian 10.5-1.pgdg90+1) Auto increment value not setting after seeding with php artisan migrate:fresh --seed.

[dhirendrasingh-kiwi]

$pdo = DB::getPdo();
$pdo->beginTransaction();
$statement = $pdo->prepare("SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)+1)");
$statement->execute();
$pdo->commit();