Declarative partitioning with Postgres for integer column

Question

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?

• a1ex07: 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.
• Alexander Mills: @a1ex07 thanks is there a good spot in the docs to look for creating a partition on an integer field?
• a1ex07: Yeah , added as an answer - quote is too long for comment.

Answer 1

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

5.10.2.1 Example To use declarative partitioning in this case, use the following steps: ....

2. 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."

• Alexander Mills: does you likes the OP?
• Alexander Mills: any idea why the insert has to be done manually? why can't PG just figure out which table to insert into etc?
• a1ex07: @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.