Partitioning problem + question
I have been trying to partition a billion rows table according to time by CALENDAR_HIERARCHY_DAY algorithm.
when I run the statement:
ALTER TABLE public.spins
PARTITION BY time::DATE
GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;
I get the error:
ROLLBACK 2648: Column time in PARTITION BY expression is not allowed, since it is not present in some projections
HINT: Projection "spins_pk" does not contain column "time"
When I try to drop the projection (I don't really need it..) I get:
WARNING 7000: The projection 'spins_pk' was used to enforce the enabled key constraint 'pk', and may be regenerated to validate a DML statement on the base table
how can I workaround that issue?should I add the column time to the pk projection?
And a general partitioning question
If I want my partitions to be as follows: 24 hours - by hours, 7 days , 1 month, 3 month, 1 year..
what is the group by clause needed for the
GROUP BY CALENDAR_HIERARCHY algorithm.