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

i want to partition customer table with the help of intermediate(temp) table — Ratan Uday Kumar
– Ratan Uday Kumar, Commented Aug 9, 2018 at 11:25

Fahmi · Accepted Answer · 2018-08-09 11:43:20Z

0

Try this method:

First of all, create an additional column in customer table as you want to logical partition.
Then update that columns using customer and intermediate table
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

answered Aug 9, 2018 at 11:43

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ratan Uday Kumar Over a year ago

can you please eloborate your answer because i am very new to postgres

Fahmi Over a year ago

this applicable for all database not particularly postgres

Chocolord · Accepted Answer · 2018-08-09 12:32:50Z

0

There is an example for your problem in the Postgres documentation.

PostgreSQL: Documentation: 10: 5.10. Table Partitioning

answered Aug 9, 2018 at 12:32

Chocolord

4913 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to partition postgres table using intermediate table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related