Re: managing table partitions automatically - Mailing list pgsql-novice

From Obe, Regina
Subject Re: managing table partitions automatically
Date
Msg-id 53F9CF533E1AA14EA1F8C5C08ABC08D20505115A@ZDND.DND.boston.cob
Whole thread Raw
In response to Re: managing table partitions automatically  ("Ertel, Steve" <Steve.Ertel@infimatic.com>)
List pgsql-novice
Steve,
I usually use an SQL script that generates SQL to dynamically create named tables and other DDL like stuff - something like described in the 2 below articles.  In your case you'd probably want to use generate_series() to generate table names based on year and month.
 
 
 
(look at the below section of the above to see how to use generate_series to generate date parts)
 
Also have an example of dates and generate_series in the cheat sheet
 
 
Hope that helps,
Regina
 
 


From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ertel, Steve
Sent: Thursday, November 13, 2008 9:42 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] managing table partitions automatically

Still haven't heard from anyone.
 
I could really use your help.
 
Do you have any suggestions?
 
 
Thanks,
Steve


From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ertel, Steve
Sent: Wednesday, November 12, 2008 12:48 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] managing table partitions automatically

Hello All,
 
I am beginning to partition some database tables and need some help thinking things through.  After evaluating my data, it appears that the best partition would be based on year and month.  I was planning to create my tables in the format of tablename_yyyy_mm. 
 
I planned to create a parent table and child tables (similar to the example at http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html).  I would create the child tables for the range of my data and maybe even a few months in advance.  I have all of the insert, update, and delete rules worked out to manage the data.  The data will be routed to tables based on a date that is passed to the table (e.g. start_time).  The date can be in the future or in the past.
 
The problem is that the databases are running on remote servers and I will not have access to them.  I need to find a way to automatically create the child tables, indexes, and rules.  Any suggestions would be helpful.  I am stuck.
 
Thanks,
Steve
 


The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.


Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

pgsql-novice by date:

Previous
From: "Ertel, Steve"
Date:
Subject: Re: managing table partitions automatically
Next
From: Joshua Tolley
Date:
Subject: Re: managing table partitions automatically