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 ?
parsel_2014_10
should run from 2014-10-01 to 2014-10-31, instead of to 2014-10-30.TO
bounds are exclusive. Patrick has answered.TO
dates should presumably be the first of the next month, this does indeed become an issue one can ignore, in this case.