Rails seed: How to truncate DB table?

Question

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;

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

score 7 · Answer 1 · answered Feb 26 '13 at 22:46

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';

score 7 · Answer 2 · answered Jul 31 '14 at 13:10

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

answered Jul 31 '14 at 13:10

Marek Příhoda

10,868
3
37
52

Vishal Nagda · Answer 3 · 2016-12-30T12:16:24.267

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.

Jay Killeen · Answer 4 · 2021-07-15T23:22:30.043

0

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

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

edited Jul 15 '21 at 23:22

answered Apr 23 '14 at 07:03

Jay Killeen

2,622
6
34
63

Rails seed: How to truncate DB table?

4 Answers4