"Benjamin Krajmalnik" <kraj@xxxxxxxxxxx> 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 it to 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
