Django + PostgreSQL: How to reset primary key?

Question

I have been working on an application in Django. To begin with, for simplicity, I had been using sqlite3 for the database.

However, once I moved to PostgreSQL, I've run into a bit of a problem: the primary key does not reset once I clear out a table.

This app is a game that is played over a long time period (weeks). As such, every time a new game starts, all of the data is cleared out of the database and then new, randomized data is added.

I'd like to be able to "start over" with primary keys starting at 1 each time I clean/rebuild the game.

The code still works as-is, but integers are a pretty natural way for describing the objects in my game. I'd like to have each new game start at 1 rather than wherever the last game left off.

How can I reset the primary key counter in PostgreSQL? Keep in mind that I don't need to preserve the data in the table since I am wiping it out anyway.

Answer 1

You need to truncate the table. See http://www.postgresql.org/docs/8.1/static/sql-truncate.html

Answer 2

In your app directory try this:

python manage.py help sqlsequencereset

Pipe it into psql like this to actually run the reset:

python manage.py sqlsequencereset myapp1 myapp2 | psql

Edit: here's an example of the output from this command on one of my tables:

BEGIN;
SELECT setval('"project_row_id_seq"', coalesce(max("id"), 1), max("id") IS NOT null) FROM "project_row";
COMMIT;

Answer 3

I view auto-increment primary keys as purely internal identifiers for database records, and I don't like exposing them to users. Granted, it's a common design to use them as part of URLs, but even there slugs or other identifiers feel more appropriate.