implement partitioning on PostgreSQL tables

Question

I'm implementing partition to PostgreSQL (version 12). I'll use the transaction_date (year) to create the partitions, one partition per year, trigger and function to save data on the right partition.

I'm applying the implementation Using Inheritance and how can I load the partitions for 2009, 2010, 2011, ...?

All the examples I found starts with an empty table.

Thanks

user330315 · Accepted Answer · 2020-08-26 15:50:56Z

1

I'm applying the implementation Using Inheritance

Don't do that.

With Postgres 12, you should use declarative partitioning which also doesn't require the use of triggers.

create table your_table
(
  ..., 
  transaction_date timestamp not null
)
partition by range (transaction_date);

create table your_table_p2018
  partition of your_table
  for values from ('2018-01-01') to ('2019-01-01');

create table your_table_p2019
  partition of your_table
  for values from ('2019-01-01') to ('2020-01-01');

create table your_table_p2020
  partition of your_table
  for values from ('2020-01-01') to ('2021-01-01');

An insert into your_table will automatically be routed into the appropriate partition.

answered Aug 26, 2020 at 15:50

user330315

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

2 Comments

Julian Leite Over a year ago

Thanks a_horse_with_no_name, this implementation I know, my point here is how to implement partition starting from an existing and populated table, how to move the data o the right partition?

user330315 Over a year ago

You can't turn an existing table into a partitioned table. Except if the existing table would be exactly one partition of the new partitioned table

Collectives™ on Stack Overflow

implement partitioning on PostgreSQL tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related