CODEWITHSUNDEEP
ruby-on-railsrubydatabasepostgresql
SL_User
SL_User

Reputation: 1954

How to restart id counting on a table in PostgreSQL after deleting some previous data?

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?

Upvotes: 10

Views: 10892

Answers (7)

user3402754
user3402754

Reputation: 3085

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;

Upvotes: 1

Nuno Silva
Nuno Silva

Reputation: 758

Try: 

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

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

Upvotes: 5

Rahul2692
Rahul2692

Reputation: 344

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.

Upvotes: 3

Rubyrider
Rubyrider

Reputation: 3587

You could do it in the following way:

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

Upvotes: 11

Ketema
Ketema

Reputation: 6548

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

Example:

TRUNCATE TABLE foo RESTART IDENTITY;

TRUNCATE DOCUMENTATION

Upvotes: 14

teambob
teambob

Reputation: 2084

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.

Upvotes: 5

vol7ron
vol7ron

Reputation: 42109

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

Upvotes: 3

Related Questions