Re: Partitioning by month causing an error?

On 12/20/19 10:56 PM, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)com> writes:
>> On 12/20/19 10:10 PM, Tom Lane wrote:
>>> This rule breaks fewer things than a rule of A <= X <= B would.
>> Why not?  For 20+ years (not on Postgres) I've been specifying partition
>> limits where the upper limits are the edge of timestamps (octaword integers
>> counting the number of 100ns ticks since November 17, 1858), and have
>> *never* had a problem:
>> IN ETC_TRAN_DETAIL_201909_UA WITH LIMIT OF '2019-09-30 23:59:59.99',
>> IN ETC_TRAN_DETAIL_201910_UA WITH LIMIT OF '2019-10-31 23:59:59.99',
> And what happens with '2019-10-31 23:59:59.9999'?

The datatype only extends to centiseconds.  Naturally, if it extended to
tenths of milliseconds, then I'd have added another two nines to the range
limits.

> I don't know about
> your other database, but in Postgres that value is legal and it's larger
> than '2019-10-31 23:59:59.99', but still less than '2019-11-01 00:00:00'.
>
> The main point here is that even if that works, it requires a whole
> lot more assumptions about the behavior of the datatype (specifically,
> its precision limit) than the other way. As soon as you look at cases
> where the precision limit isn't clearly defined, it just breaks.

It works *perfectly* on *discreet* datatypes. Honestly... experience has
demonstrated it.

Not so much on floats, but I think that's daft anyway.

>> Of course, I've also never partitioned a table on a float, thinking that's
>> utterly daft.
> [ shrug... ] Works fine as long as you have a non-daft partitioning rule.
>
> regards, tom lane

--
Angular momentum makes the world go 'round.