Re: Convert Existing Table to a Partition Table in PG10

On Sun, Jul 1, 2018 at 12:15 AM, Clifford Snow <clifford(at)snowandsnow(dot)us> wrote:
.....
> I also leaned that my range partition value I used on a timestamp needed to
> have fractional seconds. I used a range of 2017-01-01 00:00:00 to
> 2017-23:59:59 which failed when I attempted to add a record that had a
> timestamp of 2017-23:59:59. Adding a fractional second to the range solved
> the problem.

Your problem probably comes from using closed intervals. Timestamps
are like real numbers, partitions on real numbers are best done using
half closed interval. You can conver the real line using non
overlapping half open intervals, but you cannot do it with open or
closed ones ( non ov. ).

Assuming you are yearly range partitions ( 2017-23:59:59 should be
2017-12-31 23:59:59 ), to use closed interval you have to rely on
"real" second numbers being stored in the computer with a finite
precision ( so you can, say, add up to the microseconds, and pray it
does not change to picoseconds in a future release ). If you use half
open ( 2017-01-01 00:00:00 <= ts < 2018.01.01 00:00:00 ) the problem
is much easier. You can even drop the HMS ( 2017-01-01 <= ts <
2018-01-01 ) and it will work, even if the systems peeks a different
HMS value for each year, as you use the same value for an interval
start as for the previous end. And, if timestamp supported defaulting
the M and D like it does with HMS ( which it does not ) you could even
drop them.

And I think postgres does not use leap seconds, but If it did
'2016-12-31 23:59:60.9999999999999" does not need to be remembered in
the half-open style.

This is a general problem, not a postgres or timestamp related one.
Anything which has decimals is generally better partitioned with
half-open intervals. With integer-like things ( like dates, which are
countable ) it does not matter that much, I use half-open for easier
upgrading if I need to, but you can translate open-closed-half.

Francisco Olarte.