9

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 ?

5
  • 1
    Maybe your partition for parsel_2014_10 should run from 2014-10-01 to 2014-10-31, instead of to 2014-10-30.
    – RDFozz
    Jan 24, 2018 at 16:46
  • I've checked the dates and tried "2014-10-31" too.
    – Capan
    Jan 24, 2018 at 16:47
  • @RDFozz no, that wouldn't work either. The TO bounds are exclusive. Patrick has answered. Jan 24, 2018 at 17:35
  • 1
    @ypercubeᵀᴹ - 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.
    – RDFozz
    Jan 24, 2018 at 18:06
  • @RDFozz right, one more advantage of inclusive-exclusive ranges. No need to calculate how many days each month has! Jan 24, 2018 at 18:12

1 Answer 1

17

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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