pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Delete 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