6

Is there a way in postgresql to have an auto-incrementing column reset back to zero at a specified time every day?

whatWhat
  • 3,987
  • 7
  • 37
  • 44
  • 4
    why would you want to do that? could you add more info please. Rather than state your perceived solution, tell us what the real problem is. – Mitch Wheat Jan 08 '10 at 03:12
  • Seeing as Postgres got ranking functions in 8.4, a better idea would be to rank based on a timestamp – OMG Ponies Jan 08 '10 at 04:51

2 Answers2

7

It could be pretty trivial with a cronjob 

0 0 * * * echo "SELECT setval('public.my_table_id_seq', 1, false)" | psql -U my_db_user -d my_db_name

Alternately, you could set your "serial" column DEFAULT to call a stored procedure, which would check for a day rollover, reset the sequence if appropriate, and then return the result of nextval().

But other than that, no, I wouldn't expect that there's a magic ALTER SEQUENCE my_seq RESET AT INERVAL '1 day' or anything like that.

Edit: incorporated duckyfuzz's comment.

Frank Farmer
  • 38,246
  • 12
  • 71
  • 89
  • 3
    When I tried this, I got a "setval: value 0 is out of bounds for sequence "searches_id_seq"" error. The solution is to use the three parameter version of seval() like this: setval('public. my_table_id_seq', 1, false). Read more here: http://www.postgresql.org/docs/current/static/functions-sequence.html – David Tuite May 10 '11 at 12:18
  • If uniqueness is required, this is unreliable since it could theoretically reset just after a record has been inserted at the start of the day. – Zubin Jul 06 '22 at 23:27
7

Basicaly you can reset sequence with this one:

ALTER SEQUENCE your_sequence_name RESTART WITH 1;

Enjoy...

user496261
  • 71
  • 1
  • 1