On 2017/06/23 13:42, amul sul wrote:
> On Fri, Jun 23, 2017 at 6:58 AM, Amit Langote wrote:
>> On 2017/06/22 20:48, amul sul wrote:
>>> This happened because of UNBOUNDED handling, where it is a negative infinite
>>> if it is in FROM clause. Wondering can't we explicitly treat this as
>>> a positive infinite value, can we?
>>
>> No, we cannot. What would be greater than (or equal to) +infinite?
>> Nothing. So, even if you will want p3 to accept (10, 9890148), it won't
>> because 9890148 is not >= +infinite. It will accept only the rows where
>> the first column is > 10 (second column is not checked in that case).
>>
>> You will have to define p3 as follows:
>>
>> CREATE TABLE p3 PARTITION OF tab1 FOR VALUES FROM (11, UNBOUNDED) TO (20, 10);
>>
> What if the partition key column is FLOAT ?
I would say use a value such that the btfloat4cmp (or btfloat8cmp) will
tell it to be greater than 10.
Of course, we can't write what I just said in the user-level
documentation, because the fact that we use system- or user-defined btree
comparison proc (btfloat4/8cmp) for partitioning may be irrelevant to the
users. Although, we do mention in the documentation that we use btree
operator class specified semantics for partitioning. In any case,
defining your partitioning or indexing on raw float type column(s) is
prone to semantic caveats, I'd think.
Also, there was interesting exchange on this topic during the patch
development [1]. Excerpt:
"Same for ranges of floating-point numbers, which are also probably an
unlikely candidate for a partitioning key anyway."
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/CA%2BTgmoaucSqQ%3DdJFhaojSpb1706MQYo1Tfn_3tWv6CVWhAOdrQ%40mail.gmail.com