Re: Convert Existing Table to a Partition Table in PG10

On Sat, Jun 23, 2018 at 6:42 PM Ravi Krishna <srkrishna(at)yahoo(dot)com> wrote:

> Does this help:
> http://ashutoshpg.blogspot.com/2018/06/upgrade-your-partitioning-from.html
>
> Ravi,
Thanks. I used Ashutosh Bapa's blop post as a guideline.

For others attempting this, I first created my parent and child structure.
Then inserted the data from the original table into the child tables. Once
I was happy with my new structure I drop the original table. (which is
still backed just in case)

I found a couple of areas I'd missed on reading about partitions. First, no
primary key. That was a big surprise. Second, my triggers I use to catch
information going into the table, needed to be pointed at the child tables.
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.

Clifford

--
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch