I want to create a partition table month wise only 12 tables for multiple years

Question

I am new to the partitioning of a table and I want to make a partition of the table by range type on the inserted_on column in this table the records are inserted around ~ 40000 daily

I have tried creating a partition table as: CREATE TABLE My.table_name_fy2022_01 PARTITION OF My.table_name FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');

But this way i will have to create 12 tables per year and that I don't want to do.

My question is:- how to create a partition table such as the no. of partition table be only 12 (months wise) and stores the data according to a specific month's partition. For Example:- partition table June record of 2022-06-20 insert into June, record of 2023-06-16 insert into June, record of 2024-06-10 insert into June, and So on

Please show sample input and expected outcome. – Jonas Metzler May 27 '22 at 06:36 — Jonas Metzler, May 27 '22 at 06:36
Upgrade to PostgreSQL v14 if you want to use partitioning. – Laurenz Albe May 27 '22 at 06:46 — Laurenz Albe, May 27 '22 at 06:46
What happens when you used the examples from the manual? – Frank Heikens May 27 '22 at 07:00 — Frank Heikens, May 27 '22 at 07:00

score 0 · Answer 1 · answered Feb 16 '23 at 21:43

0

PARTITION BY HASH should be used like:

PARTITION BY HASH(MONTH(use_time)) PARTITIONS 12;

answered Feb 16 '23 at 21:43

J.J. Beam

2,612
2
26
55

I want to create a partition table month wise only 12 tables for multiple years

1 Answers1