0

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

Julian Leite
  • 114
  • 1
  • 2
  • 8

1 Answers1

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.

  • 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? – Julian Leite Aug 27 '20 at 13:15
  • 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 –  Aug 27 '20 at 13:17