automatic table partition when data insert

Question

I have table which is contained list partition in postgres database

create table test(id int, name character varying) partition by list(id);

when I insert data, it is throwing an error.

insert into test values(1,'test1'),(2,'test2')
ERROR: Partition key of the failing row contains (id) = (1).no partition of relation "test" found for row

Note: as now I cannot predefine what are the id's can insert

Based on the id's inserted on table, postgres tables needs to partitioned automatically

• klin: See Automating partitions creation in Postgres 10.
• Frank Heikens: id int tells me that the maximum number for the id will be 2147483647. How many records do you expect and how many partitions do you want? Pick an answer and create your partitions.

Answer 1

PostgreSQL will not create partition tables automatically for you, this is something you need to either create ahead of time, or create new partitions when the need arises.

Please refer to the step-by-step example: https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-EXAMPLE

• Amar: is the same requirement achievable using pg_cron jobs? if yes how it is
• Greg: It depends how you partition your data. If you partitioned it by some date field (say one partition per day), I would imagine you could have a cron that creates a new partition table for the next day. But this is a whole new queston – feel free to submit a new one.