Should I have to create one table for each partition for the partitioned table in PostgreSQL

0
votes

I have a partitioned table and another two tables for each partition

CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2016 PARTITION OF measurement 
FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');

CREATE TABLE measurement_y2017 PARTITION OF measurement
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

I would ask if this is the only way to work with partitioned table, create a table for each partition.

If my table has 100 partitions, then there will be 100 tables for this single partitioned table.

Is it possible to create partitioned table for Postgres like Mysql or Oracle, one table is good.

1
postgresql
This is just the same as in Oracle. In Oracle you would have to write alter table add partition ... a 100 times instead. – a_horse_with_no_name

1 Answers

0
votes

The alternative would be to create a trigger BEFORE INSERT that creates the appropriate partition on insert.

I wouldn't want to do that though, because

  1. it adds non-neglectible overhead for each insert

  2. I don't want to end up with a partition for the year 3000 just because of some data entry error