PostgreSQL Unique violation: Duplicate primary key problem

Background

Recently I faced really strange problem in my Laravel project. I was migrating 2 old project to a new single project. As part of the migration , i had to migrate a lot of old data into the new database. There was one resource which had about 500,000 rows. So i backed up the table from old database using tableplus, and then restored in new database, which went really fine. So i finished writing code in to new project, all tests are passing, great. I was working with the locally seeded data while developing, and everything was ok. Resource list was working, i could make new resources. So i pushed them to production. In production , i logged in, i can see all my old data is there. I said to myself great job!! 😃. Then I tried create a new resource, boom, my server thrwoing error. I look in to my log to see what is happening, i get the following error

 "Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"

I tried to look in to sql query, seems like it is trying to insert a row in table , with id=1, which my database rejects since there is already a row with id 1. ( Remember there was 50000 rows in the table already from previous database, one of them quite understandably had id 1)

So, what was the problem?

In my migration , i set the id column as auto incrementing  primary key. So whenever a new row is entered in the table,  it will automatically take the id after the last inserted row id. Postgres handles this auto incrementing by creating a pseudo data type named “SERIAL” . This is keeping track of the id to use. 

When we insert a new record into the table, if id field is not specified, it will use the value of the sequence, and then increment the sequence. However, if the id field is not specified, then the sequence is not used, and it is not updated, either. So when i inserted data from previous database , every row had id specified, so postgres never updated the serial. So when i tried to insert a new row, it started from one, since it never updated the serial from 1.

The Solution

To solve this, we have to manually reset the serial in postgres. We can use the following sql query to reset the counter. My table name was `sales`

SELECT setval(pg_get_serial_sequence('sales', 'id'), coalesce(max(id)+1, 1), false) FROM sales;

If for some reason it is not possible to run sql queries directly in to the database, we can do it from laravel also, since we are already using Laravel.

// get the current sequence and increment it by 1
$seq = DB::table('sales)->max('id') + 1; 

// alter the sequence to now RESTART WITH the new sequence index from above        
DB::select('ALTER SEQUENCE ' . 'sales' . '_id_seq RESTART WITH ' . $seq); 

Alternatively if we have done the migration for many tables in our database , we can do this for all table in one go:

use DB;
// Get all the tables from your database
$tables = DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema = \'public\' ORDER BY table_name;'); 

//loop through the tables
foreach ($tables as $table) { 

   //Get the max id from that table and add 1 to it
   $seq = DB::table($table->table_name)->max('id') + 1; 

    // alter the sequence to now RESTART WITH the new sequence index from above        
   DB::select('ALTER SEQUENCE ' . $table->table_name . '_id_seq RESTART WITH ' . $seq); 

}