sql - How to partition postgres table using intermediate table

Question

I am using postgres 10 db.

I am having Customers table consisting of following columns

custid (primary key),
name,
phonenumber,
email,
dateofbirth,
address,
city,
country,
status(boolean)
Join_Date(Date)

I have million of records in table. I want to partition table based on different months(Jan 2018 one partition, Feb 2018 one partition,..etc) by help of Join_Date and with help of Intermediate table.

I also want to write the automated script such that at the end of month the table have to get create another partition of last month

Answer 1

Try this method:

1. First of all, create an additional column in customer table as you want to logical partition.
2. Then update that columns using customer and intermediate table
3. After updating truncate your table

For each month you can run this script and this will give you logical partitioning.

update customer set partition_column=to_char(Join_Date, 'YYYY-MM')
join intermediate_table on intermediate_table.custid=customer.custid
and  intermediate_table.Join_Date=customer.Join_Date

truncate table intermediate_table