postgresqlprimary-key-design

Restarting Primary Key

Here is my situation:

I have a table that gets truncated once a week and new values are placed in it.

What I want to do:

I want to add a primary key that starts at 1 and increases by 1 for each row in the table that gets inserted. When the table gets truncated, I want this count to start back at 1.

Is this possible?

Solution

  • Use a serial column and use the option restart identity 

    truncate table foo restart identity

    http://www.postgresql.org/docs/current/static/sql-truncate.html