When we import rows in postgres table or restore data, sometimes sequence ID for the table is not maintained. Primary key sequence does not get updated. In postgres, we can reset sequence to max ID with an SQL statement. Rails provides a method reset_pk_sequence on ActiveRecord to achieve this task.
How to check if primary key sequence is incorrect?
We can run a couple of queries given below to figure out of primary key sequence for particular table is out of order.
1. Find out max ID for the table
This will give us maximum value of
ID column from
This considers sequence ID for the column
2. Find out next value for ID column from sequence
This will fetch value for the next record
that will be inserted in
If the output of step 1 is not greater that output of step 2,
then we will have to reset primary key sequence for the
Postgres reset sequence to max ID
We can use
sql statement given below.
This will select max ID and add 1 to to get the number for
for new record that will get created.
It is set in sequence for
id column for the
Postgres Reset sequence to max ID in Rails
When above code is run from Rails console, it will output the max ID set for the sequence of primary key of the table under consideration.
Rails Reset primary key sequence of all tables
We can use a code block given below to reset primary key sequence to max value for all postgres tables.
Subscribe to Ruby in Rails
Get the latest posts delivered right to your inbox