Thread: Delete from base table and not partitions

Question: I have a base table with several temporal partitions like this:
base_table
partition_1
partition_2

There are check rules on partitions, and trigger rules on the base
table to ensure data loaded into base_table goes to a correct
partition.

I added partition_3 and created the check rules on it, but forgot to
add trigger rules on the base_table. So for a while data inserted into
base_table and meant for partition_3 was actually inserted into
base_table.

Now I added the trigger rule to base_table and the data for
partition_3 is going to the right place, but I am stuck with some data
in the actual base_table. How could I move that data from the base
table to the partition_3?

---

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Nuwan Liyanage <alnuwan2@yahoo.com> writes:
>     Now I added the trigger rule to base_table and the data for
>     partition_3 is going to the right place, but I am stuck with some data
>     in the actual base_table. How could I move that data from the base
>     table to the partition_3?

Something involving SELECT ... FROM ONLY base_table and then DELETE FROM
ONLY base_table would do it for you.

            regards, tom lane

Thank you very much, I was not aware of the 'ONLY' clause.
Nuwan.

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nuwan Liyanage writes:
> Now I added the trigger rule to base_table and the data for
> partition_3 is going to the right place, but I am stuck with some data
> in the actual base_table. How could I move that data from the base
> table to the partition_3?

Something involving SELECT ... FROM ONLY base_table and then DELETE FROM
ONLY base_table would do it for you.

regards, tom lane

---

Looking for last minute shopping deals? Find them fast with Yahoo! Search.