pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete from base table and not partitions
5+ messages / 2 participants
[nested] [flat]
* Delete from base table and not partitions
@ 2008-03-03 16:21 Nuwan Liyanage <alnuwan2@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nuwan Liyanage @ 2008-03-03 16:21 UTC (permalink / raw)
To: pgsql-admin
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?
thanks.
---------------------------------
Never miss a thing. Make Yahoo your homepage.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Delete from base table and not partitions
@ 2008-03-03 16:35 Nuwan Liyanage <alnuwan2@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nuwan Liyanage @ 2008-03-03 16:35 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Delete from base table and not partitions
@ 2008-03-03 16:45 Nuwan Liyanage <alnuwan2@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nuwan Liyanage @ 2008-03-03 16:45 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete from base table and not partitions
@ 2008-03-03 17:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nuwan Liyanage <alnuwan2@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-03-03 17:10 UTC (permalink / raw)
To: Nuwan Liyanage <alnuwan2@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete from base table and not partitions
@ 2008-03-03 17:38 Nuwan Liyanage <alnuwan2@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Nuwan Liyanage @ 2008-03-03 17:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-03-03 17:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-03 16:21 Delete from base table and not partitions Nuwan Liyanage <alnuwan2@yahoo.com>
2008-03-03 16:35 Delete from base table and not partitions Nuwan Liyanage <alnuwan2@yahoo.com>
2008-03-03 16:45 Delete from base table and not partitions Nuwan Liyanage <alnuwan2@yahoo.com>
2008-03-03 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-03 17:38 ` Nuwan Liyanage <alnuwan2@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox