Postgres table partitioning "no partition of relation "parsel_part" found for row" error?

Question

I'm trying to use new partitioning method of PostgreSQL 10. I have a parent table that contains 1.5 million rows. I want to create partitioning on this already populated table.

I've created a new master table which has identical columns with real master table from the CREATE SCRIPT;

CREATE TABLE master_part (objectid integer,poly geometry(Geometry,2321), parcel character varying(255), m_date(date)) PARTITION BY RANGE (m_date);

Then I've created child tables, partitioned by the m_date column;

CREATE TABLE parsel_2014_04
 PARTITION OF parsel_part FOR VALUES FROM ('2014-04-01') TO ('2014-04-30');
CREATE TABLE parsel_2014_05
 PARTITION OF parsel_part FOR VALUES FROM ('2014-05-01') TO ('2014-05-31');
CREATE TABLE parsel_2014_06
 PARTITION OF parsel_part FOR VALUES FROM ('2014-06-01') TO ('2014-06-30');
CREATE TABLE parsel_2014_07
 PARTITION OF parsel_part FOR VALUES FROM ('2014-07-01') TO ('2014-07-31');
CREATE TABLE parsel_2014_08
 PARTITION OF parsel_part FOR VALUES FROM ('2014-08-01') TO ('2014-08-31');
CREATE TABLE parsel_2014_09
 PARTITION OF parsel_part FOR VALUES FROM ('2014-09-01') TO ('2014-09-30');
CREATE TABLE parsel_2014_10
 PARTITION OF parsel_part FOR VALUES FROM ('2014-10-01') TO ('2014-10-30');
CREATE TABLE parsel_2014_11
 PARTITION OF parsel_part FOR VALUES FROM ('2014-11-01') TO ('2014-11-30');
CREATE TABLE parsel_2014_12
 PARTITION OF parsel_part FOR VALUES FROM ('2014-12-01') TO ('2014-12-31');
CREATE TABLE parsel_2015_01
 PARTITION OF parsel_part FOR VALUES FROM ('2015-01-01') TO ('2015-01-31');
CREATE TABLE parsel_2015_02
 PARTITION OF parsel_part FOR VALUES FROM ('2015-02-01') TO ('2015-02-28');
CREATE TABLE parsel_2015_03
 PARTITION OF parsel_part FOR VALUES FROM ('2015-03-01') TO ('2015-03-31');
CREATE TABLE parsel_2015_04
 PARTITION OF parsel_part FOR VALUES FROM ('2015-04-01') TO ('2015-04-30');

When I run the script above to create child tables I'm getting the error below;

ERROR: no partition of relation "parsel_part" found for row DETAIL: Partition key of the failing row contains (m_date) = (2014年10月31日). SQL state: 23514

This error seems to be very rare because I couldn't find anything about it.

Maybe some one have seen it before ?

• RDFozz: Maybe your partition for parsel_2014_10 should run from 2014年10月01日 to 2014年10月31日, instead of to 2014年10月30日.
• Capan: I've checked the dates and tried "2014年10月31日" too.
• ypercubeᵀᴹ: @RDFozz no, that wouldn't work either. The TO bounds are exclusive. Patrick has answered.
• RDFozz: @ypercubeTM - Mostly just pointing out that the last day of October is the 31st, not the 30th. As all the TO dates should presumably be the first of the next month, this does indeed become an issue one can ignore, in this case.
• ypercubeᵀᴹ: @RDFozz right, one more advantage of inclusive-exclusive ranges. No need to calculate how many days each month has!

Answer 1

Your problem is related to this point in the documentation:

When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

(emphasis added)

So in fact in

CREATE TABLE parsel_2014_10 PARTITION OF parsel_part 
 FOR VALUES FROM ('2014-10-01') TO ('2014-10-31');

the date 2014年10月31日 is not included in this partition, and hence nowhere in all your partition tables (and same for all ending dates)

See the example at: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

The to value of one partition must be the same as the from value of the next one (because the to part is exclusive and the from is inclusive).