"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> As a result, I have decided to partition the table and add to each record a partition id, which can be used to route
itto the correct partition.
>
> Presently, all of the records reside on what will ultimately become the parent partition.
>
> What would be the best way of moving the data to the pertinent
> partitions?
What I use to do is to rename the current table partition_201001, say,
with a CHECK constraint date < 2010-01-01. In case of date based ranges
of course.
Then create the new parent table, empty, and set up the inheritance and
trigger. Then create some more future child tables, and commit.
New data is being routed, old data all packed. You can reshuffle the
archive like table later on, if needed.
Also, as noted down-thread, avoid rules, prefer triggers. One of the
reasons is locking behavior, where drop partition when using rules will
lock against running queries against parent table.
Regards,
--
dim
