I was reading this article: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1

the article lifts information directly from here: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

My question is - if we declaratively create a partitioned table like so:

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

and then let's say we insert into measurement, but with a peaktemp that is not covered by an existing partition.

Before we insert, do we need to manually create our own partition like so:

CREATE TABLE measurement_55 PARTITION OF measurement FOR VALUE 55;

..or will Postgres create that table for us if we insert into measurement with peaktemp as 55?

Sorry the syntax might be wrong but hopefully the question makes sense. Basically I want to partition a table by an integer column, one partition for each value of the integer. And I am wondering what is automated and what remains manual in this case?

  • It's not automated , at least in Postgres 10 and 11 for range or list partitions . If corresponding partition doesn't exist, insert throws an error. – a1ex07 Oct 24 '18 at 22:30
  • @a1ex07 thanks is there a good spot in the docs to look for creating a partition on an integer field? – Alexander Mills Oct 24 '18 at 23:04
  • 1
    Yeah , added as an answer - quote is too long for comment. – a1ex07 Oct 24 '18 at 23:16

It should be done manually. From the doc page (already mentioned in the question) - https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) -
under Example To use declarative partitioning in this case, use the following steps: ....

  1. Create partitions. Each partition's definition must specify the bounds that correspond to the partitioning method and partition key of the parent. Note that specifying bounds such that the new partition's values will overlap with those in one or more existing partitions will cause an error. Inserting data into the parent table that does not map to one of the existing partitions will cause an error; an appropriate partition must be added manually."
  • does you likes the OP? – Alexander Mills Oct 25 '18 at 0:19
  • any idea why the insert has to be done manually? why can't PG just figure out which table to insert into etc? – Alexander Mills Jan 4 at 0:03
  • 1
    @AlexanderMills . It's not insert that has to be done manually. The corresponding partition must exist before inserting record. This part (creating new partition if not exists) requires manual step, not insert itself. – a1ex07 Jan 4 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.