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

SELECT MAX(id) FROM <table>;

This will give us maximum value of ID column from <table> This considers sequence ID for the column ID.

2. Find out next value for ID column from sequence

SELECT nextval('<table>_id_seq');

This will fetch value for the next record that will be inserted in <table>

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 <table>.

Postgres reset sequence to max ID

We can use sql statement given below.

SELECT setval('<table>_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);

This will select max ID and add 1 to to get the number for ID column for new record that will get created. It is set in sequence for id column for the <table>.

Postgres Reset sequence to max ID in Rails

Rails provides a method reset_pk_sequence on ActiveRecord to reset sequence to max ID for primary key.

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

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.

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end

References

• ActiveRecord
• reset_pk_sequence
• Postgres reset primary key sequence