Reset auto-increment column to 0 daily

Question

Is there a way in postgresql to automatically increase the number of reset columns to return to zero at a specified time every day?

Answer 1

It can be pretty trivial with cronjob

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

Alternatively, you can set your "sequential" DEFAULT column to invoke a stored procedure that checks the day's rollover, reset the sequence if necessary, and then returns the result of nextval ().

But other than that, no, I would not expect that there will be a magical ALTER SEQUENCE my_seq reset AT INERVAL '1 day' or something like that.

Edit: added duckyfuzz comment.

Answer 2

Basically you can reset the sequence with this:

 ALTER SEQUENCE your_sequence_name RESTART WITH 1; 

Enjoy ...