Menu
Is there a way in postgresql to have an auto-incrementing column reset back to zero at a specified time every day?
632
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value.
Reset the auto increment fieldALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
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.
120
Basicaly you can reset sequence with this one:
ALTER SEQUENCE your_sequence_name RESTART WITH 1;
Enjoy...
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
