Hi Amit, > Thanks Jeevan, that's right.
> Another way to look at this: the partition key (100, 0) would be > insertable into r3, because the key satisfies its proposed exclusive > upper bound (< (100, 200)). The same key is also insertable into r1, > because it satisfies the latter's inclusive upper bound (>= (100, 0)). > That is, the key (100, 0) is insertable into both r1 and r3, so the > error that the proposed range of r3 would overlap r1's. Thanks for the explanation. > Well, you simply need to come up with bound values for r3 that don't > overlap with existing partitions' ranges; the following will work for > example: > > create table r3 partition of r for values from (0,100) to (100,0); Thanks for the clarification. Thanks & Regards, Nitin Jadhav On Wed, May 12, 2021 at 5:41 PM Amit Langote <amitlangot...@gmail.com> wrote: > Hi Nitin, > > On Sat, May 8, 2021 at 5:20 PM Nitin Jadhav > <nitinjadhavpostg...@gmail.com> wrote: > > Thanks Ashutosh and Jeevan for replying. > > > > > "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. That is, the values specified in the FROM > > > list are valid values of the corresponding partition key columns > > > for this partition, whereas those in the TO list are not. Note that > > > this statement must be understood according to the rules of row-wise > > > comparison (Section 9.24.5). For example, given PARTITION BY RANGE > > > (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any > y>=2, > > > x=2 with any non-null y, and x=3 with any y<4." > > > > Thanks for the detailed explanation. I understood more about how the > > partition constraints are prepared based on the RANGE bound values and > > how the tuple routing takes place based on that. > > > > > overlapping range is (100, 0), (100, 200) > > > > > Similarly, for the case-1 you mention above: > > > create table r1 partition of r for values from (100,0) to (200,100); > > > create table r3 partition of r for values from (0,100) to (100,200); > > > here, (100, 0) or r1 would overlap with (100, 200) of r3. > > Thanks Jeevan, that's right. > > Another way to look at this: the partition key (100, 0) would be > insertable into r3, because the key satisfies its proposed exclusive > upper bound (< (100, 200)). The same key is also insertable into r1, > because it satisfies the latter's inclusive upper bound (>= (100, 0)). > That is, the key (100, 0) is insertable into both r1 and r3, so the > error that the proposed range of r3 would overlap r1's. > > > postgres@68941=#\d+ r1 > > Table "public.r1" > > Column | Type | Collation | Nullable | Default | Storage | > Compression | Stats target | Description > > > --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- > > a | integer | | | | plain | > | | > > b | integer | | | | plain | > | | > > Partition of: r FOR VALUES FROM (100, 0) TO (200, 100) > > Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > > 100) OR ((a = 100) AND (b >= 0))) AND ((a < 200) OR ((a = 200) AND (b < > 100)))) > > Access method: heap > > > > I understand that partition 'r1' says, when column 'a' value is '100', > column 'b' > > values should be greater than '0'. Because of this constraint, creation > of > > partition 'r3' for values from (0,100) to (100,200) failed since the > condition > > when value of column 'a' is 100, column 'b' should be less than '200' > which > > overlaps with the constraints of 'r1'. So, based on the documentation, > the > > behaviour is correct. > > > > So in the above scenarios, users cannot create a partition for column > 'a' values > > from (0) to (100). If user tries insert any values for column 'a' > between '0' to '100', > > either it should go to default partition if exists. Otherwise it should > fail saying, no partition > > found. I feel there should be some way to create partitions in these > scenarios. > > Well, you simply need to come up with bound values for r3 that don't > overlap with existing partitions' ranges; the following will work for > example: > > create table r3 partition of r for values from (0,100) to (100,0); > > -- > Amit Langote > EDB: http://www.enterprisedb.com >
