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
