9

Before seeding test data into DB table I need to truncate the table (I need to reset primary key), I am trying to do that this way:

ActiveRecord::Base.connection.execute("TRUNCATE users")

but when I print out data from DB, I still don't see counting primary key from 1.

What am I doing wrong?

EDIT:

Also, I've tried manually run in terminal to PostgreSQL database

truncate users

But the primary count still doesn't start from 1.

SOLUTION:

In Postgres, run:

ALTER SEQUENCE users_id_seq RESTART WITH 1;
iblue
  • 29,609
  • 19
  • 89
  • 128
user984621
  • 46,344
  • 73
  • 224
  • 412
  • This isn't really a rails issue. You'll need to execute the correct SQL for your database to change the auto_increment value. You could drop the table and recreate it, but that may be overkill. See this question on stack overflow for more info: http://stackoverflow.com/questions/6241684/reset-primary-key-auto-increment – Scott Swezey Feb 26 '13 at 22:06
  • Thanks Scott, if you don't mind, I will post a solution that helped me. – user984621 Feb 26 '13 at 22:13

4 Answers4

7

In MySQL, TRUNCATE table; deletes all rows and resets the auto increment counter.

In PostgreSQL it does not do this automatically. You can use TRUNCATE TABLE table RESTART IDENTITY;.

Just for the record: In SQLite, there is no TRUNCATE statement, instead, it's

DELETE FROM table;
DELETE FROM sqlite_sequence WHERE name='table';
iblue
  • 29,609
  • 19
  • 89
  • 128
7

In case your db is postgres, you can do something like this to truncate the db tables of your models:

[
  MyModel1,
  MyModel2,
  MyModel3
].each do |m|
  ActiveRecord::Base.connection.execute("TRUNCATE TABLE #{m.table_name} RESTART IDENTITY;")
end
Marek Příhoda
  • 11,108
  • 3
  • 39
  • 53
5

This is too late I'm answering this question but I hope this will help someone else.

You've to install (OR you can add gem 'database_cleaner' to your Gemfile) a GEM called Database Cleaner which helps to clean your database without affecting your database schema._

To clean your database each time whenever you do rake db:seed then paste

DatabaseCleaner.clean_with(:truncation)

on the top of your seed file. It'll clear your database and start count from 1 again.


Disclaimer : This updated answer is tested, and working perfectly in my system.

Vishal Nagda
  • 1,165
  • 15
  • 20
0

From within Rails in a csv_upload.rb I used and it worked.

ActiveRecord::Base.connection.execute('TRUNCATE model_name RESTART IDENTITY')

Jay Killeen
  • 2,832
  • 6
  • 39
  • 66