10

I'm using PostgreSQL database on Rails 2.3.8 and I need to restart auto increment ID on my table. How can I do that?

Chidi Ekuma
  • 2,811
  • 2
  • 19
  • 30
SL_User
  • 1,934
  • 5
  • 24
  • 45

7 Answers7

14

If you truncate the table you can use the RESTART IDENTITY clause on the end.

Example:

TRUNCATE TABLE foo RESTART IDENTITY;

TRUNCATE DOCUMENTATION

Jeff Mercado
  • 129,526
  • 32
  • 251
  • 272
Ketema
  • 6,108
  • 3
  • 21
  • 24
  • 1
    Please don't use URL shorteners. That prevents people from seeing where you link to. Jeff edited the link. [See here for more on the topic](http://meta.stackexchange.com/questions/99136/let-me-flag-that-for-you-url-shortener-cleanup/108295). – Erwin Brandstetter Oct 04 '11 at 02:14
  • Thanks I was not aware shortened links were a nono. Now I'm educated. – Ketema Oct 11 '11 at 01:19
11

You could do it in the following way:

ActiveRecord::Base.connection.execute("TRUNCATE TABLE your_table_name RESTART IDENTITY")

Rubyrider
  • 3,567
  • 1
  • 28
  • 34
5

Try: 

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

and check this answer for more details: https://stackoverflow.com/a/7814519/1392282

Nuno Silva
  • 728
  • 11
  • 27
5

You can do it directly in PostgreSQL using "alter sequence": http://www.postgresql.org/docs/current/static/sql-altersequence.html

Particularly "restart with"

I don't know how you would do it via the rails abstraction.

teambob
  • 1,994
  • 14
  • 19
3

If you want to delete all data from table and want to reset id as well then try below code.

ActiveRecord::Base.connection.execute("TRUNCATE TABLE your_table_name RESTART IDENTITY")

But if some relationship is present with table and you want to delete all data from table as well as related table data then try below code.

ActiveRecord::Base.connection.execute("TRUNCATE TABLE your_table_name RESTART IDENTITY CASCADE")

Thanks and please suggest if correction needed.

Rahul2692
  • 334
  • 1
  • 10
3

Check out setval

SELECT setval('foo', 42);           Next nextval will return 43
SELECT setval('foo', 42, true);     Same as above
SELECT setval('foo', 42, false);    Next nextval will return 42
vol7ron
  • 40,809
  • 21
  • 119
  • 172
1

You can use ActiveRecord in Rails.

You can easily do it from your rails console by running the following command:

Table_name.reset_primary_key

You can also use SQL :

TRUNCATE TABLE foo RESTART IDENTITY;
Chidi Ekuma
  • 2,811
  • 2
  • 19
  • 30
  • Examining the [documentation](https://apidock.com/rails/v6.0.0/ActiveRecord/AttributeMethods/PrimaryKey/ClassMethods/reset_primary_key), this only re-fetches or updates the primary key from the base class. It has nothing to do with the database. – RWDJ Mar 08 '20 at 02:20