I have a table counter_registry that has a column priority_number with its default value from nextval('counter_registry_priority_number_seq'::regclass). My trigger function (which runs before inserting) has a snippet that resets the sequence when there is no existing transactions on the date inserted:
-- Restart the priority number sequence if current day is a new day
IF (SELECT CASE WHEN NOT EXISTS ( SELECT * FROM counter_registry WHERE transaction_date = now()::date ) THEN true ELSE false END) = true
THEN PERFORM setval('counter_registry_priority_number_seq', 1);
END IF;
When I insert a new record to the table on a new day from a client application, the priority_number column gets its value from the nextval of the sequence before the reset, even though it had already been reset. The subsequent inserts follows the newly reset sequence, starting at 1.
How do you properly reset the sequence, that after such, when I insert the first time, I would get the nextval of the altered sequence?
