CODEWITHSUNDEEP
partitioningpostgresql-12
Julian Leite
Julian Leite

Reputation: 112

implement partitioning on PostgreSQL tables

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

Upvotes: 0

Views: 492

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions