Logo

Reset Auto Increment in PostgreSQL with Rails

Have you ever migrated a large dataset into a new database as part of a new build? Let's say you are replacing an Access Database app with a Rails app. And one of the models in the database works on the ids. We will call them orders.

Each order has an id and the id composes the order number.

But when you import your data and create a new order, the id is now 1. You went from 2387 to 1.

Now you have two problems.

1) You now have multiple ids

2) Your sequence is jacked

The fix is actually pretty simple. Reset the auto increment in your database. Basically, you are setting the last id in the auto increment procedure to the last id you imported.

We will use PostgreSQL for this example. I haven't looked at MySQL or any other databases.

Resetting the Auto Increment

Turns out resetting this id to work with your existing data is pretty simple.

From the Rails console.

ModelName.connection.execute('ALTER SEQUENCE model_name_id_seq RESTART WITH 1')

This takes whatever model you designate and resets the id to the integer at the end of the command.

Using our orders example with 1344 records, with the last record being 1344, we could fire up the Rails console and enter this.

Order.connection.execute('ALTER SEQUENCE model_name_id_seq RESTART WITH 1345')

Now the next time you create a new order, it will have an id of 1345 and maintain the correct auto increment from here on out.

Hopefully this saves you time in getting your new app working with old data.