3

I was having terrible table issues because I had created my own user class than switched to the default Django class. After innumerable headaches, we decided to just do a clean python manage.py reset the syncdb. Created a new user, but the primary key counter for User.id began where the previous one left off, meaning that when creating other tables that use User.id as the primary key, they are always 14 off.

How can I either rewipe fully so that they begin at 1, or alter the counter so it is now at 2 (since I created one user)? If it involves SQL, please also let me know where to put it, as my app is hosted on Heroku.

Thanks!

Chris
  • 11,819
  • 19
  • 91
  • 145

3 Answers3

7

Postgres by default creates a new sequence for all columns created of the type serial. The default name of the django auth user is auth_user, so the default sequence name will be auth_user_id_seq or something like that.

You can find what the next sequence value is, by

SELECT nextval('auth_user_id_seq');

and you can set it to 1 by the following:

SELECT setval('auth_user_id_seq', 1);
lprsd
  • 84,407
  • 47
  • 135
  • 168
1

the easiest way to fix this problem (without dropping the entire database) is to connect to the database using either psql or a sql client (like this http://www.sqlmanager.net/en/products/postgresql/manager, there is a free light version). Then issue the following command:

truncate <user_table> reset identity

then you can repopulate the database

actually, you can actually also do this by using raw queries from django shell: https://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly

Dmitry B.
  • 9,107
  • 3
  • 43
  • 64
  • does this run in the command line, it just says windows so I'm not sure how I can get this onto heroku – Chris Nov 17 '11 at 05:33
0

To reset everything, drop the database, the recreate it and run syncdb to re-create the tables.

To only reset the increment counter, this question should be helpful: How to reset postgres' primary key sequence when it falls out of sync?

The part you say about "when creating other tables that use User.id as the primary key, they are always 14 off." is worrisome. You're not relying on the auto-incremented primary keys on each table for a relation, are you?

Community
  • 1
  • 1
JAL
  • 21,295
  • 1
  • 48
  • 66
  • The default Django user model auto increments its id and I am passing in the User as an argument as it says to in the docs – Chris Nov 17 '11 at 05:33
  • @Chris What I mean is that I don't see why it would matter if the primary keys are each are 'out of sync'... one tables auto-incrementing IDs are not expected to match another table's primary keys. Maybe I'm misunderstanding why you say the table starting at 14 is a problem. – JAL Nov 17 '11 at 06:14