Thread: sequence on daily log partitioned table

Hi All,

I'm mainly interested in understanding how this works or why it can't, as opposed to just solving the problem. AI just told me this can't be done without a trigger but I'd like to confirm in case maybe I just asked the wrong question.

I want to have a partitioned log table receiving input from sensor equipment. The partitions would cover some time range like an hour or day. In all cases I want an ID column to default to a nextval from a sequence but starting over for each day. If I set the default on the partitioned table, I would need to alter it at midnight - not optimal. A default set on the partition hasn't worked and I'm not sure why this is a problem. 

I've only ever used simplistic partitioning but this doesn't seem like an unusual need. I'm looking for the underlying strategy that prevents use of the sequence configured on the partition. A reference to something describing the decision flow would be fantastic.

I've been experimenting on version 13 out of convenience but if a newer version has relevant changes, I'm interested. I'm not interested in the discussions over whether the data volume is enough to justify partitioning. 

Thanks in advance,

Senor